469,267 Members | 1,508 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

how to execute package

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
Jun 18 '07 #1
6 101531
debasisdas
8,127 Expert 4TB
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,....)
Jun 18 '07 #2
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
Jun 18 '07 #3
debasisdas
8,127 Expert 4TB
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.
Jun 18 '07 #4
That anonymous block i want ..cud u plz send it for given package .
Jun 18 '07 #5
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.)
Feb 26 '09 #6
amitpatel66
2,367 Expert 2GB
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. ref_cur SYS_REFCURSOR;
  4. e_name VARCHAR2(100);
  5. salary NUMBER;
  6. dept_no NUMBER:
  7. BEGIN
  8. ref_use.ref_pro (20,ref_cur);
  9. LOOP
  10. EXIT WHEN ref_cur%NOTFOUND;
  11. FETCH ref_cur INTO e_name,salary,dept_no;
  12. DBMS_OUTPUT.PUT_LINE(e_name||','||salary||','||dept_no);
  13. END LOOP;
  14. END;
  15.  
Feb 26 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by CodeRazor | last post: by
3 posts views Thread by Peter Afonin | last post: by
2 posts views Thread by juventusaurabh | last post: by
1 post views Thread by juventusaurabh | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.