locked
how to get a dataset from a pl/sql function RRS feed

  • Question

  • User1529494599 posted

    Hi, i need help for a returned object from pl/sql function....

    I need to get a DataSet in c#, by calling a pl/sql function, but my code does not work!!!

    Hope someone can help me!

    This is the pl/sql function:

    create or replace 
    FUNCTION RETURN_PROD_TAB RETURN sys_refcursor as
    returned_table sys_refcursor;
    
    begin
    open returned_table for 
    select * from prodotti;
    return returned_table;
    		 
    END RETURN_PROD_TAB;

    and this is the c# code:


     

     using (OracleConnection oracleConn = new OracleConnection("DATA SOURCE=localhost;PERSIST SECURITY INFO=True;USER ID=PROVA; PASSWORD=xxxxxxxxxx"))
                {
                    
                    OracleCommand oraComm = new OracleCommand();
                    oraComm.Connection = oracleConn;
                   
                    oraComm.CommandText = "RETURN_PROD_TAB";
                    oraComm.CommandType = System.Data.CommandType.StoredProcedure;
                    
                    OracleParameter oraPar = new OracleParameter("returned_table", OracleType.Cursor);
                    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.Output;
                    
                    
                    oracleConn.Open();
                    DataSet ds = new DataSet();
                    OracleDataAdapter adapter = new OracleDataAdapter(oraComm);
                    adapter.Fill(ds);
                    oraComm.ExecuteNonQuery();
    
                }


    This code gives me the following error:

    ORA-06550: line 1, column 7:
    
    PLS-00306: wrong number or types of arguments in call to 'RETURN_PROD_TAB'
    
    ORA-06550: line 1, column 7:


    How can i solve this issue??

    what is the best way to do it?

     

    OracleConnection, OracleCommand, and OracleDataAdapter classes are deprecated???

     

    Thanks in advance.

     

    Wednesday, February 20, 2013 4:39 PM

Answers

  • User1508394307 posted

    I think

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.Output;

    must be

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.ReturnValue;

    http://www.telerik.com/help/openaccess-orm/openaccess-tasks-oracle-execute-sp-result-set.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2013 3:22 AM

All replies

  • User1508394307 posted

    I think

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.Output;

    must be

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.ReturnValue;

    http://www.telerik.com/help/openaccess-orm/openaccess-tasks-oracle-execute-sp-result-set.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2013 3:22 AM
  • User1529494599 posted

    I think

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.Output;

    must be

    oraComm.Parameters.Add(oraPar).Direction = ParameterDirection.ReturnValue;

    http://www.telerik.com/help/openaccess-orm/openaccess-tasks-oracle-execute-sp-result-set.html

    perfect... thanks al lot!!!

    Thursday, February 21, 2013 11:27 AM