平面檔案解除安裝

要把資料從一個系統移動到另一個系統,如果沒有使用EXP/IMP或EXPDP/IMPDP(用於取代EXP和IMP的新資料泵),平面解除安裝就很有用.儘管使用EXP(DP)/IMP(DP)可以很好地將資料從一個系統移到另一個系統,但要求兩個系統都是Oracle.

jy@JINGYONG> create or replace package unloader

2 authid current_user

3 as

4 /* Funaction run--unloads data from any query into a file

5 and creates a control file to reload that

6 data into another table

7 p_query=SQL query to "unload".May be virtually and query.

8 p_tname=Table to load into.Will be put into control file.

9 p_mode=REPLACE|APPEND|TRUNCATE--how to reload the data

10 p_dir=directory we will write the ctl and dat file to.

11 p_filename=name of file to write to.I will add .ctl and .dat to this

12 name

13 p_separator=field delimiter. I default this to a comma.

14 p_enclosure=what each field will be wrapped in

15 p_terminator=end of line character. We use this so we can unload

16 and reload data with newlines in it. I default to

17 "|\n"(a pipe and a newline together) and "|\r\n" on NT.

18 You need only to override this if you believe your

19 data will have that sequence in it. I ALWAYS add the

20 OS "end of line" marker to this sequence,you should not

21 */

22 function run(p_query in varchar2,

23 p_tname in varchar2,

24 p_mode in varchar2 default 'REPLACE',

25 p_dir in varchar2,

26 p_filename in varchar2,

27 p_separator in varchar2 default ',',

28 p_enclosure in varchar2 default '"',

29 p_terminator in varchar2 default '|')

30 return number;

31 end;

32 /

程式包已建立。

注意這裡使用了AUTHID CURRENT_USER.這樣一來,這個包就可以在資料庫上只安裝一次,可由任何人用來解除安裝資料.要解除安裝數據,只要求一點:對所解除安裝的表要有SELECT許可權,另外對這個包有EXECUTE許可權.如果這裡沒有使用AUTHID CURRENT_USER,則需要這個包的所有者在要解除安裝的所有表上都有直接的SELECT許可權.

注意:SQL會以這個例程的呼叫者的許可權執行.不過,所有PL/SQL呼叫都會以所呼叫例程定義者的許可權執行;因此,對於具有這個包執行許可權的所有人,都隱含地允許他使用UTL_FILE寫至一個目錄.

包體如下.我們使用UTL_FILE來寫一個控制檔案和一個資料檔案.DBMS_SQL用於動態地處理所有查詢.我們在查詢中使用了一個資料型別:VARCHAR2(4000).這說明,如果LOB大於4,000位元組,就不能使用這個方法來解除安裝LOB.不過,只需使用DBMS_LOB.SUBSTR,我們就可以使用這個方法解除安裝任何最多4,000位元組的LOB.另外,由於我們用一個VARCHAR2作為惟一的輸出資料型別,所以可以處理長度最多2,000位元組的RAW(4,000個十六進位制字元),除了LONG RAW和LOB外,這對其他型別都足夠了.另外,如果查詢引用了一個非標量屬性(一個複雜的物件型別,巢狀表等),則不能使用這個簡單的實現.以下是一個90%可用的解決方案,這說明90%的情況下它都能解決問題:

create or replace package body unloader

as

g_theCursor integer default dbms_sql.open_cursor;

g_descTbl dbms_sql.desc_tab;

g_nl varchar2(2) default chr(10);

以上是這個包體中使用的一些全域性變數.全域性遊標開啟一次,即第一次引用這個包時開啟,它會一起開啟,直到我們登出.這就不用每次呼叫這個包時都要得到一個新遊標,從而避免相應的開銷.G_DESCTBL是一個PL/SQL表,將儲存DBMS_SQL.DESCRIBE呼叫的輸出.G_NL是一個換行符.在需要內嵌有換行符的串中會使用這個變數.我們無需針對Windows調整這個變數,UTL_FILE會看到字串中的CHR(10),並自動為我們將其轉換為一個回車/換行符.

