oracle ORA-06550调用过程时参数的数量或类型错误,忽略语句

vcirk6k6  于 2023-02-07  发布在  Oracle
关注(0)|答案(1)|浏览(450)

我试图插入数据到oracle数据库使用打包存储过程,但它给出了调用中参数的错误数量或类型的异常。下面是c#代码块

_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);

以下是Oracle包sql包脚本

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;

以下是程序包主体脚本

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;

希望对理解这个问题有所帮助。
例外情况:ORA-06550:第1行,第7列:PLS-00306:调用'TAPP_MAIN_INSERT'时参数的数量或类型错误ORA-06550:第1行,第7列:PL/SQL:忽略语句
我尝试了一些文章从甲骨文社区如下
https://community.oracle.com/tech/developers/discussion/4502254/wrong-number-or-types-of-arguments-in-call-to-insert-row-how-i-can-resolve-this-error
这似乎是由于错误的数据类型在调用或拼写错误,但我已经交叉检查了所有的参数。

uoifb46i

uoifb46i1#

看起来你声明的变量是nvarchar而不是varchar。这肯定会导致这个错误。同样的事情也可能发生在decimal上...你可能无法将decimal绑定到number。尽管我不能100%确定,因为decimal在幕后使用了数字类型。确保在Oracle客户机驱动程序接口中使用与过程参数的数据类型完全匹配的数据类型。

相关问题