Best way to store refcursor to physical table without impacting performance. I do have a code built, but it takes too much time when data is huge. Also this code doesn't seems to be scalable as it continuously creates temporary tables for each different cursor passed. Any suggestion to improvise this will be great.
create or replace procedure unit_test
(p_cur_data in oUT SYS_REFCURSOR,
p_temp_tab_param in number)
as
lv_cur_pntr NUMBER;
lv_nbr_of_cols NUMBER;
lv_out_col_desc DBMS_SQL.DESC_TAB;
lv_build_gtt_struct CLOB;
lv_sql CLOB;
lv_gtt_tab_nm ALL_TABLES.TABLE_NAME%TYPE := 'ZZ_data'||p_temp_tab_param;
lv_varchar2 VARCHAR2(4000);
lv_number NUMBER;
lv_date DATE;
lv_data CLOB;
begin
lv_cur_pntr := DBMS_SQL.to_cursor_number(p_cur_data);
DBMS_SQL.DESCRIBE_COLUMNS(lv_cur_pntr, lv_nbr_of_cols, lv_out_col_desc);
begin
EXECUTE IMMEDIATE 'DROP TABLE '||lv_gtt_tab_nm;
exception
when others then
null;
end;
--lv_build_gtt_struct := 'CREATE GLOBAL TEMPORARY TABLE '||lv_gtt_tab_nm||'(';
lv_build_gtt_struct := 'CREATE TABLE '||lv_gtt_tab_nm||'(';
for i in lv_out_col_desc.first .. lv_out_col_desc.last
loop
lv_build_gtt_struct := lv_build_gtt_struct ||lv_out_col_desc(i).col_name||' '||
case
when lv_out_col_desc(i).col_type in (2,8) THEN 'NUMBER'
when lv_out_col_desc(i).col_type in (12) THEN 'DATE'
when lv_out_col_desc(i).col_type in (11) THEN 'ROWID'
when lv_out_col_desc(i).col_type in (23) THEN 'RAW'
when lv_out_col_desc(i).col_type in (23) THEN 'LONG RAW'
ELSE 'VARCHAR2(500)' END||',';
end loop;
--lv_build_gtt_struct := rtrim(lv_build_gtt_struct,',')||') ON COMMIT DELETE ROWS';
lv_build_gtt_struct := rtrim(lv_build_gtt_struct,',')||') tablespace abcdefg';
dbms_output.put_line(lv_build_gtt_struct);
EXECUTE IMMEDIATE lv_build_gtt_struct;
FOR i IN 1 .. lv_nbr_of_cols
LOOP
IF lv_out_col_desc(i).col_type IN (2,8) THEN
DBMS_SQL.DEFINE_COLUMN(lv_cur_pntr, i, lv_number);
ELSIF lv_out_col_desc(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(lv_cur_pntr, i, lv_date);
ELSE
DBMS_SQL.DEFINE_COLUMN(lv_cur_pntr, i, lv_varchar2, 4000);
END IF;
END LOOP;
WHILE DBMS_SQL.FETCH_ROWS(lv_cur_pntr) > 0 LOOP
lv_data := NULL;
FOR i IN 1 .. lv_nbr_of_cols
LOOP
IF lv_out_col_desc(i).col_type in (2,8) THEN
DBMS_SQL.COLUMN_VALUE(lv_cur_pntr, i, lv_number);
lv_data := lv_data ||nvl(lv_number,0)||',';
ELSIF lv_out_col_desc(i).col_type = 12 THEN
DBMS_SQL.COLUMN_VALUE(lv_cur_pntr, i, lv_date);
lv_data := lv_data ||''''||nvl(lv_date,'17-DEC-9999')||''',';
ELSE
DBMS_SQL.COLUMN_VALUE(lv_cur_pntr, i, lv_varchar2);
lv_data := lv_data||''''||replace(nvl(lv_varchar2,'INVALIDINVALID'),'''','')||''',';
END IF;
END LOOP;
--dbms_output.put_line('INSERT INTO '||lv_gtt_tab_nm|| ' VALUES ('||rtrim(lv_data,',')||')');
EXECUTE IMMEDIATE 'INSERT INTO '||lv_gtt_tab_nm|| ' VALUES ('||rtrim(lv_data,',')||')';
END LOOP;
DBMS_SQL.CLOSE_CURSOR(lv_cur_pntr);
COMMIT;
end;
I'm not sure what you're trying to achieve here.
Your code seems to:
- take a cursor
- create a temp table based on it
- store the data in that temp table
My question is why ?
If you need to hold that data just a little while, then you can use a flashback query to get the data repeatedly at that point in time. If you need to hold that that data for a long period of time, then I would have thought this is not something that would make a lot of sense for *arbitrary* SQL statements.
In any case, if your requirement is legitimate for whatever reason, then either
a) recode the plsql solution to use array fetching and array inserting. Example here
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:455220177497 b) Rather than pass the cursor, pass the originating SQL, and perform a CTAS operation with it