Home » SQL & PL/SQL » SQL & PL/SQL » Executing a procedure at sql prompt
Executing a procedure at sql prompt [message #36303] Thu, 15 November 2001 01:27 Go to next message
Srinivas S
Messages: 4
Registered: November 2001
Junior Member
hey i have a package like this

create or replace package curspkg_join as
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (io_cursor IN OUT t_cursor);
END curspkg_join;

and package body as

create or replace package body CURSPKG_JOIN as
AS
Procedure open_join_cursor1 (io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
OPEN v_cursor FOR
Select * from emp;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;

no errors till now. i m calling this in an ASP page and it is working fine. i want to execute the same from sql prompt.

how to do it.

Pl. help me.

thanks

srini

----------------------------------------------------------------------
Re: Executing a procedure at sql prompt [message #36304 is a reply to message #36303] Thu, 15 November 2001 01:48 Go to previous messageGo to next message
Malli
Messages: 10
Registered: November 2001
Junior Member
Hi Srinivas,
at sql prompt u type like the following
-----------------------------------------------
sql> var rct refcursor;
sql> exec :rct := open_join_cursor1 (io_cursor);
now it gives the message that procedure executed.
To see the results type the following
sql> PRINT rct;

----------------------------------------------------------------------
Re: Executing a procedure at sql prompt [message #36307 is a reply to message #36303] Thu, 15 November 2001 07:02 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Your procedure can just be:

Procedure open_join_cursor1 (io_cursor OUT t_cursor)
IS
BEGIN
OPEN io_cursor FOR Select * from emp;
END open_join_cursor1;

1) The parameter can just be OUT, not IN/OUT.
2) No need for the v_cursor variable.

To call from SQL*Plus:

var rc refcursor;
exec curspkg_join.open_join_cursor1(:rc);
print rc;

----------------------------------------------------------------------
Previous Topic: Re: msagent and oracle plz help
Next Topic: Insufficient privileges?
Goto Forum:
  


Current Time: Sun May 31 03:17:46 CDT 2020