반응형
Oracle Table을 SAM file 로 Unload 시 참고.
spool 등 다양한 방법이 있으나, 대상 테이블이 많고 format 지정 등이 필요한 경우 유용 할거 같음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'C:\TEMP';
CREATE OR REPLACE PROCEDURE ORACLE_TO_SAMFILE
IS
BEGIN
FOR TABLES IN (
SELECT OWNER||'_'||TABLE_NAME||'.txt' AS FILE_NAME
, 'SELECT * FROM "'||OWNER||'"."'||TABLE_NAME||'"' AS V_SQL
FROM ALL_TABLES
WHERE OWNER = 'ORAKIM'
AND TABLE_NAME LIKE 'T%'
ORDER BY 1
)
LOOP
DATA_DUMP
(
QUERY_IN => TABLES.V_SQL,
FILE_IN => TABLES.FILE_NAME,
DIRECTORY_IN => 'TEMP_DIR',
DELIMITER_IN => '|',
HEADER_ROW_IN => FALSE
);
END LOOP;
END;
/
-- 전체 테이블 수행
EXEC ORACLE_TO_SAMFILE;
-- 단일 테이블 수행
EXEC DATA_DUMP ( QUERY_IN => 'SELECT * FROM T10',
FILE_IN => 'ORAKIM_T10.txt',
DIRECTORY_IN => 'TEMP_DIR',
DELIMITER_IN => '|',
HEADER_ROW_IN => FALSE
);
|
cs |
DATA_DUMP 프로시저 참조 URL : https://github.com/oracle-developer/utilities/blob/master/data_dump.sql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
|
CREATE OR REPLACE PROCEDURE DATA_DUMP
(
query_in IN VARCHAR2,
file_in IN VARCHAR2,
directory_in IN VARCHAR2,
nls_date_fmt_in IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS',
write_action_in IN VARCHAR2 DEFAULT 'W',
array_size_in IN PLS_INTEGER DEFAULT 1000,
delimiter_in IN VARCHAR2 DEFAULT NULL,
dump_code_in IN BOOLEAN DEFAULT FALSE,
header_row_in IN BOOLEAN DEFAULT FALSE
) AUTHID CURRENT_USER IS
v_fh UTL_FILE.FILE_TYPE;
v_ch BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
v_sql VARCHAR2(32767) := query_in;
v_dir VARCHAR2(512) := upper(directory_in);
v_outfile VARCHAR2(128) := file_in;
v_sqlfile VARCHAR2(128) := file_in||'.sql';
v_arr_size PLS_INTEGER := array_size_in;
v_col_cnt PLS_INTEGER := 0;
v_delimiter VARCHAR2(1) := NULL;
v_write_action VARCHAR2(1) := write_action_in;
v_nls_date_fmt VARCHAR2(30) := nls_date_fmt_in;
v_dummy NUMBER;
v_type VARCHAR2(8);
v_header_row VARCHAR2(32767);
t_describe DBMS_SQL.DESC_TAB;
t_plsql DBMS_SQL.VARCHAR2A;
/* Procedure to output code for debug and assign plsql variable... */
PROCEDURE put (string_in IN VARCHAR2) IS
BEGIN
IF dump_code_in THEN
UTL_FILE.PUT_LINE(v_fh,string_in);
END IF;
t_plsql(t_plsql.COUNT + 1) := string_in;
END put;
BEGIN
/* Open the file that the dynamic PL/SQL will be written to for debug... */
IF dump_code_in THEN
v_fh := UTL_FILE.FOPEN(v_dir, v_sqlfile, 'W', 32767);
END IF;
/* Parse the query that will be used to fetch all the data to be written out... */
DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);
/* Now describe the dynamic SQL to analyze the number of columns in the query... */
DBMS_SQL.DESCRIBE_COLUMNS(v_ch, v_col_cnt, t_describe);
/* Now begin the dynamic PL/SQL... */
put('DECLARE');
put(' v_fh UTL_FILE.FILE_TYPE;');
put(' v_eol VARCHAR2(2);');
put(' CURSOR cur_sql IS');
put(' '||v_sql||';');
/* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */
FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
IF t_describe(i).col_type = 2 THEN
v_type := 'NUMBER';
ELSIF t_describe(i).col_type = 12 THEN
v_type := 'DATE';
ELSIF t_describe(i).col_type = 113 THEN
v_type := 'BLOB';
ELSE
v_type := 'VARCHAR2';
END IF;
put(' "'||t_describe(i).col_name||'" DBMS_SQL.'||v_type||'_TABLE;');
END LOOP;
/* Enclose and escape rules based on CSV RFP: https://tools.ietf.org/html/rfc4180 */
/* 2.6: "Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes." */
/* 2.7: "If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote." */
put(q'[ ]');
put(q'[ FUNCTION enclose_and_escape(p_value VARCHAR2) RETURN VARCHAR2 IS ]');
put(q'[ v_value VARCHAR2(32767); ]');
put(q'[ BEGIN ]');
put(q'[ v_value := REPLACE(p_value, '"', '""'); ]');
put(q'[ IF v_value LIKE '%'||CHR(10)||'%' OR v_value LIKE '%'||CHR(13)||'%' OR v_value LIKE '%"%' OR v_value LIKE '%]'||delimiter_in||q'[%' THEN ]');
put(q'[ v_value := '"' || v_value || '"'; ]');
put(q'[ END IF; ]');
put(q'[ RETURN v_value; ]');
put(q'[ END enclose_and_escape; ]');
put(q'[ ]');
/* Set the date format to preserve time in the output, open the out file... */
put('BEGIN');
put(' EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = '''''||v_nls_date_fmt||''''''';');
put(' v_eol := CASE');
put(' WHEN DBMS_UTILITY.PORT_STRING LIKE ''IBMPC%''');
put(' THEN CHR(13)||CHR(10)');
put(' ELSE CHR(10)');
put(' END;');
put(' v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''','''||v_write_action||''', 32767);');
put(' OPEN cur_sql;');
/* Create and print a header row... */
IF header_row_in THEN
v_header_row := 'enclose_and_escape('''||t_describe(1).col_name||''')';
FOR i IN t_describe.FIRST + 1 .. t_describe.LAST LOOP
v_header_row := v_header_row||'||'''||delimiter_in||'''||enclose_and_escape('''||t_describe(i).col_name||''')';
END LOOP;
put(' --Print header.');
put(' UTL_FILE.PUT(v_fh, '||v_header_row||');');
put(' UTL_FILE.NEW_LINE(v_fh);');
END IF;
/* Start to collect... */
put(' LOOP');
put(' FETCH cur_sql');
IF t_describe.COUNT > 1 THEN
put(' BULK COLLECT INTO "'||t_describe(t_describe.FIRST).col_name||'",');
/* Add all other arrays into the fetch list except the last... */
FOR i IN t_describe.FIRST + 1 .. t_describe.LAST - 1 LOOP
put(' "'||t_describe(i).col_name||'",');
END LOOP;
/* Add in the last array and limit... */
put(' "'||t_describe(t_describe.LAST).col_name||'" LIMIT '||v_arr_size||';');
ELSE
/* Just output the one collection and LIMIT... */
put(' BULK COLLECT INTO "'||t_describe(t_describe.FIRST).col_name||'" LIMIT '||v_arr_size||';');
END IF;
/* Now add syntax to loop though the fetched array and write out the values to file... */
put(' IF "'||t_describe(t_describe.FIRST).col_name||'".COUNT > 0 THEN');
put(' FOR i IN "'||t_describe(t_describe.FIRST).col_name||'".FIRST .. "'||
t_describe(t_describe.FIRST).col_name||'".LAST LOOP');
/* Write enclosed and escaped values... */
FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
/* Some data types are not currently supported... */
IF t_describe(i).col_type = 113 THEN
put(' UTL_FILE.PUT(v_fh,'''||v_delimiter||''' ||enclose_and_escape(''WARNING - BLOB type is not supported''));');
ELSE
put(' UTL_FILE.PUT(v_fh,'''||v_delimiter||''' ||enclose_and_escape("'||t_describe(i).col_name||'"(i)));');
END IF;
v_delimiter := NVL(delimiter_in,',');
END LOOP;
/* Add a new line marker into the file and move on to next record... */
put(' UTL_FILE.NEW_LINE(v_fh);');
put(' END LOOP;');
/* Complete the IF statement... */
put(' END IF;');
/* Add in an EXIT condition and complete the loop syntax... */
put(' EXIT WHEN cur_sql%NOTFOUND;');
put(' END LOOP;');
put(' CLOSE cur_sql;');
put(' UTL_FILE.FCLOSE(v_fh);');
/* Add in some exception handling... */
put('EXCEPTION');
put(' WHEN UTL_FILE.INVALID_PATH THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid path.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.INVALID_MODE THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid mode.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.INVALID_OPERATION THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid operation.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.INVALID_FILEHANDLE THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid filehandle.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.WRITE_ERROR THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - write error.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.READ_ERROR THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - read error.'');');
put(' RAISE;');
put(' WHEN UTL_FILE.INTERNAL_ERROR THEN');
put(' DBMS_OUTPUT.PUT_LINE(''Error - internal error.'');');
put(' RAISE;');
put('END;');
/* Now close the cursor and sql file... */
DBMS_SQL.CLOSE_CURSOR(v_ch);
IF dump_code_in THEN
UTL_FILE.FCLOSE(v_fh);
END IF;
/*
* Execute the t_plsql collection to dump the data. Use DBMS_SQL as we have a collection
* of syntax...
*/
v_ch := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_ch, t_plsql, t_plsql.FIRST, t_plsql.LAST, TRUE, DBMS_SQL.NATIVE);
v_dummy := DBMS_SQL.EXECUTE(v_ch);
DBMS_SQL.CLOSE_CURSOR(v_ch);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - write error.');
RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - read error.');
RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - internal error.');
RAISE;
END;
/
|
cs |
반응형
댓글