Thanks for the question, Tushar.

Asked: March 03, 2016 - 7:56 am UTC

Last updated: June 18, 2019 - 3:22 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I have written a procedure which extracts data from tables into a csv file. The tables and columns to be extracted are stored in two tables. My code picks up the table name and column name builds a dynamic query and then writes it to csv file. I have to optimise this. In one batch around 10 tables would be extracted but the each of the underlying tables whose data is extracted would be a million. I was trying to bulk collect this but for that how do i create a record based on tables which are going to change with every loop(dynamic record)

my code is as below:

l_query := ' SELECT ' || v_col_List || ' FROM '|| K.TABLE_NAME;

DBMS_OUTPUT.PUT_LINE(''||l_query);
v_location := '9';
DBMS_SQL.PARSE( l_theCursor, l_query, DBMS_SQL.NATIVE );

v_location := '9.1';
DBMS_SQL.DESCRIBE_COLUMNS( l_theCursor, l_colCnt, l_descTbl );

l_separator := '';

-- writting column headers into the file
FOR i in 1..l_colCnt
LOOP
v_location := '9.2';
UTL_FILE.PUT( v_file_data, l_separator || '"' || l_descTbl(i).col_name || '"' );
v_location := '9.3';
DBMS_SQL.DEFINE_COLUMN( l_theCursor, i, l_columnValue, 4000 );
v_location := '9.4';
l_separator := v_column_separator;
END LOOP;

v_location := '9.5';
UTL_FILE.NEW_LINE( v_file_data );

v_location := '9.6';
l_status := DBMS_SQL.EXECUTE(l_theCursor);

-- creating an array equal to number of columns for extraction
v_location := '9.7';
t_trailer_rec.EXTEND(l_colCnt);

-- writting column values into the file
v_location := '10';
WHILE ( DBMS_SQL.FETCH_ROWS(l_theCursor) > 0 )
LOOP
l_separator := '';
FOR i in 1..l_colCnt
LOOP
v_location := '10.1';
DBMS_SQL.COLUMN_VALUE( l_theCursor, i, l_columnValue );
UTL_FILE.PUT( v_file_data, l_separator ||'"'|| l_columnValue ||'"');
l_separator := v_column_separator;

-- accumalating trailer record data
SELECT COUNT(1)
INTO v_summation_flag
FROM BL_TXN_DT_REPORT_CONFIG
WHERE COLUMN_NAME = l_descTbl(i).col_name
AND TABLE_ID = K.TABLE_ID
AND SUMMATION = 'Y';

v_location := '10.2';
-- only numerical columns marked for summation will be summed up
IF l_descTbl(i).COL_TYPE IN (2) -- NUMBER,INTEGER
AND v_summation_flag = 1
THEN
v_location := '10.3';
t_trailer_rec(i) := TO_NUMBER(NVL(t_trailer_rec(i),0)) + l_columnValue;
ELSE
v_location := '10.5';
t_trailer_rec(i) := NULL;
END IF;

END LOOP;

v_location := '10.6';
UTL_FILE.NEW_LINE( v_file_data );
v_rec_cnt := v_rec_cnt + 1;

END LOOP;

and Connor said...

Here's a little demo. You need an array of array's, but other than that, it's not too hard to setup

set serverout on size 999999
declare
    p_query varchar2(32767) := 'select * from emp';

    type column_array is table of DBMS_SQL.VARCHAR2_TABLE index by pls_integer;
    l_col_array column_array;
    l_empty_table DBMS_SQL.VARCHAR2_TABLE;

    l_theCursor     integer default dbms_sql.open_cursor;

    l_columnValue   varchar2(4000);
    l_status        integer;
    l_row_count     integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_fetch_size    int := 10;
    n number := 0;
    l_total_rows    number := 0;
    
