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