1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creating procedure and get returned data

Discussion in 'SQL PL/SQL' started by tranceri_ku, May 19, 2009.

  1. tranceri_ku

    tranceri_ku Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,

    I want to create procedure that will return a set of data (to execute a select statement) and i want to view those returned data.

    How to create this procedure and to view returned data?

    Thnx.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Basically it can be achieved in SQL*plus by using DBMS_OUTPUT package (also by setting serveroutput) as follows.

    Code (SQL):
    SQL> SET SERVEROUT ON
    SQL> CREATE OR REPLACE PROCEDURE DISP_OP (var_ip_1 IN varchar2 ,
      2                                       var_ip_2 IN varchar2 ,
      3                                       var_op_1 OUT varchar2 )
      4  AS
      5  BEGIN
      6
      7      var_op_1 := var_ip_1 ||' '||var_ip_2;
      8
      9  END ;
     10  /

    PROCEDURE created.

    SQL> DECLARE
      2   Var1 VARCHAR2(50):= 'Hello';
      3   Var2 VARCHAR2(50):= 'World';
      4   Var3 VARCHAR2(100);
      5  BEGIN
      6   DISP_OP(Var1,Var2,Var3);
      7   DBMS_OUTPUT.PUT_LINE('Output is : '||Var3);
      8  END;
      9  /
    Output IS : Hello World

    PL/SQL PROCEDURE successfully completed.
     
    Also by using Bind variable and setting Auto print option ..

    Code (SQL):
    SQL> SET Autoprint ON
    SQL> var output_v varchar2(100)
    SQL> EXEC DISP_OP('Hello','World',:output_v);

    PL/SQL PROCEDURE successfully completed.


    OUTPUT_V
    ----------------------------------------------------------------------

    Hello World

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or do you want to display the entire result set of select statement as output in the screen? Then you go for refcursor with print as below.

    Code (SQL):

    SQL> SET autoprint off
    SQL> CREATE OR REPLACE PROCEDURE DISP_REF_OP (var_ip_1 IN  NUMBER,
      2                                           var_op_1 OUT SYS_REFCURSOR )
      3  AS
      4  BEGIN
      5   OPEN var_op_1 FOR SELECT ENAME, DEPTNO, SAL FROM EMP WHERE DEPTNO=var_ip_1;
      6  END ;
      7  /

    PROCEDURE created.

    SQL> VAR cur1 REFCURSOR;
    SQL> EXEC DISP_REF_OP(10,:cur1);

    PL/SQL PROCEDURE successfully completed.

    SQL> print cur1;

    ENAME          DEPTNO        SAL
    ---------- ---------- ----------
    CLARK              10       2450
    KING               10       5000
    MILLER             10       1300

    SQL>
     

    Also it can be done with autoprint option

    Code (SQL):

    SQL> SET autoprint ON
    SQL> EXEC DISP_REF_OP(20,:cur1);

    PL/SQL PROCEDURE successfully completed.


    ENAME          DEPTNO        SAL
    ---------- ---------- ----------
    SMITH              20        800
    JONES              20       2975
    SCOTT              20       3000
    ADAMS              20       1100
    FORD               20       3000

    SQL>
     
     
  4. tranceri_ku

    tranceri_ku Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks guys.