how to execute the procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: how to execute the procedure

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Posts
    3

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    variable x refcursor;
    exec :x = procedurea;
    print :x
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2004
    Posts
    3

    Unhappy

    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>

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    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>

  5. #5
    Join Date
    Aug 2004
    Posts
    3

    Thumbs up

    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
  •  



Click Here to Expand Forum to Full Width