none
Issue in Calling Oracle Function RRS feed

  • Question

  • Dear All

     

    I've been stuck in this problem for more than a day now, really I've checked every parameter name, type and count, but it doesn't seem to go away, the error is quiet common as I've searched on the net, but couldn't find any solution to my problem:

     

    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'ADD_CUSTOMER'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

     

    The function has 16 input and one output parameters, all defined as VarChar2, here is Function, followed by the code I am using to access it, please let me know, if you find any reason why this might be happening:

     

    Code Snippet
    function
    ADD_CUSTOMER (CUSTOMER_CODE VARCHAR2,IN_MODE VARCHAR2,CATEGORY VARCHAR2,BILLER_CODE VARCHAR2,BENEF_NAME VARCHAR2,BENEF_COUNTRY VARCHAR2,BENEF_ADDRESS VARCHAR2,BENEF_ACCT_NO VARCHAR2,BENEF_BANK VARCHAR2,BENEF_BANK_BRANCH VARCHAR2,BENEF_BANK_ADDR_LINE1 VARCHAR2,BENEF_BANK_ADDR_LINE2 VARCHAR2,BENEF_BANK_CODE VARCHAR2,BENEF_BANK_COUNTRY VARCHAR2,IS_IBAN VARCHAR2,USER_ID VARCHAR2) return VARCHAR2 as
    i_CUST_CODE VARCHAR2(
    10);
    i_BENEF_SEQ NUMBER;
    i_CATEGORY VARCHAR2(
    5) ;
    i_BENEF_NAME VARCHAR2(
    50);
    i_BENEF_COUNTRY VARCHAR2(
    50);
    i_BENEF_ADDRESS VARCHAR2(
    50);
    i_BENEF_ACCT_NO VARCHAR2(
    40);
    i_BENEF_BANK VARCHAR2(
    50);
    i_BENEF_BANK_BRANCH VARCHAR2(
    50);
    i_BENEF_BANK_ADDR_LINE1 VARCHAR2(
    50);
    i_BENEF_BANK_ADDR_LINE2 VARCHAR2(
    50);
    i_BENEF_BANK_CODE VARCHAR2(
    20);
    i_BENEF_BANK_COUNTRY VARCHAR2(
    50);
    i_BILL_BSF_BILLER_CODE VARCHAR2(
    5);
    i_IS_IBAN VARCHAR2(
    1);
    i_STATUS VARCHAR2(
    1);
    i_CREATED_USER VARCHAR2(
    20);
    begin

    i_CUST_CODE := substr(lpad(CUSTOMER_CODE,
    6,'0'),1,6);

    if trim(CATEGORY) not in (
    'BSF','KSA','INT','BILL') then
    return
    'BENF0001';
    else
    i_CATEGORY := trim(CATEGORY);
    end if;

    i_STATUS := substr(trim(in_MODE),
    1,1);
    if i_STATUS not in (
    'A','I') then
    return
    'BENF0002';
    end if;

    i_BENEF_COUNTRY := substr(trim(BENEF_COUNTRY),
    1,50);
    i_BENEF_BANK_COUNTRY := substr(trim(BENEF_BANK_COUNTRY),
    1,50);

    if i_CATEGORY in (
    'BSF','KSA') then
    i_BENEF_COUNTRY :=
    'KSA';
    i_BENEF_BANK_COUNTRY :=
    'KSA';
    end if;

    i_BENEF_NAME := substr(trim(BENEF_NAME),
    1,50);
    i_BENEF_ADDRESS := substr(trim(BENEF_ADDRESS),
    1,50);
    i_BENEF_ACCT_NO := substr(trim(BENEF_ACCT_NO),
    1,40);
    i_BENEF_BANK := substr(trim(BENEF_BANK),
    1,50);
    i_BENEF_BANK_BRANCH := substr(trim(BENEF_BANK_BRANCH),
    1,50);
    i_BENEF_BANK_ADDR_LINE1 := substr(trim(BENEF_BANK_ADDR_LINE1),
    1,50);
    i_BENEF_BANK_ADDR_LINE2 := substr(trim(BENEF_BANK_ADDR_LINE2),
    1,50);
    i_BENEF_BANK_CODE := substr(trim(BENEF_BANK_CODE),
    1,20);

    i_BILL_BSF_BILLER_CODE := substr(trim(BILLER_CODE),
    1,5);

    i_IS_IBAN := substr(trim(IS_IBAN),
    1,1);
    i_CREATED_USER := substr(trim(USER_id),
    1,20);

    select nvl(max(t.benef_seq),
    0) + 1
    into i_BENEF_SEQ
    from customer_beneficiary t
    where t.cust_code = i_CUST_CODE;


    insert into customer_beneficiary (CUST_CODE,benef_seq,category,benef_name,
    benef_country,benef_address,benef_acct_no,benef_bank,
    benef_bank_branch,benef_bank_addr_line1,benef_bank_addr_line2,
    benef_bank_code,benef_bank_country,bill_bsf_biller_code
    ,is_iban,status,created_user,modify_user)
    values (i_CUST_CODE,i_BENEF_SEQ,i_CATEGORY,i_BENEF_NAME,
    i_BENEF_COUNTRY,i_BENEF_ADDRESS,i_BENEF_ACCT_NO,i_BENEF_BANK,
    i_BENEF_BANK_BRANCH,i_BENEF_BANK_ADDR_LINE1,i_BENEF_BANK_ADDR_LINE2,
    i_BENEF_BANK_CODE,i_BENEF_BANK_COUNTRY,i_BILL_BSF_BILLER_CODE,
    i_IS_IBAN,i_STATUS,i_CREATED_USER,i_CREATED_USER);
    end;

     

     

     

    Code Snippet

    public string ADD_CUSTOMER(string customer_code, string in_mode, string category, string biller_code, string benef_name, string benef_country, string benef_address, string benef_acct_no, string benef_bank, string benef_bank_branch, string benef_bank_addr_line1, string benef_bank_addr_line2, string benef_bank_code, string benef_bank_country, string is_iban, string user_id)

    {

    OracleCommand oraComm = null;

    OracleConnection oraConn = null;

    try

    {

    SetConnectionString("BeneficiaryModule", "ConnectionSpecs");

    oraComm = new OracleCommand();

    oraConn = new OracleConnection();

    oraComm.CommandText = "beneficiary_pkg.add_customer";

    oraComm.CommandType = System.Data.CommandType.StoredProcedure;

    OracleParameter in_CustomerCode = new OracleParameter("customer_code", OracleType.VarChar, 32767);

    in_CustomerCode.Direction = System.Data.ParameterDirection.Input;

    in_CustomerCode.Value = customer_code;

    oraComm.Parameters.Add(in_CustomerCode);

    OracleParameter in_Mode = new OracleParameter("in_mode", OracleType.VarChar, 32767);

    in_Mode.Direction = System.Data.ParameterDirection.Input;

    in_Mode.Value = in_mode;

    oraComm.Parameters.Add(in_Mode);

    OracleParameter in_Category = new OracleParameter("category", OracleType.VarChar, 32767);

    in_Category.Direction = System.Data.ParameterDirection.Input;

    in_Category.Value = category;

    oraComm.Parameters.Add(in_Category);

    OracleParameter in_BillerCode = new OracleParameter("biller_code", OracleType.VarChar, 32767);

    in_BillerCode.Direction = System.Data.ParameterDirection.Input;

    in_BillerCode.Value = biller_code;

    oraComm.Parameters.Add(in_BillerCode);

    OracleParameter in_benef_name = new OracleParameter("benef_name", OracleType.VarChar, 32767);

    in_benef_name.Direction = System.Data.ParameterDirection.Input;

    in_benef_name.Value = benef_name;

    oraComm.Parameters.Add(in_benef_name);

    OracleParameter in_benef_country = new OracleParameter("benef_country", OracleType.VarChar, 32767);

    in_benef_country.Direction = System.Data.ParameterDirection.Input;

    in_benef_country.Value = benef_country;

    oraComm.Parameters.Add(in_benef_country);

    OracleParameter in_benef_address = new OracleParameter("benef_address", OracleType.VarChar, 32767);

    in_benef_address.Direction = System.Data.ParameterDirection.Input;

    in_benef_address.Value = benef_address;

    oraComm.Parameters.Add(in_benef_address);

    OracleParameter in_benef_acct_no = new OracleParameter("benef_acct_no", OracleType.VarChar, 32767);

    in_benef_acct_no.Direction = System.Data.ParameterDirection.Input;

    in_benef_acct_no.Value = benef_acct_no;

    oraComm.Parameters.Add(in_benef_acct_no);

    OracleParameter in_benef_bank = new OracleParameter("benef_bank", OracleType.VarChar, 32767);

    in_benef_bank.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank.Value = benef_bank;

    oraComm.Parameters.Add(in_benef_bank);

    OracleParameter in_benef_bank_branch = new OracleParameter("benef_bank_branch", OracleType.VarChar, 32767);

    in_benef_bank_branch.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank_branch.Value = benef_bank_branch;

    oraComm.Parameters.Add(in_benef_bank_branch);

    OracleParameter in_benef_bank_addr_line1 = new OracleParameter("benef_bank_addr_line1", OracleType.VarChar, 32767);

    in_benef_bank_addr_line1.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank_addr_line1.Value = benef_bank_addr_line1;

    oraComm.Parameters.Add(in_benef_bank_addr_line1);

    OracleParameter in_benef_bank_addr_line2 = new OracleParameter("benef_bank_addr_line2", OracleType.VarChar, 32767);

    in_benef_bank_addr_line2.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank_addr_line2.Value = benef_bank_addr_line2;

    oraComm.Parameters.Add(in_benef_bank_addr_line2);

    OracleParameter in_benef_bank_code = new OracleParameter("benef_bank_code", OracleType.VarChar, 32767);

    in_benef_bank_code.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank_code.Value = benef_bank_code;

    oraComm.Parameters.Add(in_benef_bank_code);

    OracleParameter in_benef_bank_country = new OracleParameter("benef_bank_country", OracleType.VarChar, 32767);

    in_benef_bank_country.Direction = System.Data.ParameterDirection.Input;

    in_benef_bank_country.Value = benef_bank_country;

    oraComm.Parameters.Add(in_benef_bank_country);

    OracleParameter in_is_iban = new OracleParameter("is_iban", OracleType.VarChar, 32767);

    in_is_iban.Direction = System.Data.ParameterDirection.Input;

    in_is_iban.Value = is_iban;

    oraComm.Parameters.Add(in_is_iban);

    OracleParameter in_user_id = new OracleParameter("user_id", OracleType.VarChar, 32767);

    in_user_id.Direction = System.Data.ParameterDirection.Input;

    in_user_id.Value = user_id;

    oraComm.Parameters.Add(in_user_id);

     

    OracleParameter outputParameter = new OracleParameter("result", OracleType.VarChar, 32767);

    outputParameter.Direction = System.Data.ParameterDirection.Output;

    oraComm.Parameters.Add(outputParameter);

    oraConn.ConnectionString = connectionString;

    oraComm.Connection = oraConn;

    oraConn.Open();

    oraComm.ExecuteNonQuery();

    oraConn.Close();

    //return "Hello";

    return outputParameter.Value.ToString();

    }

    catch (Exception ex)

    {

    return "99";

    }

    finally

    {

    if (oraConn != null)

    oraConn.Close();

    }

    }

     

     

    Wednesday, April 30, 2008 8:53 AM

