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:\';
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;');
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;
20. IF ROW_NUMBER > 0
21. THEN
22. col_str := '';
23. select_str := '';
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 || ',';
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;
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;
78. -- dbms_output.put_line(select_str);
79. EXECUTE IMMEDIATE select_str BULK COLLECT INTO ins_tab;
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);
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;
92. UTL_FILE.put_line (log_file, '');
93. UTL_FILE.put_line (log_file, 'commit;');
94. UTL_FILE.fclose (log_file);
95. END;
96. /