oracle导出创建用户下各个对象数据结构的sql语句的脚本，包括表、视图、索引、约束等等

 

导出创建用户下各个对象数据结构的sql语句的脚本，包括表、视图、索引、约束等等

前言：
  这是我用很长时间才写的存储包，其间经历了多次修改，以适应多种情况，今将其贡献出来，如果能读懂这个存储包，恭喜你，你的oracle知识已经达到一定程度了，而且这不失为一个学习oracle存储过程的模板，掌握它之后你可以自如的写存储包，存储过程，和函数了。

  在我的实际工作中我发现它的功能很强，可以生成我们常用的对象的创建语句，这在很多情况下比较好用
1。想导出创建用户下的对象的sql语句。
2。有时我们用copy命令与create table as .... 命令在2个数据库之间建立一个用户的所有数据的拷贝时，只能拷贝数据，而如各个约束，索引则不能导过来，此时，该脚本生成的sql语句正好可以弥补这一缺点。

不好意思独享该脚本，共享出来，忘大家共同进步。

版权所有人： 徐玉金

备注：用户可以根据实际需要修改、使用、发布该脚本，但是不能修改版权所有人


适用的数据库：8i, 9i数据库下该脚本通用

功能：导出一个用户的数据结构，包括表、视图、索引、约束、存储过程、触发器、函数、序列等等的定义，并且每个定义为一个文件
       导出的各个对象的定义格式比较规范。
       如表的定义为：
        prompt  Create Table BBNACTIVEHISTORY
        CREATE TABLE test(
                USERID                        VARCHAR2(20)        NOT NULL,
                SPUSERID                VARCHAR2(30)        NOT NULL,
                PRODUCTID                VARCHAR2(20)        NOT NULL,
                GAMETYPE                NUMBER        NOT NULL,
                STARTTIME                DATE        NOT NULL,
                CHARGED                        CHAR(1)        default        'N'                NOT NULL
        );
      
      每个存储过程有多个参数，可以控制输出何种形式的内容，如可以只生成删除表的sql，可以在生成的创建表与索引的语句上加上storage子句等等。

运行 user_dll_sql.sql的准备工作：

1.  修改win_mkdir.bat文件，以便生成存放生成脚本的基本目录及其子目录
        win_mkdir.bat文件生成上面所需要的所有目录
        打开win_mkdir.bat文件，修改BASE_DIC变量的值,如c:/temp/，该目录是存放生成的脚本文件的基本目录
       
       
2.        运行 win_mkdir.bat文件创建必要的目录

3.        用sql*plus以想导出数据结构的用户登陆到数据库
        运行user_dll_sql.sql 生成文件的路径文件，生成所有的ddl语句
        运行文件时，将存放生成脚本的基本目录作为参数(与步骤1改的基本目录一样)传入, ?要根据需要改为实际的目录
        SQL> @?/user_ddl_sql.sql c:/temp/
       

说明：
        运行win_mkdir.bat,文件后，会自动在生成脚本的基本目录下产生如下目录：
        procedures, functions, triggers, packages, temp_sql.
        每个目录的作用如下：
        procedures: 该目录存放存储过程, 有子目录separ_files, 该目录下的每个文件都是一个存储过程的源代码
        functions: 该目录存放函数, 有子目录separ_files, 该目录下的每个文件都是一个函数的源代码
        triggers: 该目录存放触发器, 有子目录separ_files, 该目录下的每个文件都是一个触发器的源代码
        packages: 该目录存放包, 有子目录separ_files, 该目录下的每个文件都是一个包的源代码
                                                        有子目录separ_headbobdy_file, 该目录下的每个文件都是一个包的包头或包体的源代码
        temp_sql：存放生成的临时文件

