locked
Return user-defined data type in call to Oracle function RRS feed

  • Question

  • User-1187146291 posted

    I'm using C# to call an Oracle function but I get an error message:

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

    Does anyone know how to return an Oracle datatype which is a table of objects containing the following fields?

    label VARCHAR2(50),
    value VARCHAR2(100)

    I'm not sure what I'm doing wrong. I'm fairly new to C# and .NET. Here is my code:

           DataTable myDataTable;
            OracleDataAdapter myOracleDataAdapter;
            System.Data.OracleClient.OracleCommand cmd;
    
    
            cmd = new System.Data.OracleClient.OracleCommand();
            myDataTable = new DataTable();
    
            OracleConnection conn = new OracleConnection("User ID=myuserid;Password=mypwd;Data Source=mydatasource;Persist Security Info=False");
            conn.Open();
    
            cmd.Connection = conn;
            cmd.CommandText = "myschema.mypkg.ProcessPayment";
            cmd.CommandType = CommandType.StoredProcedure; 
    
            ParameterDirection.ReturnValue, true, 0, 0, "retVal", DataRowVersion.Current,null));
            cmd.Parameters.Add(new OracleParameter("outCursor", OracleType.Cursor)).Direction = ParameterDirection.Output; 
            cmd.Parameters.Add("pAccountNo", OracleType.Number, 12).Value = 123456789;
            cmd.Parameters["pAccountNo"].Direction = ParameterDirection.Input;
            cmd.Parameters.Add("pTransactionType", "Credit");
            cmd.Parameters.Add("pCreditCardType", OracleType.Number, 12).Value = 0;
            cmd.Parameters["pCreditCardType"].Direction = ParameterDirection.Input;  
            cmd.Parameters.Add("pCreditCardNumber", "4111111111111111");
            cmd.Parameters.Add("pExpirationMonth", "12");
            cmd.Parameters.Add("pExpirationYear", "12");
            cmd.Parameters.Add("pAmount", 1);
            cmd.Parameters.Add("pConvenienceFeeAmount", OracleType.Number, 12).Value = 0;
            cmd.Parameters["pConvenienceFeeAmount"].Direction = ParameterDirection.Input; 
            cmd.Parameters.Add("pCVV2", "111");
            cmd.Parameters.Add("pStreetAddress", "123 Baker Dr");
            cmd.Parameters.Add("pCity", "Cincinnati");
            cmd.Parameters.Add("pState", "OH");
            cmd.Parameters.Add("pZip", "12345");
            cmd.Parameters.Add("pCheckNo", DBNull.Value);
            cmd.Parameters.Add("pBankRoutingNo", DBNull.Value);
            cmd.Parameters.Add("pBankAccountNo", DBNull.Value);
            cmd.Parameters.Add("pAccountHolderName", "sbushnell");
            cmd.Parameters.Add("pDriversLicenseNumber", DBNull.Value);
            cmd.Parameters.Add("pEmail", DBNull.Value);                             
            cmd.Parameters.Add("pBankAccountType", DBNull.Value);                              
            cmd.Parameters.Add("pAccountsToPay", DBNull.Value);                             
            cmd.Parameters.Add("pPhone", DBNull.Value);                               
            cmd.Parameters.Add("pAdminUser", DBNull.Value);                             
            cmd.Parameters.Add("pIVR", DBNull.Value);
            cmd.Parameters.Add("pMemberNo", 6000677);
            cmd.Parameters.Add("pStorePaymentData", DBNull.Value);
            cmd.Parameters.Add("pWebPayment", "Y");
            cmd.ExecuteNonQuery();
            myOracleDataAdapter = new OracleDataAdapter(cmd);
    
            myOracleDataAdapter.Fill(myDataTable);                      
            conn.Close(); 
    Sunday, March 25, 2012 11:03 AM

All replies

  • User269602965 posted

    Show the beginning of the stored procedure where you name the procedure and define the parameters of the procedure.

    Sunday, March 25, 2012 7:55 PM
  • User-1187146291 posted

    The function is on an Oracle server managed by someone else. They created the function. This is what they shared with me:

    FUNCTION ProcessPayment(pAccountNo IN NUMBER,
                            pTransactionType IN VARCHAR2,
                            pCreditCardType IN NUMBER,
                            pCreditCardNumber IN VARCHAR2,
                            pExpirationMonth IN VARCHAR2,
                            pExpirationYear IN VARCHAR2,
                            pAmount IN NUMBER,
                            pConvenienceFeeAmount IN NUMBER,
                            pCVV2 IN VARCHAR2,
                            pStreetAddress IN VARCHAR2,
                            pCity IN VARCHAR2,
                            pState IN VARCHAR2,
                            pZip IN VARCHAR2,
                            pCheckNo IN VARCHAR2,
                            pBankRoutingNo IN VARCHAR2,
                            pBankAccountNo IN VARCHAR2,
                            pAccountHolderName IN VARCHAR2,
                            pDriversLicenseNumber IN VARCHAR2,
                            pEmail IN VARCHAR2,
                            pBankAccountType IN VARCHAR2,
                            pAccountsToPay IN VARCHAR2,
                            pPhone IN VARCHAR2,
                            pAdminUser IN VARCHAR2,
                            pIVR IN VARCHAR2,
                            pMemberNo IN NUMBER,
                            pStorePaymentData IN VARCHAR2,
                            pWebPayment IN VARCHAR2 DEFAULT 'N')
        RETURN myschema.ReturnVal
    
    Sunday, March 25, 2012 10:03 PM
  • User-1187146291 posted

    ReturnVal is an Oracle datatype which is a table of objects containing the following fields:

    label        VARCHAR2(50),
    value        VARCHAR2(100)

    Sunday, March 25, 2012 10:05 PM