Oracle生成某一用户下所有表数据的insert语句

功能：生成某一用户下所有数据表数据的insert语句，放入d:\insert.sql文件。

限制：只支持number、char、varchar2、date、long、clob数据类型。

提示：数据量小还可以，大了就别用这种方式了，会很慢。

[sql] view plain copy

1. /* Formatted on 2012-12-27 20:56:24 (QP5 v5.185.11230.41888) */  
2. CREATE OR REPLACE DIRECTORY mydir AS 'D:\';  
3.   
4. DECLARE  
5.    ROW_NUMBER   NUMBER := 0;  
6.    col_str      VARCHAR2 (32767) := '';  
7.    select_str   VARCHAR2 (32767) := '';  
8.    ins_tab      DBMS_SQL.varchar2_table;  
9.    l_count      INT := 0;  
10.    log_file     UTL_FILE.file_type;  
11. BEGIN  
12.    log_file := UTL_FILE.fopen ('MYDIR', 'insert.sql', 'w');  
13.    UTL_FILE.put_line (log_file, 'set define off;');  
14.   
15.    FOR x IN (SELECT table_name FROM user_tables)  
16.    LOOP  
17.       EXECUTE IMMEDIATE 'select count(*) from ' || x.table_name  
18.          INTO ROW_NUMBER;  
19.   
20.       IF ROW_NUMBER > 0  
21.       THEN  
22.          col_str := '';  
23.          select_str := '';  
24.   
25.          FOR y IN (  SELECT column_name, data_type  
26.                        FROM user_tab_columns  
27.                       WHERE table_name = x.table_name AND data_type != 'BLOB'  
28.                    ORDER BY column_id)  
29.          LOOP  
30.             col_str := col_str || y.column_name || ',';  
31.   
32.             IF y.data_type = 'NUMBER'  
33.             THEN  
34.                select_str :=  
35.                      select_str  
36.                   || 'decode('  
37.                   || y.column_name  
38.                   || ',null,''null'','  
39.                   || y.column_name  
40.                   || ')||'',''||';  
41.             ELSE  
42.                IF y.data_type IN ('CHAR', 'VARCHAR2', 'LONG', 'CLOB')  
43.                THEN  
44.                   select_str :=  
45.                         select_str  
46.                      || 'decode('  
47.                      || y.column_name  
48.                      || ',null,''null'',''''''''||replace('  
49.                      || y.column_name  
50.                      || ','''''''','''''''''''')||'''''''')||'',''||';  
51.                ELSE  
52.                   IF y.data_type = 'DATE'  
53.                   THEN  
54.                      select_str :=  
55.                            select_str  
56.                         || 'decode('  
57.                         || y.column_name  
58.                         || ',null,''null'',''to_date(''''''||'  
59.                         || 'to_char('  
60.                         || y.column_name  
61.                         || ',''yyyy-mm-dd hh24:mi:ss'')||'''  
62.                         || '''||'''''',''''yyyy-mm-dd hh24:mi:ss'''')'')||'',''||';  
63.                   END IF;  
64.                END IF;  
65.             END IF;  
66.          END LOOP;  
67.   
68.          col_str := 'insert into ' || x.table_name || ' (' || col_str;  
69.          col_str := SUBSTR (col_str, 1, LENGTH (col_str) - 1) || ') values (';  
70.          select_str :=  
71.                'select '''  
72.             || col_str  
73.             || ''' ||'  
74.             || SUBSTR (select_str, 1, LENGTH (select_str) - 7)  
75.             || '|| ''); from '  
76.             || x.table_name;  
77.   
78.          -- dbms_output.put_line(select_str);  
79.          EXECUTE IMMEDIATE select_str BULK COLLECT INTO ins_tab;  
80.   
81.          l_count := ins_tab.COUNT;  
82.          UTL_FILE.put_line (log_file, '');  
83.          UTL_FILE.put_line (log_file, '-- table name: ' || x.table_name);  
84.   
85.          FOR i IN 1 .. l_count  
86.          LOOP  
87.             UTL_FILE.put_line (log_file, ins_tab (i));  
88.          END LOOP;  
89.       END IF;  
90.    END LOOP;  
91.   
92.    UTL_FILE.put_line (log_file, '');  
93.    UTL_FILE.put_line (log_file, 'commit;');  
94.    UTL_FILE.fclose (log_file);  
95. END;  
96. /