CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle-sqldevelopersys-refcursor
AntDC
AntDC

Reputation: 1917

Oracle 11g - sys_refcursor

I am working on a system where Oracle 11g is the back end database. I have very limited permissions on the database and as such all I can do is call procedures that reside in packages.

Gerally, these procedure return their result set via an OUT parameter of type sys_refcursor.

I can call them fine in C# and get data from the cursor via the C# OracleDataset type.

Here is my question. I want to be able to run these procedures and see the results via SQL Developer. I can execute the procedure fine, but seeing the contents of the sys_refcursor OUT parameter is boggling me.

I've done some gooling and people ar saying about creating type and other solutions I simply do not have the permissions to persue.

So, how can I possibly see the result set contained in a sys_refcursor? So say I have a procedure with this signature....

procedure an_oracle_Proc(p_ref         IN  varchar2,
                         p_result_set  OUT sys_refcursor);

I call it like this....

DECLARE
  l_ref VARCHAR2(10);
  l_result_set sys_refcursor;
BEGIN
  oracle_pkg.an_oracle_Proc(p_ref => l_ref,
                                          p_result_set => l_result_set);

  --How to select from l_result_set with limited permissions
END

How can I look at the contents of l_result_Set?

Upvotes: 0

Views: 1086

Answers (3)

Sud
Sud

Reputation: 153

Sys_refcursor form an anonymous block is bit tricky. Use the sql-developer, explore the package or procedure , right click and execute the procedure/package.

Sql-developer will open an input/output UI where you can key in values. And you can see the output on the same UI as well. Let me know if you need more details. I was actually debugging the same a couple of weeks back successfully.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191415

This is repeating the answer I linked to before really but specifically for your code:

VARIABLE result_set refcursor;

DECLARE
  l_ref VARCHAR2(10);  
BEGIN
  l_ref := 'whatever';
  oracle_pkg.an_oracle_Proc(p_ref => l_ref,
                                          p_result_set => :result_set);
END;
/

PRINT result_set

... and run all of that as a script from an SQL Worksheet. The contents of the ref cursor will be shown in the script output window.

Upvotes: 1

AntDC
AntDC

Reputation: 1917

Thought I'd have another look and found this - amazing what stepping away from the computer can do. ;) I just have to select the appropriate variable on the left pane.

http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/

Still - it would be nice to write my own SQL to do this rather than using the execute window.

Upvotes: 0

Related Questions