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.