【實驗】使用Tom解除安裝指令碼解除安裝表資料到平文字檔案
Tom為了體現“自動化精神”，提供了一整套從資料庫中解除安裝資料到平文字中的方法。這個小文兒演示一下這些指令碼的使用方法。
1.從AskTom網站上下載Tom的指令碼
Tom關於解除安裝指令碼的介紹資訊
指令碼直接下載連結
2.解壓下載得到的unloader.zip檔案後，您會得到6個檔案，分別是：
flat ---- Shell環境下以tab為間隔解除安裝資料
flat.cmd ---- Windows環境下以tab為間隔解除安裝資料
flat.sql ---- SQL*Plus環境下以tab為間隔解除安裝資料
sqlldr_exp ---- Shell環境下以“|”為間隔解除安裝資料
sqlldr_exp.cmd ---- Windows環境下以“|”為間隔解除安裝資料
sqlldr_exp.sql ---- SQL*Plus環境下以“|”為間隔解除安裝資料
3.演示一下Windows環境的指令碼sqlldr_exp.cmd的使用方法
什麼引數都不加的情況下可以得到一個簡略的使用方法的介紹
C:\>sqlldr_exp.cmd
"usage sqlldr_exp un/pw [tables|views]"
"example sqlldr_exp scott/tiger emp dept"
"description Select over standard out all rows of table or view with "
" columns delimited by tabs."
使用sqlldr_exp.cmd命令後緊接著使用者名稱、密碼以及服務名，最後緊跟著待匯出的表名就可以了。
C:\>sqlldr_exp.cmd sec/sec@144.194.192.183 emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10
OK，到此可以看到這個指令碼已經自動生成了SQLLDR的控制檔案。可以使用這個控制檔案直接載入資料到目標資料庫中。
將上面的內容儲存在emp.ctl控制檔案中，使用下面的命令就可以完成資料的遷入的功能。
$ sqlldr sec/sec control=emp.ctl
4.再演示一下sqlldr_exp.sql指令碼
這個指令碼的功能是在SQL*Plus環境下以“|”為間隔解除安裝資料。演示如下：
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:39:59 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @sqlldr_exp.sql emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1982-12-09 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1983-01-12 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
與上面的sqlldr_exp.cmd指令碼得到的內容是完全一樣的。Tom只是透過不同的方式來實現了這個功能，可以按照個人喜好來使用。
5.最後，演示一下flat.sql和flat.cmd指令碼的使用
SQL*Plus環境下以tab為間隔解除安裝資料
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:45:02 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @flat.sql emp
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
C:\>flat.cmd sec/sec@144.194.192.183 emp
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
其他的指令碼大家慢慢嘗試。
Goodluck 2 u.
【附】將六個指令碼的內容羅列記錄在此，方便查詢和使用（這些指令碼編寫的都很精緻，都值得細心的體會。）
1.flat ---- Shell環境下以tab為間隔解除安裝資料
$ cat flat
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done
2.flat.cmd ---- Windows環境下以tab為間隔解除安裝資料
$ cat flat.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @flat.sql %2
goto :done
:Usage
echo "usage flat un/pw [tables|views]"
echo "example flat scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
3.flat.sql ---- SQL*Plus環境下以tab為間隔解除安裝資料
$ cat flat.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
4.sqlldr_exp ---- Shell環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('$X')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
#rm /tmp/flat$$.sql
done
5.sqlldr_exp.cmd ---- Windows環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @sqlldr_exp.sql %2
goto :done
:Usage
echo "usage sqlldr_exp un/pw [tables|views]"
echo "example sqlldr_exp scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
6.sqlldr_exp.sql ---- SQL*Plus環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
-- The End --
1.從AskTom網站上下載Tom的指令碼
Tom關於解除安裝指令碼的介紹資訊
指令碼直接下載連結
2.解壓下載得到的unloader.zip檔案後，您會得到6個檔案，分別是：
flat ---- Shell環境下以tab為間隔解除安裝資料
flat.cmd ---- Windows環境下以tab為間隔解除安裝資料
flat.sql ---- SQL*Plus環境下以tab為間隔解除安裝資料
sqlldr_exp ---- Shell環境下以“|”為間隔解除安裝資料
sqlldr_exp.cmd ---- Windows環境下以“|”為間隔解除安裝資料
sqlldr_exp.sql ---- SQL*Plus環境下以“|”為間隔解除安裝資料
3.演示一下Windows環境的指令碼sqlldr_exp.cmd的使用方法
什麼引數都不加的情況下可以得到一個簡略的使用方法的介紹
C:\>sqlldr_exp.cmd
"usage sqlldr_exp un/pw [tables|views]"
"example sqlldr_exp scott/tiger emp dept"
"description Select over standard out all rows of table or view with "
" columns delimited by tabs."
使用sqlldr_exp.cmd命令後緊接著使用者名稱、密碼以及服務名，最後緊跟著待匯出的表名就可以了。
C:\>sqlldr_exp.cmd sec/sec@144.194.192.183 emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10
OK，到此可以看到這個指令碼已經自動生成了SQLLDR的控制檔案。可以使用這個控制檔案直接載入資料到目標資料庫中。
將上面的內容儲存在emp.ctl控制檔案中，使用下面的命令就可以完成資料的遷入的功能。
$ sqlldr sec/sec control=emp.ctl
4.再演示一下sqlldr_exp.sql指令碼
這個指令碼的功能是在SQL*Plus環境下以“|”為間隔解除安裝資料。演示如下：
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:39:59 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @sqlldr_exp.sql emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
BEGINDATA
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1982-12-09 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1983-01-12 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
與上面的sqlldr_exp.cmd指令碼得到的內容是完全一樣的。Tom只是透過不同的方式來實現了這個功能，可以按照個人喜好來使用。
5.最後，演示一下flat.sql和flat.cmd指令碼的使用
SQL*Plus環境下以tab為間隔解除安裝資料
ora10g@testdb183 /home/oracle/unloader$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 8 16:45:02 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> @flat.sql emp
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
C:\>flat.cmd sec/sec@144.194.192.183 emp
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
其他的指令碼大家慢慢嘗試。
Goodluck 2 u.
【附】將六個指令碼的內容羅列記錄在此，方便查詢和使用（這些指令碼編寫的都很精緻，都值得細心的體會。）
1.flat ---- Shell環境下以tab為間隔解除安裝資料
$ cat flat
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done
2.flat.cmd ---- Windows環境下以tab為間隔解除安裝資料
$ cat flat.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @flat.sql %2
goto :done
:Usage
echo "usage flat un/pw [tables|views]"
echo "example flat scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
3.flat.sql ---- SQL*Plus環境下以tab為間隔解除安裝資料
$ cat flat.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt select
select lower(column_name)||'||chr(9)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
4.sqlldr_exp ---- Shell環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp
#!/bin/sh
if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]
example: flat scott/tiger emp dept
description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('$X')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('$X'))
order by column_id
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
#rm /tmp/flat$$.sql
done
5.sqlldr_exp.cmd ---- Windows環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @sqlldr_exp.sql %2
goto :done
:Usage
echo "usage sqlldr_exp un/pw [tables|views]"
echo "example sqlldr_exp scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
6.sqlldr_exp.sql ---- SQL*Plus環境下以“|”為間隔解除安裝資料
$ cat sqlldr_exp.sql
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
spool ytmpy.sql
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
order by column_id
/
prompt from &1
prompt /
spool off
set termout on
@ytmpy.sql
exit
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-614181/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用Tom解除安裝指令碼解除安裝表資料到平文字檔案指令碼
- SQL Server解除安裝指令碼SQLServer指令碼
- 11g解除安裝指令碼指令碼
- Android程式碼實現APK檔案的安裝與解除安裝AndroidAPK
- 解除安裝oracleasm實驗模擬OracleASM
- cocoapods安裝/解除安裝/使用
- SQL Server解除安裝配置檔案SQLServer
- Shell指令碼 | 一鍵解除安裝安卓App指令碼安卓APP
- 【11g 單庫解除安裝、靜默安裝】實驗
- docker安裝及解除安裝Docker
- Ubuntu解除安裝和安裝Ubuntu
- Oracle 安裝與解除安裝Oracle
- solaris mysql 安裝 解除安裝MySql
- 解除安裝RAC
- 解除安裝CRS
- JDK 解除安裝JDK
- oracle解除安裝Oracle
- ORACLE 解除安裝Oracle
- docker 解除安裝Docker
- unloader將資料解除安裝為平面檔案
- android 程式碼安裝和解除安裝apkAndroidAPK
- Mac Redis安裝與解除安裝MacRedis
- Ubuntu安裝和解除安裝mongodbUbuntuMongoDB
- [雲原生]Docker - 安裝&解除安裝Docker
- JDK的安裝與解除安裝JDK
- ORACLE TEXT安裝與解除安裝Oracle
- oracle中使用plsql來進行平面檔案解除安裝OracleSQL
- 安裝npm 解除安裝npm 安裝apidocNPMAPI
- 【解除安裝】通過全面刪除Linux系統上Oracle檔案的方式解除安裝OracleLinuxOracle
- 解除安裝oracle資料庫Oracle資料庫
- 使用oracle外部表進行資料泵解除安裝資料Oracle
- air安裝win10怎麼解除安裝_air安裝win10如何解除安裝AIWin10
- 解除安裝 PythonPython
- kangle 解除安裝命令
- mac解除安裝mysqlMacMySql
- mac 解除安裝 nodeMac
- Redis（windows）解除安裝RedisWindows
- docker的解除安裝Docker