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 ***
