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!

select from stored procedure results?

Discussion in 'SQL PL/SQL' started by jseesel, Sep 27, 2010.

  1. jseesel

    jseesel Guest

    I am trying to create a stored procedure to populate a table that needs to call another stored procedure for some data.

    I basically want to do

    select *
    from STORED_PROCEDURE

    and use the data that it returns but I can't get the syntax to work out. Is this possible in oracle?
     
  2. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    you cannot do that i believe. You can use out or in out parameters and use those paramters in the VALUES clause of an INSERT INTO statements. Basically you can do it in a PLSQL block
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It isn't the way you intended to do it, but it can be possible. You can use a ref cursor as an in out parameter, open the cursor for the desired select in the second stored procedure and then select from the returned ref cursor in the first procedure:

    Code (SQL):
    CREATE OR REPLACE package my_package IS
    TYPE refcursor IS REF cursor;
    PROCEDURE proc1(p_job IN varchar2, p_cur IN OUT refcursor);
    END;
    /
    CREATE OR REPLACE package body my_package IS
    PROCEDURE proc1(p_job IN varchar2, p_cur IN OUT refcursor) AS
    l_query varchar2(255);
    BEGIN
    l_query := 'select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job = '''||p_job||'''';
    OPEN p_cur FOR l_query;
    END;
    END;
    /
    SHOW errors
    SET serveroutput ON SIZE 1000000
    DECLARE
    TYPE rcursor IS REF cursor;
    emptab rcursor;
    emprec emp%rowtype;
    BEGIN
    my_package.proc1('CLERK',emptab);
    loop
    fetch emptab INTO emprec;
    exit WHEN emptab%notfound;
    dbms_output.put_line(emprec.ename||' with employee number '||emprec.empno||' works in department number '||emprec.deptno);
    dbms_output.put_line('Hired on '||emprec.hiredate);
    END loop;
    END;
    /
     
     
    Notice that you 'load' the ref cursor in the first procedure, pass it back to the PL/SQL block (or another procedure) and fetch from it as you would a declared cursor. This allows you to pass query results from one stored procedure to another.

    You can also create pipelined functions and select directly (in a fashion) from them; read here

    http://psoug.org/reference/pipelined.html

    to see demo code for such procedures.
     
  4. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    In oracle, selection of data is possible only from Tables, not from program units.

    Instead, you can have ref cursor as one of the parameter of procedure or return from the function. Once it gets invoked, fetch the data from the ref cursor.
    Return a table collection type from the function.

    Code (SQL):
    SQL> CREATE OR REPLACE TYPE tabfun AS TABLE OF NUMBER;
      2  /

    TYPE created.

    SQL> ed
    Wrote file afiedt.buf

      1  CREATE OR REPLACE FUNCTION func RETURN tabfun IS
      2  l_rec tabfun := tabfun(100,200,300);
      3  BEGIN
      4  RETURN l_rec;
      5* END;
    SQL> /

    FUNCTION created.

    SQL> SELECT * FROM TABLE(func);

    COLUMN_VALUE
    ------------
             100
             200
             300

    Other options is to have session level Global temp table, where data can be inserted inside your procedure. After it is invoked, you can select from the GTT.

    Other option is to have pipelined function. Get the data in form of pipelined row. Select from the function using TABLE function and you can get your desired data.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do believe a pipelined function is a program unit.
     
  6. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Ya David, I agree Pipelined function is also a program unit, but this is way to get the pipelined data in form of table, similar to casting or selection of data from an object.
    I referred it as a concept that data selection can be made only from Database table, which might be object or relational.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are selecting from the function; the TABLE() operator simply is a step to format the byte stream to make the output a bit more user-friendly:

    http://oratips-ddf.blogspot.com/2009/12/in-pickle.html
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your original statement clearly states

    "In oracle, selection of data is possible only from Tables, not from program units. "

    which neglects views (wihch are not tables), other queries (wihch are not tables) and pipelined functions (which are not tables but are program units). Your statement, as you made it, is, unfortunately, incorrect.