Home » Infrastructure » Windows » Running Execute Command from C# to return a SYS_REFCURSOR! (Oracle 11g Release 2)
Running Execute Command from C# to return a SYS_REFCURSOR! [message #502441] Wed, 06 April 2011 05:54 Go to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Hi,

I'm having a problem that it is giving several headaches.
I have to implement in C# a method that executes a Function in PL/SQL which returns an SYS_REFCURSOR.
I cannot use the regular use of OracleCommand.Parameters and stuff, and I was told to use only an expression like this:
SQL= "execute :refcursor_example :=function()" to run.

The Function in PL/SQL works fine and gives really what I need to be given. And it works with an regular calling of StoredProcedure but When I use the code below it gives me allways Syntax Erro:

                ExecuteSQL = "var v sys_refcursor; \n";
                ExecuteSQL = "BEGIN; \n";
                ExecuteSQL += "execute v :=calculateparentclassnodes_del3('77014000','GPC');";
                ExecuteSQL = "END;; \n";
                OracleCommand cmd = new OracleCommand(ExecuteSQL, con);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);


Anyone can help me?

I just want to return the result of this particular Function (that is an REFCURSOR) within a simples SQL command.
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502442 is a reply to message #502441] Wed, 06 April 2011 05:59 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't really know C#, but doesn't ExecuteSQL only contains "END;; \n" at the spot where you execute execute it?
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502443 is a reply to message #502441] Wed, 06 April 2011 06:06 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Sorry,

My mistake ... the correct code that I have is bellow:
                ExecuteSQL = "DECLARE v sys_refcursor; \n";
                ExecuteSQL += "BEGIN \n";
                ExecuteSQL += "execute :v calculateparentclassnodes_del3('77014000','GPC'); \n";
                ExecuteSQL += "END; \n";


Is there any way of simply put something like this:
string ExecuteSQL= "Execute calculateparentclassnodes_del3('77014000','GPC');";
OracleCommand cmd = new OracleCommand(ExecuteSQL, con);
OracleDataAdapter da = new OracleDataAdapter(cmd);

[Updated on: Wed, 06 April 2011 06:17]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502448 is a reply to message #502443] Wed, 06 April 2011 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ExecuteSQL = "DECLARE v sys_refcursor; \n";

This part is useless you don't use it in the PL/SQL block.

Quote:
ExecuteSQL += "execute :v calculateparentclassnodes_del3('77014000','GPC'); \n";

This should be
ExecuteSQL += " :v := calculateparentclassnodes_del3('77014000','GPC'); \n";
or
ExecuteSQL += " :1 := calculateparentclassnodes_del3('77014000','GPC'); \n";
or
ExecuteSQL += " ? := calculateparentclassnodes_del3('77014000','GPC'); \n";
depending on the way you reference bind variables in C# (which I don't know).
Then you have to call the procedure that makes the bind between your C# variable and the bind variable inside the PL/SQL block.

By the way, also remove the "\n" I'm not sure how it will be interpreted at the end.

Regards
Michel



[Updated on: Wed, 06 April 2011 06:55]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502451 is a reply to message #502448] Wed, 06 April 2011 07:15 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
I've changed my code to this, and the error is "ORA-01036: illegal variable name/number"
con.Open();
string ExecuteSQL = String.Empty;
ExecuteSQL += " :cur_testes := calculateparentclassnodes_del3('77014000','GPC'); ";
OracleCommand cmd = new OracleCommand(ExecuteSQL, con);
cmd.Parameters.Add(new OracleParameter("cur_testes", OracleDbType.RefCursor,  ParameterDirection.ReturnValue);
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmd);


Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502453 is a reply to message #502451] Wed, 06 April 2011 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must enclose you statement between BEGIN and END:
ExecuteSQL += " BEGIN :cur_testes := calculateparentclassnodes_del3('77014000','GPC'); END; ";

Regards
Michel

[Updated on: Wed, 06 April 2011 07:20]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502454 is a reply to message #502453] Wed, 06 April 2011 07:28 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
It works!

Thanks very much!

There is any way of not having to create the Parameters to the OracleCommand??

[Updated on: Wed, 06 April 2011 07:28]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502455 is a reply to message #502454] Wed, 06 April 2011 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AFAIK, no, you have to declare and bind (associate) the program variables and the PL/SQL bind variables.

Regards
Michel
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502458 is a reply to message #502455] Wed, 06 April 2011 08:17 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
When I run the next statement it returns me the next results:
select calculateparentclassnodes_del3('77014000','GPC') from dual