begin
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        l_col_array(i) := l_empty_table;
        dbms_sql.define_array(l_theCursor, i, l_col_array(i), l_fetch_size, 1);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    loop
      l_row_count := dbms_sql.fetch_rows(l_theCursor);

      if l_row_count > 0 then
        dbms_output.put_line('Fetched '||l_row_count||' rows');
        for i in 1 .. l_colCnt loop
            --l_col_array(i).delete;
            dbms_sql.column_value( l_theCursor, i, l_col_array(i) );
        end loop;
        
        -- Display the data.
        for i in 1 .. l_row_count loop
           for j in 1 .. l_colCnt loop
              -- We need to offset the row index to account for previous batches
              -- otherwise we get duplicate data. Ask me how I know!  
              dbms_output.put_line(l_col_array(j)(i + l_total_rows));
           end loop;        
        end loop;
        
      end if;
      
      exit when l_row_count < l_fetch_size;
      
      -- And adjust the row offset.
      total_rows := total_rows + l_row_count;
    end loop;

    DBMS_SQL.CLOSE_CURSOR(l_theCursor);

end;
/

Rating

(5 ratings)

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

Comments

thanks Connor

Tushar, March 05, 2016 - 5:21 pm UTC

I added one dbms_output line to your code but getting error. Don't understand how to process the bulk collected value

if l_row_count > 0 then
dbms_output.put_line('Fetched '||l_row_count||' rows');
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_col_array(i) );
dbms_output.put_line('col_value'||l_col_array(i));
end loop;
end if;

March 06, 2016 - 4:16 am UTC

l_col_array is an array of arrays,

so

l_col_array(1) represents column #1
l_col_array(2) represents column #2

and so forth.

So

l_col_array(1)(1) represents the first value fetched for column 1
l_col_array(1)(2) represents the first value fetched for column 2
l_col_array(1)(3) represents the first value fetched for column 3

etc

Hope this helps.

However, if you want to extract the actual data ...

Norman Dunbar, November 30, 2018 - 12:59 pm UTC

Hi Connor,

I'm late to the party again.

I used the code in the original example to do some dynamic SQL with bulk collect. I kept getting duplicate output. Not good.

The fix is to keep a running count of rows returned so far, and offset the 'i' in DBMS_SQL.COLUMN_VALUE, as follows:

set serverout on size 999999
declare
p_query varchar2(32767) := 'select * from emp';

type column_array is table of DBMS_SQL.VARCHAR2_TABLE index by pls_integer;
l_col_array column_array;
l_empty_table DBMS_SQL.VARCHAR2_TABLE;

l_theCursor integer default dbms_sql.open_cursor;

l_columnValue varchar2(4000);
l_status integer;
l_row_count integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_fetch_size int := 10;
n number := 0;
l_total_rows number := 0;

begin
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
l_col_array(i) := l_empty_table;
dbms_sql.define_array(l_theCursor, i, l_col_array(i), l_fetch_size, 1);
end loop;

l_status := dbms_sql.execute(l_theCursor);

loop
l_row_count := dbms_sql.fetch_rows(l_theCursor);

if l_row_count > 0 then
dbms_output.put_line('Fetched '||l_row_count||' rows');
for i in 1 .. l_colCnt loop
--l_col_array(i).delete;
dbms_sql.column_value( l_theCursor, i, l_col_array(i) );
end loop;

-- Display the data.
for i in 1 .. l_row_count loop
for j in 1 .. l_colCnt loop
-- We need to offset the row index to account for previous batches
-- otherwise we get duplicate data. Ask me how I know!
dbms_output.put_line(l_col_array(j)(i + l_total_rows));
end loop;
end loop;

end if;

exit when l_row_count < l_fetch_size;

-- And adjust the row offset.
total_rows := total_rows + l_row_count;
end loop;

DBMS_SQL.CLOSE_CURSOR(l_theCursor);

end;
/

(Sorry about the formatting, it got eaten by the review submission process!)

HTH

Cheers,
Norm.

Formatting fixed. Apologies.

Norman Dunbar, November 30, 2018 - 1:01 pm UTC

Sorry, I can't read either. I fixed the formatting. (Sigh!)

set serverout on size 999999
declare
    p_query varchar2(32767) := 'select * from emp';

    type column_array is table of DBMS_SQL.VARCHAR2_TABLE index by pls_integer;
    l_col_array column_array;
    l_empty_table DBMS_SQL.VARCHAR2_TABLE;

    l_theCursor     integer default dbms_sql.open_cursor;

    l_columnValue   varchar2(4000);
    l_status        integer;
    l_row_count     integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_fetch_size    int := 10;
    n number := 0;
    l_total_rows    number := 0;
    
