Thursday, September 25, 2014

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 :)


No comments:

Post a Comment