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.
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.
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.)
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.
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.
how should I DECLARE if my select statement returns more than one column?
SELECT column1, column2
BULK COLLECT INTO .................
WHERE column3 = NNN;
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..?
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.
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.
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 ............. .....
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
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;
1 person found this helpful
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;
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
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;