2. DEFINE file_dic=1
4. CREATE TABLE temp_for_pkg_gen_sql4000_temp 
5. (   line_no         NUMBER(38,0), 
6.     object_type     VARCHAR2(30),
7.     user_name       VARCHAR2(30),
8.     object_name     VARCHAR2(200),
9.     sql_text        VARCHAR2(4000)
10. );
13. -- synonym, job, db_link, role, user_type 
14. CREATE TABLE temp_for_pkg_gen_sql2000_temp 
15. (   line_no         NUMBER(38,0), 
16.     object_type     VARCHAR2(30),
17.     user_name       VARCHAR2(30),
18.     object_name     VARCHAR2(200),
19.     sql_text        VARCHAR2(2000)
20. );
22. DELETE temp_for_pkg_gen_sql4000_temp;
23. DELETE temp_for_pkg_gen_sql2000_temp;
24. COMMIT;
26. CREATE OR REPLACE PACKAGE pkg_gen_user_sql AS
27.   -- 产生start脚本
28.   PROCEDURE pro_gen_start_sql(p_v_file_path VARCHAR2 default 'C:/');
30.   FUNCTION fun_return_table_name_tab4(p_v_table_name  VARCHAR2,
31.                                       p_n_col_max_len NUMBER) RETURN VARCHAR2;
33.   FUNCTION fun_return_table_name_tab8(p_v_table_name  VARCHAR2,
34.                                       p_n_col_max_len NUMBER) RETURN VARCHAR2;
36.   -- generate sql for create tables;
37.   PROCEDURE pro_tab_sql_tab4(p_v_gen_drop_sql        VARCHAR2 default 'T',
38.                              p_v_gen_create_sql      VARCHAR2 default 'T',
39.                              p_v_gen_store_clause    VARCHAR2 default 'T',
40.                              p_v_display_prompt_info VARCHAR2 default 'T');
42.   PROCEDURE pro_tab_sql_tab8(p_v_gen_drop_sql        VARCHAR2 default 'T',
43.                              p_v_gen_create_sql      VARCHAR2 default 'T',
44.                              p_v_gen_store_clause    VARCHAR2 default 'T',
45.                              p_v_display_prompt_info VARCHAR2 default 'T');
47.   PROCEDURE pro_con_sql(p_v_con_type            VARCHAR2 default 'ALL',
48.                         p_v_gen_drop_sql        VARCHAR2 default 'T',
49.                         p_v_gen_create_sql      VARCHAR2 default 'T',
50.                         p_v_gen_null_chk        VARCHAR2 default 'T',
51.                         p_v_display_prompt_info VARCHAR2 default 'T');
53.   PROCEDURE pro_indx_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
54.                          p_v_gen_create_sql      VARCHAR2 default 'T',
55.                          p_v_gen_store_clause    VARCHAR2 default 'T',
56.                          p_v_gen_ts_only         VARCHAR2 default 'F',
57.                          p_v_display_prompt_info VARCHAR2 default 'T');
59.   -- 重建索引
60.   PROCEDURE pro_reb_indx_sql(p_v_gen_store_clause    VARCHAR2 default 'T',
61.                              p_v_rebuild_online      VARCHAR2 default 'T',
62.                              p_v_display_prompt_info VARCHAR2 default 'T');
64.   PROCEDURE pro_view_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
65.                          p_v_gen_create_sql      VARCHAR2 default 'T',
66.                          p_v_display_prompt_info VARCHAR2 default 'T');
68.   PROCEDURE pro_seq_max_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
69.                                 p_v_gen_create_sql      VARCHAR2 default 'T',
70.                                 p_v_display_prompt_info VARCHAR2 default 'T');
72.   PROCEDURE pro_seq_next_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
73.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
74.                                  p_v_display_prompt_info VARCHAR2 default 'T');
76.   PROCEDURE pro_seq_init_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
77.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
78.                                  p_v_display_prompt_info VARCHAR2 default 'T');
80.   PROCEDURE pro_synonym_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
81.                             p_v_gen_create_sql      VARCHAR2 default 'T',
82.                             p_v_display_prompt_info VARCHAR2 default 'T');
84.   PROCEDURE pro_job_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
85.                         p_v_gen_create_sql      VARCHAR2 default 'T',
86.                         p_v_display_prompt_info VARCHAR2 default 'T');
88.   PROCEDURE pro_dl_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
89.                        p_v_gen_create_sql      VARCHAR2 default 'T',
90.                        p_v_display_prompt_info VARCHAR2 default 'T');
92.   PROCEDURE pro_role_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
93.                          p_v_gen_create_sql      VARCHAR2 default 'T',
94.                          p_v_display_prompt_info VARCHAR2 default 'T');
96.   PROCEDURE pro_priv_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
97.                          p_v_gen_create_sql      VARCHAR2 default 'T',
98.                          p_v_display_prompt_info VARCHAR2 default 'T');
100.   PROCEDURE pro_pkgprofuntri_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
101.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
102.                                  p_v_display_prompt_info VARCHAR2 default 'T',
103.                                  p_v_separ_file          VARCHAR2 default 'T',
104.                                  p_v_file_path           VARCHAR2 default 'C:/');
106. END pkg_gen_user_sql;
108. /
110. show err
113. show err
115. CREATE OR REPLACE PACKAGE BODY pkg_gen_user_sql AS
117.   PROCEDURE pro_gen_start_sql(p_v_file_path VARCHAR2 default 'C:/') AS
118.     v_username    VARCHAR2(30);
119.     v_object_type VARCHAR2(30);
120.     n_line_no     NUMBER(10, 0);
121.     v_sql         VARCHAR2(2000);
122.     v_prompt      VARCHAR2(30);
123.     v_file_path   VARCHAR2(600);
124.     v_notion      char(1);
125.     PROCEDURE insert_rec(p_line_no     INTEGER,
126.                          p_object_type VARCHAR2,
127.                          p_user_name   VARCHAR2,
128.                          p_view_name   VARCHAR2,
129.                          p_string      VARCHAR2) IS
130.     BEGIN
131.       INSERT INTO temp_for_pkg_gen_sql2000_temp
132.         (line_no, object_type, user_name, object_name, sql_text)
133.       VALUES
134.         (p_line_no, p_object_type, p_user_name, p_view_name, p_string);
135.     END;
136.   BEGIN
137.     v_object_type := 'START_SQL';
138.     v_prompt      := '-- prompt ';
139.     v_file_path   := p_v_file_path;
140.   
141.     -- find the notion between the dictionary
142.     IF instr(p_v_file_path, '/') > 0 THEN
143.       v_notion := '/';
144.     ELSE
145.       v_notion := '/';
146.     END IF;
147.   
148.     IF substr(v_file_path, length(v_file_path)) <> v_notion THEN
149.       v_file_path := v_file_path || v_notion;
150.     END IF;
151.   
152.     SELECT username INTO v_username FROM user_users;
153.   
154.     DELETE temp_for_pkg_gen_sql2000_temp WHERE object_type = v_object_type;
155.     COMMIT;
156.   
157.     n_line_no := 1;
158.   
159.     v_sql := '@ ' || v_file_path || 'cre_tables.sql';
160.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
161.     n_line_no := n_line_no + 1;
162.   
163.     v_sql := '@ ' || v_file_path || 'cre_sequences.sql';
164.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
165.     n_line_no := n_line_no + 1;
166.   
167.     v_sql := '@ ' || v_file_path || 'cre_synonyms.sql';
168.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
169.     n_line_no := n_line_no + 1;
170.   
171.     v_sql := '@ ' || v_file_path || 'cre_views.sql';
172.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
173.     n_line_no := n_line_no + 1;
174.   
175.     v_sql := '@ ' || v_file_path || 'functions/';
176.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
177.     n_line_no := n_line_no + 1;
178.   
179.     v_sql := '@ ' || v_file_path || 'procedures/';
180.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
181.     n_line_no := n_line_no + 1;
182.   
183.     v_sql := '@ ' || v_file_path || 'packages/';
184.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
185.     n_line_no := n_line_no + 1;
186.   
187.     v_sql := '@ ' || v_file_path || 'triggers/';
188.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
189.     n_line_no := n_line_no + 1;
190.   
191.     v_sql := '@ ' || v_file_path || 'cre_synonyms.sql';
192.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
193.     n_line_no := n_line_no + 1;
194.   
195.     v_sql := '   ';
196.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
197.     n_line_no := n_line_no + 1;
198.   
199.     v_sql := '-- *****      import data ***** -- ';
200.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
201.     n_line_no := n_line_no + 1;
202.   
203.     v_sql := '    ';
204.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
205.     n_line_no := n_line_no + 1;
206.   
207.     v_sql := '@ ' || v_file_path || 'cre_indexes.sql';
208.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
209.     n_line_no := n_line_no + 1;
210.   
211.     v_sql := '@ ' || v_file_path || 'cre_pri_constraint.sql';
212.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
213.     n_line_no := n_line_no + 1;
214.   
215.     v_sql := '@ ' || v_file_path || 'cre_not_pri_constraint.sql';
216.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
217.     n_line_no := n_line_no + 1;
218.   
219.     v_sql := '@ ' || v_file_path || 'cre_db_links.sql';
220.     insert_rec(n_line_no, v_object_type, v_username, v_object_type, v_sql);
221.     n_line_no := n_line_no + 1;
222.   
223.     COMMIT;
224.   END;
226.   FUNCTION fun_return_table_name_tab4(p_v_table_name  VARCHAR2,
227.                                       p_n_col_max_len NUMBER) RETURN VARCHAR2 AS
228.     v_table_name  VARCHAR2(100);
229.     v_temp        VARCHAR2(100);
230.     n_col_max_len NUMBER;
231.   BEGIN
232.     v_table_name  := trim(p_v_table_name);
233.     n_col_max_len := p_n_col_max_len;
234.     v_temp        := v_table_name;
235.   
236.     WHILE mod(length(v_temp), 4) <> 0 LOOP
237.       v_temp := v_temp || ' ';
238.     END LOOP;
239.     IF mod(length(v_table_name), 4) <> 0 THEN
240.       v_table_name := v_table_name || chr(9);
241.     END IF;
242.   
243.     WHILE length(v_temp) < n_col_max_len LOOP
244.       v_table_name := v_table_name || chr(9);
245.       v_temp       := v_temp || '    ';
246.     END LOOP;
247.   
248.     RETURN v_table_name;
249.   END;
251.   FUNCTION fun_return_table_name_tab8(p_v_table_name  VARCHAR2,
252.                                       p_n_col_max_len NUMBER) RETURN VARCHAR2 AS
253.     v_table_name  VARCHAR2(100);
254.     v_temp        VARCHAR2(100);
255.     n_col_max_len NUMBER;
256.   BEGIN
257.     v_table_name  := trim(p_v_table_name);
258.     n_col_max_len := p_n_col_max_len;
259.     v_temp        := v_table_name;
260.   
261.     WHILE mod(length(v_temp), 8) <> 0 LOOP
262.       v_temp := v_temp || ' ';
263.     END LOOP;
264.     IF mod(length(v_table_name), 8) <> 0 THEN
265.       v_table_name := v_table_name || chr(9);
266.     END IF;
267.   
268.     WHILE length(v_temp) < n_col_max_len LOOP
269.       v_table_name := v_table_name || chr(9);
270.       v_temp       := v_temp || '        ';
271.     END LOOP;
272.   
273.     RETURN v_table_name;
274.   END;
276.   PROCEDURE pro_tab_sql_tab4(p_v_gen_drop_sql        VARCHAR2 default 'T',
277.                              p_v_gen_create_sql      VARCHAR2 default 'T',
278.                              p_v_gen_store_clause    VARCHAR2 default 'T',
279.                              p_v_display_prompt_info VARCHAR2 default 'T') AS
280.     CURSOR cur_tables is
281.       SELECT table_name,
282.              tablespace_name,
283.              pct_free,
284.              pct_used,
285.              ini_trans,
286.              max_trans,
287.              initial_extent,
288.              next_extent,
289.              min_extents,
290.              max_extents,
291.              pct_increase,
292.              freelists,
293.              freelist_groups,
294.              buffer_pool,
295.              decode(logging, 'YES', 'LOGGING', 'NOLOGGING') logging
296.         FROM user_tables
297.        WHERE lower(TABLE_NAME) NOT IN
298.              ('temp_for_pkg_gen_sql2000_temp',
299.               'temp_for_pkg_gen_sql4000_temp')
300.        ORDER BY table_name;
301.   
302.     CURSOR cur_cols(t_name VARCHAR2) is
303.       SELECT table_name,
304.              column_name,
305.              data_type,
306.              data_length,
307.              data_precision,
308.              data_scale,
309.              nullable,
310.              data_default
311.         FROM user_tab_columns
312.        WHERE table_name = t_name
313.        ORDER BY column_id;
314.   
315.     v_tab_name            VARCHAR2(40);
316.     v_tabsp_name          VARCHAR2(40);
317.     n_mpct_free           user_tables.pct_free%TYPE;
318.     n_mpct_used           user_tables.pct_used%TYPE;
319.     n_mini_trans          user_tables.ini_trans%TYPE;
320.     n_mmax_trans          user_tables.max_trans%TYPE;
321.     n_mini_ext            user_tables.initial_extent%TYPE;
322.     n_mnext_ext           user_tables.next_extent%TYPE;
323.     n_mmin_ext            user_tables.min_extents%TYPE;
324.     n_mmax_ext            user_tables.max_extents%TYPE;
325.     n_mpct_inc            user_tables.pct_increase%TYPE;
326.     n_freelists           user_tables.freelists%TYPE;
327.     n_freelist_groups     user_tables.freelist_groups%TYPE;
328.     v_buffer_pool         user_tables.buffer_pool%TYPE;
329.     v_logging             VARCHAR2(15);
330.     col_name              VARCHAR2(40);
331.     ct                    NUMBER := 0;
332.     n_line_no             NUMBER := 0;
333.     col_content           VARCHAR2(120);
334.     n_col_max_len         NUMBER(10, 0);
335.     v_default_val         VARCHAR2(4000);
336.     v_gen_drop_tab_sql    VARCHAR2(10);
337.     v_gen_create_tab_sql  VARCHAR2(10);
338.     v_gen_store_clause    VARCHAR2(10);
339.     v_display_prompt_info VARCHAR2(10);
340.     v_username            VARCHAR2(30);
341.     v_sql                 VARCHAR2(2000);
342.     n_position            NUMBER(6, 0);
343.     v_object_type         VARCHAR2(10);
344.     v_prompt              VARCHAR2(10);
345.     PROCEDURE insert_rec(p_line_no     INTEGER,
346.                          p_object_type VARCHAR2,
347.                          p_user_name   VARCHAR2,
348.                          p_object_name VARCHAR2,
349.                          p_string      VARCHAR2) IS
350.     BEGIN
351.       INSERT INTO temp_for_pkg_gen_sql2000_temp
352.         (line_no, object_type, user_name, object_name, sql_text)
353.       VALUES
354.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
355.     END;
356.   BEGIN
357.     v_gen_drop_tab_sql    := p_v_gen_drop_sql;
358.     v_gen_create_tab_sql  := p_v_gen_create_sql;
359.     v_gen_store_clause    := p_v_gen_store_clause;
360.     v_display_prompt_info := p_v_display_prompt_info;
361.     v_prompt              := '-- prompt ';
362.     v_object_type         := 'TABLE_4';
363.     n_line_no             := 1;
364.   
365.     SELECT username INTO v_username FROM user_users;
366.   
367.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
368.     COMMIT;
369.   
370.     FOR cur_tables_rec IN cur_tables LOOP
371.       v_tab_name        := cur_tables_rec.table_name;
372.       v_tabsp_name      := cur_tables_rec.tablespace_name;
373.       n_mpct_free       := cur_tables_rec.pct_free;
374.       n_mpct_used       := cur_tables_rec.pct_used;
375.       n_mini_trans      := cur_tables_rec.ini_trans;
376.       n_mmax_trans      := cur_tables_rec.max_trans;
377.       n_mini_ext        := cur_tables_rec.initial_extent;
378.       n_mnext_ext       := cur_tables_rec.next_extent;
379.       n_mmin_ext        := cur_tables_rec.min_extents;
380.       n_mmax_ext        := cur_tables_rec.max_extents;
381.       n_mpct_inc        := nvl(cur_tables_rec.pct_increase, 0);
382.       n_freelists       := cur_tables_rec.freelists;
383.       n_freelist_groups := cur_tables_rec.freelist_groups;
384.       v_buffer_pool     := cur_tables_rec.buffer_pool;
385.       v_logging         := cur_tables_rec.logging;
386.     
387.       IF v_gen_drop_tab_sql = 'T' THEN
388.         IF upper(v_display_prompt_info) = 'T' THEN
389.           v_sql := v_prompt || ' Drop Table ' || v_tab_name;
390.           insert_rec(n_line_no,
391.                      v_object_type,
392.                      v_username,
393.                      v_tab_name,
394.                      v_sql);
395.           n_line_no := n_line_no + 1;
396.         END IF;
397.       
398.         v_sql := 'ALTER TABLE ' || v_tab_name ||
399.                  ' drop primary key cascade;';
400.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
401.         n_line_no := n_line_no + 1;
402.       
403.         v_sql := 'DROP TABLE ' || v_tab_name || ' cascade constraint;';
404.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
405.         n_line_no := n_line_no + 1;
406.       
407.         v_sql := '                                   ';
408.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
409.         n_line_no := n_line_no + 1;
410.       END IF;
411.     
412.       IF upper(v_gen_create_tab_sql) = 'T' THEN
413.         IF upper(v_display_prompt_info) = 'T' THEN
414.           v_sql := v_prompt || ' Create Table ' || v_tab_name;
415.           insert_rec(n_line_no,
416.                      v_object_type,
417.                      v_username,
418.                      v_tab_name,
419.                      v_sql);
420.           n_line_no := n_line_no + 1;
421.         END IF;
422.       
423.         v_sql := 'CREATE TABLE ' || v_tab_name || '(';
424.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
425.         n_line_no := n_line_no + 1;
426.       
427.         -- 找到最长的列名的长度
428.         n_col_max_len := 1;
429.         FOR cur_cols_rec IN cur_cols(v_tab_name) LOOP
430.           IF n_col_max_len < length(trim(cur_cols_rec.column_name)) THEN
431.             n_col_max_len := length(trim(cur_cols_rec.column_name));
432.           END IF;
433.         END LOOP;
434.       
435.         ct := 0;
436.       
437.         FOR cur_cols_rec IN cur_cols(v_tab_name) LOOP
438.           ct            := ct + 1;
439.           v_default_val := cur_cols_rec.data_default;
440.           v_default_val := trim(v_default_val);
441.           n_position    := instr(v_default_val, chr(10), -1);
442.           WHILE n_position > 0 LOOP
443.             IF n_position = length(v_default_val) THEN
444.               v_default_val := substr(v_default_val, 1, n_position - 1);
445.             ELSE
446.               v_default_val := substr(v_default_val, 1, n_position - 1) ||
447.                                substr(v_default_val, n_position + 1);
448.             END IF;
449.             n_position := instr(v_default_val, chr(10), -1);
450.           END LOOP;
451.           n_position := instr(v_default_val, chr(9), -1);
452.           IF n_position = length(v_default_val) THEN
453.             v_default_val := substr(v_default_val, 1, n_position - 1);
454.           END IF;
455.           IF ct = 1 THEN
456.             INSERT INTO temp_for_pkg_gen_sql2000_temp
457.               (line_no, object_type, user_name, object_name, sql_text)
458.             VALUES
459.               (n_line_no,
460.                v_object_type,
461.                v_username,
462.                v_tab_name,
463.                chr(9) ||
464.                pkg_gen_user_sql.fun_return_table_name_tab4(trim(cur_cols_rec.column_name),
465.                                                            n_col_max_len) ||
466.                chr(9) || trim(cur_cols_rec.data_type) ||
467.                decode(cur_cols_rec.data_type,
468.                       'VARCHAR2',
469.                       '(' || to_char(cur_cols_rec.data_length) || ')',
470.                       'VARCHAR',
471.                       '(' || to_char(cur_cols_rec.data_length) || ')',
472.                       'NVARCHAR2',
473.                       '(' || to_char(cur_cols_rec.data_length) || ')',
474.                       'NVARCHAR',
475.                       '(' || to_char(cur_cols_rec.data_length) || ')',
476.                       'CHAR',
477.                       '(' || to_char(cur_cols_rec.data_length) || ')',
478.                       'NCHAR',
479.                       '(' || to_char(cur_cols_rec.data_length) || ')',
480.                       'NUMBER',
481.                       decode(cur_cols_rec.data_precision,
482.                              null,
483.                              '',
484.                              '(' || to_char(cur_cols_rec.data_precision) ||
485.                              decode(cur_cols_rec.data_scale,
486.                                     null,
487.                                     ')',
488.                                     ',' || to_char(cur_cols_rec.data_scale) || ')')),
489.                       '') ||
490.                decode(cur_cols_rec.nullable,
491.                       'Y',
492.                       decode(v_default_val,
493.                              null,
494.                              ',',
495.                              chr(9) || 'default' || chr(9) || v_default_val || ','),
496.                       decode(v_default_val,
497.                              null,
498.                              chr(9) || 'NOT NULL,',
499.                              chr(9) || 'default' || chr(9) || v_default_val ||
500.                              chr(9) || chr(9) || 'NOT NULL,')));
501.           
502.             n_line_no := n_line_no + 1;
503.           ELSE
504.             INSERT INTO temp_for_pkg_gen_sql2000_temp
505.               (line_no, object_type, user_name, object_name, sql_text)
506.             VALUES
507.               (n_line_no,
508.                v_object_type,
509.                v_username,
510.                v_tab_name,
511.                chr(9) ||
512.                pkg_gen_user_sql.fun_return_table_name_tab4(trim(cur_cols_rec.column_name),
513.                                                            n_col_max_len) ||
514.                chr(9) || trim(cur_cols_rec.data_type) ||
515.                decode(cur_cols_rec.data_type,
516.                       'VARCHAR2',
517.                       '(' || to_char(cur_cols_rec.data_length) || ')',
518.                       'VARCHAR',
519.                       '(' || to_char(cur_cols_rec.data_length) || ')',
520.                       'NVARCHAR2',
521.                       '(' || to_char(cur_cols_rec.data_length) || ')',
522.                       'NVARCHAR',
523.                       '(' || to_char(cur_cols_rec.data_length) || ')',
524.                       'CHAR',
525.                       '(' || to_char(cur_cols_rec.data_length) || ')',
526.                       'NCHAR',
527.                       '(' || to_char(cur_cols_rec.data_length) || ')',
528.                       'NUMBER',
529.                       decode(cur_cols_rec.data_precision,
530.                              null,
531.                              '',
532.                              '(' || to_char(cur_cols_rec.data_precision) ||
533.                              decode(cur_cols_rec.data_scale,
534.                                     null,
535.                                     ')',
536.                                     ',' || to_char(cur_cols_rec.data_scale) || ')')),
537.                       '') ||
538.                decode(cur_cols_rec.nullable,
539.                       'Y',
540.                       decode(v_default_val,
541.                              null,
542.                              ',',
543.                              chr(9) || 'default' || chr(9) || v_default_val || ','),
544.                       decode(v_default_val,
545.                              null,
546.                              chr(9) || 'NOT NULL,',
547.                              chr(9) || 'default' || chr(9) || v_default_val ||
548.                              chr(9) || chr(9) || 'NOT NULL,')));
549.             n_line_no := n_line_no + 1;
550.           END IF;
551.         END LOOP;
552.       
553.         SELECT rtrim(sql_text)
554.           into col_content
555.           FROM temp_for_pkg_gen_sql2000_temp
556.          WHERE line_no = n_line_no - 1
557.            AND object_type = v_object_type;
558.         col_content := substr(col_content, 1, length(col_content) - 1);
559.       
560.         UPDATE temp_for_pkg_gen_sql2000_temp
561.            SET sql_text = col_content
562.          WHERE line_no = n_line_no - 1
563.            AND object_type = v_object_type;
564.       
565.         v_sql := ')';
566.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
567.         n_line_no := n_line_no + 1;
568.       
569.         IF v_gen_store_clause = 'T' THEN
570.         
571.           v_sql := v_logging;
572.           insert_rec(n_line_no,
573.                      v_object_type,
574.                      v_username,
575.                      v_tab_name,
576.                      v_sql);
577.           n_line_no := n_line_no + 1;
578.         
579.           v_sql := 'TABLESPACE ' || v_tabsp_name;
580.           insert_rec(n_line_no,
581.                      v_object_type,
582.                      v_username,
583.                      v_tab_name,
584.                      v_sql);
585.           n_line_no := n_line_no + 1;
586.         
587.           v_sql := 'PCTFREE ' || n_mpct_free || chr(9) || 'PCTUSED ' ||
588.                    n_mpct_used;
589.           insert_rec(n_line_no,
590.                      v_object_type,
591.                      v_username,
592.                      v_tab_name,
593.                      v_sql);
594.           n_line_no := n_line_no + 1;
595.         
596.           v_sql := 'INITRANS ' || n_mini_trans || chr(9) || 'MAXTRANS ' ||
597.                    n_mmax_trans;
598.           insert_rec(n_line_no,
599.                      v_object_type,
600.                      v_username,
601.                      v_tab_name,
602.                      v_sql);
603.           n_line_no := n_line_no + 1;
604.         
605.           v_sql := 'STORAGE (';
606.           insert_rec(n_line_no,
607.                      v_object_type,
608.                      v_username,
609.                      v_tab_name,
610.                      v_sql);
611.           n_line_no := n_line_no + 1;
612.         
613.           v_sql := chr(9) || 'INITIAL ' || n_mini_ext;
614.           insert_rec(n_line_no,
615.                      v_object_type,
616.                      v_username,
617.                      v_tab_name,
618.                      v_sql);
619.           n_line_no := n_line_no + 1;
620.         
621.           IF n_mnext_ext IS NOT NULL THEN
622.             UPDATE temp_for_pkg_gen_sql2000_temp
623.                SET sql_text = sql_text || chr(9) || 'NEXT ' ||
624.                               to_char(n_mnext_ext)
625.              WHERE line_no = n_line_no - 1
626.                AND object_type = v_object_type;
627.           END IF;
628.         
629.           UPDATE temp_for_pkg_gen_sql2000_temp
630.              SET sql_text = sql_text || chr(9) || 'PCTINCREASE ' ||
631.                             to_char(n_mpct_inc)
632.            WHERE line_no = n_line_no - 1
633.              AND object_type = v_object_type;
634.         
635.           v_sql := chr(9) || 'FREELISTS ' || to_char(n_freelists);
636.           insert_rec(n_line_no,
637.                      v_object_type,
638.                      v_username,
639.                      v_tab_name,
640.                      v_sql);
641.           n_line_no := n_line_no + 1;
642.         
643.           UPDATE temp_for_pkg_gen_sql2000_temp
644.              SET sql_text = sql_text || chr(9) || 'FREELIST GROUPS ' ||
645.                             to_char(n_freelist_groups)
646.            WHERE line_no = n_line_no - 1
647.              AND object_type = v_object_type;
648.         
649.           v_sql := chr(9) || 'BUFFER_POOL ' || v_buffer_pool;
650.           insert_rec(n_line_no,
651.                      v_object_type,
652.                      v_username,
653.                      v_tab_name,
654.                      v_sql);
655.           n_line_no := n_line_no + 1;
656.         
657.           UPDATE temp_for_pkg_gen_sql2000_temp
658.              SET sql_text = sql_text || ');'
659.            WHERE line_no = n_line_no - 1
660.              AND object_type = v_object_type;
661.         ELSE
662.           UPDATE temp_for_pkg_gen_sql2000_temp
663.              SET sql_text = sql_text || ';'
664.            WHERE line_no = n_line_no - 1
665.              AND object_type = v_object_type;
666.         END IF;
667.       
668.         v_sql := '                                   ';
669.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
670.         n_line_no := n_line_no + 1;
671.       END IF;
672.     END LOOP;
673.     COMMIT;
674.   END;
676.   PROCEDURE pro_tab_sql_tab8(p_v_gen_drop_sql        VARCHAR2 default 'T',
677.                              p_v_gen_create_sql      VARCHAR2 default 'T',
678.                              p_v_gen_store_clause    VARCHAR2 default 'T',
679.                              p_v_display_prompt_info VARCHAR2 default 'T') AS
680.     CURSOR cur_tables IS
681.       SELECT table_name,
682.              tablespace_name,
683.              pct_free,
684.              pct_used,
685.              ini_trans,
686.              max_trans,
687.              initial_extent,
688.              next_extent,
689.              min_extents,
690.              max_extents,
691.              pct_increase,
692.              freelists,
693.              freelist_groups,
694.              buffer_pool,
695.              decode(logging, 'YES', 'LOGGING', 'NOLOGGING') logging
696.         FROM user_tables
697.        WHERE lower(TABLE_NAME) NOT IN
698.              ('temp_for_pkg_gen_sql2000_temp',
699.               'temp_for_pkg_gen_sql4000_temp')
700.        ORDER BY table_name;
701.   
702.     CURSOR cur_cols(t_name VARCHAR2) IS
703.       SELECT table_name,
704.              column_name,
705.              data_type,
706.              data_length,
707.              data_precision,
708.              data_scale,
709.              nullable,
710.              data_default
711.         FROM user_tab_columns
712.        WHERE table_name = t_name
713.        ORDER BY column_id;
714.   
715.     v_tab_name            VARCHAR2(40);
716.     v_tabsp_name          VARCHAR2(40);
717.     n_mpct_free           user_tables.pct_free%TYPE;
718.     n_mpct_used           user_tables.pct_used%TYPE;
719.     n_mini_trans          user_tables.ini_trans%TYPE;
720.     n_mmax_trans          user_tables.max_trans%TYPE;
721.     n_mini_ext            user_tables.initial_extent%TYPE;
722.     n_mnext_ext           user_tables.next_extent%TYPE;
723.     n_mmin_ext            user_tables.min_extents%TYPE;
724.     n_mmax_ext            user_tables.max_extents%TYPE;
725.     n_mpct_inc            user_tables.pct_increase%TYPE;
726.     n_freelists           user_tables.freelists%TYPE;
727.     n_freelist_groups     user_tables.freelist_groups%TYPE;
728.     v_buffer_pool         user_tables.buffer_pool%TYPE;
729.     v_logging             VARCHAR2(15);
730.     col_name              VARCHAR2(40);
731.     ct                    NUMBER := 0;
732.     n_line_no             NUMBER := 0;
733.     col_content           VARCHAR2(120);
734.     n_col_max_len         NUMBER(10, 0);
735.     v_default_val         VARCHAR2(4000);
736.     v_gen_drop_tab_sql    VARCHAR2(10);
737.     v_gen_create_tab_sql  VARCHAR2(10);
738.     v_gen_store_clause    VARCHAR2(10);
739.     v_display_prompt_info VARCHAR2(10);
740.     v_username            VARCHAR2(30);
741.     v_sql                 VARCHAR2(2000);
742.     n_position            NUMBER(6, 0);
743.     v_object_type         VARCHAR2(10);
744.     v_prompt              VARCHAR2(10);
745.     PROCEDURE insert_rec(p_line_no     INTEGER,
746.                          p_object_type VARCHAR2,
747.                          p_user_name   VARCHAR2,
748.                          p_object_name VARCHAR2,
749.                          p_string      VARCHAR2) IS
750.     BEGIN
751.       INSERT INTO temp_for_pkg_gen_sql2000_temp
752.         (line_no, object_type, user_name, object_name, sql_text)
753.       VALUES
754.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
755.     END;
756.   BEGIN
757.     v_gen_drop_tab_sql    := p_v_gen_drop_sql;
758.     v_gen_create_tab_sql  := p_v_gen_create_sql;
759.     v_gen_store_clause    := p_v_gen_store_clause;
760.     v_display_prompt_info := p_v_display_prompt_info;
761.     v_prompt              := '-- prompt ';
762.     v_object_type         := 'TABLE_8';
763.     n_line_no             := 1;
764.   
765.     SELECT username INTO v_username FROM user_users;
766.   
767.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
768.     COMMIT;
769.   
770.     FOR cur_tables_rec IN cur_tables LOOP
771.       v_tab_name        := cur_tables_rec.table_name;
772.       v_tabsp_name      := cur_tables_rec.tablespace_name;
773.       n_mpct_free       := cur_tables_rec.pct_free;
774.       n_mpct_used       := cur_tables_rec.pct_used;
775.       n_mini_trans      := cur_tables_rec.ini_trans;
776.       n_mmax_trans      := cur_tables_rec.max_trans;
777.       n_mini_ext        := cur_tables_rec.initial_extent;
778.       n_mnext_ext       := cur_tables_rec.next_extent;
779.       n_mmin_ext        := cur_tables_rec.min_extents;
780.       n_mmax_ext        := cur_tables_rec.max_extents;
781.       n_mpct_inc        := nvl(cur_tables_rec.pct_increase, 0);
782.       n_freelists       := cur_tables_rec.freelists;
783.       n_freelist_groups := cur_tables_rec.freelist_groups;
784.       v_buffer_pool     := cur_tables_rec.buffer_pool;
785.       v_logging         := cur_tables_rec.logging;
786.     
787.       IF v_gen_drop_tab_sql = 'T' THEN
788.         IF upper(v_display_prompt_info) = 'T' THEN
789.           v_sql := v_prompt || ' Drop Table ' || v_tab_name;
790.           insert_rec(n_line_no,
791.                      v_object_type,
792.                      v_username,
793.                      v_tab_name,
794.                      v_sql);
795.           n_line_no := n_line_no + 1;
796.         END IF;
797.       
798.         v_sql := 'ALTER TABLE ' || v_tab_name ||
799.                  ' drop primary key cascade;';
800.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
801.         n_line_no := n_line_no + 1;
802.       
803.         v_sql := 'DROP TABLE ' || v_tab_name || ' cascade constraint;';
804.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
805.         n_line_no := n_line_no + 1;
806.       
807.         v_sql := '                                   ';
808.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
809.         n_line_no := n_line_no + 1;
810.       END IF;
811.     
812.       IF upper(v_gen_create_tab_sql) = 'T' THEN
813.         IF upper(v_display_prompt_info) = 'T' THEN
814.           v_sql := v_prompt || ' Create Table ' || v_tab_name;
815.           insert_rec(n_line_no,
816.                      v_object_type,
817.                      v_username,
818.                      v_tab_name,
819.                      v_sql);
820.           n_line_no := n_line_no + 1;
821.         END IF;
822.       
823.         v_sql := 'CREATE TABLE ' || v_tab_name || '(';
824.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
825.         n_line_no := n_line_no + 1;
826.       
827.         -- 找到最长的列名的长度
828.         n_col_max_len := 1;
829.         FOR cur_cols_rec IN cur_cols(v_tab_name) LOOP
830.           IF n_col_max_len < length(trim(cur_cols_rec.column_name)) THEN
831.             n_col_max_len := length(trim(cur_cols_rec.column_name));
832.           END IF;
833.         END LOOP;
834.       
835.         ct := 0;
836.       
837.         FOR cur_cols_rec IN cur_cols(v_tab_name) LOOP
838.           ct            := ct + 1;
839.           v_default_val := cur_cols_rec.data_default;
840.           v_default_val := trim(v_default_val);
841.           n_position    := instr(v_default_val, chr(10), -1);
842.           WHILE n_position > 0 LOOP
843.             IF n_position = length(v_default_val) THEN
844.               v_default_val := substr(v_default_val, 1, n_position - 1);
845.             ELSE
846.               v_default_val := substr(v_default_val, 1, n_position - 1) ||
847.                                substr(v_default_val, n_position + 1);
848.             END IF;
849.             n_position := instr(v_default_val, chr(10), -1);
850.           END LOOP;
851.           n_position := instr(v_default_val, chr(9), -1);
852.           IF n_position = length(v_default_val) THEN
853.             v_default_val := substr(v_default_val, 1, n_position - 1);
854.           END IF;
855.           IF ct = 1 THEN
856.             INSERT INTO temp_for_pkg_gen_sql2000_temp
857.               (line_no, object_type, user_name, object_name, sql_text)
858.             VALUES
859.               (n_line_no,
860.                v_object_type,
861.                v_username,
862.                v_tab_name,
863.                chr(9) ||
864.                pkg_gen_user_sql.fun_return_table_name_tab8(trim(cur_cols_rec.column_name),
865.                                                            n_col_max_len) ||
866.                chr(9) || trim(cur_cols_rec.data_type) ||
867.                decode(cur_cols_rec.data_type,
868.                       'VARCHAR2',
869.                       '(' || to_char(cur_cols_rec.data_length) || ')',
870.                       'VARCHAR',
871.                       '(' || to_char(cur_cols_rec.data_length) || ')',
872.                       'NVARCHAR2',
873.                       '(' || to_char(cur_cols_rec.data_length) || ')',
874.                       'NVARCHAR',
875.                       '(' || to_char(cur_cols_rec.data_length) || ')',
876.                       'CHAR',
877.                       '(' || to_char(cur_cols_rec.data_length) || ')',
878.                       'NCHAR',
879.                       '(' || to_char(cur_cols_rec.data_length) || ')',
880.                       'NUMBER',
881.                       decode(cur_cols_rec.data_precision,
882.                              null,
883.                              '',
884.                              '(' || to_char(cur_cols_rec.data_precision) ||
885.                              decode(cur_cols_rec.data_scale,
886.                                     null,
887.                                     ')',
888.                                     ',' || to_char(cur_cols_rec.data_scale) || ')')),
889.                       '') ||
890.                decode(cur_cols_rec.nullable,
891.                       'Y',
892.                       decode(v_default_val,
893.                              null,
894.                              ',',
895.                              chr(9) || 'default' || chr(9) || v_default_val || ','),
896.                       decode(v_default_val,
897.                              null,
898.                              chr(9) || 'NOT NULL,',
899.                              chr(9) || 'default' || chr(9) || v_default_val ||
900.                              chr(9) || chr(9) || 'NOT NULL,')));
901.           
902.             n_line_no := n_line_no + 1;
903.           ELSE
904.             INSERT INTO temp_for_pkg_gen_sql2000_temp
905.               (line_no, object_type, user_name, object_name, sql_text)
906.             VALUES
907.               (n_line_no,
908.                v_object_type,
909.                v_username,
910.                v_tab_name,
911.                chr(9) ||
912.                pkg_gen_user_sql.fun_return_table_name_tab8(trim(cur_cols_rec.column_name),
913.                                                            n_col_max_len) ||
914.                chr(9) || trim(cur_cols_rec.data_type) ||
915.                decode(cur_cols_rec.data_type,
916.                       'VARCHAR2',
917.                       '(' || to_char(cur_cols_rec.data_length) || ')',
918.                       'VARCHAR',
919.                       '(' || to_char(cur_cols_rec.data_length) || ')',
920.                       'NVARCHAR2',
921.                       '(' || to_char(cur_cols_rec.data_length) || ')',
922.                       'NVARCHAR',
923.                       '(' || to_char(cur_cols_rec.data_length) || ')',
924.                       'CHAR',
925.                       '(' || to_char(cur_cols_rec.data_length) || ')',
926.                       'NCHAR',
927.                       '(' || to_char(cur_cols_rec.data_length) || ')',
928.                       'NUMBER',
929.                       decode(cur_cols_rec.data_precision,
930.                              null,
931.                              '',
932.                              '(' || to_char(cur_cols_rec.data_precision) ||
933.                              decode(cur_cols_rec.data_scale,
934.                                     null,
935.                                     ')',
936.                                     ',' || to_char(cur_cols_rec.data_scale) || ')')),
937.                       '') ||
938.                decode(cur_cols_rec.nullable,
939.                       'Y',
940.                       decode(v_default_val,
941.                              null,
942.                              ',',
943.                              chr(9) || 'default' || chr(9) || v_default_val || ','),
944.                       decode(v_default_val,
945.                              null,
946.                              chr(9) || 'NOT NULL,',
947.                              chr(9) || 'default' || chr(9) || v_default_val ||
948.                              chr(9) || chr(9) || 'NOT NULL,')));
949.             n_line_no := n_line_no + 1;
950.           END IF;
951.         END LOOP;
952.       
953.         SELECT rtrim(sql_text)
954.           into col_content
955.           FROM temp_for_pkg_gen_sql2000_temp
956.          WHERE line_no = n_line_no - 1
957.            AND object_type = v_object_type;
958.         col_content := substr(col_content, 1, length(col_content) - 1);
959.       
960.         UPDATE temp_for_pkg_gen_sql2000_temp
961.            SET sql_text = col_content
962.          WHERE line_no = n_line_no - 1
963.            AND object_type = v_object_type;
964.       
965.         v_sql := ')';
966.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
967.         n_line_no := n_line_no + 1;
968.       
969.         IF v_gen_store_clause = 'T' THEN
970.         
971.           v_sql := v_logging;
972.           insert_rec(n_line_no,
973.                      v_object_type,
974.                      v_username,
975.                      v_tab_name,
976.                      v_sql);
977.           n_line_no := n_line_no + 1;
978.         
979.           v_sql := 'TABLESPACE ' || v_tabsp_name;
980.           insert_rec(n_line_no,
981.                      v_object_type,
982.                      v_username,
983.                      v_tab_name,
984.                      v_sql);
985.           n_line_no := n_line_no + 1;
986.         
987.           v_sql := 'PCTFREE ' || n_mpct_free || chr(9) || 'PCTUSED ' ||
988.                    n_mpct_used;
989.           insert_rec(n_line_no,
990.                      v_object_type,
991.                      v_username,
992.                      v_tab_name,
993.                      v_sql);
994.           n_line_no := n_line_no + 1;
995.         
996.           v_sql := 'INITRANS ' || n_mini_trans || chr(9) || 'MAXTRANS ' ||
997.                    n_mmax_trans;
998.           insert_rec(n_line_no,
999.                      v_object_type,
1000.                      v_username,
1001.                      v_tab_name,
1002.                      v_sql);
1003.           n_line_no := n_line_no + 1;
1004.         
1005.           v_sql := 'STORAGE (';
1006.           insert_rec(n_line_no,
1007.                      v_object_type,
1008.                      v_username,
1009.                      v_tab_name,
1010.                      v_sql);
1011.           n_line_no := n_line_no + 1;
1012.         
1013.           v_sql := chr(9) || 'INITIAL ' || n_mini_ext;
1014.           insert_rec(n_line_no,
1015.                      v_object_type,
1016.                      v_username,
1017.                      v_tab_name,
1018.                      v_sql);
1019.           n_line_no := n_line_no + 1;
1020.         
1021.           IF n_mnext_ext IS NOT NULL THEN
1022.             UPDATE temp_for_pkg_gen_sql2000_temp
1023.                SET sql_text = sql_text || chr(9) || 'NEXT ' ||
1024.                               to_char(n_mnext_ext)
1025.              WHERE line_no = n_line_no - 1
1026.                AND object_type = v_object_type;
1027.           END IF;
1028.         
1029.           UPDATE temp_for_pkg_gen_sql2000_temp
1030.              SET sql_text = sql_text || chr(9) || 'PCTINCREASE ' ||
1031.                             to_char(n_mpct_inc)
1032.            WHERE line_no = n_line_no - 1
1033.              AND object_type = v_object_type;
1034.         
1035.           v_sql := chr(9) || 'FREELISTS ' || to_char(n_freelists);
1036.           insert_rec(n_line_no,
1037.                      v_object_type,
1038.                      v_username,
1039.                      v_tab_name,
1040.                      v_sql);
1041.           n_line_no := n_line_no + 1;
1042.         
1043.           UPDATE temp_for_pkg_gen_sql2000_temp
1044.              SET sql_text = sql_text || chr(9) || 'FREELIST GROUPS ' ||
1045.                             to_char(n_freelist_groups)
1046.            WHERE line_no = n_line_no - 1
1047.              AND object_type = v_object_type;
1048.         
1049.           v_sql := chr(9) || 'BUFFER_POOL ' || v_buffer_pool;
1050.           insert_rec(n_line_no,
1051.                      v_object_type,
1052.                      v_username,
1053.                      v_tab_name,
1054.                      v_sql);
1055.           n_line_no := n_line_no + 1;
1056.         
1057.           UPDATE temp_for_pkg_gen_sql2000_temp
1058.              SET sql_text = sql_text || ');'
1059.            WHERE line_no = n_line_no - 1
1060.              AND object_type = v_object_type;
1061.         ELSE
1062.           UPDATE temp_for_pkg_gen_sql2000_temp
1063.              SET sql_text = sql_text || ';'
1064.            WHERE line_no = n_line_no - 1
1065.              AND object_type = v_object_type;
1066.         END IF;
1067.       
1068.         v_sql := '                                   ';
1069.         insert_rec(n_line_no, v_object_type, v_username, v_tab_name, v_sql);
1070.         n_line_no := n_line_no + 1;
1071.       END IF;
1072.     END LOOP;
1073.     COMMIT;
1074.   END;
1076.   PROCEDURE pro_con_sql(p_v_con_type            VARCHAR2 default 'ALL',
1077.                         p_v_gen_drop_sql        VARCHAR2 default 'T',
1078.                         p_v_gen_create_sql      VARCHAR2 default 'T',
1079.                         p_v_gen_null_chk        VARCHAR2 default 'T',
1080.                         p_v_display_prompt_info VARCHAR2 default 'T') AS
1081.     CURSOR cur_tables is
1082.       SELECT table_name FROM user_tables ORDER BY table_name;
1083.   
1084.     CURSOR cur_con_cols(c_name VARCHAR2) IS
1085.       SELECT column_name
1086.         FROM user_cons_columns
1087.        WHERE constraint_name = c_name
1088.        ORDER BY position;
1089.   
1090.     type ref_cur is REF CURSOR; -- 定义一个ref 类型的游标
1091.     cur_cons ref_cur; -- 定义游标变量
1092.     TYPE type_con IS RECORD(
1093.       constraint_name   user_constraints.constraint_name%type,
1094.       r_constraint_name user_constraints.r_constraint_name%type,
1095.       constraint_type   user_constraints.constraint_type%type,
1096.       search_condition  user_constraints.search_condition%type);
1097.   
1098.     cur_cons_rec          type_con;
1099.     v_tab_name            VARCHAR2(40);
1100.     v_table_name          VARCHAR2(40);
1101.     v_type                VARCHAR2(1);
1102.     n_ct                  NUMBER := 0;
1103.     n_line_no             NUMBER := 0;
1104.     v_con_type            VARCHAR2(10);
1105.     v_con_sql             VARCHAR2(500);
1106.     v_sql                 VARCHAR2(2000);
1107.     v_gen_drop_con_sql    VARCHAR2(10);
1108.     v_display_prompt_info VARCHAR2(10);
1109.     v_gen_create_con_sql  VARCHAR2(10);
1110.     v_gen_null_chk        VARCHAR2(10);
1111.     v_username            VARCHAR2(30);
1112.     v_object_type         VARCHAR2(30);
1113.     v_prompt              VARCHAR2(30);
1114.     v_search_condition    VARCHAR2(1000);
1115.     PROCEDURE insert_rec(p_line_no     INTEGER,
1116.                          p_object_type VARCHAR2,
1117.                          p_user_name   VARCHAR2,
1118.                          p_con_name    VARCHAR2,
1119.                          p_string      VARCHAR2) IS
1120.     BEGIN
1121.       INSERT INTO temp_for_pkg_gen_sql2000_temp
1122.         (line_no, object_type, user_name, object_name, sql_text)
1123.       VALUES
1124.         (p_line_no, p_object_type, p_user_name, p_con_name, p_string);
1125.     END;
1126.   BEGIN
1127.     v_con_type            := p_v_con_type;
1128.     v_gen_drop_con_sql    := p_v_gen_drop_sql;
1129.     v_display_prompt_info := p_v_display_prompt_info;
1130.     v_gen_create_con_sql  := p_v_gen_create_sql;
1131.     v_gen_null_chk        := p_v_gen_null_chk;
1132.   
1133.     v_object_type := 'CONSTRAINT';
1134.     v_prompt      := '-- prompt ';
1135.     n_line_no     := 1;
1136.   
1137.     SELECT username INTO v_username FROM user_users;
1138.   
1139.     DELETE FROM temp_for_pkg_gen_sql2000_temp
1140.      WHERE object_type = v_object_type;
1141.   
1142.     COMMIT;
1143.   
1144.     FOR cur_tables_rec IN cur_tables LOOP
1145.       v_tab_name := trim(cur_tables_rec.table_name);
1146.     
1147.       -- Get the constraint info for specified table
1148.       v_con_sql := 'SELECT constraint_name, r_constraint_name, ';
1149.       v_con_sql := v_con_sql || '       constraint_type, search_condition ';
1150.       v_con_sql := v_con_sql || 'FROM user_constraints ';
1151.       IF upper(v_con_type) <> 'ALL' AND upper(v_con_type) <> 'A' AND
1152.          upper(v_con_type) <> 'NOT_PRI' THEN
1153.         v_con_sql := v_con_sql || 'WHERE  table_name = ''' || v_tab_name ||
1154.                      ''' AND constraint_type = ''' || upper(v_con_type) ||
1155.                      ''' ';
1156.       ELSIF upper(v_con_type) = 'NOT_PRI' THEN
1157.         v_con_sql := v_con_sql || 'WHERE  table_name = ''' || v_tab_name ||
1158.                      ''' AND constraint_type <> ''P'' ';
1159.       ELSE
1160.         v_con_sql := v_con_sql || 'WHERE  table_name = ''' || v_tab_name ||
1161.                      ''' ';
1162.       END IF;
1163.       v_con_sql := v_con_sql || 'ORDER BY constraint_name';
1164.     
1165.       OPEN cur_cons FOR v_con_sql;
1166.       LOOP
1167.         FETCH cur_cons
1168.           INTO cur_cons_rec;
1169.         EXIT WHEN cur_cons%NOTFOUND;
1170.       
1171.         v_type             := cur_cons_rec.constraint_type;
1172.         v_search_condition := cur_cons_rec.search_condition;
1173.       
1174.         IF upper(v_gen_drop_con_sql) = 'T' THEN
1175.         
1176.           IF v_gen_null_chk = 'F' AND v_type = 'C' AND
1177.              instr(v_search_condition, 'IS NOT NULL') > 0 THEN
1178.             -- do null
1179.             v_gen_null_chk := v_gen_null_chk;
1180.           ELSE
1181.             IF upper(v_display_prompt_info) = 'T' THEN
1182.               -- insert prompt info
1183.               INSERT INTO temp_for_pkg_gen_sql2000_temp
1184.                 (line_no, object_type, user_name, object_name, sql_text)
1185.               VALUES
1186.                 (n_line_no,
1187.                  v_object_type,
1188.                  v_username,
1189.                  cur_cons_rec.constraint_name,
1190.                  v_prompt || ' Drop constraints : ' || v_tab_name || '.' ||
1191.                  trim(cur_cons_rec.constraint_name) || ';  Type : ' ||
1192.                  decode(v_type,
1193.                         'C',
1194.                         'CHECK',
1195.                         'P',
1196.                         'PRIMARY KEY',
1197.                         'U',
1198.                         'UNIQUE',
1199.                         'R',
1200.                         'FROEIGN RELATION',
1201.                         v_type));
1202.               n_line_no := n_line_no + 1;
1203.             END IF;
1204.           
1205.             v_sql := 'alter table ' || v_tab_name || ' drop constraint ' ||
1206.                      cur_cons_rec.constraint_name || ';';
1207.             insert_rec(n_line_no,
1208.                        v_object_type,
1209.                        v_username,
1210.                        cur_cons_rec.constraint_name,
1211.                        v_sql);
1212.             n_line_no := n_line_no + 1;
1213.           
1214.             v_sql := '                    ';
1215.             insert_rec(n_line_no,
1216.                        v_object_type,
1217.                        v_username,
1218.                        cur_cons_rec.constraint_name,
1219.                        v_sql);
1220.             n_line_no := n_line_no + 1;
1221.           END IF;
1222.         END IF;
1223.       
1224.         IF upper(v_gen_create_con_sql) = 'T' THEN
1225.           IF v_gen_null_chk = 'F' AND v_type = 'C' AND
1226.              instr(v_search_condition, 'IS NOT NULL') > 0 THEN
1227.             -- do null
1228.             v_gen_null_chk := v_gen_null_chk;
1229.           ELSE
1230.             IF upper(v_display_prompt_info) = 'T' THEN
1231.               -- insert prompt info
1232.               INSERT INTO temp_for_pkg_gen_sql2000_temp
1233.                 (line_no, object_type, user_name, object_name, sql_text)
1234.               VALUES
1235.                 (n_line_no,
1236.                  v_object_type,
1237.                  v_username,
1238.                  cur_cons_rec.constraint_name,
1239.                  v_prompt || ' Add constraints : ' || v_tab_name || '.' ||
1240.                  trim(cur_cons_rec.constraint_name) || ';  Type : ' ||
1241.                  decode(v_type,
1242.                         'C',
1243.                         'CHECK',
1244.                         'P',
1245.                         'PRIMARY KEY',
1246.                         'U',
1247.                         'UNIQUE',
1248.                         'R',
1249.                         'FROEIGN RELATION',
1250.                         v_type));
1251.               n_line_no := n_line_no + 1;
1252.             END IF;
1253.           
1254.             v_sql := 'alter table ' || v_tab_name || ' add constraint ' ||
1255.                      trim(cur_cons_rec.constraint_name);
1256.             insert_rec(n_line_no,
1257.                        v_object_type,
1258.                        v_username,
1259.                        cur_cons_rec.constraint_name,
1260.                        v_sql);
1261.             n_line_no := n_line_no + 1;
1262.           
1263.             IF v_type = 'C' THEN
1264.               v_sql := '     check( ' ||
1265.                        trim(cur_cons_rec.search_condition);
1266.               insert_rec(n_line_no,
1267.                          v_object_type,
1268.                          v_username,
1269.                          cur_cons_rec.constraint_name,
1270.                          v_sql);
1271.               n_line_no := n_line_no + 1;
1272.             END IF;
1273.           
1274.             n_ct := 0;
1275.           
1276.             FOR cur_con_cols_rec IN cur_con_cols(cur_cons_rec.constraint_name) LOOP
1277.               n_ct := n_ct + 1;
1278.             
1279.               IF n_ct = 1 THEN
1280.                 IF v_type = 'R' THEN
1281.                   v_sql := '     foreign key( ' ||
1282.                            trim(cur_con_cols_rec.column_name);
1283.                   insert_rec(n_line_no,
1284.                              v_object_type,
1285.                              v_username,
1286.                              cur_cons_rec.constraint_name,
1287.                              v_sql);
1288.                   n_line_no := n_line_no + 1;
1289.                 END IF;
1290.               
1291.                 IF v_type = 'P' THEN
1292.                   v_sql := '     primary key( ' ||
1293.                            trim(cur_con_cols_rec.column_name);
1294.                   insert_rec(n_line_no,
1295.                              v_object_type,
1296.                              v_username,
1297.                              cur_cons_rec.constraint_name,
1298.                              v_sql);
1299.                   n_line_no := n_line_no + 1;
1300.                 END IF;
1301.               
1302.                 IF v_type = 'U' THEN
1303.                   v_sql := '     unique( ' ||
1304.                            trim(cur_con_cols_rec.column_name);
1305.                   insert_rec(n_line_no,
1306.                              v_object_type,
1307.                              v_username,
1308.                              cur_cons_rec.constraint_name,
1309.                              v_sql);
1310.                   n_line_no := n_line_no + 1;
1311.                 END IF;
1312.               
1313.               ELSE
1314.                 UPDATE temp_for_pkg_gen_sql2000_temp
1315.                    SET sql_text = rtrim(sql_text) || ' , ' ||
1316.                                   trim(cur_con_cols_rec.column_name)
1317.                  WHERE line_no = n_line_no - 1
1318.                    AND object_type = v_object_type;
1319.               END IF;
1320.             
1321.             END LOOP;
1322.           
1323.             UPDATE temp_for_pkg_gen_sql2000_temp
1324.                SET sql_text = rtrim(sql_text) || ' )'
1325.              WHERE line_no = n_line_no - 1
1326.                AND object_type = v_object_type;
1327.           
1328.             IF v_type = 'R' THEN
1329.               SELECT table_name
1330.                 into v_table_name
1331.                 FROM user_constraints
1332.                WHERE constraint_name = cur_cons_rec.r_constraint_name;
1333.             
1334.               n_ct := 0;
1335.             
1336.               FOR cur_con_cols_rec IN cur_con_cols(cur_cons_rec.r_constraint_name) LOOP
1337.               
1338.                 n_ct := n_ct + 1;
1339.               
1340.                 IF n_ct = 1 THEN
1341.                   v_sql := '     references ' || v_table_name || '( ';
1342.                   insert_rec(n_line_no,
1343.                              v_object_type,
1344.                              v_username,
1345.                              cur_cons_rec.constraint_name,
1346.                              v_sql);
1347.                   n_line_no := n_line_no + 1;
1348.                 
1349.                   UPDATE temp_for_pkg_gen_sql2000_temp
1350.                      SET sql_text = rtrim(sql_text) ||
1351.                                     trim(cur_con_cols_rec.column_name)
1352.                    WHERE line_no = n_line_no - 1
1353.                      AND object_type = v_object_type;
1354.                 ELSE
1355.                   UPDATE temp_for_pkg_gen_sql2000_temp
1356.                      SET sql_text = rtrim(sql_text) || ' , ' ||
1357.                                     trim(cur_con_cols_rec.column_name)
1358.                    WHERE line_no = n_line_no - 1
1359.                      AND object_type = v_object_type;
1360.                 END IF;
1361.               END LOOP;
1362.             
1363.               UPDATE temp_for_pkg_gen_sql2000_temp
1364.                  SET sql_text = rtrim(sql_text) || ' )'
1365.                WHERE line_no = n_line_no - 1
1366.                  AND object_type = v_object_type;
1367.             ELSE
1368.               IF v_type = 'P' or v_type = 'U' THEN
1369.                 UPDATE temp_for_pkg_gen_sql2000_temp
1370.                    SET sql_text = rtrim(sql_text) || ' USING INDEX '
1371.                  WHERE line_no = n_line_no - 1
1372.                    AND object_type = v_object_type;
1373.               END IF;
1374.             END IF;
1375.           
1376.             UPDATE temp_for_pkg_gen_sql2000_temp
1377.                SET sql_text = sql_text || ';'
1378.              WHERE line_no = n_line_no - 1
1379.                AND object_type = v_object_type;
1380.           
1381.             v_sql := '         ';
1382.             insert_rec(n_line_no,
1383.                        v_object_type,
1384.                        v_username,
1385.                        cur_cons_rec.constraint_name,
1386.                        v_sql);
1387.             n_line_no := n_line_no + 1;
1388.           END IF;
1389.         END IF;
1390.       END LOOP;
1391.       CLOSE cur_cons;
1392.     END LOOP;
1393.     COMMIT;
1394.   END;
1396.   PROCEDURE pro_indx_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
1397.                          p_v_gen_create_sql      VARCHAR2 default 'T',
1398.                          p_v_gen_store_clause    VARCHAR2 default 'T',
1399.                          p_v_gen_ts_only         VARCHAR2 default 'F',
1400.                          p_v_display_prompt_info VARCHAR2 default 'T') AS
1401.     CURSOR cur_indxes IS
1402.       SELECT index_name,
1403.              table_owner,
1404.              table_name,
1405.              index_type,
1406.              uniqueness,
1407.              tablespace_name,
1408.              ini_trans,
1409.              max_trans,
1410.              initial_extent,
1411.              next_extent,
1412.              min_extents,
1413.              max_extents,
1414.              pct_increase,
1415.              pct_free,
1416.              freelists,
1417.              freelist_groups,
1418.              buffer_pool,
1419.              decode(logging, 'YES', 'LOGGING', 'NOLOGGING') logging
1420.         FROM user_indexes
1421.        WHERE index_type <> 'LOB'
1422.        ORDER BY index_name;
1423.   
1424.     CURSOR cur_cols(c_ind VARCHAR2, c_tab VARCHAR2) IS
1425.       SELECT column_name
1426.         FROM user_ind_columns
1427.        WHERE index_name = c_ind
1428.          AND table_name = c_tab
1429.        ORDER BY column_position;
1430.     CURSOR cur_fun_expression(c_ind VARCHAR2, c_tab VARCHAR2) IS
1431.       SELECT column_expression
1432.         FROM user_ind_expressions
1433.        WHERE index_name = c_ind
1434.          and table_name = c_tab;
1435.     v_index_name          user_indexes.index_name%TYPE;
1436.     v_table_owner         user_indexes.table_owner%TYPE;
1437.     v_table_name          user_indexes.table_name%TYPE;
1438.     v_index_type          user_indexes.index_type%TYPE;
1439.     v_uniqueness          user_indexes.uniqueness%TYPE;
1440.     v_tablespace_name     user_indexes.tablespace_name%TYPE;
1441.     n_ini_trans           user_indexes.ini_trans%TYPE;
1442.     n_max_trans           user_indexes.max_trans%TYPE;
1443.     n_initial_extent      user_indexes.initial_extent%TYPE;
1444.     n_next_extent         user_indexes.next_extent%TYPE;
1445.     n_min_extents         user_indexes.min_extents%TYPE;
1446.     n_max_extents         user_indexes.max_extents%TYPE;
1447.     n_pct_increase        user_indexes.pct_increase%TYPE;
1448.     n_pct_free            user_indexes.pct_free%TYPE;
1449.     n_freelists           user_indexes.freelists%TYPE;
1450.     n_freelist_groups     user_indexes.freelist_groups%TYPE;
1451.     v_buffer_pool         user_indexes.buffer_pool%TYPE;
1452.     v_logging             VARCHAR2(15);
1453.     lv_column_name        user_ind_columns.column_name%TYPE;
1454.     b_first_rec           BOOLEAN;
1455.     v_string              VARCHAR2(800);
1456.     n_line_no             NUMBER := 0;
1457.     v_gen_drop_indx_sql   VARCHAR2(10);
1458.     v_gen_create_indx_sql VARCHAR2(10);
1459.     v_gen_store_clause    VARCHAR2(10);
1460.     v_display_prompt_info VARCHAR2(10);
1461.     v_gen_ts_only         VARCHAR2(10);
1462.     n_temp                NUMBER(10, 0);
1463.     v_object_type         VARCHAR2(30);
1464.     v_prompt              VARCHAR2(30);
1465.     v_username            VARCHAR2(30);
1466.     procedure insert_rec(p_line_no     INTEGER,
1467.                          p_object_type VARCHAR2,
1468.                          p_user_name   VARCHAR2,
1469.                          p_index_name  VARCHAR2,
1470.                          p_string      VARCHAR2) IS
1471.     BEGIN
1472.       INSERT INTO temp_for_pkg_gen_sql2000_temp
1473.         (line_no, object_type, user_name, object_name, sql_text)
1474.       VALUES
1475.         (p_line_no, p_object_type, p_user_name, p_index_name, p_string);
1476.     END;
1477.   BEGIN
1478.     v_gen_drop_indx_sql   := p_v_gen_drop_sql;
1479.     v_gen_create_indx_sql := p_v_gen_create_sql;
1480.     v_gen_store_clause    := p_v_gen_store_clause;
1481.     v_gen_ts_only         := p_v_gen_ts_only;
1482.     v_display_prompt_info := p_v_display_prompt_info;
1483.     v_object_type         := 'INDEX';
1484.     v_prompt              := '-- prompt ';
1485.   
1486.     SELECT username INTO v_username FROM user_users;
1487.   
1488.     DELETE FROM temp_for_pkg_gen_sql2000_temp
1489.      WHERE object_type = v_object_type;
1490.     COMMIT;
1491.   
1492.     n_line_no := 1;
1493.   
1494.     OPEN cur_indxes;
1495.     LOOP
1496.       FETCH cur_indxes
1497.         INTO v_index_name, v_table_owner, v_table_name, v_index_type, v_uniqueness, v_tablespace_name, n_ini_trans, 
1499. n_max_trans, n_initial_extent, n_next_extent, n_min_extents, n_max_extents, n_pct_increase, n_pct_free, n_freelists, 
1501. n_freelist_groups, v_buffer_pool, v_logging;
1502.       EXIT WHEN cur_indxes%NOTFOUND;
1503.     
1504.       b_first_rec := TRUE;
1505.     
1506.       IF upper(v_gen_drop_indx_sql) = 'T' THEN
1507.         IF upper(v_display_prompt_info) = 'T' THEN
1508.           IF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1509.           
1510.             v_string := v_prompt || ' DROP UNIQUE INDEX ' ||
1511.                         lower(v_index_name);
1512.           ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1513.           
1514.             v_string := v_prompt || ' DROP   INDEX ' || lower(v_index_name);
1515.           ELSIF v_index_type = 'BITMAP' THEN
1516.             v_string := v_prompt || ' DROP   BITMAP INDEX ' ||
1517.                         lower(v_index_name);
1518.           ELSIF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1519.           
1520.             v_string := v_prompt || ' DROP   UNIQUE, REVERSE INDEX ' ||
1521.                         lower(v_index_name);
1522.           ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1523.           
1524.             v_string := v_prompt || ' DROP REVERSE INDEX ' ||
1525.                         lower(v_index_name);
1526.           ELSIF v_uniqueness = 'UNIQUE' AND
1527.                 v_index_type = 'FUNCTION-BASED NORMAL' THEN
1528.           
1529.             v_string := v_prompt || ' DROP   UNIQUE, FUNCTION INDEX ' ||
1530.                         lower(v_index_name);
1531.           ELSIF v_uniqueness <> 'UNIQUE' AND
1532.                 v_index_type = 'FUNCTION-BASED NORMAL' THEN
1533.           
1534.             v_string := v_prompt || ' DROP FUNCTION INDEX ' ||
1535.                         lower(v_index_name);
1536.           END IF;
1537.         
1538.           insert_rec(n_line_no,
1539.                      v_object_type,
1540.                      v_username,
1541.                      v_index_name,
1542.                      v_string);
1543.           n_line_no := n_line_no + 1;
1544.         END IF;
1545.       
1546.         v_string := 'DROP INDEX ' || lower(v_index_name) || ';';
1547.         insert_rec(n_line_no,
1548.                    v_object_type,
1549.                    v_username,
1550.                    v_index_name,
1551.                    v_string);
1552.         n_line_no := n_line_no + 1;
1553.       
1554.         v_string := '                                                  ';
1555.         insert_rec(n_line_no,
1556.                    v_object_type,
1557.                    v_username,
1558.                    v_index_name,
1559.                    v_string);
1560.         n_line_no := n_line_no + 1;
1561.       END IF;
1562.     
1563.       IF upper(v_gen_create_indx_sql) = 'T' THEN
1564.         IF upper(p_v_display_prompt_info) = 'T' THEN
1565.           IF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1566.           
1567.             v_string := v_prompt || ' CREATE UNIQUE INDEX ' ||
1568.                         lower(v_index_name);
1569.           ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1570.           
1571.             v_string := v_prompt || ' CREATE INDEX ' || lower(v_index_name);
1572.           ELSIF v_index_type = 'BITMAP' THEN
1573.           
1574.             v_string := v_prompt || ' CREATE BITMAP INDEX ' ||
1575.                         lower(v_index_name);
1576.           ELSIF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1577.           
1578.             v_string := v_prompt || ' CREATE UNIQUE, REVERSE INDEX ' ||
1579.                         lower(v_index_name);
1580.           ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1581.           
1582.             v_string := v_prompt || ' CREATE REVERSE INDEX ' ||
1583.                         lower(v_index_name);
1584.           ELSIF v_uniqueness = 'UNIQUE' AND
1585.                 v_index_type = 'FUNCTION-BASED NORMAL' THEN
1586.           
1587.             v_string := v_prompt || ' CREATE UNIQUE, FUNCTION INDEX ' ||
1588.                         lower(v_index_name);
1589.           ELSIF v_uniqueness <> 'UNIQUE' AND
1590.                 v_index_type = 'FUNCTION-BASED NORMAL' THEN
1591.           
1592.             v_string := v_prompt || ' CREATE FUNCTION INDEX ' ||
1593.                         lower(v_index_name);
1594.           END IF;
1595.         
1596.           insert_rec(n_line_no,
1597.                      v_object_type,
1598.                      v_username,
1599.                      v_index_name,
1600.                      v_string);
1601.           n_line_no := n_line_no + 1;
1602.         END IF;
1603.       
1604.         IF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1605.         
1606.           v_string := 'CREATE UNIQUE INDEX ' || lower(v_index_name);
1607.           insert_rec(n_line_no,
1608.                      v_object_type,
1609.                      v_username,
1610.                      v_index_name,
1611.                      v_string);
1612.           n_line_no := n_line_no + 1;
1613.         ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL' THEN
1614.         
1615.           v_string := 'CREATE INDEX ' || lower(v_index_name);
1616.           insert_rec(n_line_no,
1617.                      v_object_type,
1618.                      v_username,
1619.                      v_index_name,
1620.                      v_string);
1621.           n_line_no := n_line_no + 1;
1622.         ELSIF v_index_type = 'BITMAP' THEN
1623.         
1624.           v_string := 'CREATE BITMAP INDEX ' || lower(v_index_name);
1625.           insert_rec(n_line_no,
1626.                      v_object_type,
1627.                      v_username,
1628.                      v_index_name,
1629.                      v_string);
1630.           n_line_no := n_line_no + 1;
1631.         ELSIF v_uniqueness = 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1632.         
1633.           v_string := 'CREATE UNIQUE INDEX ' || lower(v_index_name);
1634.           insert_rec(n_line_no,
1635.                      v_object_type,
1636.                      v_username,
1637.                      v_index_name,
1638.                      v_string);
1639.           n_line_no := n_line_no + 1;
1640.         ELSIF v_uniqueness <> 'UNIQUE' AND v_index_type = 'NORMAL/REV' THEN
1641.         
1642.           v_string := 'CREATE INDEX ' || lower(v_index_name);
1643.           insert_rec(n_line_no,
1644.                      v_object_type,
1645.                      v_username,
1646.                      v_index_name,
1647.                      v_string);
1648.           n_line_no := n_line_no + 1;
1649.         ELSIF v_uniqueness = 'UNIQUE' AND
1650.               v_index_type = 'FUNCTION-BASED NORMAL' THEN
1651.         
1652.           v_string := 'CREATE UNIQUE INDEX ' || lower(v_index_name);
1653.           insert_rec(n_line_no,
1654.                      v_object_type,
1655.                      v_username,
1656.                      v_index_name,
1657.                      v_string);
1658.           n_line_no := n_line_no + 1;
1659.         ELSIF v_uniqueness <> 'UNIQUE' AND
1660.               v_index_type = 'FUNCTION-BASED NORMAL' THEN
1661.         
1662.           v_string := 'CREATE INDEX ' || lower(v_index_name);
1663.           insert_rec(n_line_no,
1664.                      v_object_type,
1665.                      v_username,
1666.                      v_index_name,
1667.                      v_string);
1668.           n_line_no := n_line_no + 1;
1669.         END IF;
1670.         IF v_index_type <> 'FUNCTION-BASED NORMAL' THEN
1671.           OPEN cur_cols(v_index_name, v_table_name);
1672.           LOOP
1673.             FETCH cur_cols
1674.               INTO lv_column_name;
1675.             EXIT WHEN cur_cols%NOTFOUND;
1676.             IF (b_first_rec) THEN
1677.               v_string    := chr(9) || 'ON ' || lower(v_table_name) || ' (';
1678.               b_first_rec := FALSE;
1679.             ELSE
1680.               v_string := v_string || ', ';
1681.             END IF;
1682.             v_string := v_string || lower(lv_column_name);
1683.           END LOOP;
1684.           CLOSE cur_cols;
1685.         ELSE
1686.           OPEN cur_fun_expression(v_index_name, v_table_name);
1687.           LOOP
1688.             FETCH cur_fun_expression
1689.               INTO lv_column_name;
1690.             EXIT WHEN cur_fun_expression%NOTFOUND;
1691.           
1692.             lv_column_name := replace(lv_column_name, '"', '');
1693.             v_string       := chr(9) || 'ON ' || lower(v_table_name) || ' (';
1694.             n_temp         := length(v_string);
1695.             n_temp         := length(chr(9));
1696.             v_string       := v_string || lower(lv_column_name);
1697.           END LOOP;
1698.           CLOSE cur_fun_expression;
1699.         
1700.         END IF;
1701.       
1702.         v_string := v_string || ')';
1703.         insert_rec(n_line_no,
1704.                    v_object_type,
1705.                    v_username,
1706.                    v_index_name,
1707.                    v_string);
1708.         n_line_no := n_line_no + 1;
1709.       
1710.         IF upper(v_gen_store_clause) = 'T' THEN
1711.           v_string := null;
1712.           v_string := v_logging;
1713.           insert_rec(n_line_no,
1714.                      v_object_type,
1715.                      v_username,
1716.                      v_index_name,
1717.                      v_string);
1718.           n_line_no := n_line_no + 1;
1719.         
1720.           v_string := 'PCTFREE ' || to_char(n_pct_free);
1721.           insert_rec(n_line_no,
1722.                      v_object_type,
1723.                      v_username,
1724.                      v_index_name,
1725.                      v_string);
1726.           n_line_no := n_line_no + 1;
1727.         
1728.           v_string := 'INITRANS ' || to_char(n_ini_trans) || ' MAXTRANS ' ||
1729.                       to_char(n_max_trans);
1730.           insert_rec(n_line_no,
1731.                      v_object_type,
1732.                      v_username,
1733.                      v_index_name,
1734.                      v_string);
1735.           n_line_no := n_line_no + 1;
1736.         
1737.           v_string := 'TABLESPACE ' || v_tablespace_name || ' ';
1738.           insert_rec(n_line_no,
1739.                      v_object_type,
1740.                      v_username,
1741.                      v_index_name,
1742.                      v_string);
1743.           n_line_no := n_line_no + 1;
1744.         
1745.           v_string := 'STORAGE (';
1746.           insert_rec(n_line_no,
1747.                      v_object_type,
1748.                      v_username,
1749.                      v_index_name,
1750.                      v_string);
1751.           n_line_no := n_line_no + 1;
1752.         
1753.           v_string := chr(9) || 'INITIAL ' || to_char(n_initial_extent);
1754.           IF n_next_extent IS NOT NULL THEN
1755.             v_string := v_string || ' NEXT ' || to_char(n_next_extent);
1756.           END IF;
1757.           insert_rec(n_line_no,
1758.                      v_object_type,
1759.                      v_username,
1760.                      v_index_name,
1761.                      v_string);
1762.           n_line_no := n_line_no + 1;
1763.         
1764.           v_string := chr(9) || 'MINEXTENTS ' || to_char(n_min_extents) ||
1765.                       ' MAXEXTENTS ' || to_char(n_max_extents) ||
1766.                       ' PCTINCREASE ' || to_char(nvl(n_pct_increase, 0));
1767.           insert_rec(n_line_no,
1768.                      v_object_type,
1769.                      v_username,
1770.                      v_index_name,
1771.                      v_string);
1772.           n_line_no := n_line_no + 1;
1773.         
1774.           v_string := chr(9) || 'FREELISTS ' || to_char(n_freelists) ||
1775.                       ' FREELIST GROUPS ' || to_char(n_freelist_groups);
1776.           insert_rec(n_line_no,
1777.                      v_object_type,
1778.                      v_username,
1779.                      v_index_name,
1780.                      v_string);
1781.           n_line_no := n_line_no + 1;
1782.         
1783.           v_string := chr(9) || 'BUFFER_POOL ' || v_buffer_pool || ' ';
1784.           insert_rec(n_line_no,
1785.                      v_object_type,
1786.                      v_username,
1787.                      v_index_name,
1788.                      v_string);
1789.           n_line_no := n_line_no + 1;
1790.         
1791.           UPDATE temp_for_pkg_gen_sql2000_temp
1792.              SET sql_text = sql_text || ')'
1793.            WHERE line_no = n_line_no - 1
1794.              AND object_type = v_object_type;
1795.         ELSE
1796.           IF upper(v_gen_store_clause) = 'F' AND upper(v_gen_ts_only) = 'T' THEN
1797.             v_string := 'TABLESPACE ' || v_tablespace_name || ' ';
1798.             insert_rec(n_line_no,
1799.                        v_object_type,
1800.                        v_username,
1801.                        v_index_name,
1802.                        v_string);
1803.             n_line_no := n_line_no + 1;
1804.           END IF;
1805.         END IF;
1806.       
1807.         IF v_index_type <> 'NORMAL/REV' THEN
1808.           UPDATE temp_for_pkg_gen_sql2000_temp
1809.              SET sql_text = sql_text || ';'
1810.            WHERE line_no = n_line_no - 1
1811.              AND object_type = v_object_type;
1812.         ELSE
1813.           UPDATE temp_for_pkg_gen_sql2000_temp
1814.              SET sql_text = sql_text || ' REVERSE;'
1815.            WHERE line_no = n_line_no - 1
1816.              AND object_type = v_object_type;
1817.         END IF;
1818.         v_string := '                                                  ';
1819.         insert_rec(n_line_no,
1820.                    v_object_type,
1821.                    v_username,
1822.                    v_index_name,
1823.                    v_string);
1824.         n_line_no := n_line_no + 1;
1825.       END IF;
1826.     END LOOP;
1827.     CLOSE cur_indxes;
1828.   
1829.     SELECT COUNT(*) INTO n_temp FROM user_indexes WHERE index_type = 'LOB';
1830.   
1831.     IF n_temp > 0 THEN
1832.       v_string := '                                                  ';
1833.       insert_rec(n_line_no,
1834.                  v_object_type,
1835.                  v_username,
1836.                  'LOB INDEX',
1837.                  v_string);
1838.       n_line_no := n_line_no + 1;
1839.     
1840.       v_string := '-- There is some LOB index, and we don''t list;';
1841.       insert_rec(n_line_no,
1842.                  v_object_type,
1843.                  v_username,
1844.                  'LOB INDEX',
1845.                  v_string);
1846.       n_line_no := n_line_no + 1;
1847.     
1848.       v_string := '                                                  ';
1849.       insert_rec(n_line_no,
1850.                  v_object_type,
1851.                  v_username,
1852.                  'LOB INDEX',
1853.                  v_string);
1854.       n_line_no := n_line_no + 1;
1855.     
1856.       v_string := '                                                  ';
1857.       insert_rec(n_line_no,
1858.                  v_object_type,
1859.                  v_username,
1860.                  'LOB INDEX',
1861.                  v_string);
1862.       n_line_no := n_line_no + 1;
1863.     END IF;
1864.   
1865.     COMMIT;
1866.   END;
1868.   PROCEDURE pro_reb_indx_sql(p_v_gen_store_clause    VARCHAR2 default 'T',
1869.                              p_v_rebuild_online      VARCHAR2 default 'T',
1870.                              p_v_display_prompt_info VARCHAR2 default 'T') AS
1871.     CURSOR cur_indxes IS
1872.       SELECT index_name,
1873.              table_owner,
1874.              table_name,
1875.              index_type,
1876.              uniqueness,
1877.              tablespace_name,
1878.              ini_trans,
1879.              max_trans,
1880.              initial_extent,
1881.              next_extent,
1882.              min_extents,
1883.              max_extents,
1884.              pct_increase,
1885.              pct_free,
1886.              freelists,
1887.              freelist_groups,
1888.              buffer_pool,
1889.              decode(logging, 'YES', 'LOGGING', 'NOLOGGING') logging
1890.         FROM user_indexes
1891.        WHERE index_type <> 'LOB'
1892.        ORDER BY index_name;
1893.   
1894.     v_index_name          user_indexes.index_name%TYPE;
1895.     v_table_owner         user_indexes.table_owner%TYPE;
1896.     v_table_name          user_indexes.table_name%TYPE;
1897.     v_index_type          user_indexes.index_type%TYPE;
1898.     v_uniqueness          user_indexes.uniqueness%TYPE;
1899.     v_tablespace_name     user_indexes.tablespace_name%TYPE;
1900.     n_ini_trans           user_indexes.ini_trans%TYPE;
1901.     n_max_trans           user_indexes.max_trans%TYPE;
1902.     n_initial_extent      user_indexes.initial_extent%TYPE;
1903.     n_next_extent         user_indexes.next_extent%TYPE;
1904.     n_min_extents         user_indexes.min_extents%TYPE;
1905.     n_max_extents         user_indexes.max_extents%TYPE;
1906.     n_pct_increase        user_indexes.pct_increase%TYPE;
1907.     n_pct_free            user_indexes.pct_free%TYPE;
1908.     n_freelists           user_indexes.freelists%TYPE;
1909.     n_freelist_groups     user_indexes.freelist_groups%TYPE;
1910.     v_buffer_pool         user_indexes.buffer_pool%TYPE;
1911.     v_logging             VARCHAR2(15);
1912.     lv_column_name        user_ind_columns.column_name%TYPE;
1913.     b_first_rec           BOOLEAN;
1914.     v_string              VARCHAR2(800);
1915.     n_line_no             NUMBER := 0;
1916.     v_gen_store_clause    VARCHAR2(10);
1917.     v_rebuild_online      VARCHAR2(10);
1918.     v_display_prompt_info VARCHAR2(10);
1919.     n_temp                NUMBER(10, 0);
1920.     v_object_type         VARCHAR2(30);
1921.     v_prompt              VARCHAR2(30);
1922.     v_username            VARCHAR2(30);
1923.     PROCEDURE insert_rec(p_line_no     INTEGER,
1924.                          p_object_type VARCHAR2,
1925.                          p_user_name   VARCHAR2,
1926.                          p_index_name  VARCHAR2,
1927.                          p_string      VARCHAR2) IS
1928.     BEGIN
1929.       INSERT INTO temp_for_pkg_gen_sql2000_temp
1930.         (line_no, object_type, user_name, object_name, sql_text)
1931.       VALUES
1932.         (p_line_no, p_object_type, p_user_name, p_index_name, p_string);
1933.     END;
1934.   BEGIN
1935.     v_gen_store_clause    := p_v_gen_store_clause;
1936.     v_display_prompt_info := p_v_display_prompt_info;
1937.     v_rebuild_online      := p_v_rebuild_online;
1938.     v_object_type         := 'REBUILD_INDEX';
1939.     v_prompt              := '-- prompt ';
1940.   
1941.     SELECT username INTO v_username FROM user_users;
1942.   
1943.     DELETE FROM temp_for_pkg_gen_sql2000_temp
1944.      WHERE object_type = v_object_type;
1945.     COMMIT;
1946.   
1947.     n_line_no := 1;
1948.   
1949.     OPEN cur_indxes;
1950.     LOOP
1951.       FETCH cur_indxes
1952.         INTO v_index_name, v_table_owner, v_table_name, v_index_type, v_uniqueness, v_tablespace_name, n_ini_trans, 
1954. n_max_trans, n_initial_extent, n_next_extent, n_min_extents, n_max_extents, n_pct_increase, n_pct_free, n_freelists, 
1956. n_freelist_groups, v_buffer_pool, v_logging;
1957.       EXIT WHEN cur_indxes%NOTFOUND;
1958.     
1959.       b_first_rec := TRUE;
1960.     
1961.       IF upper(p_v_display_prompt_info) = 'T' THEN
1962.         IF v_rebuild_online = 'T' THEN
1963.           v_string := v_prompt || ' REBUILD INDEX ' || lower(v_index_name) ||
1964.                       ' ONLINE ';
1965.         ELSE
1966.           v_string := v_prompt || ' REBUILD INDEX ' || lower(v_index_name);
1967.         END IF;
1968.         insert_rec(n_line_no,
1969.                    v_object_type,
1970.                    v_username,
1971.                    v_index_name,
1972.                    v_string);
1973.         n_line_no := n_line_no + 1;
1974.       END IF;
1975.     
1976.       IF v_rebuild_online = 'T' THEN
1977.         v_string := 'ALTER INDEX ' || lower(v_index_name) ||
1978.                     ' REBUILD ONLINE ';
1979.       ELSE
1980.         v_string := 'ALTER INDEX ' || lower(v_index_name) || ' REBUILD ';
1981.       END IF;
1982.       insert_rec(n_line_no,
1983.                  v_object_type,
1984.                  v_username,
1985.                  v_index_name,
1986.                  v_string);
1987.       n_line_no := n_line_no + 1;
1988.     
1989.       IF upper(v_gen_store_clause) = 'T' THEN
1990.       
1991.         v_string := null;
1992.         v_string := v_logging;
1993.         insert_rec(n_line_no,
1994.                    v_object_type,
1995.                    v_username,
1996.                    v_index_name,
1997.                    v_string);
1998.         n_line_no := n_line_no + 1;
1999.       
2000.         v_string := 'PCTFREE ' || to_char(n_pct_free);
2001.         insert_rec(n_line_no,
2002.                    v_object_type,
2003.                    v_username,
2004.                    v_index_name,
2005.                    v_string);
2006.         n_line_no := n_line_no + 1;
2007.       
2008.         v_string := 'INITRANS ' || to_char(n_ini_trans) || ' MAXTRANS ' ||
2009.                     to_char(n_max_trans);
2010.         insert_rec(n_line_no,
2011.                    v_object_type,
2012.                    v_username,
2013.                    v_index_name,
2014.                    v_string);
2015.         n_line_no := n_line_no + 1;
2016.       
2017.         v_string := 'TABLESPACE ' || v_tablespace_name || ' ';
2018.         insert_rec(n_line_no,
2019.                    v_object_type,
2020.                    v_username,
2021.                    v_index_name,
2022.                    v_string);
2023.         n_line_no := n_line_no + 1;
2024.       
2025.         v_string := 'STORAGE (';
2026.         insert_rec(n_line_no,
2027.                    v_object_type,
2028.                    v_username,
2029.                    v_index_name,
2030.                    v_string);
2031.         n_line_no := n_line_no + 1;
2032.       
2033.         v_string := chr(9) || 'INITIAL ' || to_char(n_initial_extent);
2034.         IF n_next_extent IS NOT NULL THEN
2035.           v_string := v_string || ' NEXT ' || to_char(n_next_extent);
2036.         END IF;
2037.         insert_rec(n_line_no,
2038.                    v_object_type,
2039.                    v_username,
2040.                    v_index_name,
2041.                    v_string);
2042.         n_line_no := n_line_no + 1;
2043.       
2044.         v_string := chr(9) || 'MINEXTENTS ' || to_char(n_min_extents) ||
2045.                     ' MAXEXTENTS ' || to_char(n_max_extents) ||
2046.                     ' PCTINCREASE ' || to_char(nvl(n_pct_increase, 0));
2047.         insert_rec(n_line_no,
2048.                    v_object_type,
2049.                    v_username,
2050.                    v_index_name,
2051.                    v_string);
2052.         n_line_no := n_line_no + 1;
2053.       
2054.         IF n_freelists IS NOT NULL AND n_freelist_groups IS NOT NULL THEN
2055.           v_string := chr(9) || 'FREELISTS ' || to_char(n_freelists) ||
2056.                       ' FREELIST GROUPS ' || to_char(n_freelist_groups);
2057.           insert_rec(n_line_no,
2058.                      v_object_type,
2059.                      v_username,
2060.                      v_index_name,
2061.                      v_string);
2062.           n_line_no := n_line_no + 1;
2063.         END IF;
2064.       
2065.         v_string := chr(9) || 'BUFFER_POOL ' || v_buffer_pool || ' ';
2066.         insert_rec(n_line_no,
2067.                    v_object_type,
2068.                    v_username,
2069.                    v_index_name,
2070.                    v_string);
2071.         n_line_no := n_line_no + 1;
2072.       
2073.         UPDATE temp_for_pkg_gen_sql2000_temp
2074.            SET sql_text = sql_text || ')'
2075.          WHERE line_no = n_line_no - 1
2076.            AND object_type = v_object_type;
2077.       END IF;
2078.     
2079.       UPDATE temp_for_pkg_gen_sql2000_temp
2080.          SET sql_text = sql_text || ';'
2081.        WHERE line_no = n_line_no - 1
2082.          AND object_type = v_object_type;
2083.     
2084.       v_string := '                                                  ';
2085.       insert_rec(n_line_no,
2086.                  v_object_type,
2087.                  v_username,
2088.                  v_index_name,
2089.                  v_string);
2090.       n_line_no := n_line_no + 1;
2091.     END LOOP;
2092.     CLOSE cur_indxes;
2093.   
2094.     SELECT COUNT(*) INTO n_temp FROM user_indexes WHERE index_type = 'LOB';
2095.   
2096.     IF n_temp > 0 THEN
2097.       v_string := '                                                  ';
2098.       insert_rec(n_line_no,
2099.                  v_object_type,
2100.                  v_username,
2101.                  'LOB INDEX',
2102.                  v_string);
2103.       n_line_no := n_line_no + 1;
2104.     
2105.       v_string := '-- There is some LOB index, and we don''t list;';
2106.       insert_rec(n_line_no,
2107.                  v_object_type,
2108.                  v_username,
2109.                  'LOB INDEX',
2110.                  v_string);
2111.       n_line_no := n_line_no + 1;
2112.     
2113.       v_string := '                                                  ';
2114.       insert_rec(n_line_no,
2115.                  v_object_type,
2116.                  v_username,
2117.                  'LOB INDEX',
2118.                  v_string);
2119.       n_line_no := n_line_no + 1;
2120.     
2121.       v_string := '                                                  ';
2122.       insert_rec(n_line_no,
2123.                  v_object_type,
2124.                  v_username,
2125.                  'LOB INDEX',
2126.                  v_string);
2127.       n_line_no := n_line_no + 1;
2128.     END IF;
2129.   
2130.     COMMIT;
2131.   END;
2133.   PROCEDURE pro_view_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2134.                          p_v_gen_create_sql      VARCHAR2 default 'T',
2135.                          p_v_display_prompt_info VARCHAR2 default 'T') AS
2136.     CURSOR cur_views IS
2137.       select view_name, text from user_views order by view_name;
2138.     CURSOR cur_cols(v_name VARCHAR2) IS
2139.       SELECT table_name,
2140.              column_name,
2141.              data_type,
2142.              data_length,
2143.              data_precision,
2144.              data_scale,
2145.              nullable,
2146.              data_default
2147.         FROM user_tab_columns
2148.        WHERE table_name = v_name
2149.        ORDER BY column_id;
2150.     v_username    VARCHAR2(30);
2151.     v_view_name   VARCHAR2(100);
2152.     v_col_names   VARCHAR2(2000);
2153.     v_text        VARCHAR2(30000);
2154.     v_sql         VARCHAR2(30000);
2155.     v_sql_temp    VARCHAR2(30000);
2156.     n_line_no     NUMBER(10, 0);
2157.     n38_count     NUMBER(38, 0);
2158.     n_position    NUMBER(38, 0);
2159.     v_object_type VARCHAR2(30);
2160.     v_prompt      VARCHAR2(30);
2161.     PROCEDURE insert_rec(p_line_no     INTEGER,
2162.                          p_object_type VARCHAR2,
2163.                          p_user_name   VARCHAR2,
2164.                          p_view_name   VARCHAR2,
2165.                          p_string      VARCHAR2) IS
2166.     BEGIN
2167.       INSERT INTO temp_for_pkg_gen_sql4000_temp
2168.         (line_no, object_type, user_name, object_name, sql_text)
2169.       VALUES
2170.         (p_line_no, p_object_type, p_user_name, p_view_name, p_string);
2171.     END;
2172.   BEGIN
2173.   
2174.     v_object_type := 'VIEW';
2175.     v_prompt      := '-- prompt ';
2176.   
2177.     SELECT username INTO v_username FROM user_users;
2178.   
2179.     DELETE temp_for_pkg_gen_sql4000_temp WHERE object_type = v_object_type;
2180.     COMMIT;
2181.   
2182.     n_line_no := 1;
2183.   
2184.     OPEN cur_views;
2185.     LOOP
2186.       FETCH cur_views
2187.         INTO v_view_name, v_text;
2188.       EXIT WHEN cur_views%NOTFOUND;
2189.     
2190.       IF upper(p_v_gen_drop_sql) = 'T' THEN
2191.         IF upper(p_v_display_prompt_info) = 'T' THEN
2192.           v_sql := v_prompt || ' DROP   VIEW ' || v_view_name;
2193.           insert_rec(n_line_no,
2194.                      v_object_type,
2195.                      v_username,
2196.                      v_view_name,
2197.                      v_sql);
2198.           n_line_no := n_line_no + 1;
2199.         END IF;
2200.       
2201.         v_sql := 'DROP VIEW ' || v_view_name || ';';
2202.         insert_rec(n_line_no,
2203.                    v_object_type,
2204.                    v_username,
2205.                    v_view_name,
2206.                    v_sql);
2207.         n_line_no := n_line_no + 1;
2208.       
2209.         v_sql := '                         ';
2210.         insert_rec(n_line_no,
2211.                    v_object_type,
2212.                    v_username,
2213.                    v_view_name,
2214.                    v_sql);
2215.         n_line_no := n_line_no + 1;
2216.       END IF;
2217.     
2218.       IF upper(p_v_gen_create_sql) = 'T' THEN
2219.         IF upper(p_v_display_prompt_info) = 'T' THEN
2220.           v_sql := v_prompt || ' CREATE VIEW ' || v_view_name;
2221.           insert_rec(n_line_no,
2222.                      v_object_type,
2223.                      v_username,
2224.                      v_view_name,
2225.                      v_sql);
2226.           n_line_no := n_line_no + 1;
2227.         END IF;
2228.       
2229.         v_sql := 'CREATE OR REPLACE VIEW ' || v_view_name;
2230.         insert_rec(n_line_no,
2231.                    v_object_type,
2232.                    v_username,
2233.                    v_view_name,
2234.                    v_sql);
2235.         n_line_no := n_line_no + 1;
2236.       
2237.         v_sql := '(';
2238.         insert_rec(n_line_no,
2239.                    v_object_type,
2240.                    v_username,
2241.                    v_view_name,
2242.                    v_sql);
2243.         n_line_no := n_line_no + 1;
2244.       
2245.         v_col_names := '';
2246.         n38_count   := 0;
2247.         FOR cur_cols_rec IN cur_cols(v_view_name) LOOP
2248.         
2249.           v_col_names := v_col_names || cur_cols_rec.column_name || ', ';
2250.           n38_count   := n38_count + 1;
2251.           IF MOD(n38_count, 5) = 0 THEN
2252.             v_sql := chr(9) || v_col_names;
2253.             insert_rec(n_line_no,
2254.                        v_object_type,
2255.                        v_username,
2256.                        v_view_name,
2257.                        v_sql);
2258.             n_line_no   := n_line_no + 1;
2259.             v_col_names := '';
2260.           END IF;
2261.         END LOOP;
2262.       
2263.         IF length(v_col_names) > 2 THEN
2264.           v_col_names := substr(v_col_names, 1, length(v_col_names) - 2);
2265.           v_sql       := chr(9) || v_col_names;
2266.           insert_rec(n_line_no,
2267.                      v_object_type,
2268.                      v_username,
2269.                      v_view_name,
2270.                      v_sql);
2271.           n_line_no := n_line_no + 1;
2272.         ELSE
2273.           UPDATE temp_for_pkg_gen_sql4000_temp
2274.              SET sql_text = substr(sql_text, 1, length(sql_text) - 2)
2275.            WHERE line_no = n_line_no - 1
2276.              AND object_type = v_object_type;
2277.         END IF;
2278.       
2279.         v_sql := ')';
2280.         insert_rec(n_line_no,
2281.                    v_object_type,
2282.                    v_username,
2283.                    v_view_name,
2284.                    v_sql);
2285.         n_line_no := n_line_no + 1;
2286.       
2287.         v_sql := 'AS';
2288.         insert_rec(n_line_no,
2289.                    v_object_type,
2290.                    v_username,
2291.                    v_view_name,
2292.                    v_sql);
2293.         n_line_no := n_line_no + 1;
2294.       
2295.         v_text := replace(v_text, ',', ', ');
2296.       
2297.         v_sql := v_text;
2298.         IF length(v_sql) > 3800 THEN
2299.           WHILE length(v_sql) > 3800 LOOP
2300.             n_position := instr(substr(v_sql, 1, 3800), ',', -1);
2301.             insert_rec(n_line_no,
2302.                        v_object_type,
2303.                        v_username,
2304.                        v_view_name,
2305.                        substr(v_sql, 1, n_position));
2306.             v_sql := substr(v_sql, n_position + 1);
2307.           END LOOP;
2308.           insert_rec(n_line_no,
2309.                      v_object_type,
2310.                      v_username,
2311.                      v_view_name,
2312.                      v_sql);
2313.         ELSE
2314.           insert_rec(n_line_no,
2315.                      v_object_type,
2316.                      v_username,
2317.                      v_view_name,
2318.                      v_sql);
2319.         END IF;
2320.       
2321.         n_line_no := n_line_no + 1;
2322.       
2323.         UPDATE temp_for_pkg_gen_sql4000_temp
2324.            SET sql_text = sql_text || ';'
2325.          WHERE line_no = n_line_no - 1
2326.            AND object_type = v_object_type;
2327.       
2328.         v_sql := '                         ';
2329.         insert_rec(n_line_no,
2330.                    v_object_type,
2331.                    v_username,
2332.                    v_view_name,
2333.                    v_sql);
2334.         n_line_no := n_line_no + 1;
2335.       
2336.       END IF;
2337.     END LOOP;
2338.     CLOSE cur_views;
2339.     COMMIT;
2340.   END;
2342.   PROCEDURE pro_seq_max_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2343.                                 p_v_gen_create_sql      VARCHAR2 default 'T',
2344.                                 p_v_display_prompt_info VARCHAR2 default 'T') AS
2345.     v_sql     VARCHAR2(500);
2346.     n_max_id  NUMBER(38, 0);
2347.     v_max_val VARCHAR2(1000);
2348.     CURSOR cur_etl_data IS
2349.       SELECT table_name FROM user_tables;
2350.     rec_etl_data cur_etl_data%rowtype;
2351.     type ref_cur is REF CURSOR; -- 定义一个ref 类型的游标
2352.     cur_id ref_cur;
2353.     CURSOR cur_cols(t_name VARCHAR2) IS
2354.       SELECT column_name, data_type
2355.         FROM user_tab_columns
2356.        WHERE table_name = t_name
2357.        ORDER BY column_id;
2358.     rec_col_name    cur_cols%rowtype;
2359.     v_sequence_name VARCHAR2(50);
2360.     n_line_no       NUMBER(38, 0);
2361.     v_object_type   VARCHAR2(30);
2362.     v_prompt        VARCHAR2(30);
2363.     v_username      VARCHAR2(30);
2364.     PROCEDURE insert_rec(p_line_no       INTEGER,
2365.                          p_object_type   VARCHAR2,
2366.                          p_user_name     VARCHAR2,
2367.                          p_sequence_name VARCHAR2,
2368.                          p_string        VARCHAR2) IS
2369.     BEGIN
2370.       INSERT INTO temp_for_pkg_gen_sql2000_temp
2371.         (line_no, object_type, user_name, object_name, sql_text)
2372.       VALUES
2373.         (p_line_no, p_object_type, p_user_name, p_sequence_name, p_string);
2374.     END;
2375.   BEGIN
2376.     n_line_no     := 1;
2377.     v_object_type := 'SEQUENCE_CHAR';
2378.     v_prompt      := '-- prompt ';
2379.   
2380.     SELECT username INTO v_username FROM user_users;
2381.   
2382.     DELETE temp_for_pkg_gen_sql2000_temp WHERE object_type = v_object_type;
2383.   
2384.     COMMIT;
2385.   
2386.     OPEN cur_etl_data;
2387.     LOOP
2388.       FETCH cur_etl_data
2389.         INTO rec_etl_data;
2390.       EXIT WHEN cur_etl_data%NOTFOUND;
2391.     
2392.       -- 得到表的第一个列
2393.       OPEN cur_cols(rec_etl_data.table_name);
2394.       LOOP
2395.         FETCH cur_cols
2396.           INTO rec_col_name;
2397.         EXIT WHEN cur_cols%NOTFOUND;
2398.         EXIT;
2399.       END LOOP;
2400.       CLOSE cur_cols;
2401.     
2402.       IF rec_col_name.data_type = 'VARCHAR2' OR
2403.          rec_col_name.data_type = 'VARCHAR' OR
2404.          rec_col_name.data_type = 'CHAR' THEN
2405.         BEGIN
2406.           v_sql := 'SELECT max(to_NUMBER(' || rec_col_name.column_name ||
2407.                    ')) FROM ' || rec_etl_data.table_name || '';
2408.           OPEN cur_id FOR v_sql;
2409.           LOOP
2410.             FETCH cur_id
2411.               INTO v_max_val;
2412.             EXIT WHEN cur_id%NOTFOUND;
2413.             EXIT;
2414.           END LOOP;
2415.           CLOSE cur_id;
2416.         EXCEPTION
2417.           WHEN others THEN
2418.             IF cur_id%ISOPEN THEN
2419.               CLOSE cur_id;
2420.             END IF;
2421.             v_max_val := '-1000';
2422.         END;
2423.       ELSIF rec_col_name.data_type = 'NUMBER' THEN
2424.         v_sql := 'SELECT max(' || rec_col_name.column_name || ') FROM ' ||
2425.                  rec_etl_data.table_name || '';
2426.         OPEN cur_id FOR v_sql;
2427.         LOOP
2428.           FETCH cur_id
2429.             INTO n_max_id;
2430.           EXIT WHEN cur_id%NOTFOUND;
2431.           EXIT;
2432.         END LOOP;
2433.         CLOSE cur_id;
2434.       END IF;
2435.     
2436.       -- need modify, generate sequence name 
2437.       v_sequence_name := 'seq_' || substr(rec_etl_data.table_name, 3) ||
2438.                          '_id';
2439.     
2440.       IF p_v_gen_drop_sql = 'T' THEN
2441.         IF p_v_display_prompt_info = 'T' THEN
2442.           v_sql := v_prompt || ' DROP SEQUENCE ' || v_sequence_name;
2443.           insert_rec(n_line_no,
2444.                      v_object_type,
2445.                      v_username,
2446.                      v_sequence_name,
2447.                      v_sql);
2448.           n_line_no := n_line_no + 1;
2449.         END IF;
2450.       
2451.         v_sql := 'DROP SEQUENCE ' || v_sequence_name || ';';
2452.         insert_rec(n_line_no,
2453.                    v_object_type,
2454.                    v_username,
2455.                    v_sequence_name,
2456.                    v_sql);
2457.         n_line_no := n_line_no + 1;
2458.       
2459.         v_sql := '                  ';
2460.         insert_rec(n_line_no,
2461.                    v_object_type,
2462.                    v_username,
2463.                    v_sequence_name,
2464.                    v_sql);
2465.         n_line_no := n_line_no + 1;
2466.       END IF;
2467.     
2468.       IF p_v_gen_create_sql = 'T' THEN
2469.         IF p_v_display_prompt_info = 'T' THEN
2470.           v_sql := v_prompt || ' CREATE SEQUENCE ' || v_sequence_name;
2471.           insert_rec(n_line_no,
2472.                      v_object_type,
2473.                      v_username,
2474.                      v_sequence_name,
2475.                      v_sql);
2476.           n_line_no := n_line_no + 1;
2477.         END IF;
2478.       
2479.         v_sql := 'CREATE SEQUENCE ' || v_sequence_name;
2480.         insert_rec(n_line_no,
2481.                    v_object_type,
2482.                    v_username,
2483.                    v_sequence_name,
2484.                    v_sql);
2485.         n_line_no := n_line_no + 1;
2486.       
2487.         -- handle the station : treate the sequence value as varchar column(primary key)
2488.         IF rec_col_name.data_type = 'VARCHAR2' OR
2489.            rec_col_name.data_type = 'VARCHAR' OR
2490.            rec_col_name.data_type = 'CHAR' THEN
2491.           v_sql := 'START WITH ' ||
2492.                    to_char(to_number(nvl(v_max_val, '1')) + 1) || ' ';
2493.         ELSIF rec_col_name.data_type = 'NUMBER' THEN
2494.           v_sql := 'START WITH ' || TO_CHAR(nvl(n_max_id, 1) + 1) || ' ';
2495.         END IF;
2496.         insert_rec(n_line_no,
2497.                    v_object_type,
2498.                    v_username,
2499.                    v_sequence_name,
2500.                    v_sql);
2501.         n_line_no := n_line_no + 1;
2502.       
2503.         v_sql := 'INCREMENT BY 1 ';
2504.         insert_rec(n_line_no,
2505.                    v_object_type,
2506.                    v_username,
2507.                    v_sequence_name,
2508.                    v_sql);
2509.         n_line_no := n_line_no + 1;
2510.       
2511.         v_sql := 'CACHE 100 NOCYCLE ';
2512.         insert_rec(n_line_no,
2513.                    v_object_type,
2514.                    v_username,
2515.                    v_sequence_name,
2516.                    v_sql);
2517.         n_line_no := n_line_no + 1;
2518.       
2519.         UPDATE temp_for_pkg_gen_sql2000_temp
2520.            SET sql_text = sql_text || ';'
2521.          WHERE line_no = n_line_no - 1
2522.            AND object_type = v_object_type;
2523.       
2524.         v_sql := '  ';
2525.         insert_rec(n_line_no,
2526.                    v_object_type,
2527.                    v_username,
2528.                    v_sequence_name,
2529.                    v_sql);
2530.         n_line_no := n_line_no + 1;
2531.       END IF;
2532.     
2533.     END LOOP;
2534.     CLOSE cur_etl_data;
2535.     COMMIT;
2536.   END;
2538.   PROCEDURE pro_seq_next_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2539.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
2540.                                  p_v_display_prompt_info VARCHAR2 default 'T') AS
2541.     v_sql     VARCHAR2(2000);
2542.     n_max_id  NUMBER(38, 0);
2543.     v_max_val VARCHAR2(1000);
2544.     CURSOR cur_sequence IS
2545.       SELECT * FROM user_sequences;
2546.     rec_cur_sequence cur_sequence%rowtype;
2547.     v_sequence_name  VARCHAR2(50);
2548.     n_line_no        NUMBER(38, 0);
2549.     v_object_type    VARCHAR2(30);
2550.     v_prompt         VARCHAR2(30);
2551.     v_username       VARCHAR2(30);
2552.     PROCEDURE insert_rec(p_line_no       INTEGER,
2553.                          p_object_type   VARCHAR2,
2554.                          p_user_name     VARCHAR2,
2555.                          p_sequence_name VARCHAR2,
2556.                          p_string        VARCHAR2) IS
2557.     BEGIN
2558.       INSERT INTO temp_for_pkg_gen_sql2000_temp
2559.         (line_no, object_type, user_name, object_name, sql_text)
2560.       VALUES
2561.         (p_line_no, p_object_type, p_user_name, p_sequence_name, p_string);
2562.     END;
2563.   BEGIN
2564.     n_line_no     := 1;
2565.     v_object_type := 'SEQUENCE';
2566.     v_prompt      := '-- prompt ';
2567.   
2568.     SELECT username INTO v_username FROM user_users;
2569.   
2570.     DELETE temp_for_pkg_gen_sql2000_temp WHERE object_type = v_object_type;
2571.     COMMIT;
2572.   
2573.     OPEN cur_sequence;
2574.     LOOP
2575.       FETCH cur_sequence
2576.         INTO rec_cur_sequence;
2577.       EXIT WHEN cur_sequence%NOTFOUND;
2578.     
2579.       IF p_v_gen_drop_sql = 'T' THEN
2580.         IF p_v_display_prompt_info = 'T' THEN
2581.           v_sql := v_prompt || ' DROP SEQUENCE ' ||
2582.                    rec_cur_sequence.sequence_name;
2583.           insert_rec(n_line_no,
2584.                      v_object_type,
2585.                      v_username,
2586.                      rec_cur_sequence.sequence_name,
2587.                      v_sql);
2588.           n_line_no := n_line_no + 1;
2589.         END IF;
2590.         v_sql := 'DROP SEQUENCE ' || rec_cur_sequence.sequence_name || ';';
2591.         insert_rec(n_line_no,
2592.                    v_object_type,
2593.                    v_username,
2594.                    rec_cur_sequence.sequence_name,
2595.                    v_sql);
2596.         n_line_no := n_line_no + 1;
2597.       
2598.         v_sql := '                  ';
2599.         insert_rec(n_line_no,
2600.                    v_object_type,
2601.                    v_username,
2602.                    rec_cur_sequence.sequence_name,
2603.                    v_sql);
2604.         n_line_no := n_line_no + 1;
2605.       END IF;
2606.     
2607.       IF p_v_gen_create_sql = 'T' THEN
2608.         IF p_v_display_prompt_info = 'T' THEN
2609.           v_sql := v_prompt || ' CREATE SEQUENCE ' ||
2610.                    rec_cur_sequence.sequence_name;
2611.           insert_rec(n_line_no,
2612.                      v_object_type,
2613.                      v_username,
2614.                      rec_cur_sequence.sequence_name,
2615.                      v_sql);
2616.           n_line_no := n_line_no + 1;
2617.         END IF;
2618.       
2619.         v_sql := 'CREATE SEQUENCE ' || rec_cur_sequence.sequence_name;
2620.         insert_rec(n_line_no,
2621.                    v_object_type,
2622.                    v_username,
2623.                    rec_cur_sequence.sequence_name,
2624.                    v_sql);
2625.         n_line_no := n_line_no + 1;
2626.       
2627.         v_sql := 'MINVALUE ' || to_char(rec_cur_sequence.min_value);
2628.         insert_rec(n_line_no,
2629.                    v_object_type,
2630.                    v_username,
2631.                    rec_cur_sequence.sequence_name,
2632.                    v_sql);
2633.         n_line_no := n_line_no + 1;
2634.       
2635.         v_sql := 'MAXVALUE ' || to_char(rec_cur_sequence.max_value);
2636.         insert_rec(n_line_no,
2637.                    v_object_type,
2638.                    v_username,
2639.                    rec_cur_sequence.sequence_name,
2640.                    v_sql);
2641.         n_line_no := n_line_no + 1;
2642.       
2643.         v_sql := 'START WITH ' || to_char(rec_cur_sequence.last_number);
2644.         insert_rec(n_line_no,
2645.                    v_object_type,
2646.                    v_username,
2647.                    rec_cur_sequence.sequence_name,
2648.                    v_sql);
2649.         n_line_no := n_line_no + 1;
2650.       
2651.         v_sql := 'INCREMENT BY ' || to_char(rec_cur_sequence.increment_by);
2652.         insert_rec(n_line_no,
2653.                    v_object_type,
2654.                    v_username,
2655.                    rec_cur_sequence.sequence_name,
2656.                    v_sql);
2657.         n_line_no := n_line_no + 1;
2658.       
2659.         IF rec_cur_sequence.cache_size = 0 THEN
2660.           v_sql := 'NOCACHE ';
2661.         ELSE
2662.           v_sql := 'CACHE ' || to_char(rec_cur_sequence.cache_size);
2663.         END IF;
2664.       
2665.         IF rec_cur_sequence.cycle_flag = 'N' THEN
2666.           v_sql := v_sql || ' NOCYCLE ';
2667.         ELSE
2668.           v_sql := v_sql || ' CYCLE ';
2669.         END IF;
2670.       
2671.         insert_rec(n_line_no,
2672.                    v_object_type,
2673.                    v_username,
2674.                    rec_cur_sequence.sequence_name,
2675.                    v_sql);
2676.         n_line_no := n_line_no + 1;
2677.       
2678.         UPDATE temp_for_pkg_gen_sql2000_temp
2679.            SET sql_text = sql_text || ';'
2680.          WHERE line_no = n_line_no - 1
2681.            AND object_type = v_object_type;
2682.       
2683.         v_sql := '  ';
2684.         insert_rec(n_line_no,
2685.                    v_object_type,
2686.                    v_username,
2687.                    rec_cur_sequence.sequence_name,
2688.                    v_sql);
2689.         n_line_no := n_line_no + 1;
2690.       END IF;
2691.     
2692.     END LOOP;
2693.     CLOSE cur_sequence;
2694.     COMMIT;
2695.   END;
2697.   PROCEDURE pro_seq_init_val_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2698.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
2699.                                  p_v_display_prompt_info VARCHAR2 default 'T') AS
2700.     v_sql     VARCHAR2(2000);
2701.     n_max_id  NUMBER(38, 0);
2702.     v_max_val VARCHAR2(1000);
2703.     CURSOR cur_sequence IS
2704.       SELECT * FROM user_sequences;
2705.     rec_cur_sequence cur_sequence%rowtype;
2706.     v_sequence_name  VARCHAR2(50);
2707.     n_line_no        NUMBER(38, 0);
2708.     v_object_type    VARCHAR2(30);
2709.     v_prompt         VARCHAR2(30);
2710.     v_username       VARCHAR2(30);
2711.     PROCEDURE insert_rec(p_line_no       INTEGER,
2712.                          p_object_type   VARCHAR2,
2713.                          p_user_name     VARCHAR2,
2714.                          p_sequence_name VARCHAR2,
2715.                          p_string        VARCHAR2) IS
2716.     BEGIN
2717.       INSERT INTO temp_for_pkg_gen_sql2000_temp
2718.         (line_no, object_type, user_name, object_name, sql_text)
2719.       VALUES
2720.         (p_line_no, p_object_type, p_user_name, p_sequence_name, p_string);
2721.     END;
2722.   BEGIN
2723.     n_line_no     := 1;
2724.     v_object_type := 'SEQUENCE_INIT';
2725.     v_prompt      := '-- prompt ';
2726.   
2727.     SELECT username INTO v_username FROM user_users;
2728.   
2729.     DELETE temp_for_pkg_gen_sql2000_temp WHERE object_type = v_object_type;
2730.     COMMIT;
2731.   
2732.     OPEN cur_sequence;
2733.     LOOP
2734.       FETCH cur_sequence
2735.         INTO rec_cur_sequence;
2736.       EXIT WHEN cur_sequence%NOTFOUND;
2737.     
2738.       IF p_v_gen_drop_sql = 'T' THEN
2739.         IF p_v_display_prompt_info = 'T' THEN
2740.           v_sql := v_prompt || ' DROP SEQUENCE ' ||
2741.                    rec_cur_sequence.sequence_name;
2742.           insert_rec(n_line_no,
2743.                      v_object_type,
2744.                      v_username,
2745.                      rec_cur_sequence.sequence_name,
2746.                      v_sql);
2747.           n_line_no := n_line_no + 1;
2748.         END IF;
2749.         v_sql := 'DROP SEQUENCE ' || rec_cur_sequence.sequence_name || ';';
2750.         insert_rec(n_line_no,
2751.                    v_object_type,
2752.                    v_username,
2753.                    rec_cur_sequence.sequence_name,
2754.                    v_sql);
2755.         n_line_no := n_line_no + 1;
2756.       
2757.         v_sql := '                  ';
2758.         insert_rec(n_line_no,
2759.                    v_object_type,
2760.                    v_username,
2761.                    rec_cur_sequence.sequence_name,
2762.                    v_sql);
2763.         n_line_no := n_line_no + 1;
2764.       END IF;
2765.     
2766.       IF p_v_gen_create_sql = 'T' THEN
2767.         IF p_v_display_prompt_info = 'T' THEN
2768.           v_sql := v_prompt || ' CREATE SEQUENCE ' ||
2769.                    rec_cur_sequence.sequence_name;
2770.           insert_rec(n_line_no,
2771.                      v_object_type,
2772.                      v_username,
2773.                      rec_cur_sequence.sequence_name,
2774.                      v_sql);
2775.           n_line_no := n_line_no + 1;
2776.         END IF;
2777.       
2778.         v_sql := 'CREATE SEQUENCE ' || rec_cur_sequence.sequence_name;
2779.         insert_rec(n_line_no,
2780.                    v_object_type,
2781.                    v_username,
2782.                    rec_cur_sequence.sequence_name,
2783.                    v_sql);
2784.         n_line_no := n_line_no + 1;
2785.       
2786.         v_sql := 'MINVALUE ' || to_char(rec_cur_sequence.min_value);
2787.         insert_rec(n_line_no,
2788.                    v_object_type,
2789.                    v_username,
2790.                    rec_cur_sequence.sequence_name,
2791.                    v_sql);
2792.         n_line_no := n_line_no + 1;
2793.       
2794.         v_sql := 'MAXVALUE ' || to_char(rec_cur_sequence.max_value);
2795.         insert_rec(n_line_no,
2796.                    v_object_type,
2797.                    v_username,
2798.                    rec_cur_sequence.sequence_name,
2799.                    v_sql);
2800.         n_line_no := n_line_no + 1;
2801.       
2802.         v_sql := 'START WITH 1';
2803.         insert_rec(n_line_no,
2804.                    v_object_type,
2805.                    v_username,
2806.                    rec_cur_sequence.sequence_name,
2807.                    v_sql);
2808.         n_line_no := n_line_no + 1;
2809.       
2810.         v_sql := 'INCREMENT BY ' || to_char(rec_cur_sequence.increment_by);
2811.         insert_rec(n_line_no,
2812.                    v_object_type,
2813.                    v_username,
2814.                    rec_cur_sequence.sequence_name,
2815.                    v_sql);
2816.         n_line_no := n_line_no + 1;
2817.       
2818.         IF rec_cur_sequence.cycle_flag = 'N' THEN
2819.           v_sql := 'CACHE ' || to_char(rec_cur_sequence.cache_size) ||
2820.                    ' NOCYCLE ';
2821.         ELSE
2822.           v_sql := 'CACHE ' || to_char(rec_cur_sequence.cache_size) ||
2823.                    ' CYCLE ';
2824.         END IF;
2825.         insert_rec(n_line_no,
2826.                    v_object_type,
2827.                    v_username,
2828.                    rec_cur_sequence.sequence_name,
2829.                    v_sql);
2830.         n_line_no := n_line_no + 1;
2831.       
2832.         UPDATE temp_for_pkg_gen_sql2000_temp
2833.            SET sql_text = sql_text || ';'
2834.          WHERE line_no = n_line_no - 1
2835.            AND object_type = v_object_type;
2836.       
2837.         v_sql := '  ';
2838.         insert_rec(n_line_no,
2839.                    v_object_type,
2840.                    v_username,
2841.                    rec_cur_sequence.sequence_name,
2842.                    v_sql);
2843.         n_line_no := n_line_no + 1;
2844.       END IF;
2845.     
2846.     END LOOP;
2847.     CLOSE cur_sequence;
2848.     COMMIT;
2849.   END;
2851.   PROCEDURE pro_synonym_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2852.                             p_v_gen_create_sql      VARCHAR2 default 'T',
2853.                             p_v_display_prompt_info VARCHAR2 default 'T') AS
2854.     v_sql         VARCHAR2(2000);
2855.     n_max_id      NUMBER(38, 0);
2856.     v_max_val     VARCHAR2(1000);
2857.     v_object_type VARCHAR2(30);
2858.     v_prompt      VARCHAR2(30);
2859.     v_username    VARCHAR2(30);
2860.     CURSOR cur_synonym IS
2861.       SELECT synonym_name,
2862.              'CREATE SYNONYM ' || synonym_name || '  for ' ||
2863.              decode(db_link, null, table_owner || '.', '') || table_name ||
2864.              decode(db_link, null, '', '@' || db_link) || ';' sql_text
2865.         FROM user_synonyms;
2866.     rec_cur_synonym cur_synonym%rowtype;
2867.     v_synonym_name  VARCHAR2(50);
2868.     n_line_no       NUMBER(38, 0);
2869.     PROCEDURE insert_rec(p_line_no     INTEGER,
2870.                          p_object_type VARCHAR2,
2871.                          p_user_name   VARCHAR2,
2872.                          p_object_name VARCHAR2,
2873.                          p_string      VARCHAR2) IS
2874.     BEGIN
2875.       INSERT INTO temp_for_pkg_gen_sql2000_temp
2876.         (line_no, object_type, user_name, object_name, sql_text)
2877.       VALUES
2878.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
2879.     END;
2880.   BEGIN
2881.     n_line_no     := 1;
2882.     v_object_type := upper('SYNONYM');
2883.     v_prompt      := '-- prompt ';
2884.   
2885.     SELECT username INTO v_username FROM user_users;
2886.   
2887.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
2888.     COMMIT;
2889.   
2890.     OPEN cur_synonym;
2891.     LOOP
2892.       FETCH cur_synonym
2893.         INTO rec_cur_synonym;
2894.       EXIT WHEN cur_synonym%NOTFOUND;
2895.     
2896.       IF p_v_gen_drop_sql = 'T' THEN
2897.         IF p_v_display_prompt_info = 'T' THEN
2898.           v_sql := v_prompt || ' DROP SYNONYM ' ||
2899.                    rec_cur_synonym.synonym_name;
2900.           insert_rec(n_line_no,
2901.                      v_object_type,
2902.                      v_username,
2903.                      rec_cur_synonym.synonym_name,
2904.                      v_sql);
2905.           n_line_no := n_line_no + 1;
2906.         END IF;
2907.         v_sql := 'DROP SYNONYM ' || rec_cur_synonym.synonym_name || ';';
2908.         insert_rec(n_line_no,
2909.                    v_object_type,
2910.                    v_username,
2911.                    rec_cur_synonym.synonym_name,
2912.                    v_sql);
2913.         n_line_no := n_line_no + 1;
2914.       
2915.         v_sql := '                  ';
2916.         insert_rec(n_line_no,
2917.                    v_object_type,
2918.                    v_username,
2919.                    rec_cur_synonym.synonym_name,
2920.                    v_sql);
2921.         n_line_no := n_line_no + 1;
2922.       END IF;
2923.     
2924.       IF p_v_gen_create_sql = 'T' THEN
2925.         IF p_v_display_prompt_info = 'T' THEN
2926.           v_sql := v_prompt || ' CREATE SYNONYM ' ||
2927.                    rec_cur_synonym.synonym_name;
2928.           insert_rec(n_line_no,
2929.                      v_object_type,
2930.                      v_username,
2931.                      rec_cur_synonym.synonym_name,
2932.                      v_sql);
2933.           n_line_no := n_line_no + 1;
2934.         END IF;
2935.       
2936.         v_sql := rec_cur_synonym.sql_text;
2937.         insert_rec(n_line_no,
2938.                    v_object_type,
2939.                    v_username,
2940.                    rec_cur_synonym.synonym_name,
2941.                    v_sql);
2942.         n_line_no := n_line_no + 1;
2943.       
2944.         v_sql := '  ';
2945.         insert_rec(n_line_no,
2946.                    v_object_type,
2947.                    v_username,
2948.                    rec_cur_synonym.synonym_name,
2949.                    v_sql);
2950.         n_line_no := n_line_no + 1;
2951.       END IF;
2952.     
2953.     END LOOP;
2954.     CLOSE cur_synonym;
2955.     COMMIT;
2956.   END;
2958.   PROCEDURE pro_job_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
2959.                         p_v_gen_create_sql      VARCHAR2 default 'T',
2960.                         p_v_display_prompt_info VARCHAR2 default 'T') AS
2961.     v_sql         VARCHAR2(4000);
2962.     n_max_id      NUMBER(38, 0);
2963.     v_max_val     VARCHAR2(1000);
2964.     v_object_type VARCHAR2(30);
2965.     v_prompt      VARCHAR2(30);
2966.     v_username    VARCHAR2(30);
2967.     v_object_name VARCHAR2(200);
2968.     CURSOR cur_job IS
2969.       SELECT job job_no,
2970.              decode(instr(what, chr(10)),
2971.                     0,
2972.                     what,
2973.                     substr(what, 1, instr(what, chr(10)) - 1)) job_name,
2974.              'dbms_job.submit(:jobno,''' || what || ''', sysdate, ''' ||
2975.              interval || ''');' sql_text
2976.         FROM user_jobs;
2977.     rec_cur_job cur_job%rowtype;
2978.     v_job_name  VARCHAR2(50);
2979.     n_line_no   NUMBER(38, 0);
2980.     PROCEDURE insert_rec(p_line_no     INTEGER,
2981.                          p_object_type VARCHAR2,
2982.                          p_user_name   VARCHAR2,
2983.                          p_object_name VARCHAR2,
2984.                          p_string      VARCHAR2) IS
2985.     BEGIN
2986.       INSERT INTO temp_for_pkg_gen_sql4000_temp
2987.         (line_no, object_type, user_name, object_name, sql_text)
2988.       VALUES
2989.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
2990.     END;
2991.   BEGIN
2992.     n_line_no     := 1;
2993.     v_object_type := upper('job');
2994.     v_prompt      := '-- prompt ';
2995.   
2996.     SELECT username INTO v_username FROM user_users;
2997.   
2998.     DELETE temp_for_pkg_gen_sql4000_temp WHERE OBJECT_TYPE = v_object_type;
2999.     COMMIT;
3000.   
3001.     OPEN cur_job;
3002.     LOOP
3003.       FETCH cur_job
3004.         INTO rec_cur_job;
3005.       EXIT WHEN cur_job%NOTFOUND;
3006.     
3007.       IF p_v_gen_drop_sql = 'T' THEN
3008.         IF p_v_display_prompt_info = 'T' THEN
3009.           v_sql := v_prompt || ' DROP job ' || rec_cur_job.job_name;
3010.           insert_rec(n_line_no,
3011.                      v_object_type,
3012.                      v_username,
3013.                      rec_cur_job.job_name,
3014.                      v_sql);
3015.           n_line_no := n_line_no + 1;
3016.         END IF;
3017.         v_sql := 'exec dbms_job.remove(' || to_char(rec_cur_job.job_no) || ');';
3018.         insert_rec(n_line_no,
3019.                    v_object_type,
3020.                    v_username,
3021.                    rec_cur_job.job_name,
3022.                    v_sql);
3023.         n_line_no := n_line_no + 1;
3024.       
3025.         v_sql := '                  ';
3026.         insert_rec(n_line_no,
3027.                    v_object_type,
3028.                    v_username,
3029.                    rec_cur_job.job_name,
3030.                    v_sql);
3031.         n_line_no := n_line_no + 1;
3032.       END IF;
3033.     
3034.       IF p_v_gen_create_sql = 'T' THEN
3035.         IF p_v_display_prompt_info = 'T' THEN
3036.           v_sql := v_prompt || ' CREATE job ' || rec_cur_job.job_name;
3037.           insert_rec(n_line_no,
3038.                      v_object_type,
3039.                      v_username,
3040.                      rec_cur_job.job_name,
3041.                      v_sql);
3042.           n_line_no := n_line_no + 1;
3043.         END IF;
3044.       
3045.         v_sql := 'variable jobno number;';
3046.         insert_rec(n_line_no,
3047.                    v_object_type,
3048.                    v_username,
3049.                    rec_cur_job.job_name,
3050.                    v_sql);
3051.         n_line_no := n_line_no + 1;
3052.       
3053.         v_sql := 'begin';
3054.         insert_rec(n_line_no,
3055.                    v_object_type,
3056.                    v_username,
3057.                    rec_cur_job.job_name,
3058.                    v_sql);
3059.         n_line_no := n_line_no + 1;
3060.       
3061.         v_sql := rec_cur_job.sql_text;
3062.         insert_rec(n_line_no,
3063.                    v_object_type,
3064.                    v_username,
3065.                    rec_cur_job.job_name,
3066.                    v_sql);
3067.         n_line_no := n_line_no + 1;
3068.       
3069.         v_sql := 'end;';
3070.         insert_rec(n_line_no,
3071.                    v_object_type,
3072.                    v_username,
3073.                    rec_cur_job.job_name,
3074.                    v_sql);
3075.         n_line_no := n_line_no + 1;
3076.       
3077.         v_sql := '/';
3078.         insert_rec(n_line_no,
3079.                    v_object_type,
3080.                    v_username,
3081.                    rec_cur_job.job_name,
3082.                    v_sql);
3083.         n_line_no := n_line_no + 1;
3084.       
3085.         v_sql := '  ';
3086.         insert_rec(n_line_no,
3087.                    v_object_type,
3088.                    v_username,
3089.                    rec_cur_job.job_name,
3090.                    v_sql);
3091.         n_line_no := n_line_no + 1;
3092.       END IF;
3093.     
3094.     END LOOP;
3095.     CLOSE cur_job;
3096.     COMMIT;
3097.   END;
3099.   PROCEDURE pro_dl_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
3100.                        p_v_gen_create_sql      VARCHAR2 default 'T',
3101.                        p_v_display_prompt_info VARCHAR2 default 'T') AS
3102.     v_sql         VARCHAR2(2000);
3103.     n_max_id      NUMBER(38, 0);
3104.     v_max_val     VARCHAR2(1000);
3105.     v_object_type VARCHAR2(30);
3106.     v_prompt      VARCHAR2(30);
3107.     v_username    VARCHAR2(30);
3108.     CURSOR cur_dl IS
3109.       SELECT db_link,
3110.              'CREATE DATABASE LINK ' || db_link || ' CONNECT TO ' ||
3111.              username || ' IDENTIFIED BY ' || password || ' USING ''' || HOST ||
3112.              ''';' sql_text
3113.         FROM user_db_links;
3114.     rec_cur_dl cur_dl%rowtype;
3115.     v_db_link  VARCHAR2(50);
3116.     n_line_no  NUMBER(38, 0);
3117.     PROCEDURE insert_rec(p_line_no     INTEGER,
3118.                          p_object_type VARCHAR2,
3119.                          p_user_name   VARCHAR2,
3120.                          p_object_name VARCHAR2,
3121.                          p_string      VARCHAR2) IS
3122.     BEGIN
3123.       INSERT INTO temp_for_pkg_gen_sql2000_temp
3124.         (line_no, object_type, user_name, object_name, sql_text)
3125.       VALUES
3126.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
3127.     END;
3128.   BEGIN
3129.     n_line_no     := 1;
3130.     v_object_type := upper('DL');
3131.     v_prompt      := '-- prompt ';
3132.   
3133.     SELECT username INTO v_username FROM user_users;
3134.   
3135.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
3136.     COMMIT;
3137.   
3138.     OPEN cur_dl;
3139.     LOOP
3140.       FETCH cur_dl
3141.         INTO rec_cur_dl;
3142.       EXIT WHEN cur_dl%NOTFOUND;
3143.     
3144.       IF p_v_gen_drop_sql = 'T' THEN
3145.         IF p_v_display_prompt_info = 'T' THEN
3146.           v_sql := v_prompt || ' DROP DATABASE LIKE ' || rec_cur_dl.db_link;
3147.           insert_rec(n_line_no,
3148.                      v_object_type,
3149.                      v_username,
3150.                      rec_cur_dl.db_link,
3151.                      v_sql);
3152.           n_line_no := n_line_no + 1;
3153.         END IF;
3154.         v_sql := 'DROP DATABASE LIKE ' || rec_cur_dl.db_link || ';';
3155.         insert_rec(n_line_no,
3156.                    v_object_type,
3157.                    v_username,
3158.                    rec_cur_dl.db_link,
3159.                    v_sql);
3160.         n_line_no := n_line_no + 1;
3161.       
3162.         v_sql := '                  ';
3163.         insert_rec(n_line_no,
3164.                    v_object_type,
3165.                    v_username,
3166.                    rec_cur_dl.db_link,
3167.                    v_sql);
3168.         n_line_no := n_line_no + 1;
3169.       END IF;
3170.     
3171.       IF p_v_gen_create_sql = 'T' THEN
3172.         IF p_v_display_prompt_info = 'T' THEN
3173.           v_sql := v_prompt || ' CREATE DATABASE LINK ' ||
3174.                    rec_cur_dl.db_link;
3175.           insert_rec(n_line_no,
3176.                      v_object_type,
3177.                      v_username,
3178.                      rec_cur_dl.db_link,
3179.                      v_sql);
3180.           n_line_no := n_line_no + 1;
3181.         END IF;
3182.       
3183.         v_sql := rec_cur_dl.sql_text;
3184.         insert_rec(n_line_no,
3185.                    v_object_type,
3186.                    v_username,
3187.                    rec_cur_dl.db_link,
3188.                    v_sql);
3189.         n_line_no := n_line_no + 1;
3190.       
3191.         v_sql := '  ';
3192.         insert_rec(n_line_no,
3193.                    v_object_type,
3194.                    v_username,
3195.                    rec_cur_dl.db_link,
3196.                    v_sql);
3197.         n_line_no := n_line_no + 1;
3198.       END IF;
3199.     
3200.     END LOOP;
3201.     CLOSE cur_dl;
3202.     COMMIT;
3203.   END;
3205.   PROCEDURE pro_role_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
3206.                          p_v_gen_create_sql      VARCHAR2 default 'T',
3207.                          p_v_display_prompt_info VARCHAR2 default 'T') AS
3208.     v_sql         VARCHAR2(2000);
3209.     v_option      VARCHAR2(200);
3210.     n_max_id      NUMBER(38, 0);
3211.     v_max_val     VARCHAR2(1000);
3212.     v_object_type VARCHAR2(30);
3213.     v_prompt      VARCHAR2(30);
3214.     v_username    VARCHAR2(30);
3215.     CURSOR cur_role IS
3216.       select granted_role role from user_role_privs;
3217.     rec_cur_role cur_role%rowtype;
3218.   
3219.     CURSOR cur_role_pris(c_role_name VARCHAR2) IS
3220.       SELECT type,
3221.              role,
3222.              owner,
3223.              table_name,
3224.              column_name,
3225.              privilege,
3226.              grantable
3227.         FROM (SELECT 'TAB' type,
3228.                      role,
3229.                      owner,
3230.                      table_name,
3231.                      column_name,
3232.                      privilege,
3233.                      grantable
3234.                 FROM role_tab_privs
3235.                WHERE role NOT IN ('CONNECT', 'RESOURCE', 'DBA')
3236.               UNION
3237.               SELECT 'SYS' type,
3238.                      role,
3239.                      '' owner,
3240.                      '' table_name,
3241.                      '' column_name,
3242.                      privilege,
3243.                      admin_option grantable
3244.                 FROM role_sys_privs
3245.                WHERE role NOT IN ('CONNECT', 'RESOURCE', 'DBA'))
3246.        where role = upper(c_role_name);
3247.     rec_cur_role_pris cur_role_pris%rowtype;
3248.   
3249.     v_role    VARCHAR2(50);
3250.     n_line_no NUMBER(38, 0);
3251.     PROCEDURE insert_rec(p_line_no     INTEGER,
3252.                          p_object_type VARCHAR2,
3253.                          p_user_name   VARCHAR2,
3254.                          p_object_name VARCHAR2,
3255.                          p_string      VARCHAR2) IS
3256.     BEGIN
3257.       INSERT INTO temp_for_pkg_gen_sql2000_temp
3258.         (line_no, object_type, user_name, object_name, sql_text)
3259.       VALUES
3260.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
3261.     END;
3262.   BEGIN
3263.     n_line_no     := 1;
3264.     v_object_type := UPPER('ROLE');
3265.     v_prompt      := '-- prompt ';
3266.   
3267.     SELECT username INTO v_username FROM user_users;
3268.   
3269.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
3270.     COMMIT;
3271.   
3272.     OPEN cur_role;
3273.     LOOP
3274.       FETCH cur_role
3275.         INTO rec_cur_role;
3276.       EXIT WHEN cur_role%NOTFOUND;
3277.     
3278.       IF p_v_gen_drop_sql = 'T' THEN
3279.         IF p_v_display_prompt_info = 'T' THEN
3280.           v_sql := v_prompt || ' DROP ROLE ' || rec_cur_role.role;
3281.           insert_rec(n_line_no,
3282.                      v_object_type,
3283.                      v_username,
3284.                      rec_cur_role.role,
3285.                      v_sql);
3286.           n_line_no := n_line_no + 1;
3287.         END IF;
3288.         v_sql := 'DROP ROLE ' || rec_cur_role.role || ';';
3289.         insert_rec(n_line_no,
3290.                    v_object_type,
3291.                    v_username,
3292.                    rec_cur_role.role,
3293.                    v_sql);
3294.         n_line_no := n_line_no + 1;
3295.       
3296.         v_sql := '                  ';
3297.         insert_rec(n_line_no,
3298.                    v_object_type,
3299.                    v_username,
3300.                    rec_cur_role.role,
3301.                    v_sql);
3302.         n_line_no := n_line_no + 1;
3303.       END IF;
3304.     
3305.       IF p_v_gen_create_sql = 'T' THEN
3306.         IF p_v_display_prompt_info = 'T' THEN
3307.           v_sql := v_prompt || ' CREATE ROLE ' || rec_cur_role.role;
3308.           insert_rec(n_line_no,
3309.                      v_object_type,
3310.                      v_username,
3311.                      rec_cur_role.role,
3312.                      v_sql);
3313.           n_line_no := n_line_no + 1;
3314.         END IF;
3315.       
3316.         v_sql := 'CREATE ROLE ' || rec_cur_role.role || ';';
3317.         insert_rec(n_line_no,
3318.                    v_object_type,
3319.                    v_username,
3320.                    rec_cur_role.role,
3321.                    v_sql);
3322.         n_line_no := n_line_no + 1;
3323.       
3324.         v_sql := v_prompt || ' GRANT PRIS TO  ROLE : ' || rec_cur_role.role;
3325.         insert_rec(n_line_no,
3326.                    v_object_type,
3327.                    v_username,
3328.                    rec_cur_role.role,
3329.                    v_sql);
3330.         n_line_no := n_line_no + 1;
3331.       
3332.         OPEN cur_role_pris(rec_cur_role.role);
3333.         LOOP
3334.           FETCH cur_role_pris
3335.             INTO rec_cur_role_pris;
3336.           EXIT WHEN cur_role_pris%NOTFOUND;
3337.         
3338.           IF rec_cur_role_pris.type = 'SYS' THEN
3339.             IF rec_cur_role_pris.grantable = 'NO' THEN
3340.               v_option := '';
3341.             ELSE
3342.               v_option := ' WITH ADMIN OPTION';
3343.             END IF;
3344.             v_sql := 'GRANT ' || rec_cur_role_pris.privilege || ' TO  ' ||
3345.                      rec_cur_role_pris.role || v_option || ';';
3346.             insert_rec(n_line_no,
3347.                        v_object_type,
3348.                        v_username,
3349.                        rec_cur_role.role,
3350.                        v_sql);
3351.             n_line_no := n_line_no + 1;
3352.           ELSE
3353.             IF rec_cur_role_pris.grantable = 'NO' THEN
3354.               v_option := '';
3355.             ELSE
3356.               v_option := ' WITH GRANT OPTION';
3357.             END IF;
3358.           
3359.             IF rec_cur_role_pris.column_name IS NOT NULL THEN
3360.               v_sql := 'GRANT ' || rec_cur_role_pris.privilege || '(' ||
3361.                        rec_cur_role_pris.column_name || ') ON ' ||
3362.                        rec_cur_role_pris.owner || '.' ||
3363.                        rec_cur_role_pris.table_name || ' TO ' ||
3364.                        rec_cur_role_pris.role || v_option || ';';
3365.             ELSE
3366.               v_sql := 'GRANT ' || rec_cur_role_pris.privilege || ' ON ' ||
3367.                        rec_cur_role_pris.owner || '.' ||
3368.                        rec_cur_role_pris.table_name || ' TO  ' ||
3369.                        rec_cur_role_pris.role || v_option || ';';
3370.             END IF;
3371.           
3372.             insert_rec(n_line_no,
3373.                        v_object_type,
3374.                        v_username,
3375.                        rec_cur_role.role,
3376.                        v_sql);
3377.             n_line_no := n_line_no + 1;
3378.           END IF;
3379.         END LOOP;
3380.         CLOSE cur_role_pris;
3381.       
3382.         v_sql := '  ';
3383.         insert_rec(n_line_no,
3384.                    v_object_type,
3385.                    v_username,
3386.                    rec_cur_role.role,
3387.                    v_sql);
3388.         n_line_no := n_line_no + 1;
3389.       END IF;
3390.     
3391.     END LOOP;
3392.     CLOSE cur_role;
3393.     COMMIT;
3394.   END;
3396.   PROCEDURE pro_priv_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
3397.                          p_v_gen_create_sql      VARCHAR2 default 'T',
3398.                          p_v_display_prompt_info VARCHAR2 default 'T') AS
3399.     v_sql         VARCHAR2(2000);
3400.     v_option      VARCHAR2(200);
3401.     n_max_id      NUMBER(38, 0);
3402.     v_max_val     VARCHAR2(1000);
3403.     v_object_type VARCHAR2(30);
3404.     v_prompt      VARCHAR2(30);
3405.     v_username    VARCHAR2(30);
3406.     n_count       number(10, 0);
3407.     CURSOR cur_priv IS
3408.       SELECT privilege,
3409.              owner,
3410.              table_name,
3411.              grantee,
3412.              'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
3413.              ' TO ' || grantee ||
3414.              decode(grantable, 'NO', '', ' WITH GRANT OPTION') || ';' SQL_TEXT
3415.         FROM user_tab_privs
3416.        WHERE GRANTOR = (SELECT username FROM user_users)
3417.        ORDER BY owner, table_name;
3418.     rec_cur_priv cur_priv%rowtype;
3419.   
3420.     CURSOR cur_col_priv IS
3421.       SELECT privilege,
3422.              owner,
3423.              column_name,
3424.              table_name,
3425.              grantee,
3426.              'GRANT ' || privilege || ' ' || column_name || ' ON ' || owner || '.' ||
3427.              table_name || ' TO ' || grantee ||
3428.              decode(grantable, 'NO', '', ' WITH GRANT OPTION') || ';' SQL_TEXT
3429.         FROM user_col_privs
3430.        WHERE GRANTOR = (SELECT username FROM user_users)
3431.        ORDER BY owner, table_name;
3432.     rec_cur_col_priv cur_col_priv%rowtype;
3433.   
3434.     CURSOR cur_sys_priv IS
3435.       SELECT username,
3436.              privilege,
3437.              'GRANT ' || privilege || ' TO ' || username ||
3438.              decode(admin_option, 'NO', '', ' WITH ADMIN OPTION') || ';' SQL_TEXT
3439.         FROM user_sys_privs
3440.        ORDER BY username;
3441.     rec_cur_sys_priv cur_sys_priv%rowtype;
3442.   
3443.     v_priv    VARCHAR2(50);
3444.     n_line_no NUMBER(38, 0);
3445.     PROCEDURE insert_rec(p_line_no     INTEGER,
3446.                          p_object_type VARCHAR2,
3447.                          p_user_name   VARCHAR2,
3448.                          p_object_name VARCHAR2,
3449.                          p_string      VARCHAR2) IS
3450.     BEGIN
3451.       INSERT INTO temp_for_pkg_gen_sql2000_temp
3452.         (line_no, object_type, user_name, object_name, sql_text)
3453.       VALUES
3454.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
3455.     END;
3456.   BEGIN
3457.     n_line_no     := 1;
3458.     v_object_type := UPPER('PRIV');
3459.     v_prompt      := '-- prompt ';
3460.     n_count       := 1;
3461.   
3462.     SELECT USERNAME INTO v_username FROM USER_USERS;
3463.   
3464.     DELETE temp_for_pkg_gen_sql2000_temp WHERE OBJECT_TYPE = v_object_type;
3465.     COMMIT;
3466.   
3467.     OPEN cur_priv;
3468.     LOOP
3469.       FETCH cur_priv
3470.         INTO rec_cur_priv;
3471.       EXIT WHEN cur_priv%NOTFOUND;
3472.     
3473.       IF p_v_gen_create_sql = 'T' THEN
3474.         IF p_v_display_prompt_info = 'T' THEN
3475.           v_sql := v_prompt || ' GRANT PRIVILEGE : ' ||
3476.                    rec_cur_priv.privilege || ' ON ' || rec_cur_priv.owner || '.' ||
3477.                    rec_cur_priv.table_name || ' TO ' ||
3478.                    rec_cur_priv.grantee;
3479.           insert_rec(n_line_no,
3480.                      v_object_type,
3481.                      v_username,
3482.                      'tab priv',
3483.                      v_sql);
3484.           n_line_no := n_line_no + 1;
3485.         END IF;
3486.       
3487.         v_sql := rec_cur_priv.sql_text;
3488.         insert_rec(n_line_no, v_object_type, v_username, 'tab priv', v_sql);
3489.         n_line_no := n_line_no + 1;
3490.       
3491.         v_sql := '  ';
3492.         insert_rec(n_line_no, v_object_type, v_username, 'tab priv', v_sql);
3493.         n_line_no := n_line_no + 1;
3494.       
3495.       END IF;
3496.     
3497.     END LOOP;
3498.     CLOSE cur_priv;
3499.   
3500.     OPEN cur_col_priv;
3501.     LOOP
3502.       FETCH cur_col_priv
3503.         INTO rec_cur_col_priv;
3504.       EXIT WHEN cur_col_priv%NOTFOUND;
3505.       IF n_count = 1 THEN
3506.         v_sql := '-- *********************** ';
3507.         insert_rec(n_line_no, v_object_type, v_username, 'tab priv', v_sql);
3508.         n_line_no := n_line_no + 1;
3509.       
3510.         n_count := n_count + 1;
3511.       END IF;
3512.     
3513.       v_sql := '  ';
3514.       insert_rec(n_line_no, v_object_type, v_username, 'tab priv', v_sql);
3515.       n_line_no := n_line_no + 1;
3516.     
3517.       IF p_v_display_prompt_info = 'T' THEN
3518.         v_sql := v_prompt || ' GRANT COL PRIVILEGE : ' ||
3519.                  rec_cur_col_priv.privilege || ' ON ' ||
3520.                  rec_cur_col_priv.owner || '.' ||
3521.                  rec_cur_col_priv.table_name || ' TO ' ||
3522.                  rec_cur_col_priv.grantee;
3523.         insert_rec(n_line_no, v_object_type, v_username, 'col priv', v_sql);
3524.         n_line_no := n_line_no + 1;
3525.       END IF;
3526.     
3527.       v_sql := rec_cur_col_priv.sql_text;
3528.       insert_rec(n_line_no, v_object_type, v_username, 'col priv', v_sql);
3529.       n_line_no := n_line_no + 1;
3530.     
3531.       v_sql := '  ';
3532.       insert_rec(n_line_no, v_object_type, v_username, 'col priv', v_sql);
3533.       n_line_no := n_line_no + 1;
3534.     END LOOP;
3535.     CLOSE cur_col_priv;
3536.   
3537.     n_count := 1;
3538.     OPEN cur_sys_priv;
3539.     LOOP
3540.       FETCH cur_sys_priv
3541.         INTO rec_cur_sys_priv;
3542.       EXIT WHEN cur_sys_priv%NOTFOUND;
3543.       IF n_count = 1 THEN
3544.         v_sql := '  ';
3545.         insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3546.         n_line_no := n_line_no + 1;
3547.       
3548.         v_sql := 'PROMPT *********** You must the following sql as a user with DBA priv, others you will get errors! 
3550. ************ ';
3551.         insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3552.         n_line_no := n_line_no + 1;
3553.       
3554.         v_sql := 'PROMPT *********** We comment the follow sql! ************ ';
3555.         insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3556.         n_line_no := n_line_no + 1;
3557.       
3558.         v_sql := '  ';
3559.         insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3560.         n_line_no := n_line_no + 1;
3561.       
3562.         n_count := n_count + 1;
3563.       
3564.       END IF;
3565.     
3566.       IF p_v_display_prompt_info = 'T' THEN
3567.         v_sql := v_prompt || ' GRANT SYSTEM PRIVILEGE : ' ||
3568.                  rec_cur_sys_priv.privilege || ' TO ' ||
3569.                  rec_cur_sys_priv.username;
3570.         insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3571.         n_line_no := n_line_no + 1;
3572.       END IF;
3573.     
3574.       v_sql := '-- ' || rec_cur_sys_priv.sql_text;
3575.       insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3576.       n_line_no := n_line_no + 1;
3577.     
3578.       v_sql := '  ';
3579.       insert_rec(n_line_no, v_object_type, v_username, 'sys priv', v_sql);
3580.       n_line_no := n_line_no + 1;
3581.     END LOOP;
3582.     CLOSE cur_sys_priv;
3583.   
3584.     COMMIT;
3585.   END;
3587.   PROCEDURE pro_pkgprofuntri_sql(p_v_gen_drop_sql        VARCHAR2 default 'T',
3588.                                  p_v_gen_create_sql      VARCHAR2 default 'T',
3589.                                  p_v_display_prompt_info VARCHAR2 default 'T',
3590.                                  p_v_separ_file          VARCHAR2 default 'T',
3591.                                  p_v_file_path           VARCHAR2 default 'C:/') AS
3592.     v_sql         VARCHAR2(2000);
3593.     v_object_type VARCHAR2(30);
3594.     v_prompt      VARCHAR2(30);
3595.     v_file_path   VARCHAR2(600);
3596.     v_notion      char(1);
3597.     v_username    VARCHAR2(30);
3598.     CURSOR cur_ProFunPkg_name IS
3599.       SELECT distinct type, us.name
3600.         FROM user_source us
3601.        WHERE type <> 'PACKAGE BODY'
3602.          AND us.name <> upper('pkg_gen_user_sql')
3603.        ORDER BY us.type, us.name;
3604.   
3605.     CURSOR cur_ProFunPkg_code(c_obj_name VARCHAR2, c_obj_type VARCHAR2) IS
3606.       SELECT line, type, text
3607.         FROM user_source us
3608.        WHERE NAME = c_obj_name
3609.          AND type = c_obj_type
3610.        ORDER BY type, line;
3611.   
3612.     v_object_name VARCHAR2(50);
3613.     n_line_no     NUMBER(38, 0);
3614.     PROCEDURE insert_rec(p_line_no     INTEGER,
3615.                          p_object_type VARCHAR2,
3616.                          p_user_name   VARCHAR2,
3617.                          p_object_name VARCHAR2,
3618.                          p_string      VARCHAR2) IS
3619.     BEGIN
3620.       INSERT INTO temp_for_pkg_gen_sql4000_temp
3621.         (line_no, object_type, user_name, object_name, sql_text)
3622.       VALUES
3623.         (p_line_no, p_object_type, p_user_name, p_object_name, p_string);
3624.     END;
3625.   BEGIN
3626.     n_line_no   := 1;
3627.     v_file_path := p_v_file_path;
3628.     v_prompt    := '-- prompt ';
3629.   
3630.     SELECT username INTO v_username FROM user_users;
3631.   
3632.     -- find the notion between the dictionary
3633.     IF instr(p_v_file_path, '/') > 0 THEN
3634.       v_notion := '/';
3635.     ELSE
3636.       v_notion := '/';
3637.     END IF;
3638.   
3639.     IF substr(v_file_path, length(v_file_path)) <> v_notion THEN
3640.       v_file_path := v_file_path || v_notion;
3641.     END IF;
3642.   
3643.     DELETE temp_for_pkg_gen_sql4000_temp
3644.      WHERE OBJECT_TYPE IN
3645.            ('PACKAGE BODY', 'PACKAGE', 'PROCEDURE', 'FUNCTION',
3646.             'SEPAR_PROCS', 'SEPAR_FUNCS', 'SEPAR_PKG_HEADS',
3647.             'SEPAR_PKG_BODYS', 'SEPAR_PKGS', 'TRIGGER', 'SEPAR_TRIS');
3648.     COMMIT;
3649.   
3650.     FOR rec_ProFunPkg_name IN cur_ProFunPkg_name LOOP
3651.       v_object_type := rec_ProFunPkg_name.type;
3652.     
3653.       v_sql := '--  ~~~~~~~~~~~~~~~~~~~~  ' || rec_ProFunPkg_name.type ||
3654.                ' : ' || rec_ProFunPkg_name.name ||
3655.                ' START;  ~~~~~~~~~~~~~~~~~~~~';
3656.       insert_rec(n_line_no,
3657.                  v_object_type,
3658.                  v_username,
3659.                  rec_ProFunPkg_name.name,
3660.                  v_sql);
3661.       n_line_no := n_line_no + 1;
3662.     
3663.       v_sql := '                  ';
3664.       insert_rec(n_line_no,
3665.                  v_object_type,
3666.                  v_username,
3667.                  rec_ProFunPkg_name.name,
3668.                  v_sql);
3669.       n_line_no := n_line_no + 1;
3670.     
3671.       IF p_v_gen_drop_sql = 'T' AND v_object_type <> 'PACKAGE BODY' THEN
3672.         IF p_v_display_prompt_info = 'T' THEN
3673.           v_sql := v_prompt || ' DROP ' || rec_ProFunPkg_name.type || ' ' ||
3674.                    rec_ProFunPkg_name.name;
3675.           insert_rec(n_line_no,
3676.                      v_object_type,
3677.                      v_username,
3678.                      rec_ProFunPkg_name.name,
3679.                      v_sql);
3680.           n_line_no := n_line_no + 1;
3681.         END IF;
3682.         v_sql := 'DROP ' || rec_ProFunPkg_name.type || ' ' ||
3683.                  rec_ProFunPkg_name.type || ' ' || rec_ProFunPkg_name.name || ';';
3684.         insert_rec(n_line_no,
3685.                    v_object_type,
3686.                    v_username,
3687.                    rec_ProFunPkg_name.name,
3688.                    v_sql);
3689.         n_line_no := n_line_no + 1;
3690.       
3691.         v_sql := '                  ';
3692.         insert_rec(n_line_no,
3693.                    v_object_type,
3694.                    v_username,
3695.                    rec_ProFunPkg_name.name,
3696.                    v_sql);
3697.         n_line_no := n_line_no + 1;
3698.       END IF;
3699.     
3700.       IF p_v_gen_create_sql = 'T' THEN
3701.         IF p_v_display_prompt_info = 'T' THEN
3702.           v_sql := v_prompt || ' CREATE ' || rec_ProFunPkg_name.type || ' ' ||
3703.                    rec_ProFunPkg_name.name;
3704.           insert_rec(n_line_no,
3705.                      v_object_type,
3706.                      v_username,
3707.                      rec_ProFunPkg_name.name,
3708.                      v_sql);
3709.           n_line_no := n_line_no + 1;
3710.         END IF;
3711.       
3712.         FOR rec_ProFunPkg_code IN cur_ProFunPkg_code(rec_ProFunPkg_name.name,
3713.                                                      rec_ProFunPkg_name.type) LOOP
3714.           IF rec_ProFunPkg_code.line = 1 THEN
3715.             v_sql := 'CREATE OR REPLACE ' || rec_ProFunPkg_code.text;
3716.           ELSE
3717.             v_sql := rec_ProFunPkg_code.text;
3718.           END IF;
3719.           insert_rec(n_line_no,
3720.                      v_object_type,
3721.                      v_username,
3722.                      rec_ProFunPkg_name.name,
3723.                      v_sql);
3724.           n_line_no := n_line_no + 1;
3725.         END LOOP;
3726.       
3727.         v_sql := '/';
3728.         insert_rec(n_line_no,
3729.                    v_object_type,
3730.                    v_username,
3731.                    rec_ProFunPkg_name.name,
3732.                    v_sql);
3733.         n_line_no := n_line_no + 1;
3734.       
3735.         v_sql := '@' || lower(rec_ProFunPkg_name.name) || '.sql';
3736.         IF v_object_type = 'PROCEDURE' THEN
3737.           insert_rec(n_line_no,
3738.                      v_object_type,
3739.                      v_username,
3740.                      'START_PROCS',
3741.                      v_sql);
3742.         ELSIF v_object_type = 'FUNCTION' THEN
3743.           insert_rec(n_line_no,
3744.                      v_object_type,
3745.                      v_username,
3746.                      'START_FUNCS',
3747.                      v_sql);
3748.         ELSIF v_object_type = 'PACKAGE' or v_object_type = 'PACKAGE BODY' THEN
3749.           insert_rec(n_line_no,
3750.                      v_object_type,
3751.                      v_username,
3752.                      'START_PKGS',
3753.                      v_sql);
3754.           n_line_no := n_line_no + 1;
3755.         
3756.           v_sql := '@' || lower(rec_ProFunPkg_name.name) || '.haad.sql';
3757.           insert_rec(n_line_no,
3758.                      v_object_type,
3759.                      v_username,
3760.                      'START_PKG_HS',
3761.                      v_sql);
3762.           n_line_no := n_line_no + 1;
3763.         
3764.           v_sql := '@' || lower(rec_ProFunPkg_name.name) || '.body.sql';
3765.           insert_rec(n_line_no,
3766.                      v_object_type,
3767.                      v_username,
3768.                      'START_PKG_BS',
3769.                      v_sql);
3770.         ELSIF v_object_type = 'TRIGGER' THEN
3771.           insert_rec(n_line_no,
3772.                      v_object_type,
3773.                      v_username,
3774.                      'START_TRIS',
3775.                      v_sql);
3776.         ELSIF v_object_type = 'TYPE' or v_object_type = 'TYPE BODY' THEN
3777.           insert_rec(n_line_no,
3778.                      v_object_type,
3779.                      v_username,
3780.                      'START_TYPES',
3781.                      v_sql);
3782.           n_line_no := n_line_no + 1;
3783.         
3784.           v_sql := '@' || lower(rec_ProFunPkg_name.name) || '.haad.sql';
3785.           insert_rec(n_line_no,
3786.                      v_object_type,
3787.                      v_username,
3788.                      'START_TYPE_HS',
3789.                      v_sql);
3790.           n_line_no := n_line_no + 1;
3791.         
3792.           v_sql := '@' || lower(rec_ProFunPkg_name.name) || '.body.sql';
3793.           insert_rec(n_line_no,
3794.                      v_object_type,
3795.                      v_username,
3796.                      'START_TYPE_BS',
3797.                      v_sql);
3798.         END IF;
3799.         n_line_no := n_line_no + 1;
3800.       
3801.         v_sql := '  ';
3802.         insert_rec(n_line_no,
3803.                    'START_PROCS',
3804.                    v_username,
3805.                    rec_ProFunPkg_name.name,
3806.                    v_sql);
3807.         n_line_no := n_line_no + 1;
3808.       
3809.         v_sql := '  ';
3810.         insert_rec(n_line_no,
3811.                    v_object_type,
3812.                    v_username,
3813.                    rec_ProFunPkg_name.name,
3814.                    v_sql);
3815.         n_line_no := n_line_no + 1;
3816.       
3817.         IF rec_ProFunPkg_name.type = 'PACKAGE' THEN
3818.           FOR rec_ProFunPkg_code IN cur_ProFunPkg_code(rec_ProFunPkg_name.name,
3819.                                                        rec_ProFunPkg_name.type ||
3820.                                                        ' BODY') LOOP
3821.             IF rec_ProFunPkg_code.line = 1 THEN
3822.               v_sql := 'CREATE OR REPLACE ' || rec_ProFunPkg_code.text;
3823.             ELSE
3824.               v_sql := rec_ProFunPkg_code.text;
3825.             END IF;
3826.             insert_rec(n_line_no,
3827.                        rec_ProFunPkg_name.type || ' BODY',
3828.                        v_username,
3829.                        rec_ProFunPkg_name.name,
3830.                        v_sql);
3831.             n_line_no := n_line_no + 1;
3832.           END LOOP;
3833.         
3834.           v_sql := '/';
3835.           insert_rec(n_line_no,
3836.                      rec_ProFunPkg_name.type || ' BODY',
3837.                      v_username,
3838.                      rec_ProFunPkg_name.name,
3839.                      v_sql);
3840.           n_line_no := n_line_no + 1;
3841.         
3842.           v_sql := '  ';
3843.           insert_rec(n_line_no,
3844.                      rec_ProFunPkg_name.type || ' BODY',
3845.                      v_username,
3846.                      rec_ProFunPkg_name.name,
3847.                      v_sql);
3848.           n_line_no := n_line_no + 1;
3849.         END IF;
3850.       
3851.         v_sql := '--  ~~~~~~~~~~~~~~~~~~~~  ' || rec_ProFunPkg_name.type ||
3852.                  ' : ' || rec_ProFunPkg_name.name ||
3853.                  ' END;  ~~~~~~~~~~~~~~~~~~~~';
3854.         insert_rec(n_line_no,
3855.                    v_object_type,
3856.                    v_username,
3857.                    rec_ProFunPkg_name.name,
3858.                    v_sql);
3859.         n_line_no := n_line_no + 1;
3860.       
3861.         v_sql := '  ';
3862.         insert_rec(n_line_no,
3863.                    v_object_type,
3864.                    v_username,
3865.                    rec_ProFunPkg_name.name,
3866.                    v_sql);
3867.         n_line_no := n_line_no + 1;
3868.       
3869.       END IF;
3870.     
3871.       IF v_object_type = 'PROCEDURE' THEN
3872.         v_sql := 'spool ' || v_file_path || 'procedures' || v_notion ||
3873.                  'separ_files' || v_notion ||
3874.                  lower(rec_ProFunPkg_name.name) || '.sql';
3875.         insert_rec(n_line_no,
3876.                    'SEPAR_PROCS',
3877.                    v_username,
3878.                    rec_ProFunPkg_name.name,
3879.                    v_sql);
3880.         n_line_no := n_line_no + 1;
3881.       
3882.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
3883.         insert_rec(n_line_no,
3884.                    'SEPAR_PROCS',
3885.                    v_username,
3886.                    rec_ProFunPkg_name.name,
3887.                    v_sql);
3888.         n_line_no := n_line_no + 1;
3889.       
3890.         v_sql := 'WHERE OBJECT_TYPE = ''PROCEDURE'' AND OBJECT_NAME = ''' ||
3891.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
3892.         insert_rec(n_line_no,
3893.                    'SEPAR_PROCS',
3894.                    v_username,
3895.                    rec_ProFunPkg_name.name,
3896.                    v_sql);
3897.         n_line_no := n_line_no + 1;
3898.       
3899.         v_sql := 'spool off';
3900.         insert_rec(n_line_no,
3901.                    'SEPAR_PROCS',
3902.                    v_username,
3903.                    rec_ProFunPkg_name.name,
3904.                    v_sql);
3905.         n_line_no := n_line_no + 1;
3906.       
3907.         v_sql := '  ';
3908.         insert_rec(n_line_no,
3909.                    'SEPAR_PROCS',
3910.                    v_username,
3911.                    rec_ProFunPkg_name.name,
3912.                    v_sql);
3913.         n_line_no := n_line_no + 1;
3914.       
3915.       ELSIF v_object_type = 'FUNCTION' THEN
3916.         v_sql := 'spool ' || v_file_path || 'functions' || v_notion ||
3917.                  'separ_files' || v_notion ||
3918.                  lower(rec_ProFunPkg_name.name) || '.sql';
3919.         insert_rec(n_line_no,
3920.                    'SEPAR_FUNCS',
3921.                    v_username,
3922.                    rec_ProFunPkg_name.name,
3923.                    v_sql);
3924.         n_line_no := n_line_no + 1;
3925.       
3926.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
3927.         insert_rec(n_line_no,
3928.                    'SEPAR_FUNCS',
3929.                    v_username,
3930.                    rec_ProFunPkg_name.name,
3931.                    v_sql);
3932.         n_line_no := n_line_no + 1;
3933.       
3934.         v_sql := 'WHERE OBJECT_TYPE = ''FUNCTION'' AND OBJECT_NAME = ''' ||
3935.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
3936.         insert_rec(n_line_no,
3937.                    'SEPAR_FUNCS',
3938.                    v_username,
3939.                    rec_ProFunPkg_name.name,
3940.                    v_sql);
3941.         n_line_no := n_line_no + 1;
3942.       
3943.         v_sql := 'spool off';
3944.         insert_rec(n_line_no,
3945.                    'SEPAR_FUNCS',
3946.                    v_username,
3947.                    rec_ProFunPkg_name.name,
3948.                    v_sql);
3949.         n_line_no := n_line_no + 1;
3950.       
3951.         v_sql := '  ';
3952.         insert_rec(n_line_no,
3953.                    'SEPAR_FUNCS',
3954.                    v_username,
3955.                    rec_ProFunPkg_name.name,
3956.                    v_sql);
3957.         n_line_no := n_line_no + 1;
3958.       ELSIF v_object_type = 'TRIGGER' THEN
3959.         v_sql := 'spool ' || v_file_path || 'triggers' || v_notion ||
3960.                  'separ_files' || v_notion ||
3961.                  lower(rec_ProFunPkg_name.name) || '.sql';
3962.         insert_rec(n_line_no,
3963.                    'SEPAR_TRIS',
3964.                    v_username,
3965.                    rec_ProFunPkg_name.name,
3966.                    v_sql);
3967.         n_line_no := n_line_no + 1;
3968.       
3969.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
3970.         insert_rec(n_line_no,
3971.                    'SEPAR_TRIS',
3972.                    v_username,
3973.                    rec_ProFunPkg_name.name,
3974.                    v_sql);
3975.         n_line_no := n_line_no + 1;
3976.       
3977.         v_sql := 'WHERE OBJECT_TYPE = ''TRIGGER'' AND OBJECT_NAME = ''' ||
3978.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
3979.         insert_rec(n_line_no,
3980.                    'SEPAR_TRIS',
3981.                    v_username,
3982.                    rec_ProFunPkg_name.name,
3983.                    v_sql);
3984.         n_line_no := n_line_no + 1;
3985.       
3986.         v_sql := 'spool off';
3987.         insert_rec(n_line_no,
3988.                    'SEPAR_TRIS',
3989.                    v_username,
3990.                    rec_ProFunPkg_name.name,
3991.                    v_sql);
3992.         n_line_no := n_line_no + 1;
3993.       
3994.         v_sql := '  ';
3995.         insert_rec(n_line_no,
3996.                    'SEPAR_TRIS',
3997.                    v_username,
3998.                    rec_ProFunPkg_name.name,
3999.                    v_sql);
4000.         n_line_no := n_line_no + 1;
4001.       ELSIF v_object_type = 'PACKAGE' THEN
4002.         --生成可以将包头放在一个文件中的批处理文件
4003.         v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4004.                  'separ_headbobdy_file' || v_notion ||
4005.                  lower(rec_ProFunPkg_name.name) || '.head.sql;';
4006.         insert_rec(n_line_no,
4007.                    'SEPAR_PKG_HEADS',
4008.                    v_username,
4009.                    rec_ProFunPkg_name.name,
4010.                    v_sql);
4011.         n_line_no := n_line_no + 1;
4012.       
4013.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4014.         insert_rec(n_line_no,
4015.                    'SEPAR_PKG_HEADS',
4016.                    v_username,
4017.                    rec_ProFunPkg_name.name,
4018.                    v_sql);
4019.         n_line_no := n_line_no + 1;
4020.       
4021.         v_sql := 'WHERE OBJECT_TYPE = ''PACKAGE'' AND OBJECT_NAME = ''' ||
4022.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
4023.         insert_rec(n_line_no,
4024.                    'SEPAR_PKG_HEADS',
4025.                    v_username,
4026.                    rec_ProFunPkg_name.name,
4027.                    v_sql);
4028.         n_line_no := n_line_no + 1;
4029.       
4030.         v_sql := 'spool off';
4031.         insert_rec(n_line_no,
4032.                    'SEPAR_PKG_HEADS',
4033.                    v_username,
4034.                    rec_ProFunPkg_name.name,
4035.                    v_sql);
4036.         n_line_no := n_line_no + 1;
4037.       
4038.         v_sql := '  ';
4039.         insert_rec(n_line_no,
4040.                    'SEPAR_PKG_HEADS',
4041.                    v_username,
4042.                    rec_ProFunPkg_name.name,
4043.                    v_sql);
4044.         n_line_no := n_line_no + 1;
4045.       
4046.         --生成可以将包体放在一个文件中的批处理文件
4047.         v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4048.                  'separ_headbobdy_file' || v_notion ||
4049.                  lower(rec_ProFunPkg_name.name) || '.body.sql;';
4050.         insert_rec(n_line_no,
4051.                    'SEPAR_PKG_BODYS',
4052.                    v_username,
4053.                    rec_ProFunPkg_name.name,
4054.                    v_sql);
4055.         n_line_no := n_line_no + 1;
4056.       
4057.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4058.         insert_rec(n_line_no,
4059.                    'SEPAR_PKG_BODYS',
4060.                    v_username,
4061.                    rec_ProFunPkg_name.name,
4062.                    v_sql);
4063.         n_line_no := n_line_no + 1;
4064.       
4065.         v_sql := 'WHERE OBJECT_TYPE = ''PACKAGE BODY'' AND OBJECT_NAME = ''' ||
4066.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
4067.         insert_rec(n_line_no,
4068.                    'SEPAR_PKG_BODYS',
4069.                    v_username,
4070.                    rec_ProFunPkg_name.name,
4071.                    v_sql);
4072.         n_line_no := n_line_no + 1;
4073.       
4074.         v_sql := 'spool off;';
4075.         insert_rec(n_line_no,
4076.                    'SEPAR_PKG_BODYS',
4077.                    v_username,
4078.                    rec_ProFunPkg_name.name,
4079.                    v_sql);
4080.         n_line_no := n_line_no + 1;
4081.       
4082.         v_sql := '  ';
4083.         insert_rec(n_line_no,
4084.                    'SEPAR_PKG_BODYS',
4085.                    v_username,
4086.                    rec_ProFunPkg_name.name,
4087.                    v_sql);
4088.         n_line_no := n_line_no + 1;
4089.       
4090.         v_sql := '  ';
4091.         insert_rec(n_line_no,
4092.                    'SEPAR_PKG_HEADS',
4093.                    v_username,
4094.                    rec_ProFunPkg_name.name,
4095.                    v_sql);
4096.         n_line_no := n_line_no + 1;
4097.       
4098.         -- the package head and package body allow in the same file
4099.         v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4100.                  'separ_files' || v_notion ||
4101.                  lower(rec_ProFunPkg_name.name) || '.sql;';
4102.         insert_rec(n_line_no,
4103.                    'SEPAR_PKGS',
4104.                    v_username,
4105.                    rec_ProFunPkg_name.name,
4106.                    v_sql);
4107.         n_line_no := n_line_no + 1;
4108.       
4109.         v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4110.         insert_rec(n_line_no,
4111.                    'SEPAR_PKGS',
4112.                    v_username,
4113.                    rec_ProFunPkg_name.name,
4114.                    v_sql);
4115.         n_line_no := n_line_no + 1;
4116.       
4117.         v_sql := 'WHERE OBJECT_TYPE like ''%PACKAGE%'' AND OBJECT_NAME = ''' ||
4118.                  rec_ProFunPkg_name.name || ''' ORDER BY line_no ; ';
4119.         insert_rec(n_line_no,
4120.                    'SEPAR_PKGS',
4121.                    v_username,
4122.                    rec_ProFunPkg_name.name,
4123.                    v_sql);
4124.         n_line_no := n_line_no + 1;
4125.       
4126.         v_sql := 'spool off;';
4127.         insert_rec(n_line_no,
4128.                    'SEPAR_PKGS',
4129.                    v_username,
4130.                    rec_ProFunPkg_name.name,
4131.                    v_sql);
4132.         n_line_no := n_line_no + 1;
4133.       
4134.         v_sql := '  ';
4135.         insert_rec(n_line_no,
4136.                    'SEPAR_PKGS',
4137.                    v_username,
4138.                    rec_ProFunPkg_name.name,
4139.                    v_sql);
4140.         n_line_no := n_line_no + 1;
4141.       END IF;
4142.     
4143.     END LOOP;
4144.   
4145.     -- 生成批量运行生成各个文件的批文件
4146.     v_sql := 'spool ' || v_file_path || 'procedures' || v_notion ||
4147.              'separ_files' || v_notion || 'start_procs.sql';
4148.     insert_rec(n_line_no, 'SEPAR_PROCS', v_username, 'SEPAR_PROCS', v_sql);
4149.     n_line_no := n_line_no + 1;
4150.   
4151.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4152.     insert_rec(n_line_no, 'SEPAR_PROCS', v_username, 'SEPAR_PROCS', v_sql);
4153.     n_line_no := n_line_no + 1;
4154.   
4155.     v_sql := 'WHERE OBJECT_TYPE = ''PROCEDURE'' AND OBJECT_NAME = ''START_PROCS'' ORDER BY line_no ; ';
4156.     insert_rec(n_line_no, 'SEPAR_PROCS', v_username, 'SEPAR_PROCS', v_sql);
4157.     n_line_no := n_line_no + 1;
4158.   
4159.     v_sql := 'spool off';
4160.     insert_rec(n_line_no, 'SEPAR_PROCS', v_username, 'SEPAR_PROCS', v_sql);
4161.     n_line_no := n_line_no + 1;
4162.   
4163.     v_sql := '  ';
4164.     insert_rec(n_line_no, 'SEPAR_PROCS', v_username, 'SEPAR_PROCS', v_sql);
4165.     n_line_no := n_line_no + 1;
4166.   
4167.     v_sql := 'spool ' || v_file_path || 'functions' || v_notion ||
4168.              'separ_files' || v_notion || 'start_funcs.sql';
4169.     insert_rec(n_line_no, 'SEPAR_FUNCS', v_username, 'SEPAR_FUNCS', v_sql);
4170.     n_line_no := n_line_no + 1;
4171.   
4172.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4173.     insert_rec(n_line_no, 'SEPAR_FUNCS', v_username, 'SEPAR_FUNCS', v_sql);
4174.     n_line_no := n_line_no + 1;
4175.   
4176.     v_sql := 'WHERE OBJECT_TYPE = ''FUNCTION'' AND OBJECT_NAME = ''START_FUNCS'' ORDER BY line_no ; ';
4177.     insert_rec(n_line_no, 'SEPAR_FUNCS', v_username, 'SEPAR_FUNCS', v_sql);
4178.     n_line_no := n_line_no + 1;
4179.   
4180.     v_sql := 'spool off';
4181.     insert_rec(n_line_no, 'SEPAR_FUNCS', v_username, 'SEPAR_FUNCS', v_sql);
4182.     n_line_no := n_line_no + 1;
4183.   
4184.     v_sql := '  ';
4185.     insert_rec(n_line_no, 'SEPAR_FUNCS', v_username, 'SEPAR_FUNCS', v_sql);
4186.     n_line_no := n_line_no + 1;
4187.   
4188.     v_sql := 'spool ' || v_file_path || 'triggers' || v_notion ||
4189.              'separ_files' || v_notion || 'start_tris.sql';
4190.     insert_rec(n_line_no, 'SEPAR_TRIS', v_username, 'SEPAR_TRIS', v_sql);
4191.     n_line_no := n_line_no + 1;
4192.   
4193.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4194.     insert_rec(n_line_no, 'SEPAR_TRIS', v_username, 'SEPAR_TRIS', v_sql);
4195.     n_line_no := n_line_no + 1;
4196.   
4197.     v_sql := 'WHERE OBJECT_TYPE = ''TRIGGER'' AND OBJECT_NAME = ''SEPAR_TRIS'' ORDER BY line_no ; ';
4198.     insert_rec(n_line_no, 'SEPAR_TRIS', v_username, 'SEPAR_TRIS', v_sql);
4199.     n_line_no := n_line_no + 1;
4200.   
4201.     v_sql := 'spool off';
4202.     insert_rec(n_line_no, 'SEPAR_TRIS', v_username, 'SEPAR_TRIS', v_sql);
4203.     n_line_no := n_line_no + 1;
4204.   
4205.     v_sql := '  ';
4206.     insert_rec(n_line_no, 'SEPAR_TRIS', v_username, 'SEPAR_TRIS', v_sql);
4207.     n_line_no := n_line_no + 1;
4208.   
4209.     v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4210.              'separ_headbobdy_file' || v_notion || 'start_pkg_heads.sql;';
4211.     insert_rec(n_line_no,
4212.                'SEPAR_PKG_HEADS',
4213.                v_username,
4214.                'SEPAR_PKG_HEADS',
4215.                v_sql);
4216.     n_line_no := n_line_no + 1;
4217.   
4218.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4219.     insert_rec(n_line_no,
4220.                'SEPAR_PKG_HEADS',
4221.                v_username,
4222.                'SEPAR_PKG_HEADS',
4223.                v_sql);
4224.     n_line_no := n_line_no + 1;
4225.   
4226.     v_sql := 'WHERE OBJECT_TYPE = ''PACKAGE'' AND OBJECT_NAME = ''START_PKG_HS'' ORDER BY line_no ; ';
4227.     insert_rec(n_line_no,
4228.                'SEPAR_PKG_HEADS',
4229.                v_username,
4230.                'SEPAR_PKG_HEADS',
4231.                v_sql);
4232.     n_line_no := n_line_no + 1;
4233.   
4234.     v_sql := 'spool off';
4235.     insert_rec(n_line_no,
4236.                'SEPAR_PKG_HEADS',
4237.                v_username,
4238.                'SEPAR_PKG_HEADS',
4239.                v_sql);
4240.     n_line_no := n_line_no + 1;
4241.   
4242.     v_sql := '  ';
4243.     insert_rec(n_line_no,
4244.                'SEPAR_PKG_HEADS',
4245.                v_username,
4246.                'SEPAR_PKG_HEADS',
4247.                v_sql);
4248.     n_line_no := n_line_no + 1;
4249.   
4250.     v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4251.              'separ_headbobdy_file' || v_notion || 'start_pkg_bodys.sql;';
4252.     insert_rec(n_line_no,
4253.                'SEPAR_PKG_BODYS',
4254.                v_username,
4255.                'SEPAR_PKG_BODYS',
4256.                v_sql);
4257.     n_line_no := n_line_no + 1;
4258.   
4259.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4260.     insert_rec(n_line_no,
4261.                'SEPAR_PKG_BODYS',
4262.                v_username,
4263.                'SEPAR_PKG_BODYS',
4264.                v_sql);
4265.     n_line_no := n_line_no + 1;
4266.   
4267.     v_sql := 'WHERE OBJECT_TYPE = ''PACKAGE'' AND OBJECT_NAME = ''START_PKG_BS'' ORDER BY line_no ; ';
4268.     insert_rec(n_line_no,
4269.                'SEPAR_PKG_BODYS',
4270.                v_username,
4271.                'SEPAR_PKG_BODYS',
4272.                v_sql);
4273.     n_line_no := n_line_no + 1;
4274.   
4275.     v_sql := 'spool off';
4276.     insert_rec(n_line_no,
4277.                'SEPAR_PKG_BODYS',
4278.                v_username,
4279.                'SEPAR_PKG_BODYS',
4280.                v_sql);
4281.     n_line_no := n_line_no + 1;
4282.   
4283.     v_sql := '  ';
4284.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4285.     n_line_no := n_line_no + 1;
4286.   
4287.     v_sql := 'spool ' || v_file_path || 'packages' || v_notion ||
4288.              'separ_files' || v_notion || 'start_pkgs.sql;';
4289.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4290.     n_line_no := n_line_no + 1;
4291.   
4292.     v_sql := 'SELECT rtrim(sql_text) FROM temp_for_pkg_gen_sql4000_temp ';
4293.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4294.     n_line_no := n_line_no + 1;
4295.   
4296.     v_sql := 'WHERE OBJECT_TYPE = ''PACKAGE'' AND OBJECT_NAME = ''START_PKGS'' ORDER BY line_no ; ';
4297.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4298.     n_line_no := n_line_no + 1;
4299.   
4300.     v_sql := 'spool off';
4301.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4302.     n_line_no := n_line_no + 1;
4303.   
4304.     v_sql := '  ';
4305.     insert_rec(n_line_no, 'SEPAR_PKGS', v_username, 'SEPAR_PKGS', v_sql);
4306.     n_line_no := n_line_no + 1;
4307.     COMMIT;
4308.   
4309.   END;
4311. END pkg_gen_user_sql;
4313. /
4315. show err
4319. set echo off
4320. set feedback off
4321. set newpage none
4322. set pagesize 0
4323. set verify off
4324. set pagesize 0
4325. set term off
4326. set trims on
4327. set linesize 5005
4328. set heading  off 
4329. set timing off
4330. set verify off
4331. set numwidth 38
4333. -- 生成start
4334. exec pkg_gen_user_sql.pro_gen_start_sql('&&file_dic')
4335. spool &&file_dic/start_all.sql  
4336. SELECT rtrim(sql_text) 
4337. FROM temp_for_pkg_gen_sql2000_temp
4338. WHERE OBJECT_TYPE = 'START_SQL'
4339. ORDER BY line_no ;  
4340. spool off  
4342. -- 生成创建表的sql语句
4343. exec pkg_gen_user_sql.pro_tab_sql_tab8('F','T','F','T');
4344. spool &&file_dic/cre_tables.sql  
4345. SELECT rtrim(sql_text) 
4346. FROM temp_for_pkg_gen_sql2000_temp
4347. WHERE OBJECT_TYPE = 'TABLE_8'
4348. ORDER BY line_no ;  
4349. spool off  
4351. -- 生成创建索引的sql语句
4352. exec pkg_gen_user_sql.pro_indx_sql('F','T','F','T')
4353. spool &&file_dic/cre_indexes.sql
4354. SELECT   sql_text
4355. FROM temp_for_pkg_gen_sql2000_temp
4356. WHERE OBJECT_TYPE = 'INDEX'
4357. ORDER BY line_no;
4358. spool off
4360. -- 生成创建主键约束的sql语句
4361. exec pkg_gen_user_sql.pro_con_sql('P','F','T','T','T');  
4362. spool &&file_dic/cre_pri_constraint.sql  
4363. SELECT rtrim(sql_text) 
4364. FROM temp_for_pkg_gen_sql2000_temp
4365. WHERE OBJECT_TYPE = 'CONSTRAINT'
4366. ORDER BY line_no;  
4367. spool off
4369. -- 生成创建出主键约束的其它约束的sql语句
4370. exec pkg_gen_user_sql.pro_con_sql('NOT_PRI','F','T','F','T');  
4371. spool &&file_dic/cre_not_pri_constraint.sql  
4372. SELECT rtrim(sql_text) 
4373. FROM temp_for_pkg_gen_sql2000_temp
4374. WHERE OBJECT_TYPE = 'CONSTRAINT'
4375. ORDER BY line_no;  
4376. spool off
4378. -- 生成创建所有约束的sql语句
4379. exec pkg_gen_user_sql.pro_con_sql('A','F','T','T','T');  
4380. spool &&file_dic/cre_constraints.sql  
4381. SELECT rtrim(sql_text) 
4382. FROM temp_for_pkg_gen_sql2000_temp
4383. WHERE OBJECT_TYPE = 'CONSTRAINT'
4384. ORDER BY line_no;  
4385. spool off
4387. -- 生成重建索引的sql语句
4388. exec pkg_gen_user_sql.pro_reb_indx_sql('F','F')
4389. spool &&file_dic/reb_indexes.sql
4390. SELECT   sql_text
4391. FROM temp_for_pkg_gen_sql2000_temp
4392. WHERE OBJECT_TYPE = 'REBUILD_INDEX'
4393. ORDER BY line_no;
4394. spool off
4397. -- 生成创建视图的sql语句
4398. exec pkg_gen_user_sql.pro_view_sql('F','T','T');
4399. spool &&file_dic/cre_views.sql
4400. SELECT   sql_text
4401. FROM temp_for_pkg_gen_sql4000_temp
4402. WHERE OBJECT_TYPE = 'VIEW'
4403. ORDER BY line_no;
4404. spool off
4407. -- 生成创建序列的sql语句
4408. exec pkg_gen_user_sql.pro_seq_next_val_sql('F');
4409. spool &&file_dic/cre_sequences.sql
4410. SELECT   sql_text
4411. FROM temp_for_pkg_gen_sql2000_temp
4412. WHERE OBJECT_TYPE = 'SEQUENCE'
4413. ORDER BY line_no;
4414. spool off
4416. -- 生成创建初始序列的sql语句, 每个序列的开始值为1
4417. exec pkg_gen_user_sql.pro_seq_init_val_sql('F');
4418. spool &&file_dic/cre_sequences_init.sql
4419. SELECT   sql_text
4420. FROM temp_for_pkg_gen_sql2000_temp
4421. WHERE OBJECT_TYPE = 'SEQUENCE_INIT'
4422. ORDER BY line_no;
4423. spool off
4426. -- 生成创建同义词的sql语句
4427. exec pkg_gen_user_sql.pro_synonym_sql('F')
4428. spool &&file_dic/cre_synonyms.sql  
4429. SELECT rtrim(sql_text) 
4430. FROM temp_for_pkg_gen_sql2000_temp
4431. WHERE OBJECT_TYPE = 'SYNONYM'
4432. ORDER BY line_no ;  
4433. spool off  
4436. -- 生成创建job的sql语句
4437. exec pkg_gen_user_sql.pro_job_sql('F')
4438. spool &&file_dic/cre_jobs.sql  
4439. SELECT rtrim(sql_text) 
4440. FROM temp_for_pkg_gen_sql4000_temp
4441. WHERE OBJECT_TYPE = 'JOB'
4442. ORDER BY line_no ;  
4443. spool off  
4446. -- 生成创建数据库链的sql语句
4447. exec pkg_gen_user_sql.pro_dl_sql('F')
4448. spool &&file_dic/cre_db_links.sql  
4449. SELECT rtrim(sql_text) 
4450. FROM temp_for_pkg_gen_sql2000_temp
4451. WHERE OBJECT_TYPE = 'DL'
4452. ORDER BY line_no ;  
4453. spool off  
4456. -- 生成创建角色的sql语句
4457. exec pkg_gen_user_sql.pro_role_sql('F')
4458. spool &&file_dic/cre_roles.sql  
4459. SELECT rtrim(sql_text) 
4460. FROM temp_for_pkg_gen_sql2000_temp
4461. WHERE OBJECT_TYPE = 'ROLE'
4462. ORDER BY line_no ;  
4463. spool off 
4465. -- 生成创建授权的sql语句
4466. exec pkg_gen_user_sql.pro_priv_sql('F')
4467. spool &&file_dic/cre_privs.sql  
4468. SELECT rtrim(sql_text) 
4469. FROM temp_for_pkg_gen_sql2000_temp
4470. WHERE OBJECT_TYPE = 'PRIV'
4471. ORDER BY line_no ;  
4472. spool off 
4475. -- 生成创建存储过程的sql语句，所有存储过程放在一个文件中
4476. exec pkg_gen_user_sql.pro_pkgprofuntri_sql('F','T','T','T','&&file_dic')
4478. spool &&file_dic/procedures/procedures.sql  
4479. SELECT rtrim(sql_text) 
4480. FROM temp_for_pkg_gen_sql4000_temp
4481. WHERE OBJECT_TYPE = 'PROCEDURE'
4482.     AND lower(rtrim(sql_text)) not like '@%.sql'
4483. ORDER BY line_no ;  
4484. spool off 
4487. -- 生成创建函数的sql语句，所有函数放在一个文件中
4488. spool &&file_dic/functions/functions.sql
4489. SELECT rtrim(sql_text) 
4490. FROM temp_for_pkg_gen_sql4000_temp
4491. WHERE OBJECT_TYPE = 'FUNCTION'
4492.      AND lower(rtrim(sql_text)) not like '@%.sql'
4493. ORDER BY line_no ;  
4494. spool off 
4497. -- 生成创建包的sql语句，所有包放在一个文件中
4498. spool &&file_dic/packages/packages.sql
4499. SELECT rtrim(sql_text) 
4500. FROM temp_for_pkg_gen_sql4000_temp
4501. WHERE OBJECT_TYPE like '%PACKAGE%'
4502.      AND lower(rtrim(sql_text)) not like '@%.sql'
4503. ORDER BY line_no ;
4504. spool off
4507. -- 生成创建触发器的sql语句
4508. spool &&file_dic/triggers/triggers.sql
4509. SELECT rtrim(sql_text) 
4510. FROM temp_for_pkg_gen_sql4000_temp
4511. WHERE OBJECT_TYPE = 'TRIGGER'
4512. ORDER BY line_no ;  
4513. spool off 
4517. -- 生成创建存储过程的sql语句，每个存储过程放在单独的文件中
4518. spool &&file_dic/temp_sql/separ_pros.sql
4519. SELECT rtrim(sql_text) 
4520. FROM temp_for_pkg_gen_sql4000_temp
4521. WHERE OBJECT_TYPE = 'SEPAR_PROCS'
4522. ORDER BY line_no ;  
4523. spool off 
4524. @ &&file_dic/temp_sql/separ_pros.sql 
4527. -- 生成创建函数的sql语句，每个函数放在单独的文件中
4528. spool &&file_dic/temp_sql/separ_funcs.sql
4529. SELECT rtrim(sql_text) 
4530. FROM temp_for_pkg_gen_sql4000_temp
4531. WHERE OBJECT_TYPE = 'SEPAR_FUNCS'
4532. ORDER BY line_no ;  
4533. spool off 
4534. @ &&file_dic/temp_sql/separ_funcs.sql  
4537. -- 生成创建包的sql语句，每个包放在单独的文件中
4538. spool &&file_dic/temp_sql/separ_pkgs.sql
4539. SELECT rtrim(sql_text)
4540. FROM temp_for_pkg_gen_sql4000_temp
4541. WHERE OBJECT_TYPE like '%SEPAR_PKGS%'
4542. ORDER BY line_no ;  
4543. spool off 
4544. @ &&file_dic/temp_sql/separ_pkgs.sql
4547. -- 生成创建存储过程的sql语句，每个包的声明与包体各放在单独的文件中
4548. spool &&file_dic/temp_sql/separ_hb_pkgs.sql
4549. SELECT rtrim(sql_text)
4550. FROM temp_for_pkg_gen_sql4000_temp
4551. WHERE OBJECT_TYPE in ('SEPAR_PKG_HEADS','SEPAR_PKG_BODYS')
4552. ORDER BY object_name,OBJECT_TYPE desc, line_no ;  
4553. spool off 
4554. @ &&file_dic/temp_sql/separ_hb_pkgs.sql
4556. -- 生成创建触发器的sql语句，每个触发器放在单独的文件中
4557. spool &&file_dic/temp_sql/separ_tris.sql
4558. SELECT rtrim(sql_text) 
4559. FROM temp_for_pkg_gen_sql4000_temp
4560. WHERE OBJECT_TYPE = 'SEPAR_TRIS'
4561. ORDER BY line_no ;  
4562. spool off 
4563. @ &&file_dic/temp_sql/separ_tris.sql  
4566. set echo on
4567. set feedback on
4568. set newpage 1
4569. set pagesize 500
4570. set linesize 80
4571. set verify on
4572. set term on
4573. set trims on
4574. set linesize 600
4575. set heading  on 
4576. set timing off
4577. set verify on
4578. set numwidth 15
4580. drop table temp_for_pkg_gen_sql2000_temp; 
4581. drop table temp_for_pkg_gen_sql4000_temp;
4582. drop package pkg_gen_user_sql;

来自： http://www.oracle.com.cn/viewthread.php?tid=69847&extra=page%3D1%26amp%3Bfilter%3Ddigest