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

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

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

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

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

版权所有人： 徐玉金

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


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

功能：导出一个用户的数据结构，包括表、视图、索引、约束、存储过程、触发器、函数、序列等等的定义，并且每个定义为一个文件
导出的各个对象的定义格式比较规范。
如表的定义为：
promptCreate 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. DEFINEfile_dic=1
4. CREATETABLEtemp_for_pkg_gen_sql4000_temp
5. (line_noNUMBER(38,0),
6. object_typeVARCHAR2(30),
7. user_nameVARCHAR2(30),
8. object_nameVARCHAR2(200),
9. sql_textVARCHAR2(4000)
10. );
13. --synonym,job,db_link,role,user_type
14. CREATETABLEtemp_for_pkg_gen_sql2000_temp
15. (line_noNUMBER(38,0),
16. object_typeVARCHAR2(30),
17. user_nameVARCHAR2(30),
18. object_nameVARCHAR2(200),
19. sql_textVARCHAR2(2000)
20. );
22. DELETEtemp_for_pkg_gen_sql4000_temp;
23. DELETEtemp_for_pkg_gen_sql2000_temp;
24. COMMIT;
26. CREATEORREPLACEPACKAGEpkg_gen_user_sqlAS
27. --产生start脚本
28. PROCEDUREpro_gen_start_sql(p_v_file_pathVARCHAR2default'C:/');
30. FUNCTIONfun_return_table_name_tab4(p_v_table_nameVARCHAR2,
31. p_n_col_max_lenNUMBER)RETURNVARCHAR2;
33. FUNCTIONfun_return_table_name_tab8(p_v_table_nameVARCHAR2,
34. p_n_col_max_lenNUMBER)RETURNVARCHAR2;
36. --generatesqlforcreatetables;
37. PROCEDUREpro_tab_sql_tab4(p_v_gen_drop_sqlVARCHAR2default'T',
38. p_v_gen_create_sqlVARCHAR2default'T',
39. p_v_gen_store_clauseVARCHAR2default'T',
40. p_v_display_prompt_infoVARCHAR2default'T');
42. PROCEDUREpro_tab_sql_tab8(p_v_gen_drop_sqlVARCHAR2default'T',
43. p_v_gen_create_sqlVARCHAR2default'T',
44. p_v_gen_store_clauseVARCHAR2default'T',
45. p_v_display_prompt_infoVARCHAR2default'T');
47. PROCEDUREpro_con_sql(p_v_con_typeVARCHAR2default'ALL',
48. p_v_gen_drop_sqlVARCHAR2default'T',
49. p_v_gen_create_sqlVARCHAR2default'T',
50. p_v_gen_null_chkVARCHAR2default'T',
51. p_v_display_prompt_infoVARCHAR2default'T');
53. PROCEDUREpro_indx_sql(p_v_gen_drop_sqlVARCHAR2default'T',
54. p_v_gen_create_sqlVARCHAR2default'T',
55. p_v_gen_store_clauseVARCHAR2default'T',
56. p_v_gen_ts_onlyVARCHAR2default'F',
57. p_v_display_prompt_infoVARCHAR2default'T');
59. --重建索引
60. PROCEDUREpro_reb_indx_sql(p_v_gen_store_clauseVARCHAR2default'T',
61. p_v_rebuild_onlineVARCHAR2default'T',
62. p_v_display_prompt_infoVARCHAR2default'T');
64. PROCEDUREpro_view_sql(p_v_gen_drop_sqlVARCHAR2default'T',
65. p_v_gen_create_sqlVARCHAR2default'T',
66. p_v_display_prompt_infoVARCHAR2default'T');
68. PROCEDUREpro_seq_max_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
69. p_v_gen_create_sqlVARCHAR2default'T',
70. p_v_display_prompt_infoVARCHAR2default'T');
72. PROCEDUREpro_seq_next_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
73. p_v_gen_create_sqlVARCHAR2default'T',
74. p_v_display_prompt_infoVARCHAR2default'T');
76. PROCEDUREpro_seq_init_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
77. p_v_gen_create_sqlVARCHAR2default'T',
78. p_v_display_prompt_infoVARCHAR2default'T');
80. PROCEDUREpro_synonym_sql(p_v_gen_drop_sqlVARCHAR2default'T',
81. p_v_gen_create_sqlVARCHAR2default'T',
82. p_v_display_prompt_infoVARCHAR2default'T');
84. PROCEDUREpro_job_sql(p_v_gen_drop_sqlVARCHAR2default'T',
85. p_v_gen_create_sqlVARCHAR2default'T',
86. p_v_display_prompt_infoVARCHAR2default'T');
88. PROCEDUREpro_dl_sql(p_v_gen_drop_sqlVARCHAR2default'T',
89. p_v_gen_create_sqlVARCHAR2default'T',
90. p_v_display_prompt_infoVARCHAR2default'T');
92. PROCEDUREpro_role_sql(p_v_gen_drop_sqlVARCHAR2default'T',
93. p_v_gen_create_sqlVARCHAR2default'T',
94. p_v_display_prompt_infoVARCHAR2default'T');
96. PROCEDUREpro_priv_sql(p_v_gen_drop_sqlVARCHAR2default'T',
97. p_v_gen_create_sqlVARCHAR2default'T',
98. p_v_display_prompt_infoVARCHAR2default'T');
100. PROCEDUREpro_pkgprofuntri_sql(p_v_gen_drop_sqlVARCHAR2default'T',
101. p_v_gen_create_sqlVARCHAR2default'T',
102. p_v_display_prompt_infoVARCHAR2default'T',
103. p_v_separ_fileVARCHAR2default'T',
104. p_v_file_pathVARCHAR2default'C:/');
106. ENDpkg_gen_user_sql;
108. /
110. showerr
113. showerr
115. CREATEORREPLACEPACKAGEBODYpkg_gen_user_sqlAS
117. PROCEDUREpro_gen_start_sql(p_v_file_pathVARCHAR2default'C:/')AS
118. v_usernameVARCHAR2(30);
119. v_object_typeVARCHAR2(30);
120. n_line_noNUMBER(10,0);
121. v_sqlVARCHAR2(2000);
122. v_promptVARCHAR2(30);
123. v_file_pathVARCHAR2(600);
124. v_notionchar(1);
125. PROCEDUREinsert_rec(p_line_noINTEGER,
126. p_object_typeVARCHAR2,
127. p_user_nameVARCHAR2,
128. p_view_nameVARCHAR2,
129. p_stringVARCHAR2)IS
130. BEGIN
131. INSERTINTOtemp_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;
141. --findthenotionbetweenthedictionary
142. IFinstr(p_v_file_path,'/')>0THEN
143. v_notion:='/';
144. ELSE
145. v_notion:='/';
146. ENDIF;
148. IFsubstr(v_file_path,length(v_file_path))<>v_notionTHEN
149. v_file_path:=v_file_path||v_notion;
150. ENDIF;
152. SELECTusernameINTOv_usernameFROMuser_users;
154. DELETEtemp_for_pkg_gen_sql2000_tempWHEREobject_type=v_object_type;
155. COMMIT;
157. n_line_no:=1;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
199. v_sql:='--*****importdata*****--';
200. insert_rec(n_line_no,v_object_type,v_username,v_object_type,v_sql);
201. n_line_no:=n_line_no+1;
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;
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;
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;
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;
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;
223. COMMIT;
224. END;
226. FUNCTIONfun_return_table_name_tab4(p_v_table_nameVARCHAR2,
227. p_n_col_max_lenNUMBER)RETURNVARCHAR2AS
228. v_table_nameVARCHAR2(100);
229. v_tempVARCHAR2(100);
230. n_col_max_lenNUMBER;
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;
236. WHILEmod(length(v_temp),4)<>0LOOP
237. v_temp:=v_temp||'';
238. ENDLOOP;
239. IFmod(length(v_table_name),4)<>0THEN
240. v_table_name:=v_table_name||chr(9);
241. ENDIF;
243. WHILElength(v_temp)<n_col_max_lenLOOP
244. v_table_name:=v_table_name||chr(9);
245. v_temp:=v_temp||'';
246. ENDLOOP;
248. RETURNv_table_name;
249. END;
251. FUNCTIONfun_return_table_name_tab8(p_v_table_nameVARCHAR2,
252. p_n_col_max_lenNUMBER)RETURNVARCHAR2AS
253. v_table_nameVARCHAR2(100);
254. v_tempVARCHAR2(100);
255. n_col_max_lenNUMBER;
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;
261. WHILEmod(length(v_temp),8)<>0LOOP
262. v_temp:=v_temp||'';
263. ENDLOOP;
264. IFmod(length(v_table_name),8)<>0THEN
265. v_table_name:=v_table_name||chr(9);
266. ENDIF;
268. WHILElength(v_temp)<n_col_max_lenLOOP
269. v_table_name:=v_table_name||chr(9);
270. v_temp:=v_temp||'';
271. ENDLOOP;
273. RETURNv_table_name;
274. END;
276. PROCEDUREpro_tab_sql_tab4(p_v_gen_drop_sqlVARCHAR2default'T',
277. p_v_gen_create_sqlVARCHAR2default'T',
278. p_v_gen_store_clauseVARCHAR2default'T',
279. p_v_display_prompt_infoVARCHAR2default'T')AS
280. CURSORcur_tablesis
281. SELECTtable_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. FROMuser_tables
297. WHERElower(TABLE_NAME)NOTIN
298. ('temp_for_pkg_gen_sql2000_temp',
299. 'temp_for_pkg_gen_sql4000_temp')
300. ORDERBYtable_name;
302. CURSORcur_cols(t_nameVARCHAR2)is
303. SELECTtable_name,
304. column_name,
305. data_type,
306. data_length,
307. data_precision,
308. data_scale,
309. nullable,
310. data_default
311. FROMuser_tab_columns
312. WHEREtable_name=t_name
313. ORDERBYcolumn_id;
315. v_tab_nameVARCHAR2(40);
316. v_tabsp_nameVARCHAR2(40);
317. n_mpct_freeuser_tables.pct_free%TYPE;
318. n_mpct_useduser_tables.pct_used%TYPE;
319. n_mini_transuser_tables.ini_trans%TYPE;
320. n_mmax_transuser_tables.max_trans%TYPE;
321. n_mini_extuser_tables.initial_extent%TYPE;
322. n_mnext_extuser_tables.next_extent%TYPE;
323. n_mmin_extuser_tables.min_extents%TYPE;
324. n_mmax_extuser_tables.max_extents%TYPE;
325. n_mpct_incuser_tables.pct_increase%TYPE;
326. n_freelistsuser_tables.freelists%TYPE;
327. n_freelist_groupsuser_tables.freelist_groups%TYPE;
328. v_buffer_pooluser_tables.buffer_pool%TYPE;
329. v_loggingVARCHAR2(15);
330. col_nameVARCHAR2(40);
331. ctNUMBER:=0;
332. n_line_noNUMBER:=0;
333. col_contentVARCHAR2(120);
334. n_col_max_lenNUMBER(10,0);
335. v_default_valVARCHAR2(4000);
336. v_gen_drop_tab_sqlVARCHAR2(10);
337. v_gen_create_tab_sqlVARCHAR2(10);
338. v_gen_store_clauseVARCHAR2(10);
339. v_display_prompt_infoVARCHAR2(10);
340. v_usernameVARCHAR2(30);
341. v_sqlVARCHAR2(2000);
342. n_positionNUMBER(6,0);
343. v_object_typeVARCHAR2(10);
344. v_promptVARCHAR2(10);
345. PROCEDUREinsert_rec(p_line_noINTEGER,
346. p_object_typeVARCHAR2,
347. p_user_nameVARCHAR2,
348. p_object_nameVARCHAR2,
349. p_stringVARCHAR2)IS
350. BEGIN
351. INSERTINTOtemp_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;
365. SELECTusernameINTOv_usernameFROMuser_users;
367. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
368. COMMIT;
370. FORcur_tables_recINcur_tablesLOOP
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;
387. IFv_gen_drop_tab_sql='T'THEN
388. IFupper(v_display_prompt_info)='T'THEN
389. v_sql:=v_prompt||'DropTable'||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. ENDIF;
398. v_sql:='ALTERTABLE'||v_tab_name||
399. 'dropprimarykeycascade;';
400. insert_rec(n_line_no,v_object_type,v_username,v_tab_name,v_sql);
401. n_line_no:=n_line_no+1;
403. v_sql:='DROPTABLE'||v_tab_name||'cascadeconstraint;';
404. insert_rec(n_line_no,v_object_type,v_username,v_tab_name,v_sql);
405. n_line_no:=n_line_no+1;
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. ENDIF;
412. IFupper(v_gen_create_tab_sql)='T'THEN
413. IFupper(v_display_prompt_info)='T'THEN
414. v_sql:=v_prompt||'CreateTable'||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. ENDIF;
423. v_sql:='CREATETABLE'||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;
427. --找到最长的列名的长度
428. n_col_max_len:=1;
429. FORcur_cols_recINcur_cols(v_tab_name)LOOP
430. IFn_col_max_len<length(trim(cur_cols_rec.column_name))THEN
431. n_col_max_len:=length(trim(cur_cols_rec.column_name));
432. ENDIF;
433. ENDLOOP;
435. ct:=0;
437. FORcur_cols_recINcur_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. WHILEn_position>0LOOP
443. IFn_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. ENDIF;
449. n_position:=instr(v_default_val,chr(10),-1);
450. ENDLOOP;
451. n_position:=instr(v_default_val,chr(9),-1);
452. IFn_position=length(v_default_val)THEN
453. v_default_val:=substr(v_default_val,1,n_position-1);
454. ENDIF;
455. IFct=1THEN
456. INSERTINTOtemp_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)||'NOTNULL,',
499. chr(9)||'default'||chr(9)||v_default_val||
500. chr(9)||chr(9)||'NOTNULL,')));
502. n_line_no:=n_line_no+1;
503. ELSE
504. INSERTINTOtemp_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)||'NOTNULL,',
547. chr(9)||'default'||chr(9)||v_default_val||
548. chr(9)||chr(9)||'NOTNULL,')));
549. n_line_no:=n_line_no+1;
550. ENDIF;
551. ENDLOOP;
553. SELECTrtrim(sql_text)
554. intocol_content
555. FROMtemp_for_pkg_gen_sql2000_temp
556. WHEREline_no=n_line_no-1
557. ANDobject_type=v_object_type;
558. col_content:=substr(col_content,1,length(col_content)-1);
560. UPDATEtemp_for_pkg_gen_sql2000_temp
561. SETsql_text=col_content
562. WHEREline_no=n_line_no-1
563. ANDobject_type=v_object_type;
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;
569. IFv_gen_store_clause='T'THEN
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;
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;
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;
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;
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;
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;
621. IFn_mnext_extISNOTNULLTHEN
622. UPDATEtemp_for_pkg_gen_sql2000_temp
623. SETsql_text=sql_text||chr(9)||'NEXT'||
624. to_char(n_mnext_ext)
625. WHEREline_no=n_line_no-1
626. ANDobject_type=v_object_type;
627. ENDIF;
629. UPDATEtemp_for_pkg_gen_sql2000_temp
630. SETsql_text=sql_text||chr(9)||'PCTINCREASE'||
631. to_char(n_mpct_inc)
632. WHEREline_no=n_line_no-1
633. ANDobject_type=v_object_type;
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;
643. UPDATEtemp_for_pkg_gen_sql2000_temp
644. SETsql_text=sql_text||chr(9)||'FREELISTGROUPS'||
645. to_char(n_freelist_groups)
646. WHEREline_no=n_line_no-1
647. ANDobject_type=v_object_type;
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;
657. UPDATEtemp_for_pkg_gen_sql2000_temp
658. SETsql_text=sql_text||');'
659. WHEREline_no=n_line_no-1
660. ANDobject_type=v_object_type;
661. ELSE
662. UPDATEtemp_for_pkg_gen_sql2000_temp
663. SETsql_text=sql_text||';'
664. WHEREline_no=n_line_no-1
665. ANDobject_type=v_object_type;
666. ENDIF;
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. ENDIF;
672. ENDLOOP;
673. COMMIT;
674. END;
676. PROCEDUREpro_tab_sql_tab8(p_v_gen_drop_sqlVARCHAR2default'T',
677. p_v_gen_create_sqlVARCHAR2default'T',
678. p_v_gen_store_clauseVARCHAR2default'T',
679. p_v_display_prompt_infoVARCHAR2default'T')AS
680. CURSORcur_tablesIS
681. SELECTtable_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. FROMuser_tables
697. WHERElower(TABLE_NAME)NOTIN
698. ('temp_for_pkg_gen_sql2000_temp',
699. 'temp_for_pkg_gen_sql4000_temp')
700. ORDERBYtable_name;
702. CURSORcur_cols(t_nameVARCHAR2)IS
703. SELECTtable_name,
704. column_name,
705. data_type,
706. data_length,
707. data_precision,
708. data_scale,
709. nullable,
710. data_default
711. FROMuser_tab_columns
712. WHEREtable_name=t_name
713. ORDERBYcolumn_id;
715. v_tab_nameVARCHAR2(40);
716. v_tabsp_nameVARCHAR2(40);
717. n_mpct_freeuser_tables.pct_free%TYPE;
718. n_mpct_useduser_tables.pct_used%TYPE;
719. n_mini_transuser_tables.ini_trans%TYPE;
720. n_mmax_transuser_tables.max_trans%TYPE;
721. n_mini_extuser_tables.initial_extent%TYPE;
722. n_mnext_extuser_tables.next_extent%TYPE;
723. n_mmin_extuser_tables.min_extents%TYPE;
724. n_mmax_extuser_tables.max_extents%TYPE;
725. n_mpct_incuser_tables.pct_increase%TYPE;
726. n_freelistsuser_tables.freelists%TYPE;
727. n_freelist_groupsuser_tables.freelist_groups%TYPE;
728. v_buffer_pooluser_tables.buffer_pool%TYPE;
729. v_loggingVARCHAR2(15);
730. col_nameVARCHAR2(40);
731. ctNUMBER:=0;
732. n_line_noNUMBER:=0;
733. col_contentVARCHAR2(120);
734. n_col_max_lenNUMBER(10,0);
735. v_default_valVARCHAR2(4000);
736. v_gen_drop_tab_sqlVARCHAR2(10);
737. v_gen_create_tab_sqlVARCHAR2(10);
738. v_gen_store_clauseVARCHAR2(10);
739. v_display_prompt_infoVARCHAR2(10);
740. v_usernameVARCHAR2(30);
741. v_sqlVARCHAR2(2000);
742. n_positionNUMBER(6,0);
743. v_object_typeVARCHAR2(10);
744. v_promptVARCHAR2(10);
745. PROCEDUREinsert_rec(p_line_noINTEGER,
746. p_object_typeVARCHAR2,
747. p_user_nameVARCHAR2,
748. p_object_nameVARCHAR2,
749. p_stringVARCHAR2)IS
750. BEGIN
751. INSERTINTOtemp_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;
765. SELECTusernameINTOv_usernameFROMuser_users;
767. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
768. COMMIT;
770. FORcur_tables_recINcur_tablesLOOP
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;
787. IFv_gen_drop_tab_sql='T'THEN
788. IFupper(v_display_prompt_info)='T'THEN
789. v_sql:=v_prompt||'DropTable'||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. ENDIF;
798. v_sql:='ALTERTABLE'||v_tab_name||
799. 'dropprimarykeycascade;';
800. insert_rec(n_line_no,v_object_type,v_username,v_tab_name,v_sql);
801. n_line_no:=n_line_no+1;
803. v_sql:='DROPTABLE'||v_tab_name||'cascadeconstraint;';
804. insert_rec(n_line_no,v_object_type,v_username,v_tab_name,v_sql);
805. n_line_no:=n_line_no+1;
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. ENDIF;
812. IFupper(v_gen_create_tab_sql)='T'THEN
813. IFupper(v_display_prompt_info)='T'THEN
814. v_sql:=v_prompt||'CreateTable'||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. ENDIF;
823. v_sql:='CREATETABLE'||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;
827. --找到最长的列名的长度
828. n_col_max_len:=1;
829. FORcur_cols_recINcur_cols(v_tab_name)LOOP
830. IFn_col_max_len<length(trim(cur_cols_rec.column_name))THEN
831. n_col_max_len:=length(trim(cur_cols_rec.column_name));
832. ENDIF;
833. ENDLOOP;
835. ct:=0;
837. FORcur_cols_recINcur_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. WHILEn_position>0LOOP
843. IFn_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. ENDIF;
849. n_position:=instr(v_default_val,chr(10),-1);
850. ENDLOOP;
851. n_position:=instr(v_default_val,chr(9),-1);
852. IFn_position=length(v_default_val)THEN
853. v_default_val:=substr(v_default_val,1,n_position-1);
854. ENDIF;
855. IFct=1THEN
856. INSERTINTOtemp_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)||'NOTNULL,',
899. chr(9)||'default'||chr(9)||v_default_val||
900. chr(9)||chr(9)||'NOTNULL,')));
902. n_line_no:=n_line_no+1;
903. ELSE
904. INSERTINTOtemp_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)||'NOTNULL,',
947. chr(9)||'default'||chr(9)||v_default_val||
948. chr(9)||chr(9)||'NOTNULL,')));
949. n_line_no:=n_line_no+1;
950. ENDIF;
951. ENDLOOP;
953. SELECTrtrim(sql_text)
954. intocol_content
955. FROMtemp_for_pkg_gen_sql2000_temp
956. WHEREline_no=n_line_no-1
957. ANDobject_type=v_object_type;
958. col_content:=substr(col_content,1,length(col_content)-1);
960. UPDATEtemp_for_pkg_gen_sql2000_temp
961. SETsql_text=col_content
962. WHEREline_no=n_line_no-1
963. ANDobject_type=v_object_type;
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;
969. IFv_gen_store_clause='T'THEN
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;
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;
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;
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;
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;
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;
1021. IFn_mnext_extISNOTNULLTHEN
1022. UPDATEtemp_for_pkg_gen_sql2000_temp
1023. SETsql_text=sql_text||chr(9)||'NEXT'||
1024. to_char(n_mnext_ext)
1025. WHEREline_no=n_line_no-1
1026. ANDobject_type=v_object_type;
1027. ENDIF;
1029. UPDATEtemp_for_pkg_gen_sql2000_temp
1030. SETsql_text=sql_text||chr(9)||'PCTINCREASE'||
1031. to_char(n_mpct_inc)
1032. WHEREline_no=n_line_no-1
1033. ANDobject_type=v_object_type;
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;
1043. UPDATEtemp_for_pkg_gen_sql2000_temp
1044. SETsql_text=sql_text||chr(9)||'FREELISTGROUPS'||
1045. to_char(n_freelist_groups)
1046. WHEREline_no=n_line_no-1
1047. ANDobject_type=v_object_type;
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;
1057. UPDATEtemp_for_pkg_gen_sql2000_temp
1058. SETsql_text=sql_text||');'
1059. WHEREline_no=n_line_no-1
1060. ANDobject_type=v_object_type;
1061. ELSE
1062. UPDATEtemp_for_pkg_gen_sql2000_temp
1063. SETsql_text=sql_text||';'
1064. WHEREline_no=n_line_no-1
1065. ANDobject_type=v_object_type;
1066. ENDIF;
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. ENDIF;
1072. ENDLOOP;
1073. COMMIT;
1074. END;
1076. PROCEDUREpro_con_sql(p_v_con_typeVARCHAR2default'ALL',
1077. p_v_gen_drop_sqlVARCHAR2default'T',
1078. p_v_gen_create_sqlVARCHAR2default'T',
1079. p_v_gen_null_chkVARCHAR2default'T',
1080. p_v_display_prompt_infoVARCHAR2default'T')AS
1081. CURSORcur_tablesis
1082. SELECTtable_nameFROMuser_tablesORDERBYtable_name;
1084. CURSORcur_con_cols(c_nameVARCHAR2)IS
1085. SELECTcolumn_name
1086. FROMuser_cons_columns
1087. WHEREconstraint_name=c_name
1088. ORDERBYposition;
1090. typeref_curisREFCURSOR;--定义一个ref类型的游标
1091. cur_consref_cur;--定义游标变量
1092. TYPEtype_conISRECORD(
1093. constraint_nameuser_constraints.constraint_name%type,
1094. r_constraint_nameuser_constraints.r_constraint_name%type,
1095. constraint_typeuser_constraints.constraint_type%type,
1096. search_conditionuser_constraints.search_condition%type);
1098. cur_cons_rectype_con;
1099. v_tab_nameVARCHAR2(40);
1100. v_table_nameVARCHAR2(40);
1101. v_typeVARCHAR2(1);
1102. n_ctNUMBER:=0;
1103. n_line_noNUMBER:=0;
1104. v_con_typeVARCHAR2(10);
1105. v_con_sqlVARCHAR2(500);
1106. v_sqlVARCHAR2(2000);
1107. v_gen_drop_con_sqlVARCHAR2(10);
1108. v_display_prompt_infoVARCHAR2(10);
1109. v_gen_create_con_sqlVARCHAR2(10);
1110. v_gen_null_chkVARCHAR2(10);
1111. v_usernameVARCHAR2(30);
1112. v_object_typeVARCHAR2(30);
1113. v_promptVARCHAR2(30);
1114. v_search_conditionVARCHAR2(1000);
1115. PROCEDUREinsert_rec(p_line_noINTEGER,
1116. p_object_typeVARCHAR2,
1117. p_user_nameVARCHAR2,
1118. p_con_nameVARCHAR2,
1119. p_stringVARCHAR2)IS
1120. BEGIN
1121. INSERTINTOtemp_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;
1133. v_object_type:='CONSTRAINT';
1134. v_prompt:='--prompt';
1135. n_line_no:=1;
1137. SELECTusernameINTOv_usernameFROMuser_users;
1139. DELETEFROMtemp_for_pkg_gen_sql2000_temp
1140. WHEREobject_type=v_object_type;
1142. COMMIT;
1144. FORcur_tables_recINcur_tablesLOOP
1145. v_tab_name:=trim(cur_tables_rec.table_name);
1147. --Gettheconstraintinfoforspecifiedtable
1148. v_con_sql:='SELECTconstraint_name,r_constraint_name,';
1149. v_con_sql:=v_con_sql||'constraint_type,search_condition';
1150. v_con_sql:=v_con_sql||'FROMuser_constraints';
1151. IFupper(v_con_type)<>'ALL'ANDupper(v_con_type)<>'A'AND
1152. upper(v_con_type)<>'NOT_PRI'THEN
1153. v_con_sql:=v_con_sql||'WHEREtable_name='''||v_tab_name||
1154. '''ANDconstraint_type='''||upper(v_con_type)||
1155. '''';
1156. ELSIFupper(v_con_type)='NOT_PRI'THEN
1157. v_con_sql:=v_con_sql||'WHEREtable_name='''||v_tab_name||
1158. '''ANDconstraint_type<>''P''';
1159. ELSE
1160. v_con_sql:=v_con_sql||'WHEREtable_name='''||v_tab_name||
1161. '''';
1162. ENDIF;
1163. v_con_sql:=v_con_sql||'ORDERBYconstraint_name';
1165. OPENcur_consFORv_con_sql;
1166. LOOP
1167. FETCHcur_cons
1168. INTOcur_cons_rec;
1169. EXITWHENcur_cons%NOTFOUND;
1171. v_type:=cur_cons_rec.constraint_type;
1172. v_search_condition:=cur_cons_rec.search_condition;
1174. IFupper(v_gen_drop_con_sql)='T'THEN
1176. IFv_gen_null_chk='F'ANDv_type='C'AND
1177. instr(v_search_condition,'ISNOTNULL')>0THEN
1178. --donull
1179. v_gen_null_chk:=v_gen_null_chk;
1180. ELSE
1181. IFupper(v_display_prompt_info)='T'THEN
1182. --insertpromptinfo
1183. INSERTINTOtemp_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||'Dropconstraints:'||v_tab_name||'.'||
1191. trim(cur_cons_rec.constraint_name)||';Type:'||
1192. decode(v_type,
1193. 'C',
1194. 'CHECK',
1195. 'P',
1196. 'PRIMARYKEY',
1197. 'U',
1198. 'UNIQUE',
1199. 'R',
1200. 'FROEIGNRELATION',
1201. v_type));
1202. n_line_no:=n_line_no+1;
1203. ENDIF;
1205. v_sql:='altertable'||v_tab_name||'dropconstraint'||
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;
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. ENDIF;
1222. ENDIF;
1224. IFupper(v_gen_create_con_sql)='T'THEN
1225. IFv_gen_null_chk='F'ANDv_type='C'AND
1226. instr(v_search_condition,'ISNOTNULL')>0THEN
1227. --donull
1228. v_gen_null_chk:=v_gen_null_chk;
1229. ELSE
1230. IFupper(v_display_prompt_info)='T'THEN
1231. --insertpromptinfo
1232. INSERTINTOtemp_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||'Addconstraints:'||v_tab_name||'.'||
1240. trim(cur_cons_rec.constraint_name)||';Type:'||
1241. decode(v_type,
1242. 'C',
1243. 'CHECK',
1244. 'P',
1245. 'PRIMARYKEY',
1246. 'U',
1247. 'UNIQUE',
1248. 'R',
1249. 'FROEIGNRELATION',
1250. v_type));
1251. n_line_no:=n_line_no+1;
1252. ENDIF;
1254. v_sql:='altertable'||v_tab_name||'addconstraint'||
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;
1263. IFv_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. ENDIF;
1274. n_ct:=0;
1276. FORcur_con_cols_recINcur_con_cols(cur_cons_rec.constraint_name)LOOP
1277. n_ct:=n_ct+1;
1279. IFn_ct=1THEN
1280. IFv_type='R'THEN
1281. v_sql:='foreignkey('||
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. ENDIF;
1291. IFv_type='P'THEN
1292. v_sql:='primarykey('||
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. ENDIF;
1302. IFv_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. ENDIF;
1313. ELSE
1314. UPDATEtemp_for_pkg_gen_sql2000_temp
1315. SETsql_text=rtrim(sql_text)||','||
1316. trim(cur_con_cols_rec.column_name)
1317. WHEREline_no=n_line_no-1
1318. ANDobject_type=v_object_type;
1319. ENDIF;
1321. ENDLOOP;
1323. UPDATEtemp_for_pkg_gen_sql2000_temp
1324. SETsql_text=rtrim(sql_text)||')'
1325. WHEREline_no=n_line_no-1
1326. ANDobject_type=v_object_type;
1328. IFv_type='R'THEN
1329. SELECTtable_name
1330. intov_table_name
1331. FROMuser_constraints
1332. WHEREconstraint_name=cur_cons_rec.r_constraint_name;
1334. n_ct:=0;
1336. FORcur_con_cols_recINcur_con_cols(cur_cons_rec.r_constraint_name)LOOP
1338. n_ct:=n_ct+1;
1340. IFn_ct=1THEN
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;
1349. UPDATEtemp_for_pkg_gen_sql2000_temp
1350. SETsql_text=rtrim(sql_text)||
1351. trim(cur_con_cols_rec.column_name)
1352. WHEREline_no=n_line_no-1
1353. ANDobject_type=v_object_type;
1354. ELSE
1355. UPDATEtemp_for_pkg_gen_sql2000_temp
1356. SETsql_text=rtrim(sql_text)||','||
1357. trim(cur_con_cols_rec.column_name)
1358. WHEREline_no=n_line_no-1
1359. ANDobject_type=v_object_type;
1360. ENDIF;
1361. ENDLOOP;
1363. UPDATEtemp_for_pkg_gen_sql2000_temp
1364. SETsql_text=rtrim(sql_text)||')'
1365. WHEREline_no=n_line_no-1
1366. ANDobject_type=v_object_type;
1367. ELSE
1368. IFv_type='P'orv_type='U'THEN
1369. UPDATEtemp_for_pkg_gen_sql2000_temp
1370. SETsql_text=rtrim(sql_text)||'USINGINDEX'
1371. WHEREline_no=n_line_no-1
1372. ANDobject_type=v_object_type;
1373. ENDIF;
1374. ENDIF;
1376. UPDATEtemp_for_pkg_gen_sql2000_temp
1377. SETsql_text=sql_text||';'
1378. WHEREline_no=n_line_no-1
1379. ANDobject_type=v_object_type;
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. ENDIF;
1389. ENDIF;
1390. ENDLOOP;
1391. CLOSEcur_cons;
1392. ENDLOOP;
1393. COMMIT;
1394. END;
1396. PROCEDUREpro_indx_sql(p_v_gen_drop_sqlVARCHAR2default'T',
1397. p_v_gen_create_sqlVARCHAR2default'T',
1398. p_v_gen_store_clauseVARCHAR2default'T',
1399. p_v_gen_ts_onlyVARCHAR2default'F',
1400. p_v_display_prompt_infoVARCHAR2default'T')AS
1401. CURSORcur_indxesIS
1402. SELECTindex_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. FROMuser_indexes
1421. WHEREindex_type<>'LOB'
1422. ORDERBYindex_name;
1424. CURSORcur_cols(c_indVARCHAR2,c_tabVARCHAR2)IS
1425. SELECTcolumn_name
1426. FROMuser_ind_columns
1427. WHEREindex_name=c_ind
1428. ANDtable_name=c_tab
1429. ORDERBYcolumn_position;
1430. CURSORcur_fun_expression(c_indVARCHAR2,c_tabVARCHAR2)IS
1431. SELECTcolumn_expression
1432. FROMuser_ind_expressions
1433. WHEREindex_name=c_ind
1434. andtable_name=c_tab;
1435. v_index_nameuser_indexes.index_name%TYPE;
1436. v_table_owneruser_indexes.table_owner%TYPE;
1437. v_table_nameuser_indexes.table_name%TYPE;
1438. v_index_typeuser_indexes.index_type%TYPE;
1439. v_uniquenessuser_indexes.uniqueness%TYPE;
1440. v_tablespace_nameuser_indexes.tablespace_name%TYPE;
1441. n_ini_transuser_indexes.ini_trans%TYPE;
1442. n_max_transuser_indexes.max_trans%TYPE;
1443. n_initial_extentuser_indexes.initial_extent%TYPE;
1444. n_next_extentuser_indexes.next_extent%TYPE;
1445. n_min_extentsuser_indexes.min_extents%TYPE;
1446. n_max_extentsuser_indexes.max_extents%TYPE;
1447. n_pct_increaseuser_indexes.pct_increase%TYPE;
1448. n_pct_freeuser_indexes.pct_free%TYPE;
1449. n_freelistsuser_indexes.freelists%TYPE;
1450. n_freelist_groupsuser_indexes.freelist_groups%TYPE;
1451. v_buffer_pooluser_indexes.buffer_pool%TYPE;
1452. v_loggingVARCHAR2(15);
1453. lv_column_nameuser_ind_columns.column_name%TYPE;
1454. b_first_recBOOLEAN;
1455. v_stringVARCHAR2(800);
1456. n_line_noNUMBER:=0;
1457. v_gen_drop_indx_sqlVARCHAR2(10);
1458. v_gen_create_indx_sqlVARCHAR2(10);
1459. v_gen_store_clauseVARCHAR2(10);
1460. v_display_prompt_infoVARCHAR2(10);
1461. v_gen_ts_onlyVARCHAR2(10);
1462. n_tempNUMBER(10,0);
1463. v_object_typeVARCHAR2(30);
1464. v_promptVARCHAR2(30);
1465. v_usernameVARCHAR2(30);
1466. procedureinsert_rec(p_line_noINTEGER,
1467. p_object_typeVARCHAR2,
1468. p_user_nameVARCHAR2,
1469. p_index_nameVARCHAR2,
1470. p_stringVARCHAR2)IS
1471. BEGIN
1472. INSERTINTOtemp_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';
1486. SELECTusernameINTOv_usernameFROMuser_users;
1488. DELETEFROMtemp_for_pkg_gen_sql2000_temp
1489. WHEREobject_type=v_object_type;
1490. COMMIT;
1492. n_line_no:=1;
1494. OPENcur_indxes;
1495. LOOP
1496. FETCHcur_indxes
1497. INTOv_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. EXITWHENcur_indxes%NOTFOUND;
1504. b_first_rec:=TRUE;
1506. IFupper(v_gen_drop_indx_sql)='T'THEN
1507. IFupper(v_display_prompt_info)='T'THEN
1508. IFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL'THEN
1510. v_string:=v_prompt||'DROPUNIQUEINDEX'||
1511. lower(v_index_name);
1512. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL'THEN
1514. v_string:=v_prompt||'DROPINDEX'||lower(v_index_name);
1515. ELSIFv_index_type='BITMAP'THEN
1516. v_string:=v_prompt||'DROPBITMAPINDEX'||
1517. lower(v_index_name);
1518. ELSIFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1520. v_string:=v_prompt||'DROPUNIQUE,REVERSEINDEX'||
1521. lower(v_index_name);
1522. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1524. v_string:=v_prompt||'DROPREVERSEINDEX'||
1525. lower(v_index_name);
1526. ELSIFv_uniqueness='UNIQUE'AND
1527. v_index_type='FUNCTION-BASEDNORMAL'THEN
1529. v_string:=v_prompt||'DROPUNIQUE,FUNCTIONINDEX'||
1530. lower(v_index_name);
1531. ELSIFv_uniqueness<>'UNIQUE'AND
1532. v_index_type='FUNCTION-BASEDNORMAL'THEN
1534. v_string:=v_prompt||'DROPFUNCTIONINDEX'||
1535. lower(v_index_name);
1536. ENDIF;
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. ENDIF;
1546. v_string:='DROPINDEX'||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;
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. ENDIF;
1563. IFupper(v_gen_create_indx_sql)='T'THEN
1564. IFupper(p_v_display_prompt_info)='T'THEN
1565. IFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL'THEN
1567. v_string:=v_prompt||'CREATEUNIQUEINDEX'||
1568. lower(v_index_name);
1569. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL'THEN
1571. v_string:=v_prompt||'CREATEINDEX'||lower(v_index_name);
1572. ELSIFv_index_type='BITMAP'THEN
1574. v_string:=v_prompt||'CREATEBITMAPINDEX'||
1575. lower(v_index_name);
1576. ELSIFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1578. v_string:=v_prompt||'CREATEUNIQUE,REVERSEINDEX'||
1579. lower(v_index_name);
1580. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1582. v_string:=v_prompt||'CREATEREVERSEINDEX'||
1583. lower(v_index_name);
1584. ELSIFv_uniqueness='UNIQUE'AND
1585. v_index_type='FUNCTION-BASEDNORMAL'THEN
1587. v_string:=v_prompt||'CREATEUNIQUE,FUNCTIONINDEX'||
1588. lower(v_index_name);
1589. ELSIFv_uniqueness<>'UNIQUE'AND
1590. v_index_type='FUNCTION-BASEDNORMAL'THEN
1592. v_string:=v_prompt||'CREATEFUNCTIONINDEX'||
1593. lower(v_index_name);
1594. ENDIF;
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. ENDIF;
1604. IFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL'THEN
1606. v_string:='CREATEUNIQUEINDEX'||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. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL'THEN
1615. v_string:='CREATEINDEX'||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. ELSIFv_index_type='BITMAP'THEN
1624. v_string:='CREATEBITMAPINDEX'||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. ELSIFv_uniqueness='UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1633. v_string:='CREATEUNIQUEINDEX'||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. ELSIFv_uniqueness<>'UNIQUE'ANDv_index_type='NORMAL/REV'THEN
1642. v_string:='CREATEINDEX'||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. ELSIFv_uniqueness='UNIQUE'AND
1650. v_index_type='FUNCTION-BASEDNORMAL'THEN
1652. v_string:='CREATEUNIQUEINDEX'||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. ELSIFv_uniqueness<>'UNIQUE'AND
1660. v_index_type='FUNCTION-BASEDNORMAL'THEN
1662. v_string:='CREATEINDEX'||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. ENDIF;
1670. IFv_index_type<>'FUNCTION-BASEDNORMAL'THEN
1671. OPENcur_cols(v_index_name,v_table_name);
1672. LOOP
1673. FETCHcur_cols
1674. INTOlv_column_name;
1675. EXITWHENcur_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. ENDIF;
1682. v_string:=v_string||lower(lv_column_name);
1683. ENDLOOP;
1684. CLOSEcur_cols;
1685. ELSE
1686. OPENcur_fun_expression(v_index_name,v_table_name);
1687. LOOP
1688. FETCHcur_fun_expression
1689. INTOlv_column_name;
1690. EXITWHENcur_fun_expression%NOTFOUND;
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. ENDLOOP;
1698. CLOSEcur_fun_expression;
1700. ENDIF;
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;
1710. IFupper(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;
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;
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;
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;
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;
1753. v_string:=chr(9)||'INITIAL'||to_char(n_initial_extent);
1754. IFn_next_extentISNOTNULLTHEN
1755. v_string:=v_string||'NEXT'||to_char(n_next_extent);
1756. ENDIF;
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;
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;
1774. v_string:=chr(9)||'FREELISTS'||to_char(n_freelists)||
1775. 'FREELISTGROUPS'||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;
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;
1791. UPDATEtemp_for_pkg_gen_sql2000_temp
1792. SETsql_text=sql_text||')'
1793. WHEREline_no=n_line_no-1
1794. ANDobject_type=v_object_type;
1795. ELSE
1796. IFupper(v_gen_store_clause)='F'ANDupper(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. ENDIF;
1805. ENDIF;
1807. IFv_index_type<>'NORMAL/REV'THEN
1808. UPDATEtemp_for_pkg_gen_sql2000_temp
1809. SETsql_text=sql_text||';'
1810. WHEREline_no=n_line_no-1
1811. ANDobject_type=v_object_type;
1812. ELSE
1813. UPDATEtemp_for_pkg_gen_sql2000_temp
1814. SETsql_text=sql_text||'REVERSE;'
1815. WHEREline_no=n_line_no-1
1816. ANDobject_type=v_object_type;
1817. ENDIF;
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. ENDIF;
1826. ENDLOOP;
1827. CLOSEcur_indxes;
1829. SELECTCOUNT(*)INTOn_tempFROMuser_indexesWHEREindex_type='LOB';
1831. IFn_temp>0THEN
1832. v_string:='';
1833. insert_rec(n_line_no,
1834. v_object_type,
1835. v_username,
1836. 'LOBINDEX',
1837. v_string);
1838. n_line_no:=n_line_no+1;
1840. v_string:='--ThereissomeLOBindex,andwedon''tlist;';
1841. insert_rec(n_line_no,
1842. v_object_type,
1843. v_username,
1844. 'LOBINDEX',
1845. v_string);
1846. n_line_no:=n_line_no+1;
1848. v_string:='';
1849. insert_rec(n_line_no,
1850. v_object_type,
1851. v_username,
1852. 'LOBINDEX',
1853. v_string);
1854. n_line_no:=n_line_no+1;
1856. v_string:='';
1857. insert_rec(n_line_no,
1858. v_object_type,
1859. v_username,
1860. 'LOBINDEX',
1861. v_string);
1862. n_line_no:=n_line_no+1;
1863. ENDIF;
1865. COMMIT;
1866. END;
1868. PROCEDUREpro_reb_indx_sql(p_v_gen_store_clauseVARCHAR2default'T',
1869. p_v_rebuild_onlineVARCHAR2default'T',
1870. p_v_display_prompt_infoVARCHAR2default'T')AS
1871. CURSORcur_indxesIS
1872. SELECTindex_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. FROMuser_indexes
1891. WHEREindex_type<>'LOB'
1892. ORDERBYindex_name;
1894. v_index_nameuser_indexes.index_name%TYPE;
1895. v_table_owneruser_indexes.table_owner%TYPE;
1896. v_table_nameuser_indexes.table_name%TYPE;
1897. v_index_typeuser_indexes.index_type%TYPE;
1898. v_uniquenessuser_indexes.uniqueness%TYPE;
1899. v_tablespace_nameuser_indexes.tablespace_name%TYPE;
1900. n_ini_transuser_indexes.ini_trans%TYPE;
1901. n_max_transuser_indexes.max_trans%TYPE;
1902. n_initial_extentuser_indexes.initial_extent%TYPE;
1903. n_next_extentuser_indexes.next_extent%TYPE;
1904. n_min_extentsuser_indexes.min_extents%TYPE;
1905. n_max_extentsuser_indexes.max_extents%TYPE;
1906. n_pct_increaseuser_indexes.pct_increase%TYPE;
1907. n_pct_freeuser_indexes.pct_free%TYPE;
1908. n_freelistsuser_indexes.freelists%TYPE;
1909. n_freelist_groupsuser_indexes.freelist_groups%TYPE;
1910. v_buffer_pooluser_indexes.buffer_pool%TYPE;
1911. v_loggingVARCHAR2(15);
1912. lv_column_nameuser_ind_columns.column_name%TYPE;
1913. b_first_recBOOLEAN;
1914. v_stringVARCHAR2(800);
1915. n_line_noNUMBER:=0;
1916. v_gen_store_clauseVARCHAR2(10);
1917. v_rebuild_onlineVARCHAR2(10);
1918. v_display_prompt_infoVARCHAR2(10);
1919. n_tempNUMBER(10,0);
1920. v_object_typeVARCHAR2(30);
1921. v_promptVARCHAR2(30);
1922. v_usernameVARCHAR2(30);
1923. PROCEDUREinsert_rec(p_line_noINTEGER,
1924. p_object_typeVARCHAR2,
1925. p_user_nameVARCHAR2,
1926. p_index_nameVARCHAR2,
1927. p_stringVARCHAR2)IS
1928. BEGIN
1929. INSERTINTOtemp_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';
1941. SELECTusernameINTOv_usernameFROMuser_users;
1943. DELETEFROMtemp_for_pkg_gen_sql2000_temp
1944. WHEREobject_type=v_object_type;
1945. COMMIT;
1947. n_line_no:=1;
1949. OPENcur_indxes;
1950. LOOP
1951. FETCHcur_indxes
1952. INTOv_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. EXITWHENcur_indxes%NOTFOUND;
1959. b_first_rec:=TRUE;
1961. IFupper(p_v_display_prompt_info)='T'THEN
1962. IFv_rebuild_online='T'THEN
1963. v_string:=v_prompt||'REBUILDINDEX'||lower(v_index_name)||
1964. 'ONLINE';
1965. ELSE
1966. v_string:=v_prompt||'REBUILDINDEX'||lower(v_index_name);
1967. ENDIF;
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. ENDIF;
1976. IFv_rebuild_online='T'THEN
1977. v_string:='ALTERINDEX'||lower(v_index_name)||
1978. 'REBUILDONLINE';
1979. ELSE
1980. v_string:='ALTERINDEX'||lower(v_index_name)||'REBUILD';
1981. ENDIF;
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;
1989. IFupper(v_gen_store_clause)='T'THEN
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;
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;
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;
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;
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;
2033. v_string:=chr(9)||'INITIAL'||to_char(n_initial_extent);
2034. IFn_next_extentISNOTNULLTHEN
2035. v_string:=v_string||'NEXT'||to_char(n_next_extent);
2036. ENDIF;
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;
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;
2054. IFn_freelistsISNOTNULLANDn_freelist_groupsISNOTNULLTHEN
2055. v_string:=chr(9)||'FREELISTS'||to_char(n_freelists)||
2056. 'FREELISTGROUPS'||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. ENDIF;
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;
2073. UPDATEtemp_for_pkg_gen_sql2000_temp
2074. SETsql_text=sql_text||')'
2075. WHEREline_no=n_line_no-1
2076. ANDobject_type=v_object_type;
2077. ENDIF;
2079. UPDATEtemp_for_pkg_gen_sql2000_temp
2080. SETsql_text=sql_text||';'
2081. WHEREline_no=n_line_no-1
2082. ANDobject_type=v_object_type;
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. ENDLOOP;
2092. CLOSEcur_indxes;
2094. SELECTCOUNT(*)INTOn_tempFROMuser_indexesWHEREindex_type='LOB';
2096. IFn_temp>0THEN
2097. v_string:='';
2098. insert_rec(n_line_no,
2099. v_object_type,
2100. v_username,
2101. 'LOBINDEX',
2102. v_string);
2103. n_line_no:=n_line_no+1;
2105. v_string:='--ThereissomeLOBindex,andwedon''tlist;';
2106. insert_rec(n_line_no,
2107. v_object_type,
2108. v_username,
2109. 'LOBINDEX',
2110. v_string);
2111. n_line_no:=n_line_no+1;
2113. v_string:='';
2114. insert_rec(n_line_no,
2115. v_object_type,
2116. v_username,
2117. 'LOBINDEX',
2118. v_string);
2119. n_line_no:=n_line_no+1;
2121. v_string:='';
2122. insert_rec(n_line_no,
2123. v_object_type,
2124. v_username,
2125. 'LOBINDEX',
2126. v_string);
2127. n_line_no:=n_line_no+1;
2128. ENDIF;
2130. COMMIT;
2131. END;
2133. PROCEDUREpro_view_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2134. p_v_gen_create_sqlVARCHAR2default'T',
2135. p_v_display_prompt_infoVARCHAR2default'T')AS
2136. CURSORcur_viewsIS
2137. selectview_name,textfromuser_viewsorderbyview_name;
2138. CURSORcur_cols(v_nameVARCHAR2)IS
2139. SELECTtable_name,
2140. column_name,
2141. data_type,
2142. data_length,
2143. data_precision,
2144. data_scale,
2145. nullable,
2146. data_default
2147. FROMuser_tab_columns
2148. WHEREtable_name=v_name
2149. ORDERBYcolumn_id;
2150. v_usernameVARCHAR2(30);
2151. v_view_nameVARCHAR2(100);
2152. v_col_namesVARCHAR2(2000);
2153. v_textVARCHAR2(30000);
2154. v_sqlVARCHAR2(30000);
2155. v_sql_tempVARCHAR2(30000);
2156. n_line_noNUMBER(10,0);
2157. n38_countNUMBER(38,0);
2158. n_positionNUMBER(38,0);
2159. v_object_typeVARCHAR2(30);
2160. v_promptVARCHAR2(30);
2161. PROCEDUREinsert_rec(p_line_noINTEGER,
2162. p_object_typeVARCHAR2,
2163. p_user_nameVARCHAR2,
2164. p_view_nameVARCHAR2,
2165. p_stringVARCHAR2)IS
2166. BEGIN
2167. INSERTINTOtemp_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
2174. v_object_type:='VIEW';
2175. v_prompt:='--prompt';
2177. SELECTusernameINTOv_usernameFROMuser_users;
2179. DELETEtemp_for_pkg_gen_sql4000_tempWHEREobject_type=v_object_type;
2180. COMMIT;
2182. n_line_no:=1;
2184. OPENcur_views;
2185. LOOP
2186. FETCHcur_views
2187. INTOv_view_name,v_text;
2188. EXITWHENcur_views%NOTFOUND;
2190. IFupper(p_v_gen_drop_sql)='T'THEN
2191. IFupper(p_v_display_prompt_info)='T'THEN
2192. v_sql:=v_prompt||'DROPVIEW'||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. ENDIF;
2201. v_sql:='DROPVIEW'||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;
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. ENDIF;
2218. IFupper(p_v_gen_create_sql)='T'THEN
2219. IFupper(p_v_display_prompt_info)='T'THEN
2220. v_sql:=v_prompt||'CREATEVIEW'||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. ENDIF;
2229. v_sql:='CREATEORREPLACEVIEW'||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;
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;
2245. v_col_names:='';
2246. n38_count:=0;
2247. FORcur_cols_recINcur_cols(v_view_name)LOOP
2249. v_col_names:=v_col_names||cur_cols_rec.column_name||',';
2250. n38_count:=n38_count+1;
2251. IFMOD(n38_count,5)=0THEN
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. ENDIF;
2261. ENDLOOP;
2263. IFlength(v_col_names)>2THEN
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. UPDATEtemp_for_pkg_gen_sql4000_temp
2274. SETsql_text=substr(sql_text,1,length(sql_text)-2)
2275. WHEREline_no=n_line_no-1
2276. ANDobject_type=v_object_type;
2277. ENDIF;
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;
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;
2295. v_text:=replace(v_text,',',',');
2297. v_sql:=v_text;
2298. IFlength(v_sql)>3800THEN
2299. WHILElength(v_sql)>3800LOOP
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. ENDLOOP;
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. ENDIF;
2321. n_line_no:=n_line_no+1;
2323. UPDATEtemp_for_pkg_gen_sql4000_temp
2324. SETsql_text=sql_text||';'
2325. WHEREline_no=n_line_no-1
2326. ANDobject_type=v_object_type;
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;
2336. ENDIF;
2337. ENDLOOP;
2338. CLOSEcur_views;
2339. COMMIT;
2340. END;
2342. PROCEDUREpro_seq_max_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2343. p_v_gen_create_sqlVARCHAR2default'T',
2344. p_v_display_prompt_infoVARCHAR2default'T')AS
2345. v_sqlVARCHAR2(500);
2346. n_max_idNUMBER(38,0);
2347. v_max_valVARCHAR2(1000);
2348. CURSORcur_etl_dataIS
2349. SELECTtable_nameFROMuser_tables;
2350. rec_etl_datacur_etl_data%rowtype;
2351. typeref_curisREFCURSOR;--定义一个ref类型的游标
2352. cur_idref_cur;
2353. CURSORcur_cols(t_nameVARCHAR2)IS
2354. SELECTcolumn_name,data_type
2355. FROMuser_tab_columns
2356. WHEREtable_name=t_name
2357. ORDERBYcolumn_id;
2358. rec_col_namecur_cols%rowtype;
2359. v_sequence_nameVARCHAR2(50);
2360. n_line_noNUMBER(38,0);
2361. v_object_typeVARCHAR2(30);
2362. v_promptVARCHAR2(30);
2363. v_usernameVARCHAR2(30);
2364. PROCEDUREinsert_rec(p_line_noINTEGER,
2365. p_object_typeVARCHAR2,
2366. p_user_nameVARCHAR2,
2367. p_sequence_nameVARCHAR2,
2368. p_stringVARCHAR2)IS
2369. BEGIN
2370. INSERTINTOtemp_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';
2380. SELECTusernameINTOv_usernameFROMuser_users;
2382. DELETEtemp_for_pkg_gen_sql2000_tempWHEREobject_type=v_object_type;
2384. COMMIT;
2386. OPENcur_etl_data;
2387. LOOP
2388. FETCHcur_etl_data
2389. INTOrec_etl_data;
2390. EXITWHENcur_etl_data%NOTFOUND;
2392. --得到表的第一个列
2393. OPENcur_cols(rec_etl_data.table_name);
2394. LOOP
2395. FETCHcur_cols
2396. INTOrec_col_name;
2397. EXITWHENcur_cols%NOTFOUND;
2398. EXIT;
2399. ENDLOOP;
2400. CLOSEcur_cols;
2402. IFrec_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:='SELECTmax(to_NUMBER('||rec_col_name.column_name||
2407. '))FROM'||rec_etl_data.table_name||'';
2408. OPENcur_idFORv_sql;
2409. LOOP
2410. FETCHcur_id
2411. INTOv_max_val;
2412. EXITWHENcur_id%NOTFOUND;
2413. EXIT;
2414. ENDLOOP;
2415. CLOSEcur_id;
2416. EXCEPTION
2417. WHENothersTHEN
2418. IFcur_id%ISOPENTHEN
2419. CLOSEcur_id;
2420. ENDIF;
2421. v_max_val:='-1000';
2422. END;
2423. ELSIFrec_col_name.data_type='NUMBER'THEN
2424. v_sql:='SELECTmax('||rec_col_name.column_name||')FROM'||
2425. rec_etl_data.table_name||'';
2426. OPENcur_idFORv_sql;
2427. LOOP
2428. FETCHcur_id
2429. INTOn_max_id;
2430. EXITWHENcur_id%NOTFOUND;
2431. EXIT;
2432. ENDLOOP;
2433. CLOSEcur_id;
2434. ENDIF;
2436. --needmodify,generatesequencename
2437. v_sequence_name:='seq_'||substr(rec_etl_data.table_name,3)||
2438. '_id';
2440. IFp_v_gen_drop_sql='T'THEN
2441. IFp_v_display_prompt_info='T'THEN
2442. v_sql:=v_prompt||'DROPSEQUENCE'||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. ENDIF;
2451. v_sql:='DROPSEQUENCE'||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;
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. ENDIF;
2468. IFp_v_gen_create_sql='T'THEN
2469. IFp_v_display_prompt_info='T'THEN
2470. v_sql:=v_prompt||'CREATESEQUENCE'||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. ENDIF;
2479. v_sql:='CREATESEQUENCE'||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;
2487. --handlethestation:treatethesequencevalueasvarcharcolumn(primarykey)
2488. IFrec_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:='STARTWITH'||
2492. to_char(to_number(nvl(v_max_val,'1'))+1)||'';
2493. ELSIFrec_col_name.data_type='NUMBER'THEN
2494. v_sql:='STARTWITH'||TO_CHAR(nvl(n_max_id,1)+1)||'';
2495. ENDIF;
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;
2503. v_sql:='INCREMENTBY1';
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;
2511. v_sql:='CACHE100NOCYCLE';
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;
2519. UPDATEtemp_for_pkg_gen_sql2000_temp
2520. SETsql_text=sql_text||';'
2521. WHEREline_no=n_line_no-1
2522. ANDobject_type=v_object_type;
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. ENDIF;
2533. ENDLOOP;
2534. CLOSEcur_etl_data;
2535. COMMIT;
2536. END;
2538. PROCEDUREpro_seq_next_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2539. p_v_gen_create_sqlVARCHAR2default'T',
2540. p_v_display_prompt_infoVARCHAR2default'T')AS
2541. v_sqlVARCHAR2(2000);
2542. n_max_idNUMBER(38,0);
2543. v_max_valVARCHAR2(1000);
2544. CURSORcur_sequenceIS
2545. SELECT*FROMuser_sequences;
2546. rec_cur_sequencecur_sequence%rowtype;
2547. v_sequence_nameVARCHAR2(50);
2548. n_line_noNUMBER(38,0);
2549. v_object_typeVARCHAR2(30);
2550. v_promptVARCHAR2(30);
2551. v_usernameVARCHAR2(30);
2552. PROCEDUREinsert_rec(p_line_noINTEGER,
2553. p_object_typeVARCHAR2,
2554. p_user_nameVARCHAR2,
2555. p_sequence_nameVARCHAR2,
2556. p_stringVARCHAR2)IS
2557. BEGIN
2558. INSERTINTOtemp_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';
2568. SELECTusernameINTOv_usernameFROMuser_users;
2570. DELETEtemp_for_pkg_gen_sql2000_tempWHEREobject_type=v_object_type;
2571. COMMIT;
2573. OPENcur_sequence;
2574. LOOP
2575. FETCHcur_sequence
2576. INTOrec_cur_sequence;
2577. EXITWHENcur_sequence%NOTFOUND;
2579. IFp_v_gen_drop_sql='T'THEN
2580. IFp_v_display_prompt_info='T'THEN
2581. v_sql:=v_prompt||'DROPSEQUENCE'||
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. ENDIF;
2590. v_sql:='DROPSEQUENCE'||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;
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. ENDIF;
2607. IFp_v_gen_create_sql='T'THEN
2608. IFp_v_display_prompt_info='T'THEN
2609. v_sql:=v_prompt||'CREATESEQUENCE'||
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. ENDIF;
2619. v_sql:='CREATESEQUENCE'||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;
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;
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;
2643. v_sql:='STARTWITH'||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;
2651. v_sql:='INCREMENTBY'||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;
2659. IFrec_cur_sequence.cache_size=0THEN
2660. v_sql:='NOCACHE';
2661. ELSE
2662. v_sql:='CACHE'||to_char(rec_cur_sequence.cache_size);
2663. ENDIF;
2665. IFrec_cur_sequence.cycle_flag='N'THEN
2666. v_sql:=v_sql||'NOCYCLE';
2667. ELSE
2668. v_sql:=v_sql||'CYCLE';
2669. ENDIF;
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;
2678. UPDATEtemp_for_pkg_gen_sql2000_temp
2679. SETsql_text=sql_text||';'
2680. WHEREline_no=n_line_no-1
2681. ANDobject_type=v_object_type;
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. ENDIF;
2692. ENDLOOP;
2693. CLOSEcur_sequence;
2694. COMMIT;
2695. END;
2697. PROCEDUREpro_seq_init_val_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2698. p_v_gen_create_sqlVARCHAR2default'T',
2699. p_v_display_prompt_infoVARCHAR2default'T')AS
2700. v_sqlVARCHAR2(2000);
2701. n_max_idNUMBER(38,0);
2702. v_max_valVARCHAR2(1000);
2703. CURSORcur_sequenceIS
2704. SELECT*FROMuser_sequences;
2705. rec_cur_sequencecur_sequence%rowtype;
2706. v_sequence_nameVARCHAR2(50);
2707. n_line_noNUMBER(38,0);
2708. v_object_typeVARCHAR2(30);
2709. v_promptVARCHAR2(30);
2710. v_usernameVARCHAR2(30);
2711. PROCEDUREinsert_rec(p_line_noINTEGER,
2712. p_object_typeVARCHAR2,
2713. p_user_nameVARCHAR2,
2714. p_sequence_nameVARCHAR2,
2715. p_stringVARCHAR2)IS
2716. BEGIN
2717. INSERTINTOtemp_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';
2727. SELECTusernameINTOv_usernameFROMuser_users;
2729. DELETEtemp_for_pkg_gen_sql2000_tempWHEREobject_type=v_object_type;
2730. COMMIT;
2732. OPENcur_sequence;
2733. LOOP
2734. FETCHcur_sequence
2735. INTOrec_cur_sequence;
2736. EXITWHENcur_sequence%NOTFOUND;
2738. IFp_v_gen_drop_sql='T'THEN
2739. IFp_v_display_prompt_info='T'THEN
2740. v_sql:=v_prompt||'DROPSEQUENCE'||
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. ENDIF;
2749. v_sql:='DROPSEQUENCE'||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;
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. ENDIF;
2766. IFp_v_gen_create_sql='T'THEN
2767. IFp_v_display_prompt_info='T'THEN
2768. v_sql:=v_prompt||'CREATESEQUENCE'||
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. ENDIF;
2778. v_sql:='CREATESEQUENCE'||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;
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;
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;
2802. v_sql:='STARTWITH1';
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;
2810. v_sql:='INCREMENTBY'||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;
2818. IFrec_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. ENDIF;
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;
2832. UPDATEtemp_for_pkg_gen_sql2000_temp
2833. SETsql_text=sql_text||';'
2834. WHEREline_no=n_line_no-1
2835. ANDobject_type=v_object_type;
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. ENDIF;
2846. ENDLOOP;
2847. CLOSEcur_sequence;
2848. COMMIT;
2849. END;
2851. PROCEDUREpro_synonym_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2852. p_v_gen_create_sqlVARCHAR2default'T',
2853. p_v_display_prompt_infoVARCHAR2default'T')AS
2854. v_sqlVARCHAR2(2000);
2855. n_max_idNUMBER(38,0);
2856. v_max_valVARCHAR2(1000);
2857. v_object_typeVARCHAR2(30);
2858. v_promptVARCHAR2(30);
2859. v_usernameVARCHAR2(30);
2860. CURSORcur_synonymIS
2861. SELECTsynonym_name,
2862. 'CREATESYNONYM'||synonym_name||'for'||
2863. decode(db_link,null,table_owner||'.','')||table_name||
2864. decode(db_link,null,'','@'||db_link)||';'sql_text
2865. FROMuser_synonyms;
2866. rec_cur_synonymcur_synonym%rowtype;
2867. v_synonym_nameVARCHAR2(50);
2868. n_line_noNUMBER(38,0);
2869. PROCEDUREinsert_rec(p_line_noINTEGER,
2870. p_object_typeVARCHAR2,
2871. p_user_nameVARCHAR2,
2872. p_object_nameVARCHAR2,
2873. p_stringVARCHAR2)IS
2874. BEGIN
2875. INSERTINTOtemp_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';
2885. SELECTusernameINTOv_usernameFROMuser_users;
2887. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
2888. COMMIT;
2890. OPENcur_synonym;
2891. LOOP
2892. FETCHcur_synonym
2893. INTOrec_cur_synonym;
2894. EXITWHENcur_synonym%NOTFOUND;
2896. IFp_v_gen_drop_sql='T'THEN
2897. IFp_v_display_prompt_info='T'THEN
2898. v_sql:=v_prompt||'DROPSYNONYM'||
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. ENDIF;
2907. v_sql:='DROPSYNONYM'||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;
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. ENDIF;
2924. IFp_v_gen_create_sql='T'THEN
2925. IFp_v_display_prompt_info='T'THEN
2926. v_sql:=v_prompt||'CREATESYNONYM'||
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. ENDIF;
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;
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. ENDIF;
2953. ENDLOOP;
2954. CLOSEcur_synonym;
2955. COMMIT;
2956. END;
2958. PROCEDUREpro_job_sql(p_v_gen_drop_sqlVARCHAR2default'T',
2959. p_v_gen_create_sqlVARCHAR2default'T',
2960. p_v_display_prompt_infoVARCHAR2default'T')AS
2961. v_sqlVARCHAR2(4000);
2962. n_max_idNUMBER(38,0);
2963. v_max_valVARCHAR2(1000);
2964. v_object_typeVARCHAR2(30);
2965. v_promptVARCHAR2(30);
2966. v_usernameVARCHAR2(30);
2967. v_object_nameVARCHAR2(200);
2968. CURSORcur_jobIS
2969. SELECTjobjob_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. FROMuser_jobs;
2977. rec_cur_jobcur_job%rowtype;
2978. v_job_nameVARCHAR2(50);
2979. n_line_noNUMBER(38,0);
2980. PROCEDUREinsert_rec(p_line_noINTEGER,
2981. p_object_typeVARCHAR2,
2982. p_user_nameVARCHAR2,
2983. p_object_nameVARCHAR2,
2984. p_stringVARCHAR2)IS
2985. BEGIN
2986. INSERTINTOtemp_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';
2996. SELECTusernameINTOv_usernameFROMuser_users;
2998. DELETEtemp_for_pkg_gen_sql4000_tempWHEREOBJECT_TYPE=v_object_type;
2999. COMMIT;
3001. OPENcur_job;
3002. LOOP
3003. FETCHcur_job
3004. INTOrec_cur_job;
3005. EXITWHENcur_job%NOTFOUND;
3007. IFp_v_gen_drop_sql='T'THEN
3008. IFp_v_display_prompt_info='T'THEN
3009. v_sql:=v_prompt||'DROPjob'||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. ENDIF;
3017. v_sql:='execdbms_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;
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. ENDIF;
3034. IFp_v_gen_create_sql='T'THEN
3035. IFp_v_display_prompt_info='T'THEN
3036. v_sql:=v_prompt||'CREATEjob'||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. ENDIF;
3045. v_sql:='variablejobnonumber;';
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;
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;
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;
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;
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;
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. ENDIF;
3094. ENDLOOP;
3095. CLOSEcur_job;
3096. COMMIT;
3097. END;
3099. PROCEDUREpro_dl_sql(p_v_gen_drop_sqlVARCHAR2default'T',
3100. p_v_gen_create_sqlVARCHAR2default'T',
3101. p_v_display_prompt_infoVARCHAR2default'T')AS
3102. v_sqlVARCHAR2(2000);
3103. n_max_idNUMBER(38,0);
3104. v_max_valVARCHAR2(1000);
3105. v_object_typeVARCHAR2(30);
3106. v_promptVARCHAR2(30);
3107. v_usernameVARCHAR2(30);
3108. CURSORcur_dlIS
3109. SELECTdb_link,
3110. 'CREATEDATABASELINK'||db_link||'CONNECTTO'||
3111. username||'IDENTIFIEDBY'||password||'USING'''||HOST||
3112. ''';'sql_text
3113. FROMuser_db_links;
3114. rec_cur_dlcur_dl%rowtype;
3115. v_db_linkVARCHAR2(50);
3116. n_line_noNUMBER(38,0);
3117. PROCEDUREinsert_rec(p_line_noINTEGER,
3118. p_object_typeVARCHAR2,
3119. p_user_nameVARCHAR2,
3120. p_object_nameVARCHAR2,
3121. p_stringVARCHAR2)IS
3122. BEGIN
3123. INSERTINTOtemp_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';
3133. SELECTusernameINTOv_usernameFROMuser_users;
3135. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
3136. COMMIT;
3138. OPENcur_dl;
3139. LOOP
3140. FETCHcur_dl
3141. INTOrec_cur_dl;
3142. EXITWHENcur_dl%NOTFOUND;
3144. IFp_v_gen_drop_sql='T'THEN
3145. IFp_v_display_prompt_info='T'THEN
3146. v_sql:=v_prompt||'DROPDATABASELIKE'||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. ENDIF;
3154. v_sql:='DROPDATABASELIKE'||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;
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. ENDIF;
3171. IFp_v_gen_create_sql='T'THEN
3172. IFp_v_display_prompt_info='T'THEN
3173. v_sql:=v_prompt||'CREATEDATABASELINK'||
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. ENDIF;
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;
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. ENDIF;
3200. ENDLOOP;
3201. CLOSEcur_dl;
3202. COMMIT;
3203. END;
3205. PROCEDUREpro_role_sql(p_v_gen_drop_sqlVARCHAR2default'T',
3206. p_v_gen_create_sqlVARCHAR2default'T',
3207. p_v_display_prompt_infoVARCHAR2default'T')AS
3208. v_sqlVARCHAR2(2000);
3209. v_optionVARCHAR2(200);
3210. n_max_idNUMBER(38,0);
3211. v_max_valVARCHAR2(1000);
3212. v_object_typeVARCHAR2(30);
3213. v_promptVARCHAR2(30);
3214. v_usernameVARCHAR2(30);
3215. CURSORcur_roleIS
3216. selectgranted_rolerolefromuser_role_privs;
3217. rec_cur_rolecur_role%rowtype;
3219. CURSORcur_role_pris(c_role_nameVARCHAR2)IS
3220. SELECTtype,
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. FROMrole_tab_privs
3235. WHEREroleNOTIN('CONNECT','RESOURCE','DBA')
3236. UNION
3237. SELECT'SYS'type,
3238. role,
3239. ''owner,
3240. ''table_name,
3241. ''column_name,
3242. privilege,
3243. admin_optiongrantable
3244. FROMrole_sys_privs
3245. WHEREroleNOTIN('CONNECT','RESOURCE','DBA'))
3246. whererole=upper(c_role_name);
3247. rec_cur_role_priscur_role_pris%rowtype;
3249. v_roleVARCHAR2(50);
3250. n_line_noNUMBER(38,0);
3251. PROCEDUREinsert_rec(p_line_noINTEGER,
3252. p_object_typeVARCHAR2,
3253. p_user_nameVARCHAR2,
3254. p_object_nameVARCHAR2,
3255. p_stringVARCHAR2)IS
3256. BEGIN
3257. INSERTINTOtemp_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';
3267. SELECTusernameINTOv_usernameFROMuser_users;
3269. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
3270. COMMIT;
3272. OPENcur_role;
3273. LOOP
3274. FETCHcur_role
3275. INTOrec_cur_role;
3276. EXITWHENcur_role%NOTFOUND;
3278. IFp_v_gen_drop_sql='T'THEN
3279. IFp_v_display_prompt_info='T'THEN
3280. v_sql:=v_prompt||'DROPROLE'||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. ENDIF;
3288. v_sql:='DROPROLE'||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;
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. ENDIF;
3305. IFp_v_gen_create_sql='T'THEN
3306. IFp_v_display_prompt_info='T'THEN
3307. v_sql:=v_prompt||'CREATEROLE'||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. ENDIF;
3316. v_sql:='CREATEROLE'||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;
3324. v_sql:=v_prompt||'GRANTPRISTOROLE:'||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;
3332. OPENcur_role_pris(rec_cur_role.role);
3333. LOOP
3334. FETCHcur_role_pris
3335. INTOrec_cur_role_pris;
3336. EXITWHENcur_role_pris%NOTFOUND;
3338. IFrec_cur_role_pris.type='SYS'THEN
3339. IFrec_cur_role_pris.grantable='NO'THEN
3340. v_option:='';
3341. ELSE
3342. v_option:='WITHADMINOPTION';
3343. ENDIF;
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. IFrec_cur_role_pris.grantable='NO'THEN
3354. v_option:='';
3355. ELSE
3356. v_option:='WITHGRANTOPTION';
3357. ENDIF;
3359. IFrec_cur_role_pris.column_nameISNOTNULLTHEN
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. ENDIF;
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. ENDIF;
3379. ENDLOOP;
3380. CLOSEcur_role_pris;
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. ENDIF;
3391. ENDLOOP;
3392. CLOSEcur_role;
3393. COMMIT;
3394. END;
3396. PROCEDUREpro_priv_sql(p_v_gen_drop_sqlVARCHAR2default'T',
3397. p_v_gen_create_sqlVARCHAR2default'T',
3398. p_v_display_prompt_infoVARCHAR2default'T')AS
3399. v_sqlVARCHAR2(2000);
3400. v_optionVARCHAR2(200);
3401. n_max_idNUMBER(38,0);
3402. v_max_valVARCHAR2(1000);
3403. v_object_typeVARCHAR2(30);
3404. v_promptVARCHAR2(30);
3405. v_usernameVARCHAR2(30);
3406. n_countnumber(10,0);
3407. CURSORcur_privIS
3408. SELECTprivilege,
3409. owner,
3410. table_name,
3411. grantee,
3412. 'GRANT'||privilege||'ON'||owner||'.'||table_name||
3413. 'TO'||grantee||
3414. decode(grantable,'NO','','WITHGRANTOPTION')||';'SQL_TEXT
3415. FROMuser_tab_privs
3416. WHEREGRANTOR=(SELECTusernameFROMuser_users)
3417. ORDERBYowner,table_name;
3418. rec_cur_privcur_priv%rowtype;
3420. CURSORcur_col_privIS
3421. SELECTprivilege,
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','','WITHGRANTOPTION')||';'SQL_TEXT
3429. FROMuser_col_privs
3430. WHEREGRANTOR=(SELECTusernameFROMuser_users)
3431. ORDERBYowner,table_name;
3432. rec_cur_col_privcur_col_priv%rowtype;
3434. CURSORcur_sys_privIS
3435. SELECTusername,
3436. privilege,
3437. 'GRANT'||privilege||'TO'||username||
3438. decode(admin_option,'NO','','WITHADMINOPTION')||';'SQL_TEXT
3439. FROMuser_sys_privs
3440. ORDERBYusername;
3441. rec_cur_sys_privcur_sys_priv%rowtype;
3443. v_privVARCHAR2(50);
3444. n_line_noNUMBER(38,0);
3445. PROCEDUREinsert_rec(p_line_noINTEGER,
3446. p_object_typeVARCHAR2,
3447. p_user_nameVARCHAR2,
3448. p_object_nameVARCHAR2,
3449. p_stringVARCHAR2)IS
3450. BEGIN
3451. INSERTINTOtemp_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;
3462. SELECTUSERNAMEINTOv_usernameFROMUSER_USERS;
3464. DELETEtemp_for_pkg_gen_sql2000_tempWHEREOBJECT_TYPE=v_object_type;
3465. COMMIT;
3467. OPENcur_priv;
3468. LOOP
3469. FETCHcur_priv
3470. INTOrec_cur_priv;
3471. EXITWHENcur_priv%NOTFOUND;
3473. IFp_v_gen_create_sql='T'THEN
3474. IFp_v_display_prompt_info='T'THEN
3475. v_sql:=v_prompt||'GRANTPRIVILEGE:'||
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. 'tabpriv',
3483. v_sql);
3484. n_line_no:=n_line_no+1;
3485. ENDIF;
3487. v_sql:=rec_cur_priv.sql_text;
3488. insert_rec(n_line_no,v_object_type,v_username,'tabpriv',v_sql);
3489. n_line_no:=n_line_no+1;
3491. v_sql:='';
3492. insert_rec(n_line_no,v_object_type,v_username,'tabpriv',v_sql);
3493. n_line_no:=n_line_no+1;
3495. ENDIF;
3497. ENDLOOP;
3498. CLOSEcur_priv;
3500. OPENcur_col_priv;
3501. LOOP
3502. FETCHcur_col_priv
3503. INTOrec_cur_col_priv;
3504. EXITWHENcur_col_priv%NOTFOUND;
3505. IFn_count=1THEN
3506. v_sql:='--***********************';
3507. insert_rec(n_line_no,v_object_type,v_username,'tabpriv',v_sql);
3508. n_line_no:=n_line_no+1;
3510. n_count:=n_count+1;
3511. ENDIF;
3513. v_sql:='';
3514. insert_rec(n_line_no,v_object_type,v_username,'tabpriv',v_sql);
3515. n_line_no:=n_line_no+1;
3517. IFp_v_display_prompt_info='T'THEN
3518. v_sql:=v_prompt||'GRANTCOLPRIVILEGE:'||
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,'colpriv',v_sql);
3524. n_line_no:=n_line_no+1;
3525. ENDIF;
3527. v_sql:=rec_cur_col_priv.sql_text;
3528. insert_rec(n_line_no,v_object_type,v_username,'colpriv',v_sql);
3529. n_line_no:=n_line_no+1;
3531. v_sql:='';
3532. insert_rec(n_line_no,v_object_type,v_username,'colpriv',v_sql);
3533. n_line_no:=n_line_no+1;
3534. ENDLOOP;
3535. CLOSEcur_col_priv;
3537. n_count:=1;
3538. OPENcur_sys_priv;
3539. LOOP
3540. FETCHcur_sys_priv
3541. INTOrec_cur_sys_priv;
3542. EXITWHENcur_sys_priv%NOTFOUND;
3543. IFn_count=1THEN
3544. v_sql:='';
3545. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3546. n_line_no:=n_line_no+1;
3548. v_sql:='PROMPT***********YoumustthefollowingsqlasauserwithDBApriv,othersyouwillgeterrors!
3550. ************';
3551. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3552. n_line_no:=n_line_no+1;
3554. v_sql:='PROMPT***********Wecommentthefollowsql!************';
3555. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3556. n_line_no:=n_line_no+1;
3558. v_sql:='';
3559. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3560. n_line_no:=n_line_no+1;
3562. n_count:=n_count+1;
3564. ENDIF;
3566. IFp_v_display_prompt_info='T'THEN
3567. v_sql:=v_prompt||'GRANTSYSTEMPRIVILEGE:'||
3568. rec_cur_sys_priv.privilege||'TO'||
3569. rec_cur_sys_priv.username;
3570. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3571. n_line_no:=n_line_no+1;
3572. ENDIF;
3574. v_sql:='--'||rec_cur_sys_priv.sql_text;
3575. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3576. n_line_no:=n_line_no+1;
3578. v_sql:='';
3579. insert_rec(n_line_no,v_object_type,v_username,'syspriv',v_sql);
3580. n_line_no:=n_line_no+1;
3581. ENDLOOP;
3582. CLOSEcur_sys_priv;
3584. COMMIT;
3585. END;
3587. PROCEDUREpro_pkgprofuntri_sql(p_v_gen_drop_sqlVARCHAR2default'T',
3588. p_v_gen_create_sqlVARCHAR2default'T',
3589. p_v_display_prompt_infoVARCHAR2default'T',
3590. p_v_separ_fileVARCHAR2default'T',
3591. p_v_file_pathVARCHAR2default'C:/')AS
3592. v_sqlVARCHAR2(2000);
3593. v_object_typeVARCHAR2(30);
3594. v_promptVARCHAR2(30);
3595. v_file_pathVARCHAR2(600);
3596. v_notionchar(1);
3597. v_usernameVARCHAR2(30);
3598. CURSORcur_ProFunPkg_nameIS
3599. SELECTdistincttype,us.name
3600. FROMuser_sourceus
3601. WHEREtype<>'PACKAGEBODY'
3602. ANDus.name<>upper('pkg_gen_user_sql')
3603. ORDERBYus.type,us.name;
3605. CURSORcur_ProFunPkg_code(c_obj_nameVARCHAR2,c_obj_typeVARCHAR2)IS
3606. SELECTline,type,text
3607. FROMuser_sourceus
3608. WHERENAME=c_obj_name
3609. ANDtype=c_obj_type
3610. ORDERBYtype,line;
3612. v_object_nameVARCHAR2(50);
3613. n_line_noNUMBER(38,0);
3614. PROCEDUREinsert_rec(p_line_noINTEGER,
3615. p_object_typeVARCHAR2,
3616. p_user_nameVARCHAR2,
3617. p_object_nameVARCHAR2,
3618. p_stringVARCHAR2)IS
3619. BEGIN
3620. INSERTINTOtemp_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';
3630. SELECTusernameINTOv_usernameFROMuser_users;
3632. --findthenotionbetweenthedictionary
3633. IFinstr(p_v_file_path,'/')>0THEN
3634. v_notion:='/';
3635. ELSE
3636. v_notion:='/';
3637. ENDIF;
3639. IFsubstr(v_file_path,length(v_file_path))<>v_notionTHEN
3640. v_file_path:=v_file_path||v_notion;
3641. ENDIF;
3643. DELETEtemp_for_pkg_gen_sql4000_temp
3644. WHEREOBJECT_TYPEIN
3645. ('PACKAGEBODY','PACKAGE','PROCEDURE','FUNCTION',
3646. 'SEPAR_PROCS','SEPAR_FUNCS','SEPAR_PKG_HEADS',
3647. 'SEPAR_PKG_BODYS','SEPAR_PKGS','TRIGGER','SEPAR_TRIS');
3648. COMMIT;
3650. FORrec_ProFunPkg_nameINcur_ProFunPkg_nameLOOP
3651. v_object_type:=rec_ProFunPkg_name.type;
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;
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;
3671. IFp_v_gen_drop_sql='T'ANDv_object_type<>'PACKAGEBODY'THEN
3672. IFp_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. ENDIF;
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;
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. ENDIF;
3700. IFp_v_gen_create_sql='T'THEN
3701. IFp_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. ENDIF;
3712. FORrec_ProFunPkg_codeINcur_ProFunPkg_code(rec_ProFunPkg_name.name,
3713. rec_ProFunPkg_name.type)LOOP
3714. IFrec_ProFunPkg_code.line=1THEN
3715. v_sql:='CREATEORREPLACE'||rec_ProFunPkg_code.text;
3716. ELSE
3717. v_sql:=rec_ProFunPkg_code.text;
3718. ENDIF;
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. ENDLOOP;
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;
3735. v_sql:='@'||lower(rec_ProFunPkg_name.name)||'.sql';
3736. IFv_object_type='PROCEDURE'THEN
3737. insert_rec(n_line_no,
3738. v_object_type,
3739. v_username,
3740. 'START_PROCS',
3741. v_sql);
3742. ELSIFv_object_type='FUNCTION'THEN
3743. insert_rec(n_line_no,
3744. v_object_type,
3745. v_username,
3746. 'START_FUNCS',
3747. v_sql);
3748. ELSIFv_object_type='PACKAGE'orv_object_type='PACKAGEBODY'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;
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;
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. ELSIFv_object_type='TRIGGER'THEN
3771. insert_rec(n_line_no,
3772. v_object_type,
3773. v_username,
3774. 'START_TRIS',
3775. v_sql);
3776. ELSIFv_object_type='TYPE'orv_object_type='TYPEBODY'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;
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;
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. ENDIF;
3799. n_line_no:=n_line_no+1;
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;
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;
3817. IFrec_ProFunPkg_name.type='PACKAGE'THEN
3818. FORrec_ProFunPkg_codeINcur_ProFunPkg_code(rec_ProFunPkg_name.name,
3819. rec_ProFunPkg_name.type||
3820. 'BODY')LOOP
3821. IFrec_ProFunPkg_code.line=1THEN
3822. v_sql:='CREATEORREPLACE'||rec_ProFunPkg_code.text;
3823. ELSE
3824. v_sql:=rec_ProFunPkg_code.text;
3825. ENDIF;
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. ENDLOOP;
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;
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. ENDIF;
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;
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;
3869. ENDIF;
3871. IFv_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;
3882. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
3890. v_sql:='WHEREOBJECT_TYPE=''PROCEDURE''ANDOBJECT_NAME='''||
3891. rec_ProFunPkg_name.name||'''ORDERBYline_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;
3899. v_sql:='spooloff';
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;
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;
3915. ELSIFv_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;
3926. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
3934. v_sql:='WHEREOBJECT_TYPE=''FUNCTION''ANDOBJECT_NAME='''||
3935. rec_ProFunPkg_name.name||'''ORDERBYline_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;
3943. v_sql:='spooloff';
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;
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. ELSIFv_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;
3969. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
3977. v_sql:='WHEREOBJECT_TYPE=''TRIGGER''ANDOBJECT_NAME='''||
3978. rec_ProFunPkg_name.name||'''ORDERBYline_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;
3986. v_sql:='spooloff';
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;
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. ELSIFv_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;
4013. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4021. v_sql:='WHEREOBJECT_TYPE=''PACKAGE''ANDOBJECT_NAME='''||
4022. rec_ProFunPkg_name.name||'''ORDERBYline_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;
4030. v_sql:='spooloff';
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;
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;
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;
4057. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4065. v_sql:='WHEREOBJECT_TYPE=''PACKAGEBODY''ANDOBJECT_NAME='''||
4066. rec_ProFunPkg_name.name||'''ORDERBYline_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;
4074. v_sql:='spooloff;';
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;
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;
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;
4098. --thepackageheadandpackagebodyallowinthesamefile
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;
4109. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4117. v_sql:='WHEREOBJECT_TYPElike''%PACKAGE%''ANDOBJECT_NAME='''||
4118. rec_ProFunPkg_name.name||'''ORDERBYline_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;
4126. v_sql:='spooloff;';
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;
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. ENDIF;
4143. ENDLOOP;
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;
4151. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4155. v_sql:='WHEREOBJECT_TYPE=''PROCEDURE''ANDOBJECT_NAME=''START_PROCS''ORDERBYline_no;';
4156. insert_rec(n_line_no,'SEPAR_PROCS',v_username,'SEPAR_PROCS',v_sql);
4157. n_line_no:=n_line_no+1;
4159. v_sql:='spooloff';
4160. insert_rec(n_line_no,'SEPAR_PROCS',v_username,'SEPAR_PROCS',v_sql);
4161. n_line_no:=n_line_no+1;
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;
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;
4172. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4176. v_sql:='WHEREOBJECT_TYPE=''FUNCTION''ANDOBJECT_NAME=''START_FUNCS''ORDERBYline_no;';
4177. insert_rec(n_line_no,'SEPAR_FUNCS',v_username,'SEPAR_FUNCS',v_sql);
4178. n_line_no:=n_line_no+1;
4180. v_sql:='spooloff';
4181. insert_rec(n_line_no,'SEPAR_FUNCS',v_username,'SEPAR_FUNCS',v_sql);
4182. n_line_no:=n_line_no+1;
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;
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;
4193. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4197. v_sql:='WHEREOBJECT_TYPE=''TRIGGER''ANDOBJECT_NAME=''SEPAR_TRIS''ORDERBYline_no;';
4198. insert_rec(n_line_no,'SEPAR_TRIS',v_username,'SEPAR_TRIS',v_sql);
4199. n_line_no:=n_line_no+1;
4201. v_sql:='spooloff';
4202. insert_rec(n_line_no,'SEPAR_TRIS',v_username,'SEPAR_TRIS',v_sql);
4203. n_line_no:=n_line_no+1;
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;
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;
4218. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4226. v_sql:='WHEREOBJECT_TYPE=''PACKAGE''ANDOBJECT_NAME=''START_PKG_HS''ORDERBYline_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;
4234. v_sql:='spooloff';
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;
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;
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;
4259. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4267. v_sql:='WHEREOBJECT_TYPE=''PACKAGE''ANDOBJECT_NAME=''START_PKG_BS''ORDERBYline_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;
4275. v_sql:='spooloff';
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;
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;
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;
4292. v_sql:='SELECTrtrim(sql_text)FROMtemp_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;
4296. v_sql:='WHEREOBJECT_TYPE=''PACKAGE''ANDOBJECT_NAME=''START_PKGS''ORDERBYline_no;';
4297. insert_rec(n_line_no,'SEPAR_PKGS',v_username,'SEPAR_PKGS',v_sql);
4298. n_line_no:=n_line_no+1;
4300. v_sql:='spooloff';
4301. insert_rec(n_line_no,'SEPAR_PKGS',v_username,'SEPAR_PKGS',v_sql);
4302. n_line_no:=n_line_no+1;
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;
4309. END;
4311. ENDpkg_gen_user_sql;
4313. /
4315. showerr
4319. setechooff
4320. setfeedbackoff
4321. setnewpagenone
4322. setpagesize0
4323. setverifyoff
4324. setpagesize0
4325. settermoff
4326. settrimson
4327. setlinesize5005
4328. setheadingoff
4329. settimingoff
4330. setverifyoff
4331. setnumwidth38
4333. --生成start
4334. execpkg_gen_user_sql.pro_gen_start_sql('&&file_dic')
4335. spool&&file_dic/start_all.sql
4336. SELECTrtrim(sql_text)
4337. FROMtemp_for_pkg_gen_sql2000_temp
4338. WHEREOBJECT_TYPE='START_SQL'
4339. ORDERBYline_no;
4340. spooloff
4342. --生成创建表的sql语句
4343. execpkg_gen_user_sql.pro_tab_sql_tab8('F','T','F','T');
4344. spool&&file_dic/cre_tables.sql
4345. SELECTrtrim(sql_text)
4346. FROMtemp_for_pkg_gen_sql2000_temp
4347. WHEREOBJECT_TYPE='TABLE_8'
4348. ORDERBYline_no;
4349. spooloff
4351. --生成创建索引的sql语句
4352. execpkg_gen_user_sql.pro_indx_sql('F','T','F','T')
4353. spool&&file_dic/cre_indexes.sql
4354. SELECTsql_text
4355. FROMtemp_for_pkg_gen_sql2000_temp
4356. WHEREOBJECT_TYPE='INDEX'
4357. ORDERBYline_no;
4358. spooloff
4360. --生成创建主键约束的sql语句
4361. execpkg_gen_user_sql.pro_con_sql('P','F','T','T','T');
4362. spool&&file_dic/cre_pri_constraint.sql
4363. SELECTrtrim(sql_text)
4364. FROMtemp_for_pkg_gen_sql2000_temp
4365. WHEREOBJECT_TYPE='CONSTRAINT'
4366. ORDERBYline_no;
4367. spooloff
4369. --生成创建出主键约束的其它约束的sql语句
4370. execpkg_gen_user_sql.pro_con_sql('NOT_PRI','F','T','F','T');
4371. spool&&file_dic/cre_not_pri_constraint.sql
4372. SELECTrtrim(sql_text)
4373. FROMtemp_for_pkg_gen_sql2000_temp
4374. WHEREOBJECT_TYPE='CONSTRAINT'
4375. ORDERBYline_no;
4376. spooloff
4378. --生成创建所有约束的sql语句
4379. execpkg_gen_user_sql.pro_con_sql('A','F','T','T','T');
4380. spool&&file_dic/cre_constraints.sql
4381. SELECTrtrim(sql_text)
4382. FROMtemp_for_pkg_gen_sql2000_temp
4383. WHEREOBJECT_TYPE='CONSTRAINT'
4384. ORDERBYline_no;
4385. spooloff
4387. --生成重建索引的sql语句
4388. execpkg_gen_user_sql.pro_reb_indx_sql('F','F')
4389. spool&&file_dic/reb_indexes.sql
4390. SELECTsql_text
4391. FROMtemp_for_pkg_gen_sql2000_temp
4392. WHEREOBJECT_TYPE='REBUILD_INDEX'
4393. ORDERBYline_no;
4394. spooloff
4397. --生成创建视图的sql语句
4398. execpkg_gen_user_sql.pro_view_sql('F','T','T');
4399. spool&&file_dic/cre_views.sql
4400. SELECTsql_text
4401. FROMtemp_for_pkg_gen_sql4000_temp
4402. WHEREOBJECT_TYPE='VIEW'
4403. ORDERBYline_no;
4404. spooloff
4407. --生成创建序列的sql语句
4408. execpkg_gen_user_sql.pro_seq_next_val_sql('F');
4409. spool&&file_dic/cre_sequences.sql
4410. SELECTsql_text
4411. FROMtemp_for_pkg_gen_sql2000_temp
4412. WHEREOBJECT_TYPE='SEQUENCE'
4413. ORDERBYline_no;
4414. spooloff
4416. --生成创建初始序列的sql语句,每个序列的开始值为1
4417. execpkg_gen_user_sql.pro_seq_init_val_sql('F');
4418. spool&&file_dic/cre_sequences_init.sql
4419. SELECTsql_text
4420. FROMtemp_for_pkg_gen_sql2000_temp
4421. WHEREOBJECT_TYPE='SEQUENCE_INIT'
4422. ORDERBYline_no;
4423. spooloff
4426. --生成创建同义词的sql语句
4427. execpkg_gen_user_sql.pro_synonym_sql('F')
4428. spool&&file_dic/cre_synonyms.sql
4429. SELECTrtrim(sql_text)
4430. FROMtemp_for_pkg_gen_sql2000_temp
4431. WHEREOBJECT_TYPE='SYNONYM'
4432. ORDERBYline_no;
4433. spooloff
4436. --生成创建job的sql语句
4437. execpkg_gen_user_sql.pro_job_sql('F')
4438. spool&&file_dic/cre_jobs.sql
4439. SELECTrtrim(sql_text)
4440. FROMtemp_for_pkg_gen_sql4000_temp
4441. WHEREOBJECT_TYPE='JOB'
4442. ORDERBYline_no;
4443. spooloff
4446. --生成创建数据库链的sql语句
4447. execpkg_gen_user_sql.pro_dl_sql('F')
4448. spool&&file_dic/cre_db_links.sql
4449. SELECTrtrim(sql_text)
4450. FROMtemp_for_pkg_gen_sql2000_temp
4451. WHEREOBJECT_TYPE='DL'
4452. ORDERBYline_no;
4453. spooloff
4456. --生成创建角色的sql语句
4457. execpkg_gen_user_sql.pro_role_sql('F')
4458. spool&&file_dic/cre_roles.sql
4459. SELECTrtrim(sql_text)
4460. FROMtemp_for_pkg_gen_sql2000_temp
4461. WHEREOBJECT_TYPE='ROLE'
4462. ORDERBYline_no;
4463. spooloff
4465. --生成创建授权的sql语句
4466. execpkg_gen_user_sql.pro_priv_sql('F')
4467. spool&&file_dic/cre_privs.sql
4468. SELECTrtrim(sql_text)
4469. FROMtemp_for_pkg_gen_sql2000_temp
4470. WHEREOBJECT_TYPE='PRIV'
4471. ORDERBYline_no;
4472. spooloff
4475. --生成创建存储过程的sql语句，所有存储过程放在一个文件中
4476. execpkg_gen_user_sql.pro_pkgprofuntri_sql('F','T','T','T','&&file_dic')
4478. spool&&file_dic/procedures/procedures.sql
4479. SELECTrtrim(sql_text)
4480. FROMtemp_for_pkg_gen_sql4000_temp
4481. WHEREOBJECT_TYPE='PROCEDURE'
4482. ANDlower(rtrim(sql_text))notlike'@%.sql'
4483. ORDERBYline_no;
4484. spooloff
4487. --生成创建函数的sql语句，所有函数放在一个文件中
4488. spool&&file_dic/functions/functions.sql
4489. SELECTrtrim(sql_text)
4490. FROMtemp_for_pkg_gen_sql4000_temp
4491. WHEREOBJECT_TYPE='FUNCTION'
4492. ANDlower(rtrim(sql_text))notlike'@%.sql'
4493. ORDERBYline_no;
4494. spooloff
4497. --生成创建包的sql语句，所有包放在一个文件中
4498. spool&&file_dic/packages/packages.sql
4499. SELECTrtrim(sql_text)
4500. FROMtemp_for_pkg_gen_sql4000_temp
4501. WHEREOBJECT_TYPElike'%PACKAGE%'
4502. ANDlower(rtrim(sql_text))notlike'@%.sql'
4503. ORDERBYline_no;
4504. spooloff
4507. --生成创建触发器的sql语句
4508. spool&&file_dic/triggers/triggers.sql
4509. SELECTrtrim(sql_text)
4510. FROMtemp_for_pkg_gen_sql4000_temp
4511. WHEREOBJECT_TYPE='TRIGGER'
4512. ORDERBYline_no;
4513. spooloff
4517. --生成创建存储过程的sql语句，每个存储过程放在单独的文件中
4518. spool&&file_dic/temp_sql/separ_pros.sql
4519. SELECTrtrim(sql_text)
4520. FROMtemp_for_pkg_gen_sql4000_temp
4521. WHEREOBJECT_TYPE='SEPAR_PROCS'
4522. ORDERBYline_no;
4523. spooloff
4524. @&&file_dic/temp_sql/separ_pros.sql
4527. --生成创建函数的sql语句，每个函数放在单独的文件中
4528. spool&&file_dic/temp_sql/separ_funcs.sql
4529. SELECTrtrim(sql_text)
4530. FROMtemp_for_pkg_gen_sql4000_temp
4531. WHEREOBJECT_TYPE='SEPAR_FUNCS'
4532. ORDERBYline_no;
4533. spooloff
4534. @&&file_dic/temp_sql/separ_funcs.sql
4537. --生成创建包的sql语句，每个包放在单独的文件中
4538. spool&&file_dic/temp_sql/separ_pkgs.sql
4539. SELECTrtrim(sql_text)
4540. FROMtemp_for_pkg_gen_sql4000_temp
4541. WHEREOBJECT_TYPElike'%SEPAR_PKGS%'
4542. ORDERBYline_no;
4543. spooloff
4544. @&&file_dic/temp_sql/separ_pkgs.sql
4547. --生成创建存储过程的sql语句，每个包的声明与包体各放在单独的文件中
4548. spool&&file_dic/temp_sql/separ_hb_pkgs.sql
4549. SELECTrtrim(sql_text)
4550. FROMtemp_for_pkg_gen_sql4000_temp
4551. WHEREOBJECT_TYPEin('SEPAR_PKG_HEADS','SEPAR_PKG_BODYS')
4552. ORDERBYobject_name,OBJECT_TYPEdesc,line_no;
4553. spooloff
4554. @&&file_dic/temp_sql/separ_hb_pkgs.sql
4556. --生成创建触发器的sql语句，每个触发器放在单独的文件中
4557. spool&&file_dic/temp_sql/separ_tris.sql
4558. SELECTrtrim(sql_text)
4559. FROMtemp_for_pkg_gen_sql4000_temp
4560. WHEREOBJECT_TYPE='SEPAR_TRIS'
4561. ORDERBYline_no;
4562. spooloff
4563. @&&file_dic/temp_sql/separ_tris.sql
4566. setechoon
4567. setfeedbackon
4568. setnewpage1
4569. setpagesize500
4570. setlinesize80
4571. setverifyon
4572. settermon
4573. settrimson
4574. setlinesize600
4575. setheadingon
4576. settimingoff
4577. setverifyon
4578. setnumwidth15
4580. droptabletemp_for_pkg_gen_sql2000_temp;
4581. droptabletemp_for_pkg_gen_sql4000_temp;
4582. droppackagepkg_gen_user_sql;

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