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.
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.
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
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;
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;