This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 7:07 AM by michaelrozar17 RSS

remember result of select statement to use it few times in procedure

marco Newbie
Currently Being Moderated

Hi all,

 

I'm sorry for this kind of question, I'm not newbie, but still need your help.

 

My need is remember result of select statement to use it few times in procedure.

My first guess is to use temporary table, but I think there are better decisions.

 

For example, I should perform some heavy query

 

select id from table_function(param1)

 

Then this query is used to insert list of id into table1, delete from table2 and update in table3.

 

Help me please to avoid using temporary tables if possible.

  • 1. Re: remember result of select statement to use it few times in procedure
    Lalit Kumar B Explorer
    Currently Being Moderated

    You can use pipelined table function to fetch the data so that you do not have to wait for the PL/SQL collection to get ready till the last row is fetched. And then in a bunch/limit of lets say 1000 at a time do the DML on the respective tables. You can use FORALL and BULK operations for faster result. To handle the exceptions you would have to use SQL%BULK_EXCEPTIONS.

  • 2. Re: remember result of select statement to use it few times in procedure
    Lalit Kumar B Explorer
    Currently Being Moderated

    Alternatively, to my above mentioned approach, you can use Result Cache as well. It is a 11g feature. It is also applicable for cross session availability.

  • 3. Re: remember result of select statement to use it few times in procedure
    pollywog Expert
    Currently Being Moderated

    could you do your original query and collect its result into some kind of collection and then cast this collection into a table when you want to do your inserts, deletes etc.)

    DECLARE
       employee_ids   SYS.odcinumberlist;
    BEGIN
       SELECT employee_id
         BULK COLLECT INTO employee_ids
         FROM employees
        WHERE employee_id < 200;
    
       FOR c
          IN (SELECT *
                FROM employees e,
                     (SELECT COLUMN_VALUE FROM TABLE (employee_ids)) t
               WHERE e.employee_id = t.COLUMN_VALUE)
       LOOP
          DBMS_OUTPUT.put_line (c.employee_id || ' ' || c.first_name);
       END LOOP;
    END;
    
    
  • 4. Re: remember result of select statement to use it few times in procedure
    Lalit Kumar B Explorer
    Currently Being Moderated

    Pipelined table function would be better than this, OP can build the pipeline function as a dynamic sql and execute it wherever within the scope of the session for any type of DML.

  • 5. Re: remember result of select statement to use it few times in procedure
    Master105 Newbie
    Currently Being Moderated

    You can do is define a collection at session level for example in your package.

    Fill the Collection with the data you want.

    You can use this particular Collection as many times you want in your session.

  • 6. Re: remember result of select statement to use it few times in procedure
    marco Newbie
    Currently Being Moderated

    pollywog,

     

    how should I DECLARE if my select statement returns more than one column?

     

       SELECT column1, column2

         BULK COLLECT INTO .................

         FROM table1

        WHERE column3 = NNN;

  • 7. Re: remember result of select statement to use it few times in procedure
    michaelrozar17 Pro
    Currently Being Moderated

    Pipelined functions are not always better. I have seen in one of my application where a pipelined function is used to generate just numbers which is then used to derive date ranges. But the SELECT query that uses these dates seems to do better if the date is generated just by using DUAL and CONNECT BY clause.

    So we could say that it all depends how and where we implement. If the number of records that OP wanted to store in cache is minimal (maybe 10 or 100 etc) then it would be better to store it in a collection. Pipelined function could be used if there are thousands of rows.

    And do Pipelined function cache results so that the results could be re-used in more than one place in a procedure..?

  • 8. Re: remember result of select statement to use it few times in procedure
    pollywog Expert
    Currently Being Moderated

    I think in that scenario you will need a nested table because I don't think you can cast an associative array into a table.

    I believe the collections that can be cast into table are varray(the one in my example) and nested tables.

    so unfortunately you will need to define your nested table outside of the procedure.

     

    or you could cheat and select your column values into a varray where the column values are deliminated by something

    so select column1||'.'||column2  bulk collect into myvarray

     

    I do this .... er I mean I've heard some people do  this sometimes.

  • 9. Re: remember result of select statement to use it few times in procedure
    michaelrozar17 Pro
    Currently Being Moderated

    If there are more than one columns, you would need to create an Object type at database level. Then create a collection of that object type in the procedure.

    Example:

     

    CREATE OR REPLACE TYPE "OBJ1" as OBJECT(
            column1 varchar2(256 CHAR),
            column2 varchar2(35 CHAR)
            );
    CREATE OR REPLACE TYPE "nt_obj1" as table of OBJ1; -- this could be done at procedure level as well
    DECLARE
       t_employee_ids   nt_obj1;
    BEGIN
       SELECT OBJ1(column1,column2)
         BULK COLLECT INTO  t_employee_ids
         FROM table1
        WHERE column3 = NNN
    .............
    .....
  • 10. Re: remember result of select statement to use it few times in procedure
    pollywog Expert
    Currently Being Moderated

    think I'm missing something here but I can't quite figure it out

    CREATE TYPE employee_info AS OBJECT
    (
       employee_id NUMBER (6),
       employee_first_name VARCHAR2 (20 BYTE),
       employee_last_name VARCHAR2 (25 BYTE)
    );
    
    
    CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;
    
    
    
    DECLARE
       employee_ids   employee_info_table;
    BEGIN
       SELECT EMPLOYEES.EMPLOYEE_ID,
              EMPLOYEES.FIRST_NAME,
              EMPLOYEES.LAST_NAME  BULK COLLECT
         INTO employee_ids (employee_id, employee_first_name, employee_last_name)
         FROM employees
        WHERE employee_id < 200;
    END;
    
    ORA-06550: line 7, column 11:
    PLS-00316: PL/SQL TABLEs must use a single index
    ORA-06550: line 8, column 6:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 4, column 4:
    PL/SQL: SQL Statement ignored
    
  • 11. Re: remember result of select statement to use it few times in procedure
    padders Pro
    Currently Being Moderated

    More like this (untested)...

     

    DECLARE
       employee_ids employee_info_table;
    BEGIN
       SELECT employee_ids (
                 e.employee_id,
                 e.first_name,
                 e.last_name)
       BULK COLLECT
       INTO employee_ids
       FROM employees e
       WHERE employee_id < 200;
    END;
    
  • 12. Re: remember result of select statement to use it few times in procedure
    pollywog Expert
    Currently Being Moderated

    never mind I got it

    drop type employee_info_table;
    drop type employee_info;
    CREATE TYPE employee_info AS OBJECT
    (
       employee_id NUMBER (6),
       first_name VARCHAR2 (20 BYTE),
       last_name VARCHAR2 (25 BYTE)
    );
    
    
    CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;;
    
    
    
    DECLARE
       employee_ids   employee_info_table;
    BEGIN
    
       SELECT employee_info(employee_id, first_name, last_name)
        bulk collect INTO employee_ids
         FROM employees
        WHERE employee_id < 200
        order by employee_id;
    END;
    
  • 13. Re: remember result of select statement to use it few times in procedure
    marco Newbie
    Currently Being Moderated

    michaelrozar17, pollywog,

     

     

    Great!

    How do I use the content of t_employee_ids (michaelrozar17's) employee_ids (pollywog's) after inserting data in my next query?

    Can I perform something like

     

     

    select count(1) into VARIABLE from t_employee_ids

    ?

  • 14. Re: remember result of select statement to use it few times in procedure
    pollywog Expert
    Currently Being Moderated

    ok I think it is something like this

    CREATE TYPE employee_info AS OBJECT
    (
       employee_id NUMBER (6),
       first_name VARCHAR2 (20 BYTE),
       last_name VARCHAR2 (25 BYTE)
    );
    
    
    CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;
    
    ;
    
    
    
    DECLARE
       employee_ids   employee_info_table;
    BEGIN
         SELECT employee_info (employee_id, first_name, last_name)
           BULK COLLECT INTO employee_ids
           FROM employees
          WHERE employee_id < 200
       ORDER BY employee_id;
    
    
       FOR c IN (SELECT e.*
                   FROM employees e, TABLE (employee_ids) t
                  WHERE e.employee_id = t.employee_id)
       LOOP
          DBMS_OUTPUT.put_line (c.employee_id || ' ' || c.first_name);
       END LOOP;
    END;
    
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points