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

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