1 2 Previous Next 16 Replies Latest reply: Jul 26, 2013 9:07 AM by michaelrozar17 RSS

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

    marco

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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