Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, M K.

Asked: May 08, 2017 - 11:30 am UTC

Last updated: May 09, 2017 - 2:46 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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;

and Connor said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here