I am trying to insert data in to oracle database using Packaged stored procedure, But its giving the exception that wrong number or types of argument in call. Below is c# Code block
_unitOfWork.BeginTransaction();
var ID = new OracleParameter("i_ID", OracleDbType.Decimal, null, ParameterDirection.InputOutput);
var para = new OracleParameter[] {
ID,
new OracleParameter("i_PERSON_ID", OracleDbType.Decimal, iCPREC.PERSON_ID, ParameterDirection.Input),
new OracleParameter("i_VENDOR_NAME", OracleDbType.NVarchar2, iCPREC.VENDOR_NAME, ParameterDirection.Input),
new OracleParameter("i_BUSINESS_ID", OracleDbType.NVarchar2, iCPREC.BUSINESS_ID, ParameterDirection.Input),
new OracleParameter("i_MODIFIEDBY", OracleDbType.NVarchar2, iCPREC.MODIFIEDBY, ParameterDirection.Input),
new OracleParameter("i_VENDOR_PROVINCE", OracleDbType.NVarchar2, iCPREC.VENDOR_PROVINCE, ParameterDirection.Input),
new OracleParameter("i_GST_HST_NUMBER", OracleDbType.NVarchar2, iCPREC.GST_HST_NUMBER, ParameterDirection.Input),
new OracleParameter("i_VENDOR_SUITE", OracleDbType.NVarchar2, iCPREC.VENDOR_SUITE, ParameterDirection.Input),
new OracleParameter("i_PSF_SUPPLIER_ID", OracleDbType.NVarchar2, iCPREC.PSF_SUPPLIER_ID, ParameterDirection.Input),
new OracleParameter("i_TAX_EXEMPT", OracleDbType.NVarchar2, iCPREC.TAX_EXEMPT, ParameterDirection.Input),
new OracleParameter("i_DATEMODIFIED", OracleDbType.Date, iCPREC.DATEMODIFIED, ParameterDirection.Input),
new OracleParameter("i_VENDOR_STREET", OracleDbType.Varchar2, iCPREC.VENDOR_STREET, ParameterDirection.Input),
new OracleParameter("i_SK_PST_NUMBER", OracleDbType.Varchar2, iCPREC.SK_PST_NUMBER, ParameterDirection.Input),
new OracleParameter("i_QST_NUMBER", OracleDbType.Varchar2, iCPREC.QST_NUMBER, ParameterDirection.Input),
new OracleParameter("i_CREATEDBY", OracleDbType.Varchar2, iCPREC.CREATEDBY, ParameterDirection.Input),
new OracleParameter("i_VENDOR_POSTALCODE", OracleDbType.Varchar2, iCPREC.VENDOR_POSTALCODE, ParameterDirection.Input),
new OracleParameter("i_DATECREATED", OracleDbType.Date, iCPREC.DATECREATED, ParameterDirection.Input),
new OracleParameter("i_PERSON_EMAIL", OracleDbType.Varchar2, iCPREC.PERSON_EMAIL, ParameterDirection.Input),
new OracleParameter("i_VENDOR_CITY", OracleDbType.Varchar2, iCPREC.VENDOR_CITY, ParameterDirection.Input),
};
var result = await _unitOfWork.ExecuteSqlCommandAsync("TAPK_ICPREC_DATA.tapp_main_insert", para);
Below is Oracle package sql package script
create or replace PACKAGE TAPK_ICPREC_DATA IS PROCEDURE tapp_main_insert(
i_ID IN OUT number,
i_VENDOR_NAME IN varchar2,
i_BUSINESS_ID IN varchar2,
i_MODIFIEDBY IN varchar2,
i_VENDOR_PROVINCE IN varchar2,
i_GST_HST_NUMBER IN varchar2,
i_VENDOR_SUITE IN varchar2,
i_PSF_SUPPLIER_ID IN varchar2,
i_TAX_EXEMPT IN varchar2,
i_DATEMODIFIED IN date,
i_PERSON_ID IN number,
i_VENDOR_STREET IN varchar2,
i_SK_PST_NUMBER IN varchar2,
i_QST_NUMBER IN varchar2,
i_CREATEDBY IN varchar2,
i_VENDOR_POSTALCODE IN varchar2,
i_DATECREATED IN date,
i_PERSON_EMAIL IN varchar2,
i_VENDOR_CITY IN varchar2
);
END TAPK_ICPREC_DATA;
Below is Package Body Script
create or replace PACKAGE BODY TAPK_ICPREC_DATA AS
PROCEDURE tapp_main_insert
(
i_ID IN OUT number,
i_VENDOR_NAME IN varchar2,
i_BUSINESS_ID IN varchar2,
i_MODIFIEDBY IN varchar2,
i_VENDOR_PROVINCE IN varchar2,
i_GST_HST_NUMBER IN varchar2,
i_VENDOR_SUITE IN varchar2,
i_PSF_SUPPLIER_ID IN varchar2,
i_TAX_EXEMPT IN varchar2,
i_DATEMODIFIED IN date,
i_PERSON_ID IN number,
i_VENDOR_STREET IN varchar2,
i_SK_PST_NUMBER IN varchar2,
i_QST_NUMBER IN varchar2,
i_CREATEDBY IN varchar2,
i_VENDOR_POSTALCODE IN varchar2,
i_DATECREATED IN date,
i_PERSON_EMAIL IN varchar2 ,
i_VENDOR_CITY IN varchar2
) IS
-- --------------------------------------------------
v_Count NUMBER ;
v_timestamp VARCHAR2 (16) ;
v_id NUMBER ;
JustModified EXCEPTION ;
JustDeleted EXCEPTION ;
-- --------------------------------------------------
BEGIN
SELECT TAPT_ICPREC_SEQ.NEXTVAL INTO v_id FROM DUAL ;
INSERT INTO TAPT_IC_PREC_VENDOR
(
Id
,VENDOR_NAME
,BUSINESS_ID
,MODIFIEDBY
,VENDOR_PROVINCE
,GST_HST_NUMBER
,VENDOR_SUITE
,PSF_SUPPLIER_ID
,TAX_EXEMPT
,DATEMODIFIED
,PERSON_ID
,VENDOR_STREET
,SK_PST_NUMBER
,QST_NUMBER
,CREATEDBY
,VENDOR_POSTALCODE
,DATECREATED
,PERSON_EMAIL
,VENDOR_CITY)
VALUES
(v_id
,i_VENDOR_NAME
,i_BUSINESS_ID
,i_MODIFIEDBY
,i_VENDOR_PROVINCE
,i_GST_HST_NUMBER
,i_VENDOR_SUITE
,i_PSF_SUPPLIER_ID
,i_TAX_EXEMPT
,i_DATEMODIFIED
,i_PERSON_ID
,i_VENDOR_STREET
,i_SK_PST_NUMBER
,i_QST_NUMBER
,i_CREATEDBY
,i_VENDOR_POSTALCODE
,i_DATECREATED
,i_PERSON_EMAIL
,i_VENDOR_CITY) ;
i_ID := v_id ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20303, SQLERRM (SQLCODE)) ;
END ;
-- --------------------------------------------------
END TAPK_ICPREC_DATA;
Hope it helps to understand the issue.
Exception : ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TAPP_MAIN_INSERT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
I tried some articles from oracle community as below
It seems like Its due to wrong data type in call or spelling mistake , but I have crossed checked all the parameters.
CodePudding user response：
It looks like you are declaring the variables as nvarchar rather than varchar. That will cause this error for sure. The same thing might be true with decimal... you probably can't bind a decimal to a number, though I'm not 100% sure of that since decimal uses a number type under the covers. Make sure you use the exact matching datatypes in your Oracle client driver interface that corresponds with the datatypes of your procedure parameters.