{<PARENTCLASSCATEGORYCODE=77010000>,<PARENTCLASSCATEGORYCODE=77000000>,

Is there any way to return it as a table records. By other words, is any way to return it with two lines, one with '77010000' value and another with '77000000' value?

Thanks!


Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502459 is a reply to message #502458] Wed, 06 April 2011 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please use SQL*Plus and copy and paste the result of your statement.

Regards
Michel
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502464 is a reply to message #502459] Wed, 06 April 2011 08:39 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
The result in SQL Plus is at the file anexed... but in SQL Developer it results is this:
{<PARENTCLASSCATEGORYCODE=77010000>,<PARENTCLASSCATEGORYCODE=77000000>,}
  • Attachment: SQLPlus.jpg
    (Size: 24.02KB, Downloaded 427 times)

[Updated on: Wed, 06 April 2011 08:39]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502465 is a reply to message #502464] Wed, 06 April 2011 08:41 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
If I do the next code in C#, it brings me allways the exception:
{"Object reference not set to an instance of an object."}
at da.Fill(ds);.


                ExecuteSQL = "select calculateparentclassnodes_del3('77014000','GPC') from dual; ";
                OracleCommand cmd = new OracleCommand(ExecuteSQL, con);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502466 is a reply to message #502465] Wed, 06 April 2011 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your function returns a ref cursor, it is hard to manage it in your program.
Why don't you want to use the PL/SQL block?

Regards
Michel

[Updated on: Wed, 06 April 2011 10:22]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502467 is a reply to message #502466] Wed, 06 April 2011 09:09 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Because of company policies ....

Thanks any way!

I've tried to manage myself with a workaround that includes the usage of an Package like that:
create or replace
PACKAGE Pkg_parentclasscatycode IS
 TYPE classparent_tbl IS TABLE OF classrelation.parentclasscategorycode%TYPE INDEX BY BINARY_INTEGER;
 FUNCTION calculateparentclassnodes_Cur ( iCode IN VARCHAR2,  iAgency IN VARCHAR2) return classparent_tbl;
END Pkg_parentclasscatycode;

create or replace
PACKAGE BODY Pkg_parentclasscatycode AS 
  FUNCTION  calculateparentclassnodes_Cur(
    iCode IN VARCHAR2,
		iAgency IN VARCHAR2)
    RETURN classparent_tbl
  AS 
  CURSOR cur_parentclasscatycode (in_iCode IN VARCHAR2, in_iAgency IN VARCHAR2 ) IS
    WITH parents  (parentclasscategorycode ) AS (SELECT parentclasscategorycode FROM classrelation WHERE childclasscategorycode=in_iCode 
        and classagencyname=in_iAgency 
    UNION ALL
        SELECT cl.parentclasscategorycode FROM classrelation cl, parents pa WHERE cl.childclasscategorycode = pa.parentclasscategorycode
            AND cl.classagencyname = in_iAgency)
    SELECT distinct parentclasscategorycode FROM parents;
    
    parentClassNodeCode_TabAux classparent_tbl;
    
  BEGIN
  IF NOT cur_parentclasscatycode%ISOPEN then
        OPEN cur_parentclasscatycode(iCode, iAgency);
  END IF;

  FETCH cur_parentclasscatycode bulk collect into parentClassNodeCode_TabAux;
  return parentClassNodeCode_TabAux;
  END calculateparentclassnodes_Cur;
END Pkg_parentclasscatycode;


But when I execute the next statement:
 select Pkg_parentclasscatycode.calculateparentclassnodes_Cur('77014000','GPC') from dual 


it gives me allways the necxt error:
 ORA-00902: tipo de dados inválido
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error at Line: 2 Column: 7 


I just want to return a structure with the results of that Cursor in an only commandtext .

[Updated on: Wed, 06 April 2011 09:14]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502475 is a reply to message #502467] Wed, 06 April 2011 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Because of company policies ....

If it does not calling PL/SQL block but allow calling PL/SQL function through a SQL statement, it is a stupid policy.

You have to check if your code first works in SQL*Plus, if it works then and only then try to make it work with C#.

Regards
Michel
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502510 is a reply to message #502475] Wed, 06 April 2011 21:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
If you use a sql type instead of a pl/sql type, then you can select it from sql and use the table function, as shown below.

SCOTT@orcl_11gR2> create table classrelation
  2    (parentclasscategorycode  varchar2 (23),
  3  	childclasscategorycode	 varchar2 (22),
  4  	classagencyname 	 varchar2 (15))
  5  /

Table created.

SCOTT@orcl_11gR2> insert into classrelation values
  2  ('77000000', '77010000', 'GPC')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into classrelation values
  2  ('77010000', '77014000', 'GPC')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into classrelation values
  2  ('77014000', '2', 'GPC')
  3  /

1 row created.

SCOTT@orcl_11gR2> create or replace type classparent_tbl as table of varchar2 (23);
  2  /

Type created.

SCOTT@orcl_11gR2> create or replace
  2  PACKAGE Pkg_parentclasscatycode IS
  3  -- TYPE classparent_tbl IS TABLE OF classrelation.parentclasscategorycode%TYPE INDEX BY BINARY_INTEGER;
  4   FUNCTION calculateparentclassnodes_Cur ( iCode IN VARCHAR2,  iAgency IN VARCHAR2) return classparent_tbl;
  5  END Pkg_parentclasscatycode;
  6  /

Package created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace
  2  PACKAGE BODY Pkg_parentclasscatycode AS
  3    FUNCTION  calculateparentclassnodes_Cur(
  4  	 iCode IN VARCHAR2,
  5  		     iAgency IN VARCHAR2)
  6  	 RETURN classparent_tbl
  7    AS
  8    CURSOR cur_parentclasscatycode (in_iCode IN VARCHAR2, in_iAgency IN VARCHAR2 ) IS
  9  	 WITH parents  (parentclasscategorycode ) AS (SELECT parentclasscategorycode FROM classrelation WHERE childclasscategorycode=in_iCode
 10  	     and classagencyname=in_iAgency
 11  	 UNION ALL
 12  	     SELECT cl.parentclasscategorycode FROM classrelation cl, parents pa WHERE cl.childclasscategorycode = pa.parentclasscategorycode
 13  		 AND cl.classagencyname = in_iAgency)
 14  	 SELECT distinct parentclasscategorycode FROM parents;
 15  
 16  	 parentClassNodeCode_TabAux classparent_tbl;
 17  
 18    BEGIN
 19    IF NOT cur_parentclasscatycode%ISOPEN then
 20  	     OPEN cur_parentclasscatycode(iCode, iAgency);
 21    END IF;
 22  
 23    FETCH cur_parentclasscatycode bulk collect into parentClassNodeCode_TabAux;
 24    return parentClassNodeCode_TabAux;
 25    END calculateparentclassnodes_Cur;
 26  END Pkg_parentclasscatycode;
 27  /

Package body created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select Pkg_parentclasscatycode.calculateparentclassnodes_Cur('77014000','GPC')
  2  from   dual
  3  /

PKG_PARENTCLASSCATYCODE.CALCULATEPARENTCLASSNODES_CUR('77014000','GPC')
--------------------------------------------------------------------------------
CLASSPARENT_TBL('77010000', '77000000')

1 row selected.

SCOTT@orcl_11gR2> select *
  2  from   table
  3  	      (Pkg_parentclasscatycode.calculateparentclassnodes_Cur
  4  		 ('77014000','GPC'))
  5  /

COLUMN_VALUE
-----------------------
77010000
77000000

2 rows selected.

SCOTT@orcl_11gR2>

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502560 is a reply to message #502510] Thu, 07 April 2011 05:40 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
Michel Cadot and Barbara Boehmer I want to say both of you a huge THANK YOU.

This final solution works just fine!

One more question, if I may ...
What is more efficient ...

This:
  CURSOR cur_parentclasscatycode (in_iCode IN VARCHAR2, in_iAgency IN VARCHAR2 ) IS
    WITH parents  (parentclasscategorycode ) AS (SELECT parentclasscategorycode FROM classrelation WHERE childclasscategorycode=in_iCode 
        and classagencyname=in_iAgency 
    UNION ALL
        SELECT cl.parentclasscategorycode FROM classrelation cl, parents pa WHERE cl.childclasscategorycode = pa.parentclasscategorycode
            AND cl.classagencyname = in_iAgency)
    SELECT distinct parentclasscategorycode FROM parents;


or this:
OPEN cur_testes FOR select parentclasscategorycode
from
  classrelation
  where
  classagencyname=iAgency
start with
  childclasscategorycode = iCode
connect by 
  prior parentclasscategorycode = childclasscategorycode;


Thanks!

[Updated on: Thu, 07 April 2011 05:41]

Report message to a moderator

Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502561 is a reply to message #502560] Thu, 07 April 2011 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As always, the answer will be it depends, it depends on your data, number of rows, indexes...
The first one works only in 11.2 while the last one in all versions, this may be important.

Regards
Michel
Re: Running Execute Command from C# to return a SYS_REFCURSOR! [message #502702 is a reply to message #502561] Fri, 08 April 2011 03:49 Go to previous message
aucrun
Messages: 114
Registered: February 2011
Senior Member
This particular table is a simple table, with only 3 columns, indexed by all of them.
It has only 25.000 records.
Both queries are really fast, but I get best performances using the UNION ALL syntax.
I'll guess I'm using it...

Again I woul like to thank you both a lot for helping me out.

Thank You!
Previous Topic: Installed ODAC 11g ver Release 3 (11.2.0.2.1) - no tnsping utility
Next Topic: Oracle Client and Windows 7 64 Bits
Goto Forum:
  


Current Time: Sat Dec 05 11:23:48 CST 2020