All replies

  • i think here might be your problem

     

    Code Snippet

    OracleParameter outputParameter = new OracleParameter("result", OracleType.VarChar, 32767);

    outputParameter.Direction = System.Data.ParameterDirection.Output;

    oraComm.Parameters.Add(outputParameter);

     

     

    your function takes 16 arguments, and you are forwarding this as 17th. try to comment this 3 lines and then run it.

    i haevnt worked with functions but with procedures it goes something like:

    Code Snippet

    procedure pgetPLPLT(pl OUT refCursorxx, plt OUT refCursorxx, PID IN number) AS
      BEGIN

    and so on

     

    and from .net

    Code Snippet

    cmd.Parameters.Add(new OracleParameter("pl", OracleType.Cursor)).Direction = ParameterDirection.Output;

    cmd.Parameters.Add(new OracleParameter("plt", OracleType.Cursor)).Direction = ParameterDirection.Output;

    cmd.Parameters.Add(new OracleParameter("pid", OracleType.Number)).Direction = ParameterDirection.Input;

     

     

    Hope this helps Smile

     

    Edit:

    Maybe if you try this

     

    Code Snippet
    function ADD_CUSTOMER (CUSTOMER_CODE VARCHAR2,IN_MODE VARCHAR2,CATEGORY VARCHAR2,BILLER_CODE VARCHAR2,BENEF_NAME VARCHAR2,BENEF_COUNTRY VARCHAR2,BENEF_ADDRESS VARCHAR2,BENEF_ACCT_NO VARCHAR2,BENEF_BANK VARCHAR2,BENEF_BANK_BRANCH VARCHAR2,BENEF_BANK_ADDR_LINE1 VARCHAR2,BENEF_BANK_ADDR_LINE2 VARCHAR2,BENEF_BANK_CODE VARCHAR2,BENEF_BANK_COUNTRY VARCHAR2,IS_IBAN VARCHAR2,USER_ID VARCHAR2, RESULT OUT VARCHAR2)

     

     

     

    Wednesday, April 30, 2008 12:39 PM