1) Export data to text mode from oracle db
Note: use the "sql developer" which could be downloaded from Oracle Web Site. It is a good tool to export data, however, still many limits. Title: Spool or UTL_FILE ? 1@@@@ Use Spool by sqlplus. HR@ocm> !cat tmpx.sql spool spool_emp.dat set feedback off set linesize 240 set termout off set heading off SELECT ROWNUM + 100000 AS employee_id , first_name , last_name , email , phone_number , to_char(hire_date,'yyyy-mm-dd') --You had batter do it. , job_id , salary , commission_pct , manager_id , department_id FROM hr.employees , (SELECT ROWNUM AS r FROM dual CONNECT BY ROWNUM <= 107) WHERE ROWNUM <= 107; spool off HR@ocm> @tmpx.sql @@@ @@@Use AWK to modify the output, method 1 In this method, I use printf, mean print as my assigned format. [oracle@station78 ~]$ cat awk_configfile01.awk BEGIN{ FS=" ";} { printf "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10 } [oracle@station78 ~]$ cat spool_emp.dat | tail -n 5 | awk -f awk_configfile01.awk 100103,Britney,Everett,BEVERETT,650.501.2876,1997-03-03,SH_CLERK,3900,123,50 100104,Samuel,McCain,SMCCAIN,650.501.3876,1998-07-01,SH_CLERK,3200,123,50 100105,Vance,Jones,VJONES,650.501.4876,1999-03-17,SH_CLERK,2800,123,50 100106,Alana,Walsh,AWALSH,650.507.9811,1998-04-24,SH_CLERK,3100,124,50 100107,Kevin,Feeney,KFEENEY,650.507.9822,1998-05-23,SH_CLERK,3000,124,50 @@@ @@@Use AWK to modify the output, method 2 FS is the input Field Separator, RS is the input Record Separator, OFS is the Output Field Separator, ORS is the Output Record Separator. [oracle@station78 ~]$ cat awk_configfile02.awk BEGIN {FS=" "; RS="\n"; OFS=":"; ORS="\n"} { print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10} [oracle@station78 ~]$ cat spool_emp.dat | tail -n 5 | awk -f awk_configfile02.awk 100103:Britney:Everett:BEVERETT:650.501.2876:1997-03-03:SH_CLERK:3900:123:50 100104:Samuel:McCain:SMCCAIN:650.501.3876:1998-07-01:SH_CLERK:3200:123:50 100105:Vance:Jones:VJONES:650.501.4876:1999-03-17:SH_CLERK:2800:123:50 100106:Alana:Walsh:AWALSH:650.507.9811:1998-04-24:SH_CLERK:3100:124:50 100107:Kevin:Feeney:KFEENEY:650.507.9822:1998-05-23:SH_CLERK:3000:124:50 2@@@@ Use the UTL_FILE built-in package. SYS@ocm> !mkdir -p /u01/app/oracle/dir01 SYS@ocm> CREATE DIRECTORY dir01 AS '/u01/app/oracle/dir01' SYS@ocm> GRANT all ON DIRECTORY dir01 TO hr; HR@ocm> !cat tmp.sql DECLARE output_file UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('DIR01','employees.dat','w'); n PLS_INTEGER := &num_rows; BEGIN FOR rec IN ( SELECT ROWNUM + 100000 AS employee_id , first_name , last_name , email , phone_number , hire_date , job_id , salary , commission_pct , manager_id , department_id FROM hr.employees , ( SELECT ROWNUM AS r FROM dual CONNECT BY ROWNUM <= n ) WHERE ROWNUM <= n ) LOOP UTL_FILE.PUT_LINE ( output_file , rec.employee_id ||','|| rec.first_name ||','|| rec.last_name ||','|| rec.email ||','|| rec.phone_number ||','|| to_char(rec.hire_date,'yyyy-mm-dd') ||','|| rec.job_id ||','|| rec.salary ||','|| nvl(rec.commission_pct,0) ||','|| rec.manager_id ||','|| rec.department_id ); END LOOP; UTL_FILE.FCLOSE(output_file); END; / HR@ocm> @tmp.sql Enter value for num_rows: 107 old 3: n PLS_INTEGER := &num_rows; new 3: n PLS_INTEGER := 107; PL/SQL procedure successfully completed. @@@ @@@Use the PLSQL to set the format arbitrarily. [oracle@station78 dir01]$ pwd /u01/app/oracle/dir01 [oracle@station78 dir01]$ cat employees.dat | tail -n 5 100103,Britney,Everett,BEVERETT,650.501.2876,1997-03-03,SH_CLERK,3900,0,123,50 100104,Samuel,McCain,SMCCAIN,650.501.3876,1998-07-01,SH_CLERK,3200,0,123,50 100105,Vance,Jones,VJONES,650.501.4876,1999-03-17,SH_CLERK,2800,0,123,50 100106,Alana,Walsh,AWALSH,650.507.9811,1998-04-24,SH_CLERK,3100,0,124,50 100107,Kevin,Feeney,KFEENEY,650.507.9822,1998-05-23,SH_CLERK,3000,0,124,50 @@@ @@@We would ask, which method is more speed? Let's have a test. 3@@@@Compare with performance @@@ @@@<1> Part 1# use spool 198 seconds + 3 seonds @@@ @@@Prepare a 1 million table CREATE TABLE source_table AS SELECT * FROM dba_source; INSERT INTO source_table SELECT * FROM source_table; INSERT INTO source_table SELECT * FROM source_table; COMMIT; HR@ocm> SELECT count(*) FROM source_table; COUNT(*) ---------- 1180796 @@@Prepare a sql script to export a 1 million rows table. [oracle@station78 ~]$ cat tmpx.sql spool spool_1_million.dat set feedback off set linesize 230 set termout off set heading off set timing on SELECT owner , name , type , line , substr(text,1,30) FROM hr.source_table; spool off @@@Write the shell to count the time [oracle@station78 ~]$ cat spool.sh #!/bin/sh #it count from 1970 to now by seconds start_time=`date +%s` sqlplus hr/hr<<EOF @tmpx.sql EOF end_time=`date +%s` elapsed_time=$(($end_time - $start_time)) echo " " echo "Elapsed: $elapsed_time seconds" @@@ @@@run the spool.sh [oracle@station78 ~]$ sh spool.sh ......ignore Elapsed: 198 seconds @@@ @@@At last, I use awk to transfer the format. [oracle@station78 ~]$ cat test.sh #!/bin/sh start=`date +%s` cat spool_1_million.dat | awk -f awk_configfile01.awk > spool_1_million_tr.dat end=`date +%s` time=$(($end - start)) echo "" echo "Elapsed: $time seconds" [oracle@station78 ~]$ sh test.sh Elapsed: 3 seconds [oracle@station78 ~]$ cat spool_1_million_tr.dat | tail -n 6 SYSMAN,JOB_TARGET_INSERT_TRIGGER,TRIGGER,4,MGMT_JOB_ENGINE.insert_ext,,,,, SYSMAN,JOB_TARGET_INSERT_TRIGGER,TRIGGER,5,,,,,, SYSMAN,JOB_TARGET_INSERT_TRIGGER,TRIGGER,6,,,,,, SYSMAN,JOB_TARGET_INSERT_TRIGGER,TRIGGER,7,,,,,, SYSMAN,JOB_TARGET_INSERT_TRIGGER,TRIGGER,8,END;,,,,, Elapsed:,00:03:17.17,,,,,,,, @@@ @@@<2> part 2# use utl_file, 10 seconds @@@ @@@Prepare a directory object for hr. SYS@ocm> !mkdir -p /u01/app/oracle/dir01 SYS@ocm> CREATE DIRECTORY dir01 AS '/u01/app/oracle/dir01' SYS@ocm> GRANT all ON DIRECTORY dir01 TO hr; @@@Write the utl_file. HR@ocm> !cat tmpxx.sql DECLARE output_file UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('DIR01','utl_file.dat','w'); BEGIN FOR rec IN ( SELECT owner , name , type , line , substr(text,1,30) FROM hr.source_table ) LOOP UTL_FILE.PUT_LINE ( output_file , rec.owner ||','|| rec.name ||','|| rec.type ||','|| rec.line||','|| rec.text ); END LOOP; UTL_FILE.FCLOSE(output_file); END; / HR@ocm> set timing on HR@ocm> @tmpxx.sql PL/SQL procedure successfully completed. Elapsed: 00:00:10.29 @@@ @@@the format already could be used for sql loader and external table. [oracle@station78 dir01]$ cat utl_file.dat | head -n 5 SYS,STANDARD,PACKAGE,1,package STANDARD AUTHID CURREN SYS,STANDARD,PACKAGE,2, SYS,STANDARD,PACKAGE,3, /********** Types and subtyp SYS,STANDARD,PACKAGE,4, type BOOLEAN is (FALSE, TRUE 4@@@@ more example UTL_FILE HR@ocm> !cat tmp.sql CREATE OR REPLACE PROCEDURE sal_status ( p_filedir_in IN VARCHAR2 , p_filename_in IN VARCHAR2 ) IS lv_filehandle UTL_FILE.file_type; CURSOR cur_emp_info IS SELECT last_name, salary, department_id FROM employees ORDER BY department_id; lv_newdeptno employees.department_id%TYPE; lv_olddeptno employees.department_id%TYPE; BEGIN lv_filehandle := UTL_FILE.fopen(p_filedir_in, p_filename_in, 'w'); UTL_FILE.putf( lv_filehandle , 'SALARY REPORT: GENDERAPTED ON %s\n' , SYSDATE); UTL_FILE.new_line(lv_filehandle); FOR r IN cur_emp_info LOOP lv_newdeptno := r.department_id; IF lv_newdeptno <> lv_olddeptno THEN UTL_FILE.putf( lv_filehandle , 'DEPARTMENT: %s\n' , r.department_id); END IF; UTL_FILE.putf( lv_filehandle , ' EMPLOYEE: %s earns: %s\n' , r.last_name , r.salary ); lv_olddeptno := lv_newdeptno; END LOOP; UTL_FILE.put_line( lv_filehandle , '**** END OF REPORT ***' ); UTL_FILE.fclose(lv_filehandle); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'Error occurs, Please check!!!'); END; / HR@ocm> @tmp.sql Procedure created. @@@ @@@result: [oracle@station78 dir01]$ cat emp.dat SALARY REPORT: GENDERAPTED ON 2013-01-19 10:59:06 EMPLOYEE: Whalen earns: 4400 DEPARTMENT: 20 EMPLOYEE: Fay earns: 6000 EMPLOYEE: Hartstein earns: 13000 DEPARTMENT: 40 EMPLOYEE: Baida earns: 2900 EMPLOYEE: Tobias earns: 2800 EMPLOYEE: Himuro earns: 2600 EMPLOYEE: Khoo earns: 3100 EMPLOYEE: Colmenares earns: 2500 EMPLOYEE: Mavris earns: 6500 EMPLOYEE: Raphaely earns: 11000 DEPARTMENT: 50 EMPLOYEE: OConnell earns: 2600 EMPLOYEE: Grant earns: 2600 EMPLOYEE: Weiss earns: 8000 EMPLOYEE: Fripp earns: 8200 EMPLOYEE: Kaufling earns: 7900 EMPLOYEE: Vollman earns: 6500 EMPLOYEE: Mourgos earns: 5800 EMPLOYEE: Nayer earns: 3200 EMPLOYEE: Mikkilineni earns: 2700 EMPLOYEE: Landry earns: 2400 EMPLOYEE: Markle earns: 2200 EMPLOYEE: Bissot earns: 3300 EMPLOYEE: Atkinson earns: 2800 EMPLOYEE: Marlow earns: 2500 EMPLOYEE: Olson earns: 2100 EMPLOYEE: Mallin earns: 3300 EMPLOYEE: Rogers earns: 2900 EMPLOYEE: Gee earns: 2400 EMPLOYEE: Philtanker earns: 2200 EMPLOYEE: Ladwig earns: 3600 EMPLOYEE: Stiles earns: 3200 EMPLOYEE: Seo earns: 2700 EMPLOYEE: Patel earns: 2500 EMPLOYEE: Rajs earns: 3500 EMPLOYEE: Davies earns: 3100 EMPLOYEE: Matos earns: 2600 EMPLOYEE: Vargas earns: 2500 EMPLOYEE: Taylor earns: 3200 EMPLOYEE: Fleaur earns: 3100 EMPLOYEE: Sullivan earns: 2500 EMPLOYEE: Geoni earns: 2800 EMPLOYEE: Sarchand earns: 4200 EMPLOYEE: Bull earns: 4100 EMPLOYEE: Dellinger earns: 3400 EMPLOYEE: Cabrio earns: 3000 EMPLOYEE: Chung earns: 3800 EMPLOYEE: Dilly earns: 3600 EMPLOYEE: Gates earns: 2900 EMPLOYEE: Perkins earns: 2500 EMPLOYEE: Bell earns: 4000 EMPLOYEE: Everett earns: 3900 EMPLOYEE: McCain earns: 3200 EMPLOYEE: Jones earns: 2800 EMPLOYEE: Walsh earns: 3100 EMPLOYEE: Feeney earns: 3000 DEPARTMENT: 60 EMPLOYEE: Ernst earns: 6000 EMPLOYEE: Hunold earns: 9000 EMPLOYEE: Lorentz earns: 4200 EMPLOYEE: Pataballa earns: 4800 EMPLOYEE: Austin earns: 4800 DEPARTMENT: 70 EMPLOYEE: Baer earns: 10000 DEPARTMENT: 80 EMPLOYEE: Taylor earns: 8600 EMPLOYEE: Livingston earns: 8400 EMPLOYEE: Johnson earns: 6200 EMPLOYEE: Hutton earns: 8800 EMPLOYEE: Abel earns: 11000 EMPLOYEE: Kumar earns: 6100 EMPLOYEE: Bates earns: 7300 EMPLOYEE: Smith earns: 7400 EMPLOYEE: Fox earns: 9600 EMPLOYEE: Bloom earns: 10000 EMPLOYEE: Ozer earns: 11500 EMPLOYEE: Russell earns: 14000 EMPLOYEE: Partners earns: 13500 EMPLOYEE: Errazuriz earns: 12000 EMPLOYEE: Cambrault earns: 11000 EMPLOYEE: Zlotkey earns: 10500 EMPLOYEE: Tucker earns: 10000 EMPLOYEE: Bernstein earns: 9500 EMPLOYEE: Hall earns: 9000 EMPLOYEE: Olsen earns: 8000 EMPLOYEE: Cambrault earns: 7500 EMPLOYEE: Tuvault earns: 7000 EMPLOYEE: King earns: 10000 EMPLOYEE: Sully earns: 9500 EMPLOYEE: McEwen earns: 9000 EMPLOYEE: Smith earns: 8000 EMPLOYEE: Doran earns: 7500 EMPLOYEE: Sewall earns: 7000 EMPLOYEE: Vishney earns: 10500 EMPLOYEE: Greene earns: 9500 EMPLOYEE: Marvins earns: 7200 EMPLOYEE: Lee earns: 6800 EMPLOYEE: Ande earns: 6400 EMPLOYEE: Banda earns: 6200 DEPARTMENT: 90 EMPLOYEE: Kochhar earns: 17000 EMPLOYEE: King earns: 14000 EMPLOYEE: De Haan earns: 17000 DEPARTMENT: 100 EMPLOYEE: Chen earns: 8200 EMPLOYEE: Greenberg earns: 12000 EMPLOYEE: Sciarra earns: 7700 EMPLOYEE: Urman earns: 7800 EMPLOYEE: Popp earns: 6900 EMPLOYEE: Faviet earns: 9000 DEPARTMENT: 110 EMPLOYEE: Gietz earns: 8300 EMPLOYEE: Higgins earns: 12000 EMPLOYEE: Grant earns: 7000 **** END OF REPORT ***