how to get a dataset from a pl/sql function RSS

• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  how to get a dataset from a pl/sql function

  Feb 20, 2013 08:39 PM|LINK

  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.

   

• • Reply
  

  smirnov

  All-Star

  23436 Points

  4029 Posts

  Re: how to get a dataset from a pl/sql function

  Feb 21, 2013 07:22 AM|LINK

  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

  • Edited by smirnov on Feb 21, 2013 07:31 AM
  • Marked as answer by Young Yang - MSFT on Feb 28, 2013 11:55 AM
• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  Re: how to get a dataset from a pl/sql function

  Feb 21, 2013 03:27 PM|LINK

  smirnov

  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!!!