CODEWITHSUNDEEP
oracle-databaseplsql
steve
steve

Reputation: 362

How get dynamic query data into CLOB column in Oracle

I want to insert the select query data into a CLOB column as a comma-separated value. Below is the anonymous block that I've created to do this. I'm using dbms_sql to fetch all the table headers. Now, I want to do the same for the table rows so that it will work with any query. I'm stuck into the logic. Any help would be appreciated.

 declare
l_clob  clob;
l_row   varchar2(4000);
v_sql varchar2(32767) := 'select * from all_tables';
out varchar2(6000);
out2 varchar2(6000);
v_cursor_id integer;
v_col_cnt integer;
v_columns dbms_sql.desc_tab;

 begin

    v_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native);
    dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);

-- get header data
    for i in 1 .. v_columns.count loop
        out:= v_columns(i).col_name;
         out2:=out2||','||out;
    end loop;
out2:= LTRIM(out2,',') ||chr(10);

 dbms_lob.createtemporary(l_clob, true);


   dbms_lob.writeappend (l_clob, length(out2), out2);

--get table rows

--DBMS_SQL.FETCH_ROWS ??

 -- for i in ( select * from user_tables ) loop

  --l_row := i.table_name||','||i.num_rows||','||i.blocks||chr(10);

 --dbms_lob.writeappend (l_clob, length(l_row), l_row);  end loop;

  insert into convert_clob values ('demo.csv' ,'application/vnd.ms-excel',l_clob );  -- l_clob = clob column
    commit;

dbms_lob.freetemporary(l_clob);


dbms_sql.close_cursor(v_cursor_id);
exception when others then
dbms_sql.close_cursor(v_cursor_id);
raise;

  end;
  /

Upvotes: 1

Views: 997

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

I use this standard procedure to display the result of any dynamic query, with the header. I have borrowed some of the code from this Ask TOM post.

create or replace procedure return_result( l_query varchar2 )
   is
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(4000);
       l_status        integer;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );

       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );


           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_output.put( l_separator || l_descTbl(i).col_name );
               l_separator := ',';
           end loop;
           dbms_output.put_line('');

        for i in 1 .. l_colCnt loop
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
       end loop;
       l_status := dbms_sql.execute(l_theCursor);

       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put( l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           dbms_output.new_line;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   end;
 /

Modify it to suit your requirement, something like

l_clob := l_clob ||  l_separator || l_descTbl(i).col_name ;

for headers, and

l_clob := l_clob || l_separator || l_columnValue;

for rows

DEMO

Upvotes: 1

Related Questions