Skip to Main Content
  • Questions
  • How to extract table data into CSV file dynamically using generic procedure

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Shiva.

Asked: November 28, 2019 - 6:52 am UTC

Answered by: Connor McDonald - Last updated: November 29, 2019 - 2:19 am UTC

Category: PL/SQL - Version: 12c

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Transaction subtleties

You Asked

Hi,

Need help on how to generate the CSV file for the given tablename dynamically using PLSQL procedure.
I understand we can use UTL_FILE oracle package to generate the CSV file however I would like to know how we can create generic script which will accept the <TABLE NAME> as the parameter and extract the all column data into given CSV file.

Thanks,
Shiva

and we said...

Here's a script that takes any query and presents the data in tabular format.

set serverout on size 999999
set verify off
declare
p_query varchar2(32767) := q'{&1}';

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    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
        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
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/

set verify on



You can easily just manipulate this to output the columns as CSV, or any other format you like.

However, don't forget that you could always just use 'sqlcl' on the server, and just run queries as:

select /*csv*/ * from table

or on modern versions of SQLPlus you can do:

SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]


More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.