-
how to execute the procedure
i want to execute this procedure in sql plus usnig exec statement
iam trying like this
declare
begin
exec pkg.procedurea
end;
but my question is what to pass the value of in out parameter in executing the procedure.
i have created the ref cursor type in package as below
CURSOR c1 IS
SELECT UD_USER_ID,
UD_USER_Name
FROM tablea
WHERE end_flag <> 'Y';
TYPE rc_cursor IS REF CURSOR RETURN c1%ROWTYPE;
name of the procedure to execute is below.
PROCEDURE procedureA
( pc_ref IN OUT pkg_ABC.rc_cursor)
AS
BEGIN
OPEN pc_ref
FOR SELECT UD_USER_ID,
UD_USER_Name
FROM tablea
WHERE end_flag <> 'Y';
END procedureA;
please help in this regard.
thanks in advance.
-
variable x refcursor;
exec :x = procedurea;
print :x
Jeff Hunter
-
Hi Jeff
Thanks for responding, but please see below what i got when i tried to execute it.
please help me in detail.
Thanks
SQL> variable x ref cursor;
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR ] ]
SQL> exec :x = pkg_fe_system_dw.p_fe_user;
BEGIN :x = pkg_fe_system_dw.p_fe_user; END;
*
ERROR at line 1:
ORA-06550: line 1, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ; indicator
ORA-06550: line 1, column 40:
PLS-00103: Encountered the symbol "END"
SQL> print :x
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "x"
SQL> /
variable x ref cursor;
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL>
-
Line 1 you typed wrong. SQL*Plus variable type is 'REFCURSOR' and *not* 'REF CURSOR' as it is in PL/SQL (confusing I know).
Line 2 is wrong. Assignment operator in PL/SQL is ':=' but in any case you are not trying to make a function call rather you are calling a procedure, so use...
Code:
EXEC procedurea (:x);
Perhaps a full example would help...
Code:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> CREATE OR REPLACE PACKAGE package_name
2 AS
3 TYPE ref_cursor_type IS REF CURSOR;
4
5 PROCEDURE procedure_name (
6 parameter_name OUT ref_cursor_type);
7
8 END package_name;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY package_name
2 AS
3 PROCEDURE procedure_name (
4 parameter_name OUT ref_cursor_type)
5 IS
6 BEGIN
7 OPEN parameter_name
8 FOR
9 SELECT empno, ename, job, sal
10 FROM emp;
11 END procedure_name;
12
13 END package_name;
14 /
Package body created.
SQL> VARIABLE x REFCURSOR;
SQL> EXEC package_name.procedure_name (:x);
PL/SQL procedure successfully completed.
SQL> PRINT :x;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
14 rows selected.
SQL>
-
Padders
Thank you very much for the script you have give,it helped in solving my wuery.
regards
sk
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|