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,deptno 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.procedure_name(:io_cursor);
print io_cursor;

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

[amitpatel66]

Try this:

 
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%NOTFOUND;
FETCH ref_cur INTO e_name,salary,dept_no;
DBMS_OUTPUT.PUT_LINE(e_name||','||salary||','||dept_no);
END LOOP;
END;