0

I am trying to extract data from tables in a delimiter separated value format. To extract data i am concatenating all the columns of the table with each other and then converting the concatenated record into binary and storing into a table. Below is the code that i am using

DECLARE
l_temp_blob blob;
begin
dbms_lob.createtemporary(l_temp_blob, TRUE);
For src in (select A||'~'||B||'~'||C||..........||'`'||Z AS DAT FROM <TABLE>)
LOOP
dbms_lob.append(l_temp_blob,utl_raw.cast_to_raw(src.DAT||chr(10)));
END LOOP;
insert into temp_table('FIle',l_temp_blob);
COMMIT;
END;

I am facing severe performance issue in this, a table with 88k records is getting extracted in 8 hours. My tables have CLOB columns in them along with VARCHAR2 and NUMBER columns.

Is there a better way to do this. Since we don't have direct access to server where database is installed we cannot use Oracle Export utility and Data Pump utility.

  • If you want a CSV export, then there are a gazillion client tools that can do that. It's even possible with sqlplus – a_horse_with_no_name May 8 at 6:38
  • There is a limitation of using PLSQL only. Also if you can share how to do it with sqlplus for tables with CLOB columns that would be really helpful. – Vishesh May 8 at 7:18
  • CSV is the wrong file format when dealing with CLOB data...or any data that could contain the "end of line" delimiter – Michael Kutz May 8 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.