begin
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        l_col_array(i) := l_empty_table;
        dbms_sql.define_array(l_theCursor, i, l_col_array(i), l_fetch_size, 1);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    loop
      l_row_count := dbms_sql.fetch_rows(l_theCursor);

      if l_row_count > 0 then
        dbms_output.put_line('Fetched '||l_row_count||' rows');
        for i in 1 .. l_colCnt loop
            --l_col_array(i).delete;
            dbms_sql.column_value( l_theCursor, i, l_col_array(i) );
        end loop;
        
        -- Display the data.
        for i in 1 .. l_row_count loop
           for j in 1 .. l_colCnt loop
              -- We need to offset the row index to account for previous batches
              -- otherwise we get duplicate data. Ask me how I know!  
              dbms_output.put_line(l_col_array(j)(i + l_total_rows));
           end loop;        
        end loop;
        
      end if;
      
      exit when l_row_count < l_fetch_size;
      
      -- And adjust the row offset.
      total_rows := total_rows + l_row_count;
    end loop;

    DBMS_SQL.CLOSE_CURSOR(l_theCursor);

end;
/

December 03, 2018 - 3:29 am UTC

Thanks for the update. I've replaced our original answer with yours.

Awesome example of using dbms_sql with bulk collect

A reader, February 20, 2019 - 10:20 am UTC

Great work ..please keep doing it.

Thanks a lot.

February 20, 2019 - 11:51 am UTC

You're welcome

jron, June 14, 2019 - 10:03 am UTC

<more code>
loop
      l_row_count := dbms_sql.fetch_rows(l_theCursor);

      if l_row_count > 0 then
        dbms_output.put_line('Fetched '||l_row_count||' rows');
        for i in 1 .. l_colCnt loop
            --l_col_array(i).delete;
            dbms_sql.column_value( l_theCursor, i, l_col_array(i) );
        end loop;
        
        -- Display the data.
        for i in 1 .. l_row_count loop
           for j in 1 .. l_colCnt loop
              -- We need to offset the row index to account for previous batches
              -- otherwise we get duplicate data. Ask me how I know!  
              dbms_output.put_line(l_col_array(j)(i + l_total_rows));
           end loop;        
        end loop;
        
      end if;
      
      exit when l_row_count < l_fetch_size;
      
      -- And adjust the row offset.
      total_rows := total_rows + l_row_count;
    end loop;
   
   <more code>

Assuming a generic procedure/function will be created based on above code, if there are a few millions of records to be printed (e.g., in a file) then that means all records will be loaded in the array once the last batch of rows are retrieved, right? Wouldn't it be better to refresh the array before retrieving another set of records?

   <more code>
   
loop

 -- define_array in the loop
 for i in 1 .. l_colCnt loop
    l_col_array(i) := l_empty_table;
    dbms_sql.define_array(l_theCursor, i, l_col_array(i), l_fetch_size, 1);
 end loop;

 l_row_count := dbms_sql.fetch_rows(l_theCursor);

 if l_row_count > 0 then

    for i in 1 .. l_colCnt 
    loop
       dbms_sql.column_value(l_theCursor, i, l_col_array(i));
    end loop;

    -- Display the data.
    for i in 1 .. l_row_count 
    loop

       v_Separator := '';         
       for j in 1 .. l_colCnt 
       loop
          -- no need to offset row index 
          utl_file.put(v_Separator || l_col_array(j)(i));
          v_Separator := p_Separator;
       end loop;

       utl_file.put_line('');
       l_total_rows := l_total_rows + l_row_count;

    end loop;

 end if;

 exit when l_row_count < l_fetch_size;

end loop;
   
   <more code>

June 18, 2019 - 3:22 am UTC

Agreed. Depends on what you want to achieve here, ie, get the entire result set, or loop through it and discard processed data.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

thanks Connor

However, if you want to extract the actual data ...

Formatting fixed. Apologies.

Awesome example of using dbms_sql with bulk collect

More to Explore

PL/SQL demos

PL/SQL docs

bulk collect through dynamic sql

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

thanks Connor

However, if you want to extract the actual data ...

Formatting fixed. Apologies.

Awesome example of using dbms_sql with bulk collect

More to Explore

PL/SQL demos

PL/SQL docs