how to execute package

[amoldiego]

Dear all,

1)
I have created package ref test for declaring ref cursor as shown .....
create or replace package ref_test as
type empty is ref cursor;
end;


2)
created package ref_use and used the reference of that ref cursor .....

create or replace package ref_use as
procedure ref_pro (p_empno number,P_result out ref_test.empty) ;
end;


3)
created package body as shown


create or replace package body ref_use as
procedure ref_pro (p_empno number,P_result out ref_test.empty) as
begin
open P_result for select ename,sal,deptn o from emp where empno=p_empno;
end;
end;
/


now i want to execute this package in SQL(+) .could u plz send me the execution code of
this package

[debasisdas]

to execute any member of the package, it must be qualified by the package name

if package is PACK1

PROCEDURE NAME IS PROC1(PI,P2,... .)

to execute

SQL> PACK1.PROC1(PI, P2,....)

[amoldiego]

Thanks for the answer ... But cud u plz tell me how to execute the given package ....I created Ref cusor type inside the package...
How to open that ref cursor inside the calling procedure ......
plz help

[debasisdas]

Once a ref cursor is declared with a package specification means it has global scope .

ans since your procedure inside the package has an OUT parameter it can't be executed from SQL pronpt.

For that u need to write an anonymous block.

[amoldiego]

That anonymous block i want ..cud u plz send it for given package .

[vergilfrans]

You can view the result in the cursor as follows in sql prompt.

VARIABLE io_cursor refcursor;
Execute package_name.pr ocedure_name(:i o_cursor);
print io_cursor;

(hope this will be useful for others because you must be an expert by now.)

[amitpatel66]

Try this:

[code=oracle]

DECLARE
ref_cur SYS_REFCURSOR;
e_name VARCHAR2(100);
salary NUMBER;
dept_no NUMBER:
BEGIN
ref_use.ref_pro (20,ref_cur);
LOOP
EXIT WHEN ref_cur%NOTFOUN D;
FETCH ref_cur INTO e_name,salary,d ept_no;
DBMS_OUTPUT.PUT _LINE(e_name||' ,'||salary||',' ||dept_no);
END LOOP;
END;
[/code]