0

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?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
AntDC
  • 1,807
  • 14
  • 23

3 Answers3

1

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

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.

AntDC
  • 1,807
  • 14
  • 23
  • You mean [like this](http://stackoverflow.com/a/8618084/266304)? Or do you want to loop over the cursor rows in PL/SQL? – Alex Poole Jun 23 '16 at 13:36
  • Print is not available in SQL Developer :( plus, I ofter don't actually know what columns are going to be returned until I actually run it. I know I know, but its a permission thing. Coding in the dark – AntDC Jun 23 '16 at 14:04
  • Print is absolutely available in SQL Developer, and it prints into the script output window. (Back to at least version 3; not sure about the 1.x version that ships with the DB; your answer only applies to later versions I believe). That answer was specifically about SQL Developer. The article you linked to even mentions it, but Jeff seems to prefer using the output window. – Alex Poole Jun 23 '16 at 14:09
  • I must be missing a trick here. So, using a worksheet in SQL Developer (Version 4.1.3.20)...... How can I call a proc that has an OUT parameter of SYS_REFCURSOR and then print the contents of that cursor? I've tried too many things to mention here but with no joy. It's probable I am missing something completely fundamental :( I'm simply clicking the "New SQL Worksheet" btn and putting my SQL in there and hitting "Run Script"..... – AntDC Jun 23 '16 at 15:10
  • That's exactly what the linked answer is doing. You have a bind `variable` (declared outside the anonymous PL/SQL block); you use that as the OUT parameter for the proc with in the anonymous block; then after the PL/SQL block you use `print`. – Alex Poole Jun 23 '16 at 15:13
0

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.

Sud
  • 153
  • 4