Home » SQL & PL/SQL » Client Tools » how to execute stored Procedure with cursor with in pl/sql
how to execute stored Procedure with cursor with in pl/sql [message #502184] Tue, 05 April 2011 03:06 Go to next message
viperz
Messages: 2
Registered: April 2011
Junior Member
hi all.
im kinda new to oracle pl/sql
i'v been trying to do this simple thing for a while now, with no luck (and searched all over the net...)

to keep things simple:
lets say that this is my procedure:

create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;


how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...


my goal is in the end to execute this from Magic 8 (to those who herd of it)
p.s this needs to work in oracle 8
thanks for any help

[Updated on: Tue, 05 April 2011 03:08]

Report message to a moderator

Re: how to execute stored Procedure with cursor with in pl/sql [message #502185 is a reply to message #502184] Tue, 05 April 2011 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you have to use sqltools or can you use sqlplus?
Re: how to execute stored Procedure with cursor with in pl/sql [message #502186 is a reply to message #502184] Tue, 05 April 2011 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
im kinda new to oracle pl/sql

Your question has no actual relation with PL/SQL, it is rather a question about your tool, whatever RDBMS you use the answer will be the same one: how to get the result from a cursor; I think your product documentation will tell it.

Regards
Michel
Re: how to execute stored Procedure with cursor with in pl/sql [message #502192 is a reply to message #502186] Tue, 05 April 2011 03:27 Go to previous messageGo to next message
viperz
Messages: 2
Registered: April 2011
Junior Member
hi Michel,
i have looked all over including sqltool docs....
tho even when using SQLPLUS i still in the same problem.
to clarify, lets say that you have wrote :
create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;

how would you execute it and view it from the same tool that you wrote it in?
Re: how to execute stored Procedure with cursor with in pl/sql [message #502197 is a reply to message #502192] Tue, 05 April 2011 03:36 Go to previous message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

SQL> create or replace procedure testSp(x out sys_refcursor )
  2  is
  3  begin
  4  open x for
  5  select 1 from dual;
  6  end;
  7  
  8  /

Procedure created.

SQL> var x refcursor
SQL> exec testSp (:x)

PL/SQL procedure successfully completed.

SQL> print x
         1
----------
         1

1 row selected.

Regards
Michel
Previous Topic: Can you help me to improve my database?
Next Topic: escape/quote Ampersand (&) because no variable substitution
Goto Forum:
  


Current Time: Wed Feb 21 23:59:39 CST 2018

Total time taken to generate the page: 0.03804 seconds