0

I am trying to execute an Oracle function that returns a ref cursor but nothing is working. I have tried a couple of solutions suggested by some stackoverflow posts. Below is what I have tried:

Function

FUNCTION market.get_countries_list RETURN REF CURSOR 
(
  COUNTRY_CODE NUMBER(3), 
  COUNTRY_NAME VARCHAR2(50)
);

C# code:

System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection();
conn.ConnectionString = "...";
conn.Open();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("MARKET.GET_COUNTRIES_LIST", conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add(new System.Data.OracleClient.OracleParameter("ref_cur", System.Data.OracleClient.OracleType.Cursor)).Direction = System.Data.ParameterDirection.Output;
System.Data.OracleClient.OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
  Label1.Text = rdr.GetOracleNumber(0).ToString();
  rdr.Close();
}

The above code produces the error below:

ORA-01036: illegal variable name/number

What am I missing?

MethodMan
  • 18,625
  • 6
  • 34
  • 52
mpora
  • 1,411
  • 5
  • 24
  • 65
  • 1
    Looks like you're using the built-in .NET Framework Data Provider for Oracle. I believe this is deprecated. Use the ODP.NET library instead. Google it, and you'll find it. – sstan Jul 06 '16 at 21:05
  • 1
    `.NET Data Provider` for oracle is still good we currently use the `32Bit` one because the `64bit dll` has known bugs – MethodMan Jul 06 '16 at 21:09
  • did you look at the Oracle Documentation on how to create a Function that returns a `SYS_REFCURSOR` here is an example http://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exe – MethodMan Jul 06 '16 at 21:14

1 Answers1

0

This post fixed my issue: How to return a RefCursor from Oracle function?

I ended up using Oracle Data Access

Community
  • 1
  • 1
mpora
  • 1,411
  • 5
  • 24
  • 65