Extract/Export Data from Tables into flat files...

We all know that the best and fastest way of exporting data from a table(s) in Oracle is using either EXP or EXPDP.
What if there is a need to fetch records based on some conditions or for certain criteria etc.,? Well, EXP and EXPDP supports with WHERE clause right so whats the big deal? hmm... let me think of another reason...
Yeah, how about exporting data from a query which joins more than one table??? Yes. neither EXP or EXPDP cant do that.
Also, with EXPDP you need to have an access to directory that is defined in the database.

Anyways, lets get to the point.
What I want to show in this post is, a way to extract data from any table dynamically using two options:
1. using plain old DBMS_SQL method to execute Dynamic SQL and
2. using combination of DBMS_SQL and NDS (Native Dynamic SQL) with EXECUTE IMMEDIATE.

Don't want to go too deep in to each (you can read manual if interested in knowing every single bit of it).
Here is the sample code demonstrating 1st option using only DBMS_SQL.
High Level Steps:
1. Form a Query
2. Execute Query
3. Fetch the Results

declare

t_cursor integer default dbms_sql.open_cursor;

t_sql varchar2(4000) := 'select * from user_tables' ; --> this query here can be built dynamically

t_col_count number;

t_col_value varchar2(4000);

t_desc_tbl dbms_sql.desc_tab;

t_exec_status integer;

t_rec_count number := 0;

t_col_separator char(2) := '||';

t_record varchar2(32567);

t_column_string varchar2(4000) := null;

begin

  --parse SQL

  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL

  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);

  --Show column names from the t_desc_tbl

  for x in 1 .. t_col_count loop

    t_column_string := t_column_string ||t_desc_tbl(x).col_name|| ',';

  end loop;

  dbms_output.put_line('Columns => '||rtrim(t_column_string,','));

     

  dbms_output.put_line('Fetching define '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  --define the output for the columns

  for j in 1..t_col_count loop

    dbms_sql.define_column(t_cursor, j, t_col_value, 4000);

    dbms_output.put_line('Column Value '||t_col_value);

  end loop;

  dbms_output.put_line('Execute '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  --execute the SQL

  t_exec_status := dbms_sql.execute(t_cursor);

     

  --Fetch the records from the above execution

  dbms_output.put_line('Fetching Rows '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));

  while dbms_sql.fetch_rows(t_cursor) > 0

  loop

    t_rec_count := t_rec_count + 1;

    for k in 1..t_col_count loop

      dbms_sql.column_value(t_cursor, k, t_col_value);

      t_record := t_record||t_col_value||t_col_separator;                   

    end loop;

    dbms_output.put_line('Record# '||t_rec_count||'=> '||rtrim(t_record,t_col_separator));

    t_record := null;

  end loop;

  dbms_sql.close_cursor(t_cursor);

end;

/ 

Here is the sample code using DBMS_SQL along with EXECUTE IMMEDIATE. 

High Level Steps:

1. Form a Query

2. Get the column(s) list from the Query

3. Re-form a query with individual Columns - showing an example to fetch the records as pipe delimited 

4. Execute and Fetch query using BULK COLLECT 

declare

t_cursor integer default dbms_sql.open_cursor;

t_sql varchar2(4000) := 'select * from user_tables' ;

t_col_count number;

t_desc_tbl dbms_sql.desc_tab;

t_rec_count number := 0;

t_col_separator char(2) := '||';

t_separator_csv varchar2(10) := null;

t_column_string varchar2(4000) := null;

t_bulk_limit number := 10000; --Define bulk collect limit

t_line_buffer varchar2(32767);

type csv_cur_type is ref cursor;

csv_cur csv_cur_type;

type myarray is table of varchar2(4000); --assuming the entire record length is 4000 bytes

t_data myarray;

begin

  --parse SQL

  dbms_sql.parse(t_cursor, t_sql, dbms_sql.native);

  --Get the column(s) list from the SQL

  dbms_sql.describe_columns(t_cursor, t_col_count, t_desc_tbl);

 

  --Close cursor as this is not needed anymore

  dbms_sql.close_cursor(t_cursor);

  --Show column names from the t_desc_tbl

  for x in 1 .. t_col_count loop

    t_column_string := t_column_string ||t_separator_csv||t_desc_tbl(x).col_name;

    t_separator_csv := '||''' || t_col_separator || '''||';

  end loop;

 

  dbms_output.put_line('Columns String to use in dynamic SQL');

  dbms_output.put_line(t_column_string);

 

  --now repalce select clause in SQL to use the above column string   

  --t_sql := regexp_replace(upper(t_sql), '^SELECT (.*?) FROM', 'SELECT ' || t_column_string || ' FROM');

  t_sql := replace(t_sql, ' * from', ' '||t_column_string || ' from');

  dbms_output.put_line('Actual SQL With Column String ');

  dbms_output.put_line(t_sql);

  --fetch records using bulk collect and process each row

  open csv_cur for t_sql;

  loop

    fetch csv_cur bulk collect

      into t_data limit t_bulk_limit;

    for j in 1 .. t_data.count loop

      begin

        if length(t_line_buffer || t_data(j)) >= 30000 then

          dbms_output.put_line(t_line_buffer);

          t_line_buffer := t_data(j);

        else

          if t_line_buffer is null then

            t_line_buffer := t_data(j);

          else

            t_line_buffer := t_line_buffer || chr(10) || t_data(j);

          end if;

        end if;

      end;

      t_rec_count := t_rec_count + 1;

    end loop;

    exit when csv_cur%notfound;

  end loop;

  close csv_cur;

  --show remaining records

  dbms_output.put_line(t_line_buffer);

end;

/

Now compare the results from option#1 and #2 and you will be amazed to see how best the 2nd option is using Execute Immediate with BULK COLLECT.

You can easily replace dbms_output with utl_file to write the content to a flat file on the server side.

Hope this helps me when I am in search of the samples :)