接下來,我們使用了一個很小的便利函式,它能將字元轉換為一個十六進位制數,為此使用了內建函式:

function to_hex(p_str in varchar2) return varchar2

is

begin

return to_char(ascii(p_str),'fm0x');

end;

最後,我們又建立了另一個便利函式IS_WINDOWS,它會返回TRUE或FALSE,這取決於我們所用的是否是Windows平臺,如果在 Windows平臺上,行結束符就是一個兩字元的串,而大多數其他平臺上的行結束符只是單字元.我們使用了內建DBMS_UTILITY函數:GET_PARAMETER_VALUE,可以用這個函式讀取幾乎所有引數.我們獲取了CONTROL_FILES引數,並查詢其中是否存在\,如果有,則說明在Windows平臺上:

function is_windows return boolean

is

l_cfiles varchar2(4000);

l_dummy number;

begin

if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then

return instr(l_cfiles,'\')>0;

else

return FALSE;

end if;

end;

注意:IS_WINDOWS函式依賴於CONTROL_FILES引數中使用了\.要記住,其中也有可能使用/,但這極為少見.

下面的過程會建立一個控制檔案來重新載入解除安裝的資料,這裡使用了DBMS_SQL.DESCRIBE_COLUMN生成的DESCRIBE表.它會為我們處理有關作業系統的細節,如作業系統是否使用回車/換行符(用於STR屬性):

procedure dump_ctl(

p_dir in varchar2,

p_filename in varchar2,

p_tname in varchar2,

p_mode in varchar2,

p_separator in varchar2,

p_enclosure in varchar2,

p_terminator in varchar2)

is

l_output utl_file.file_type;

l_sep varchar2(5);

l_str varchar2(5):=chr(10);

begin

if(is_windows) then

l_str:=chr(13)||chr(10);

end if;

l_output:=utl_file.fopen(p_dir,p_filename|\'.ctl','w');

utl_file.put_line(l_output,'load data');

utl_file.put_line( l_output, 'infile ''' ||

p_filename || '.dat'' "str x''' ||

utl_raw.cast_to_raw( p_terminator ||

l_str ) || '''"' );

utl_file.put_line( l_output, 'into table ' || p_tname );

utl_file.put_line( l_output, p_mode );

utl_file.put_line( l_output, 'fields terminated by X''' ||

to_hex(p_separator) ||

''' enclosed by X''' ||

to_hex(p_enclosure) || ''' ' );

utl_file.put_line( l_output, '(' );

for i in 1 .. g_descTbl.count loop

if ( g_descTbl(i).col_type = 12 ) then

utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||

' date ''ddmmyyyyhh24miss'' ');

else

utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||

' char(' ||

to_char(g_descTbl(i).col_max_len*2) ||' )' );

end if;

l_sep := ','||g_nl ;

end loop;

utl_file.put_line( l_output, g_nl || ')' );

utl_file.fclose( l_output );

end;

這是一個簡單的函式,會返回一個加引號的串(使用所選擇的包圍字元作為引號);注意,串不只是包含字元,倘若串中還存在包圍字元,還會把包圍字元重複兩次,從而保留這些包圍字元:

function quote(p_str in varchar2,p_enclosure in varchar2)

return varchar2

is

begin

return p_enclosure||

replace(p_str,p_enclosure,p_enclosure||p_enclosure)||

p_enclosure;

end;

下面是主函式RUN.因為這個函式相當大,會列出函式的解釋:

function run(p_query in varchar2,

p_tname in varchar2,

p_mode in varchar2 default 'REPLACE',

p_dir in varchar2,

p_filename in varchar2,

p_separator in varchar2 default ',',

p_enclosure in varchar2 default '"',

p_terminator in varchar2 default '|')

return number

is

l_output utl_file.file_type;

l_columnValue varchar2(4000);

l_colCnt number default 0;

l_separator varchar2(10) default '';

l_cnt number default 0;

l_line long;

l_datefmt varchar2(255);

l_descTbl dbms_sql.desc_tab;

begin

我們將NLS_DATE_FORMAT儲存到一個變數中,從而在將資料轉儲到磁碟上時可以把它改為一種保留日期和時間的格式.採用這種方式,我們會保留日期的時間分量.然後建立一個異常塊,從而在接收到錯誤時重置NLS_DATE_FORMAT:

select value into l_datefmt

from nls_session_parameters where parameter='NLS_DATE_FORMAT';

/* 設定日期格式為是一個大數字字串避免所有的NLS問題並保留時間和日期 */

execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';

/* 設定一個異常塊在出現任何錯誤時重新設定日期格式 */

接下來,解析並描述這個查詢.將G_DESCTBL設定為L_DESCTBL來重置全域性表;否則,其中會包含前一個DESCRIBE生成的資料,而不只是當前查詢生成的資料.一旦完成,再呼叫DUMP_CTL具體建立控制檔案:

begin

/*

解析和描述這個查詢.將重設descTbl為了一個空表

*/

dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );

g_descTbl := l_descTbl;

dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );

/*

建立一個控制檔案來重新載入資料到你所期望的表中

*/

dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,

p_enclosure, p_terminator );

/*

繫結每一個列轉換為一個長度為4000字串.我們不在乎我們獲取是數字還是日期,因為它們都可以

看成是一個字串

*/

現在可以將具體資料轉儲到磁碟上了.首先將每個列定義為VARCHAR2(4000)來獲取資料.所有型別(NUMBER,DATE,RAW)都要轉換為VARCHAR2.在此之後,執行查詢來準備獲取:

for i in 1 .. l_colCnt loop

dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);

end loop;

/*

執行這個查詢--忽略執行的輸出,它只是來驗證一個插入/更新或刪除操作

*/

現在開啟資料檔案準備寫,從查詢獲取所有行,並將其列印到資料檔案:

l_cnt := dbms_sql.execute(g_theCursor);

/*

開啟檔案然後將帶有分隔符的資料寫入到檔案中

*/

l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );

loop

exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 );

l_separator := '';

l_line := null;

for i in 1 .. l_colCnt loop

dbms_sql.column_value( g_theCursor, i,l_columnValue );

l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );

l_separator := p_separator;

end loop;

l_line := l_line || p_terminator;

utl_file.put_line( l_output, l_line );

l_cnt := l_cnt+1;

end loop;

utl_file.fclose( l_output );

最後,將日期格式設定回原來的樣子(如果先前的程式碼由於某種原因失敗了,異常塊也會做這個工作),並返回:

/*

現在重新設定日期格式並將返回的行資料寫入到輸出檔案中

*/

execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';

return l_cnt;

exception

/*

如果發現任何錯誤會重新設定日期並重新觸發錯誤

*/

when others then

execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';

RAISE;

end;

end run;

end unloader;

執行完整的編譯

SQL> create or replace package body unloader

2 as

3 g_theCursor integer default dbms_sql.open_cursor;

4 g_descTbl dbms_sql.desc_tab;

5 g_nl varchar2(2) default chr(10);

6

7 function to_hex(p_str in varchar2) return varchar2

8 is

9 begin

10 return to_char(ascii(p_str),'fm0x');

11 end;

12

13 function is_windows return boolean

14 is

15 l_cfiles varchar2(4000);

16 l_dummy number;

17 begin

18 if(dbms_utility.get_parameter_value('control_files',l_dummy,l_cfiles)>0) then

19 return instr(l_cfiles,'\')>0;

20 else

21 return FALSE;

22 end if;

23 end;

24

25 procedure dump_ctl(

26 p_dir in varchar2,

27 p_filename in varchar2,

28 p_tname in varchar2,

29 p_mode in varchar2,

30 p_separator in varchar2,

31 p_enclosure in varchar2,

32 p_terminator in varchar2)

33 is

34 l_output utl_file.file_type;

35 l_sep varchar2(5);

36 l_str varchar2(5):=chr(10);

37 begin

38 if(is_windows) then

39 l_str:=chr(13)||chr(10);

40 end if;

41 l_output:=utl_file.fopen(p_dir,p_filename||'.ctl','w');

42 utl_file.put_line(l_output,'load data');

43 utl_file.put_line( l_output, 'infile ''' ||

44 p_filename || '.dat'' "str x''' ||

45 utl_raw.cast_to_raw( p_terminator ||

46 l_str ) || '''"' );

47 utl_file.put_line( l_output, 'into table ' || p_tname );

48 utl_file.put_line( l_output, p_mode );

49 utl_file.put_line( l_output, 'fields terminated by X''' ||

50 to_hex(p_separator) ||

51 ''' enclosed by X''' ||

52 to_hex(p_enclosure) || ''' ' );

53 utl_file.put_line( l_output, '(' );

54

55 for i in 1 .. g_descTbl.count loop

56 if ( g_descTbl(i).col_type = 12 ) then

57 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||

58 ' date ''ddmmyyyyhh24miss'' ');

59 else

60 utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||

61 ' char(' ||

62 to_char(g_descTbl(i).col_max_len*2) ||' )' );

63 end if;

64 l_sep := ','||g_nl ;

65 end loop;

66 utl_file.put_line( l_output, g_nl || ')' );

67 utl_file.fclose( l_output );

68 end;

69

70 function quote(p_str in varchar2,p_enclosure in varchar2)

71 return varchar2

72 is

73 begin

74 return p_enclosure||

75 replace(p_str,p_enclosure,p_enclosure||p_enclosure)||

76 p_enclosure;

77 end;

78

79

80 function run(p_query in varchar2,

81 p_tname in varchar2,

82 p_mode in varchar2 default 'REPLACE',

83 p_dir in varchar2,

84 p_filename in varchar2,

85 p_separator in varchar2 default ',',

86 p_enclosure in varchar2 default '"',

87 p_terminator in varchar2 default '|')

88 return number

89 is

90 l_output utl_file.file_type;

91 l_columnValue varchar2(4000);

92 l_colCnt number default 0;

93 l_separator varchar2(10) default '';

94 l_cnt number default 0;

95 l_line long;

96 l_datefmt varchar2(255);

97 l_descTbl dbms_sql.desc_tab;

98 begin

99 select value into l_datefmt

100 from nls_session_parameters where parameter='NLS_DATE_FORMAT';

101 /* 設定日期格式為是一個大數字字串避免所有的NLS問題並保留時間和日期 */

102 execute immediate 'alter session set nls_date_format="yyyymmddhh24miss"';

103 /* 設定一個異常塊在出現任何錯誤時重新設定日期格式 */

104 begin

105 /*

106 解析和描述這個查詢.將重設descTbl為了一個空表

107 */

108 dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );

109 g_descTbl := l_descTbl;

110 dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );

111 /*

112 建立一個控制檔案來重新載入資料到你所期望的表中

113 */

114 dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,

115 p_enclosure, p_terminator );

116 /*

117 繫結每一個列轉換為一個長度為4000字串.我們不在乎我們獲取是數字還是日期,因為它們都可以

118 看成是一個字串

119 */

120 for i in 1 .. l_colCnt loop

121 dbms_sql.define_column(g_theCursor,i,l_columnValue,4000);

122 end loop;

123 /*

124 執行這個查詢--忽略執行的輸出,它只是來驗證一個插入/更新或刪除操作

125 */

126 l_cnt := dbms_sql.execute(g_theCursor);

127 /*

128 開啟檔案然後將帶有分隔符的資料寫入到檔案中

129 */

130 l_output := utl_file.fopen( p_dir, p_filename || '.dat', 'w',32760 );

131 loop

132 exit when ( dbms_sql.fetch_rows(g_theCursor) <= 0 );

133 l_separator := '';

134 l_line := null;

135 for i in 1 .. l_colCnt loop

136 dbms_sql.column_value( g_theCursor, i,l_columnValue );

137 l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );

138 l_separator := p_separator;

139 end loop;

140 l_line := l_line || p_terminator;

141 utl_file.put_line( l_output, l_line );

142 l_cnt := l_cnt+1;

143 end loop;

144 utl_file.fclose( l_output );

145 /*

146 現在重新設定日期格式並將返回的行資料寫入到輸出檔案中

147 */

148 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';

149 return l_cnt;

150 exception

151 /*

152 如果發現任何錯誤會重新設定日期並重新觸發錯誤

153 */

154 when others then

155 execute immediate 'alter session set nls_date_format=''' || l_datefmt || '''';

156 RAISE;

157 end;

158 end run;

159 end unloader;

160 /

Package body created

要執行這個程式碼,可以使用以下命令(要注意,當然以下程式碼需要你將SCOTT.EMP的SELECT許可權授予某個角色,或者直接授予你自己):

jy@JINGYONG> set serveroutput on

jy@JINGYONG> create or replace directory dir1 as '/home/oracle';

目錄已建立。

jy@JINGYONG> declare

2 l_rows number;

3 begin

4 l_rows:=unloader.run

5 (p_query=>'select * from scott.emp order by empno',

6 p_tname=>'emp',

7 p_mode=>'replace',

8 p_dir=>'DIR1',

9 p_filename=>'emp',

10 p_separator=>',',

11 p_enclosure=>'"',

12 p_terminator=>'~');

13 dbms_output.put_line(to_char(l_rows)||' rows extracted to ascii file');

14 end;

15 /

14 rows extracted to ascii file

PL/SQL 過程已成功完成。

由此生成的emp.ctl控制檔案顯示如下(注意,括號裡粗體顯示的數字並不是真的包括在檔案中:加上這些數字只是為了便於引用):

[oracle@jy ~]$ cat emp.ctl

load data

infile 'emp.dat' "str x'7E0A'"

into table emp

replace

fields terminated by X'2c' enclosed by X'22'

(

EMPNO char(44 ),

ENAME char(20 ),

JOB char(18 ),

MGR char(44 ),

HIREDATE date 'ddmmyyyyhh24miss' ,

SAL char(44 ),

COMM char(44 ),

DEPTNO char(44 )

)

關於這個控制檔案,要注意以下幾點:

第2行:使用了SQLLDR的STR特性.可以指定用什麼字元或串來結束一個記錄.這樣就能很容易地載入有內嵌換行符的資料.串x '7E0A'只是換行符後面跟一個波浪號"~".

第5行:使用了我們的分隔符和包圍符.這裡沒有使用OPTIONALLY ENCLOSED BY,因為我們將把原資料中包圍字元的所有出現都重複兩次,再把每個欄位括起來.

第11行:使用了一個很大的"數值"日期格式.這有兩個作用:可以避免與日期有關的所有NLS問題,還可以保留日期欄位的時間分量.

從前面的程式碼生成的原始資料（emp.dat）檔案如下：

[oracle@jy ~]$ cat emp.dat

"7369","SMITH","CLERK","7902","19801217000000","800","","20"~

"7499","ALLEN","SALESMAN","7698","19810220000000","1600","300","30"~

"7521","WARD","SALESMAN","7698","19810222000000","1250","500","30"~

"7566","JONES","MANAGER","7839","19810402000000","2975","","20"~

"7654","MARTIN","SALESMAN","7698","19810928000000","1250","1400","30"~

"7698","BLAKE","MANAGER","7839","19810501000000","2850","","30"~

"7782","CLARK","MANAGER","7839","19810609000000","2450","","10"~

"7788","SCOTT","ANALYST","7566","19870419000000","3000","","20"~

"7839","KING","PRESIDENT","","19811117000000","5000","","10"~

"7844","TURNER","SALESMAN","7698","19810908000000","1500","0","30"~

"7876","ADAMS","CLERK","7788","19870523000000","1100","","20"~

"7900","JAMES","CLERK","7698","19811203000000","950","","30"~

"7902","FORD","ANALYST","7566","19811203000000","3000","","20"~

"7934","MILLER","CLERK","7782","19820123000000","1300","","10"~

emp.dat檔案中要注意的問題如下:

每個欄位都用包圍字元括起來.

DATE解除安裝為很大的數字>

這個檔案中的資料行按要求以一個～結束>

現在可以使用SQLLDR很容易地重新載入這個資料,你可以向SQLLDR命令列增加你認為合適的選項.

oracle中使用plsql來進行平面檔案解除安裝

相關文章