[Home] [Help]
PACKAGE BODY: APPS.CN_UTILS
Source
1 PACKAGE BODY CN_UTILS AS
2 -- $Header: cnsyutlb.pls 120.6 2005/09/28 05:11:50 apink ship $
3
4
5
6 --+
7 -- Private constants (these would have been macros in C) */
8 --+
9
10 -- cr CONSTANT CHAR := CHR(10); -- carriage retur
11 --cr CONSTANT CHAR := '
12 -- '; -- carriage return
13
14 indent_by CONSTANT NUMBER := 2; -- # spaces to indent by
15
16 -- a constant which has a null value for passing to the insert_row
17 -- procedures of table handler APIs as the primary key value
18 null_id CONSTANT NUMBER := NULL;
19
20 g_org_id NUMBER;
21
22 null_org_id EXCEPTION;
23 --+
24 -- Private functions and procedures
25 --+
26
27 FUNCTION spaces (i NUMBER) RETURN VARCHAR2 IS
28 str VARCHAR2(200) := '';
29 j NUMBER;
30 BEGIN
31 -- hard code some common values of i
32 IF (i = 2) THEN
33 RETURN ' ';
34 ELSIF (i = 4) THEN
35 RETURN ' ';
36 ELSIF (i = 6) THEN
37 RETURN ' ';
38 ELSIF (i = 8) THEN
39 RETURN ' ';
40 ELSE
41 FOR j in 1..i LOOP
42 str := str || ' ';
43 END LOOP;
44 RETURN str;
45 END IF;
46 END spaces;
47
48
49 --+
50 -- Public Functions and Procedures
51 --+
52
53 PROCEDURE set_org_id(p_org_id IN NUMBER)
54 IS
55 BEGIN
56 g_org_id := p_org_id;
57 END;
58
59 PROCEDURE unset_org_id
60 IS
61 BEGIN
62 g_org_id := null;
63 END;
64
65 PROCEDURE delete_module (x_module_id cn_modules.module_id%TYPE,
66 x_package_spec_id cn_objects.object_id%TYPE,
67 x_package_body_id cn_objects.object_id%TYPE,
68 p_org_id IN NUMBER) IS
69 BEGIN
70 DELETE FROM cn_source_all
71 WHERE (object_id = x_package_spec_id
72 OR object_id = x_package_body_id)
73 AND org_id = p_org_id ;
74
75 END delete_module;
76
77
78 PROCEDURE init_code (
79 X_object_id cn_objects.object_id%TYPE,
80 code IN OUT NOCOPY cn_utils.code_type) IS
81 BEGIN
82 code.object_id := X_object_id;
83 code.line := 1;
84 code.indent := 0;
85 code.text := NULL;
86 END init_code;
87
88 -- for clob
89 PROCEDURE init_code (
90 X_object_id cn_objects.object_id%TYPE,
91 code IN OUT NOCOPY cn_utils.clob_code_type) IS
92 BEGIN
93
94 code.object_id := X_object_id;
95 code.line := 1;
96 code.indent := 0;
97 DBMS_LOB.FREETEMPORARY(code.text);
98 DBMS_LOB.CREATETEMPORARY(code.text,false,DBMS_LOB.CALL);
99
100 END init_code;
101 -- end clob
102
103
104 PROCEDURE indent ( code IN OUT NOCOPY cn_utils.code_type,
105 nesting_level NUMBER) IS
106 BEGIN
107 code.indent := code.indent + (nesting_level * indent_by);
108 END indent;
109
110
111 --for clob
112 PROCEDURE indent ( code IN OUT NOCOPY cn_utils.clob_code_type,
113 nesting_level NUMBER) IS
114 BEGIN
115 code.indent := code.indent + (nesting_level * indent_by);
116 END indent;
117 --end clob
118
119 PROCEDURE unindent ( code IN OUT NOCOPY cn_utils.code_type,
120 nesting_level NUMBER) IS
121 BEGIN
122 code.indent := code.indent - (nesting_level * indent_by);
123 END unindent;
124
125 --for clob
126 PROCEDURE unindent ( code IN OUT NOCOPY cn_utils.clob_code_type,
127 nesting_level NUMBER) IS
128 BEGIN
129
130 code.indent := code.indent - (nesting_level * indent_by);
131
132 END unindent;
133 --end clob
134
135 PROCEDURE append (code IN OUT NOCOPY cn_utils.code_type,
136 str2 VARCHAR2) IS
137 BEGIN
138 code.text := code.text || str2;
139 END append;
140
141 --for clob
142 PROCEDURE append (code IN OUT NOCOPY cn_utils.clob_code_type,
143 str2 VARCHAR2) IS
144 BEGIN
145
146 DBMS_LOB.WRITEAPPEND(code.text,length(str2),str2);
147
148 END append;
149 --end clob
150
151 --for clob
152 PROCEDURE append (code IN OUT NOCOPY cn_utils.clob_code_type,
153 expr clob,
154 str2 VARCHAR2) IS
155 BEGIN
156
157 DBMS_LOB.APPEND(code.text,expr);
158 DBMS_LOB.WRITEAPPEND(code.text,length(str2),str2);
159
160 END append;
161 --end clob
162
163
164
165
166 PROCEDURE appind (code IN OUT NOCOPY cn_utils.code_type,
167 str2 VARCHAR2) IS
168 BEGIN
169 code.text := code.text || spaces(code.indent) || str2;
170 END appind;
171
172
173 --for clob
174 PROCEDURE appind (code IN OUT NOCOPY cn_utils.clob_code_type,
175 str2 VARCHAR2) IS
176 BEGIN
177
178 DBMS_LOB.WRITEAPPEND(code.text,length(str2),str2);
179
180 END appind;
181 --end clob
182
183 PROCEDURE appendcr (code IN OUT NOCOPY cn_utils.code_type) IS
184 BEGIN
185 code.text := code.text || '
186 ';
187 cn_utils.dump_line( code );
188 END appendcr;
189
190 --for clob
191 PROCEDURE appendcr (code IN OUT NOCOPY cn_utils.clob_code_type) IS
192 line varchar2(32700);
193 BEGIN
194
195 line := '
196 ';
197
198 DBMS_LOB.WRITEAPPEND(code.text,length(line),line);
199 cn_utils.dump_line( code );
200
201 END appendcr;
202 --end clob
203
204 PROCEDURE appendcr (code IN OUT NOCOPY cn_utils.code_type,
205 str2 VARCHAR2) IS
206 BEGIN
207 code.text := code.text || str2 || '
208 ';
209 cn_utils.dump_line( code );
210 END appendcr;
211
212
213 --for clob
214 PROCEDURE appendcr (code IN OUT NOCOPY cn_utils.clob_code_type,
215 str2 VARCHAR2) IS
216 line varchar2(32700);
217 BEGIN
218
219 line := str2 || '
220 ';
221
222 DBMS_LOB.WRITEAPPEND(code.text,length(line),line);
223 cn_utils.dump_line( code );
224
225 END appendcr;
226 --end clob
227
228 PROCEDURE appindcr (code IN OUT NOCOPY cn_utils.code_type,
229 str2 VARCHAR2) IS
230 BEGIN
231 code.text := code.text || spaces(code.indent) || str2 || '
232 ';
233 cn_utils.dump_line( code );
234 END appindcr;
235
236 PROCEDURE appindcr (code IN OUT NOCOPY cn_utils.clob_code_type,
237 str2 VARCHAR2) IS
238 line varchar2(32700);
239 BEGIN
240
241 line := spaces(code.indent) || str2 || '
242 ';
243
244 DBMS_LOB.WRITEAPPEND(code.text,length(line),line);
245
246 cn_utils.dump_line( code );
247
248 END appindcr;
249
250 -- Note: STRIP works on the current line, before it is written out.
251 PROCEDURE strip (code IN OUT NOCOPY cn_utils.code_type, i NUMBER) IS
252 BEGIN
253 code.text := substr(code.text, 1, length(code.text) - i);
254 END strip;
255
256
257 -- AE 08-24-95
258 -- Note: STRIP_PREV works on the previous line, after it is written out.
259 PROCEDURE strip_prev (code IN OUT NOCOPY cn_utils.code_type,
260 i NUMBER) IS
261 BEGIN
262
263 code.line := code.line - 1 ; -- back up to prev line.
264 code.text := NULL ;
265
266 SELECT text
267 INTO code.text
268 FROM cn_source_all
269 WHERE object_id = code.object_id
270 AND line_no = code.line
271 AND org_id = g_org_id;
272
273 -- remove number of bytes requested plus 1 for the CR at the end.
274 code.text := substr(code.text, 1, length(code.text) - (i + 1));
275 code.text := code.text || '
276 ';
277
278 UPDATE cn_source_all
279 SET text = code.text
280 WHERE object_id = code.object_id
281 AND line_no = code.line
282 AND org_id = g_org_id;
283
284 -- Restore the line number to its original value.
285 code.line := code.line + 1 ;
286 code.text := NULL ;
287
288 END strip_prev;
289
290
291
292 PROCEDURE dump_line ( code IN OUT NOCOPY cn_utils.code_type) IS
293
294 l_pos NUMBER;
295 l_text VARCHAR2(32000);
296 l_text1 VARCHAR2(1900);
297 BEGIN
298
299 if (g_org_id is null) then
300 raise null_org_id;
301 end if;
302
303 IF length(code.text) > 1900 then
304 l_text := code.text;
305
306 LOOP
307 l_pos := instr(l_text,')');
308 l_text1 :=substr(l_text,1,l_pos);
309 INSERT into cn_source_all (source_id, object_id, line_no, text, org_id)
310 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line,l_text1, g_org_id);
311 l_text := substr(l_text, l_pos +1 );
312
313 IF nvl(l_pos,0) = 0 then
314 INSERT into cn_source_all (source_id, object_id, line_no, text, org_id)
315 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line,substr(l_text,l_pos), g_org_id);
316 EXIT;
317 END IF;
318
319 IF l_pos= 0 then
320 INSERT into cn_source_all (source_id, object_id, line_no, text, org_id)
321 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line,substr(l_text,l_pos), g_org_id);
322 EXIT;
323 END IF;
324
325 code.line := code.line + 1 ;
326
327 END LOOP;
328
329 code.text := NULL ;
330
331 ELSE
332
333 INSERT into cn_source (source_id, object_id, line_no, text, org_id)
334 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line, code.text, g_org_id );
335 code.text := NULL ;
336
337 END IF;
338
339 code.line := code.line + 1 ;
340
341 END dump_line;
342
343 --for clob
344 PROCEDURE dump_line (code IN OUT NOCOPY cn_utils.clob_code_type) IS
345
346 l_curr_pos NUMBER;
347 l_text VARCHAR2(32000);
348 l_text1 VARCHAR2(1900);
349 codelen number;
350 amount number;
351
352 l_clob_text clob;
353 l_prev_pos number;
354
355 BEGIN
356
357
358 if (g_org_id is null) then
359 raise null_org_id;
360 end if;
361
362 l_prev_pos := 1;
363 DBMS_LOB.CREATETEMPORARY(l_clob_text,FALSE,DBMS_LOB.CALL);
364
365 -- If the length of the expression is less than 1900 characters
366 -- the expression can be inserted directly into cn_source
367 -- otherwise need to find the close braces and insert the string
368 -- upto close bracess.
369 -- l_curr_pos - is used to store the no. of characters between the previously found
370 -- paranthesis and the newly found paranthesis
371 -- l_prev_pos - is used to store the previous position of previously found parantesis
372
373 IF DBMS_LOB.GETLENGTH(code.text) > 1900 then
374 DBMS_LOB.COPY(l_clob_text,code.text,DBMS_LOB.GETLENGTH(code.text),1,1);
375 LOOP
376
377 l_curr_pos := abs(DBMS_LOB.INSTR(l_clob_text,')',l_prev_pos) - l_prev_pos) + 1;
378
379 l_text1 :=DBMS_LOB.SUBSTR(l_clob_text,l_curr_pos,l_prev_pos);
380
381 INSERT into cn_source (source_id, object_id, line_no, text, org_id)
382 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line,l_text1, g_org_id);
383
384 DBMS_LOB.ERASE(l_clob_text,l_curr_pos,l_prev_pos);
385
386
387 code.line := code.line + 1 ;
388 l_prev_pos := l_prev_pos + l_curr_pos ;
389
390 IF nvl(abs(DBMS_LOB.INSTR(l_clob_text,')',l_prev_pos)),0) = 0 THEN
391 INSERT into cn_source (source_id, object_id, line_no, text, org_id)
392 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line,DBMS_LOB.SUBSTR(l_clob_text,DBMS_LOB.GETLENGTH(code.text)-l_prev_pos,l_prev_pos), g_org_id);
393 EXIT;
394 END IF;
395
396 END LOOP;
397
398 codelen := DBMS_LOB.GETLENGTH(code.text);
399
400 ELSE
401 codelen := DBMS_LOB.GETLENGTH(code.text);
402 DBMS_LOB.READ(code.text,codelen,1,l_text1);
403 INSERT into cn_source (source_id, object_id, line_no, text, org_id)
404 VALUES (cn_source_s.NEXTVAL, code.object_id, code.line, l_text1, g_org_id);
405 END IF;
406
407 DBMS_LOB.ERASE(code.text,codelen,1);
408 DBMS_LOB.FREETEMPORARY(l_clob_text);
409 DBMS_LOB.FREETEMPORARY(code.text);
410 DBMS_LOB.CREATETEMPORARY(code.text,FALSE,DBMS_LOB.CALL);
411
412 code.line := code.line + 1 ;
413
414 END dump_line;
415 --end clob
416 --
417 -- Procedure Name
418 -- record_process_start
419 -- Purpose
420 -- This procedure generates some text to record the start of a process
421 -- History
422 -- 17-NOV-93 Devesh Khatu Created
423 -- 22-MAR-94 Devesh Khatu Modified
424 --
425 PROCEDURE record_process_start (
426 audit_type VARCHAR2,
427 audit_desc VARCHAR2,
428 parent_audit_id VARCHAR2,
429 code IN OUT NOCOPY cn_utils.code_type) IS
430 BEGIN
431 cn_debug.print_msg('record_process_start>>', 1);
432
433 cn_utils.appindcr(code, 'x_proc_audit_id := NULL; -- Will get a value in the call below');
434 cn_utils.appindcr(code, 'cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, ' || parent_audit_id || ',');
435 cn_utils.appindcr(code, ' ''' || audit_type || ''',' || audit_desc || ', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL,'||g_org_id||');');
436 --AE cn_utils.appindcr(code, 'COMMIT;');
437 cn_utils.appendcr(code);
438
439 cn_debug.print_msg('record_process_start<<', 1);
440 END record_process_start;
441
442
443 --
444 -- Procedure Name
445 -- record_process_success
446 -- Purpose
447 -- Generates some boilerplate text to record success of the process
448 -- History
449 -- 22-MAR-94 Devesh Khatu Created
450 --
451 PROCEDURE record_process_success (
452 message VARCHAR2,
453 code IN OUT NOCOPY cn_utils.code_type) IS
454 BEGIN
455 cn_debug.print_msg('record_process_status>>', 1);
456 -- Generate code to record success of the process
457 cn_utils.appindcr(code, 'cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0, ' || message || ');');
458 --AE cn_utils.appindcr(code, 'COMMIT;');
459 cn_utils.appendcr(code);
460 cn_debug.print_msg('record_process_success<<', 1);
461 END record_process_success;
462
463
464 --
465 -- Procedure Name
466 -- record_process_exception
467 -- Purpose
468 -- Generates some boilerplate text to record exception of the process
469 -- History
470 -- 22-MAR-94 Devesh Khatu Created
471 --
472 PROCEDURE record_process_exception (
473 procedure_name cn_obj_procedures_v.name%TYPE,
474 savepoint_name VARCHAR2,
475 code IN OUT NOCOPY cn_utils.code_type) IS
476 BEGIN
477 -- Generate code to handle errors
478 cn_debug.print_msg('record_process_exception>>', 1);
479 cn_utils.unindent(code, 1);
480 cn_utils.appindcr(code, 'EXCEPTION');
481 cn_utils.indent(code, 1);
482 IF (savepoint_name IS NOT NULL) THEN
483 cn_utils.appindcr(code, 'WHEN OTHERS THEN ROLLBACK TO ' || savepoint_name || ';');
484 ELSE
485 --AE cn_utils.appindcr(code, 'WHEN OTHERS THEN ROLLBACK;');
486 cn_utils.appindcr(code, 'WHEN OTHERS THEN ');
487 END IF;
488 cn_utils.appindcr(code, 'cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, SQLCODE,');
489 cn_utils.appindcr(code, ' SQLERRM);');
490 --AE cn_utils.appindcr(code, 'COMMIT;');
491 cn_utils.appendcr(code);
492
493 cn_utils.appindcr(code, 'app_exception.raise_exception;'); --AE 04-28-95
494 cn_utils.appendcr(code);
495 END record_process_exception;
496
497 --for clob
498 --
499 -- Procedure Name
500 -- record_process_exception
501 -- Purpose
502 -- Generates some boilerplate text to record exception of the process
503 -- History
504 -- 22-MAR-94 Devesh Khatu Created
505 --
506 PROCEDURE record_process_exception (
507 procedure_name cn_obj_procedures_v.name%TYPE,
508 savepoint_name VARCHAR2,
509 code IN OUT NOCOPY cn_utils.clob_code_type) IS
510 BEGIN
511 -- Generate code to handle errors
512 cn_debug.print_msg('record_process_exception>>', 1);
513 cn_utils.unindent(code, 1);
514 cn_utils.appindcr(code, 'EXCEPTION');
515 cn_utils.indent(code, 1);
516 IF (savepoint_name IS NOT NULL) THEN
517 cn_utils.appindcr(code, 'WHEN OTHERS THEN ROLLBACK TO ' || savepoint_name || ';');
518 ELSE
519 --AE cn_utils.appindcr(code, 'WHEN OTHERS THEN ROLLBACK;');
520 cn_utils.appindcr(code, 'WHEN OTHERS THEN ');
521 END IF;
522 cn_utils.appindcr(code, 'cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, SQLCODE,');
523 cn_utils.appindcr(code, ' SQLERRM);');
524 --AE cn_utils.appindcr(code, 'COMMIT;');
525 cn_utils.appendcr(code);
526
527 cn_utils.appindcr(code, 'app_exception.raise_exception;'); --AE 04-28-95
528 cn_utils.appendcr(code);
529 END record_process_exception;
530 --end clob
531
532 PROCEDURE pkg_init_boilerplate (
533 code IN OUT NOCOPY cn_utils.code_type,
534 package_name cn_obj_packages_v.name%TYPE,
535 description cn_obj_packages_v.description%TYPE,
536 object_type VARCHAR2) IS
537
538 X_userid VARCHAR2(20);
539
540 BEGIN
541 cn_utils.appendcr(code, '-- +======================================================================+ --');
542 cn_utils.appendcr(code, '-- | Copyright (c) 1994 Oracle Corporation | --');
543 cn_utils.appendcr(code, '-- | Redwood Shores, California, USA | --');
544 cn_utils.appendcr(code, '-- | All rights reserved. | --');
545 cn_utils.appendcr(code, '-- +======================================================================+ --');
546 cn_utils.appendcr(code);
547
548 SELECT user INTO X_userid FROM sys.dual;
549
550 cn_utils.appendcr(code, '--');
551 cn_utils.appendcr(code, '-- Package Name');
552 cn_utils.appendcr(code, '-- ' || package_name);
553 cn_utils.appendcr(code, '-- Purpose');
554 cn_utils.appendcr(code, '-- ' || description);
555 cn_utils.appendcr(code, '-- History');
556 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
557 cn_utils.appendcr(code, '--');
558 cn_utils.appendcr(code);
559
560 cn_utils.appendcr(code, 'SET VERIFY OFF');
561 cn_utils.appendcr(code, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
562 cn_utils.appendcr(code, 'DEFINE PACKAGE_NAME="' || LOWER(package_name) || '"');
563
564 IF (object_type = 'PKS') THEN
565 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' || package_name || ' AS');
566 ELSE
567 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' || package_name || ' AS');
568 END IF;
569 cn_utils.appendcr(code);
570
571 END pkg_init_boilerplate;
572
573
574 --for clob
575 PROCEDURE pkg_init_boilerplate (
576 code IN OUT NOCOPY cn_utils.clob_code_type,
577 package_name cn_obj_packages_v.name%TYPE,
578 description cn_obj_packages_v.description%TYPE,
579 object_type VARCHAR2) IS
580 X_userid VARCHAR2(20);
581
582 BEGIN
583 cn_utils.appendcr(code, '-- +======================================================================+ --');
584 cn_utils.appendcr(code, '-- | Copyright (c) 1994 Oracle Corporation | --');
585 cn_utils.appendcr(code, '-- | Redwood Shores, California, USA | --');
586 cn_utils.appendcr(code, '-- | All rights reserved. | --');
587 cn_utils.appendcr(code, '-- +======================================================================+ --');
588 cn_utils.appendcr(code);
589
590 SELECT user INTO X_userid FROM sys.dual;
591
592 cn_utils.appendcr(code, '--');
593 cn_utils.appendcr(code, '-- Package Name');
594 cn_utils.appendcr(code, '-- ' || package_name);
595 cn_utils.appendcr(code, '-- Purpose');
596 cn_utils.appendcr(code, '-- ' || description);
597 cn_utils.appendcr(code, '-- History');
598 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
599 cn_utils.appendcr(code, '--');
600 cn_utils.appendcr(code);
601
602 cn_utils.appendcr(code, 'SET VERIFY OFF');
603 cn_utils.appendcr(code, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
604 cn_utils.appendcr(code, 'DEFINE PACKAGE_NAME="' || LOWER(package_name) || '"');
605
606 IF (object_type = 'PKS') THEN
607 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' || package_name || ' AS');
608 ELSE
609 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' || package_name || ' AS');
610 END IF;
611 cn_utils.appendcr(code);
612
613 END pkg_init_boilerplate;
614
615 --end clob
616
617
618 PROCEDURE pkg_end_boilerplate (
619 code IN OUT NOCOPY cn_utils.code_type,
620 object_type cn_obj_packages_v.object_type%TYPE) IS
621
622 BEGIN
623 cn_utils.appendcr(code, 'END &' || 'PACKAGE_NAME;');
624 cn_utils.appendcr(code, '/');
625 cn_utils.appendcr(code);
626 IF (object_type = 'PKS') THEN
627 cn_utils.appendcr(code, 'SHOW ERRORS PACKAGE &' || 'PACKAGE_NAME');
628 ELSE
629 cn_utils.appendcr(code, 'SHOW ERRORS PACKAGE BODY &' || 'PACKAGE_NAME');
630 END IF;
631 cn_utils.appendcr(code, 'SELECT TEXT ');
632 cn_utils.appendcr(code, ' FROM USER_ERRORS');
633
634 IF (object_type = 'PKS') THEN
635 cn_utils.appendcr(code, ' WHERE TYPE = ''PACKAGE''');
636 ELSE
637 cn_utils.appendcr(code, ' WHERE TYPE = ''PACKAGE BODY''');
638 END IF;
639 cn_utils.appendcr(code, ' AND NAME = UPPER(''&' || 'PACKAGE_NAME'');');
640 cn_utils.appendcr(code);
641 cn_utils.appendcr(code, 'COMMIT;');
642 cn_utils.appendcr(code, '-- EXIT;'); --AE 03-22-96
643 cn_utils.appendcr(code);
644
645 END pkg_end_boilerplate;
646
647 --for clob
648 PROCEDURE pkg_end_boilerplate (
649 code IN OUT NOCOPY cn_utils.clob_code_type,
650 object_type cn_obj_packages_v.object_type%TYPE) IS
651
652 BEGIN
653 cn_utils.appendcr(code, 'END &' || 'PACKAGE_NAME;');
654 cn_utils.appendcr(code, '/');
655 cn_utils.appendcr(code);
656 IF (object_type = 'PKS') THEN
657 cn_utils.appendcr(code, 'SHOW ERRORS PACKAGE &' || 'PACKAGE_NAME');
658 ELSE
659 cn_utils.appendcr(code, 'SHOW ERRORS PACKAGE BODY &' || 'PACKAGE_NAME');
660 END IF;
661 cn_utils.appendcr(code, 'SELECT TEXT ');
662 cn_utils.appendcr(code, ' FROM USER_ERRORS');
663
664 IF (object_type = 'PKS') THEN
665 cn_utils.appendcr(code, ' WHERE TYPE = ''PACKAGE''');
666 ELSE
667 cn_utils.appendcr(code, ' WHERE TYPE = ''PACKAGE BODY''');
668 END IF;
669 cn_utils.appendcr(code, ' AND NAME = UPPER(''&' || 'PACKAGE_NAME'');');
670 cn_utils.appendcr(code);
671 cn_utils.appendcr(code, 'COMMIT;');
672 cn_utils.appendcr(code, '-- EXIT;'); --AE 03-22-96
673 cn_utils.appendcr(code);
674
675 END pkg_end_boilerplate;
676 --end clob
677
678 PROCEDURE pkg_init (
679 module_id cn_modules.module_id%TYPE,
680 package_name cn_obj_packages_v.name%TYPE,
681 package_org_append VARCHAR2,
682 package_type cn_obj_packages_v.package_type%TYPE,
683 package_spec_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
684 package_body_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
685 package_spec_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
686 package_body_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
687
688 spec_code IN OUT NOCOPY cn_utils.code_type,
689 body_code IN OUT NOCOPY cn_utils.code_type) IS
690
691 x_rowid ROWID;
692 null_id NUMBER;
693
694 BEGIN
695
696 if (g_org_id is null) then
697 raise null_org_id;
698 end if;
699
700 -- Find the package objects AE 01-08-96
701 cn_utils.find_object(package_name,'PKS',package_spec_id, package_spec_desc, g_org_id);
702 cn_utils.find_object(package_name,'PKB',package_body_id, package_body_desc, g_org_id);
703
704 -- Delete module source code from cn_source
705 -- Delete module object dependencies for this module
706 cn_utils.delete_module(module_id, package_spec_id, package_body_id, g_org_id);
707
708 cn_utils.init_code (package_spec_id, spec_code); -- AE 05-02-95
709 cn_utils.init_code (package_body_id, body_code); -- AE 05-02-95
710
711 cn_utils.pkg_init_boilerplate(spec_code, package_name|| package_org_append, package_spec_desc, 'PKS');
712 cn_utils.pkg_init_boilerplate(body_code, package_name|| package_org_append, package_body_desc, 'PKB');
713
714 cn_utils.indent(spec_code, 1);
715 cn_utils.indent(body_code, 1);
716
717 END pkg_init;
718
719
720 PROCEDURE pkg_end (
721 package_name cn_obj_packages_v.name%TYPE,
722 package_spec_id cn_obj_packages_v.package_id%TYPE,
723 package_body_id cn_obj_packages_v.package_id%TYPE,
724 spec_code IN OUT NOCOPY cn_utils.code_type,
725 body_code IN OUT NOCOPY cn_utils.code_type) IS
726
727 BEGIN
728 cn_utils.unindent(spec_code, 1);
729 cn_utils.unindent(body_code, 1);
730
731 cn_utils.pkg_end_boilerplate(spec_code, 'PKS'); --AE 04-28-95
732 cn_utils.pkg_end_boilerplate(body_code, 'PKB'); --AE 04-28-95
733
734 END pkg_end;
735
736 --for clob
737 PROCEDURE pkg_end (
738 package_name cn_obj_packages_v.name%TYPE,
739 package_spec_id cn_obj_packages_v.package_id%TYPE,
740 package_body_id cn_obj_packages_v.package_id%TYPE,
741 spec_code IN OUT NOCOPY cn_utils.clob_code_type,
742 body_code IN OUT NOCOPY cn_utils.clob_code_type) IS
743
744 BEGIN
745 cn_utils.unindent(spec_code, 1);
746 cn_utils.unindent(body_code, 1);
747
748 cn_utils.pkg_end_boilerplate(spec_code, 'PKS'); --AE 04-28-95
749 cn_utils.pkg_end_boilerplate(body_code, 'PKB'); --AE 04-28-95
750
751 END pkg_end;
752 --end clob
753
754
755 -- overloaded for use in formula generation
756 PROCEDURE pkg_init_boilerplate (code IN OUT NOCOPY cn_utils.code_type,
757 package_name cn_obj_packages_v.name%TYPE,
758 description cn_obj_packages_v.description%TYPE,
759 object_type VARCHAR2,
760 package_flag VARCHAR2 ) IS
761
762 X_userid VARCHAR2(20);
763 BEGIN
764 cn_utils.appendcr(code, '-- +======================================================================+ --');
765 cn_utils.appendcr(code, '-- | Copyright (c) 1994 Oracle Corporation | --');
766 cn_utils.appendcr(code, '-- | Redwood Shores, California, USA | --');
767 cn_utils.appendcr(code, '-- | All rights reserved. | --');
768 cn_utils.appendcr(code, '-- +======================================================================+ --');
769 cn_utils.appendcr(code);
770
771 SELECT user INTO X_userid FROM sys.dual;
772
773 cn_utils.appendcr(code, '--');
774 cn_utils.appendcr(code, '-- Package Name');
775 cn_utils.appendcr(code, '-- ' || package_name);
776 cn_utils.appendcr(code, '-- Purpose');
777 cn_utils.appendcr(code, '-- ' || description);
778 cn_utils.appendcr(code, '-- History');
779 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
780 cn_utils.appendcr(code, '--');
781 cn_utils.appendcr(code);
782
783 IF (object_type = 'PKS') THEN
784 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' || package_name || ' AS');
785 ELSE
786 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' || package_name || ' AS');
787 END IF;
788 cn_utils.appendcr(code);
789
790 END pkg_init_boilerplate;
791
792 --for clob
793
794 -- overloaded for use in formula generation
795 PROCEDURE pkg_init_boilerplate (code IN OUT NOCOPY cn_utils.clob_code_type,
796 package_name cn_obj_packages_v.name%TYPE,
797 description cn_obj_packages_v.description%TYPE,
798 object_type VARCHAR2,
799 package_flag VARCHAR2 ) IS
800
801 X_userid VARCHAR2(20);
802 BEGIN
803 cn_utils.appendcr(code, '-- +======================================================================+ --');
804 cn_utils.appendcr(code, '-- | Copyright (c) 1994 Oracle Corporation | --');
805 cn_utils.appendcr(code, '-- | Redwood Shores, California, USA | --');
806 cn_utils.appendcr(code, '-- | All rights reserved. | --');
807 cn_utils.appendcr(code, '-- +======================================================================+ --');
808 cn_utils.appendcr(code);
809
810 SELECT user INTO X_userid FROM sys.dual;
811
812 cn_utils.appendcr(code, '--');
813 cn_utils.appendcr(code, '-- Package Name');
814 cn_utils.appendcr(code, '-- ' || package_name);
815 cn_utils.appendcr(code, '-- Purpose');
816 cn_utils.appendcr(code, '-- ' || description);
817 cn_utils.appendcr(code, '-- History');
818 cn_utils.appendcr(code, '-- ' || SYSDATE || ' ' || X_userid || ' Created');
819 cn_utils.appendcr(code, '--');
820 cn_utils.appendcr(code);
821
822 IF (object_type = 'PKS') THEN
823 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' || package_name || ' AS');
824 ELSE
825 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' || package_name || ' AS');
826 END IF;
827 cn_utils.appendcr(code);
828
829 END pkg_init_boilerplate;
830
831
832 --end clob
833
834 -- overloaded for use in formula generation, adding package_flag = 'FORMULA'
835 PROCEDURE pkg_init (
836 module_id cn_modules.module_id%TYPE,
837 package_name cn_obj_packages_v.name%TYPE,
838 package_org_append VARCHAR2,
839 package_type cn_obj_packages_v.package_type%TYPE,
840 package_flag VARCHAR2,
841 package_spec_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
842 package_body_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
843 package_spec_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
844 package_body_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
845 spec_code IN OUT NOCOPY cn_utils.code_type,
846 body_code IN OUT NOCOPY cn_utils.code_type) IS
847
848 x_rowid ROWID;
849 null_id NUMBER;
850
851 BEGIN
852
853 if (g_org_id is null) then
854 raise null_org_id;
855 end if;
856
857 -- Find the package objects AE 01-08-96
858 cn_utils.find_object(package_name,'PKS',package_spec_id, package_spec_desc, g_org_id);
859 cn_utils.find_object(package_name,'PKB',package_body_id, package_body_desc, g_org_id);
860
861 -- Delete module source code from cn_source
862 -- Delete module object dependencies for this module
863 cn_utils.delete_module(module_id, package_spec_id, package_body_id, g_org_id);
864
865 cn_utils.init_code (package_spec_id, spec_code); -- AE 05-02-95
866 cn_utils.init_code (package_body_id, body_code); -- AE 05-02-95
867
868 cn_utils.pkg_init_boilerplate(spec_code, package_name, package_spec_desc, 'PKS', package_flag);
869 cn_utils.pkg_init_boilerplate(body_code, package_name, package_body_desc, 'PKB', package_flag);
870
871 cn_utils.indent(spec_code, 1);
872 cn_utils.indent(body_code, 1);
873
874 END pkg_init;
875
876 --for clob
877 -- overloaded for use in formula generation, adding package_flag = 'FORMULA'
878 PROCEDURE pkg_init (
879 module_id cn_modules.module_id%TYPE,
880 package_name cn_obj_packages_v.name%TYPE,
881 package_org_append VARCHAR2,
882 package_type cn_obj_packages_v.package_type%TYPE,
883 package_flag VARCHAR2,
884 package_spec_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
885 package_body_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
886 package_spec_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
887 package_body_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
888 spec_code IN OUT NOCOPY cn_utils.clob_code_type,
889 body_code IN OUT NOCOPY cn_utils.clob_code_type) IS
890
891 x_rowid ROWID;
892 null_id NUMBER;
893
894 BEGIN
895
896 if (g_org_id is null) then
897 raise null_org_id;
898 end if;
899
900 -- Find the package objects AE 01-08-96
901 cn_utils.find_object(package_name,'PKS',package_spec_id, package_spec_desc, g_org_id);
902 cn_utils.find_object(package_name,'PKB',package_body_id, package_body_desc, g_org_id);
903
904 -- Delete module source code from cn_source
905 -- Delete module object dependencies for this module
906 cn_utils.delete_module(module_id, package_spec_id, package_body_id, g_org_id);
907
908 cn_utils.init_code (package_spec_id, spec_code); -- AE 05-02-95
909 cn_utils.init_code (package_body_id, body_code); -- AE 05-02-95
910
911 cn_utils.pkg_init_boilerplate(spec_code, package_name, package_spec_desc, 'PKS', package_flag);
912 cn_utils.pkg_init_boilerplate(body_code, package_name, package_body_desc, 'PKB', package_flag);
913
914 cn_utils.indent(spec_code, 1);
915 cn_utils.indent(body_code, 1);
916
917 END pkg_init;
918 --end clob
919
920
921 -- overloaded procedure used for formula generation
922 PROCEDURE pkg_end_boilerplate (code IN OUT NOCOPY cn_utils.code_type,
923 package_name cn_obj_packages_v.name%TYPE,
924 object_type cn_obj_packages_v.object_type%TYPE) IS
925
926 BEGIN
927 cn_utils.appendcr(code, 'END ' || package_name || ' ;');
928 cn_utils.appendcr(code);
929 END pkg_end_boilerplate;
930
931 --for clob
932 PROCEDURE pkg_end_boilerplate (code IN OUT NOCOPY cn_utils.clob_code_type,
933 package_name cn_obj_packages_v.name%TYPE,
934 object_type cn_obj_packages_v.object_type%TYPE) IS
935
936 BEGIN
937 cn_utils.appendcr(code, 'END ' || package_name || ' ;');
938 cn_utils.appendcr(code);
939 END pkg_end_boilerplate;
940 --end clob
941
942 -- overloaded procedure for use in formula generation
943 PROCEDURE pkg_end (package_name cn_obj_packages_v.name%TYPE,
944 spec_code IN OUT NOCOPY cn_utils.code_type,
945 body_code IN OUT NOCOPY cn_utils.code_type) IS
946
947 BEGIN
948 cn_utils.unindent(spec_code, 1);
949 cn_utils.unindent(body_code, 1);
950
951 cn_utils.pkg_end_boilerplate(spec_code, package_name,'PKS');
952 cn_utils.pkg_end_boilerplate(body_code, package_name,'PKB');
953 END pkg_end;
954
955 --for clob
956 -- overloaded procedure for use in formula generation
957 PROCEDURE pkg_end (package_name cn_obj_packages_v.name%TYPE,
958 spec_code IN OUT NOCOPY cn_utils.clob_code_type,
959 body_code IN OUT NOCOPY cn_utils.clob_code_type) IS
960
961 BEGIN
962 cn_utils.unindent(spec_code, 1);
963 cn_utils.unindent(body_code, 1);
964
965 cn_utils.pkg_end_boilerplate(spec_code, package_name,'PKS');
966 cn_utils.pkg_end_boilerplate(body_code, package_name,'PKB');
967 END pkg_end;
968
969 --end clob
970
971
972 PROCEDURE proc_init_boilerplate (
973 code IN OUT NOCOPY cn_utils.code_type,
974 procedure_name cn_obj_procedures_v.name%TYPE,
975 description cn_obj_procedures_v.description%TYPE) IS
976
977 X_userid VARCHAR2(20);
978
979 BEGIN
980 SELECT user INTO X_userid FROM sys.dual;
981
982 cn_utils.appendcr(code, '--');
983 cn_utils.appendcr(code, '-- Procedure Name');
984 cn_utils.appendcr(code, '-- ' || procedure_name);
985 cn_utils.appendcr(code, '-- Purpose');
986 cn_utils.appendcr(code, '-- ' || description);
987 cn_utils.appendcr(code, '-- History');
988 cn_utils.appendcr(code, '-- ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI') || ' ' || X_userid || ' Created');
989 cn_utils.appendcr(code, '--');
990
991 END proc_init_boilerplate;
992
993 --for clob
994 PROCEDURE proc_init_boilerplate (
995 code IN OUT NOCOPY cn_utils.clob_code_type,
996 procedure_name cn_obj_procedures_v.name%TYPE,
997 description cn_obj_procedures_v.description%TYPE) IS
998
999 X_userid VARCHAR2(20);
1000
1001 BEGIN
1002 SELECT user INTO X_userid FROM sys.dual;
1003
1004 cn_utils.appendcr(code, '--');
1005 cn_utils.appendcr(code, '-- Procedure Name');
1006 cn_utils.appendcr(code, '-- ' || procedure_name);
1007 cn_utils.appendcr(code, '-- Purpose');
1008 cn_utils.appendcr(code, '-- ' || description);
1009 cn_utils.appendcr(code, '-- History');
1010 cn_utils.appendcr(code, '-- ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI') || ' ' || X_userid || ' Created');
1011 cn_utils.appendcr(code, '--');
1012
1013 END proc_init_boilerplate;
1014 --end clob
1015
1016 PROCEDURE proc_init (
1017 procedure_name cn_obj_procedures_v.name%TYPE,
1018 description cn_obj_procedures_v.description%TYPE,
1019 parameter_list cn_obj_procedures_v.parameter_list%TYPE,
1020 procedure_type cn_obj_procedures_v.procedure_type%TYPE,
1021 return_type cn_obj_procedures_v.return_type%TYPE,
1022 package_id cn_obj_procedures_v.package_id%TYPE,
1023 repository_id cn_obj_procedures_v.repository_id%TYPE,
1024 spec_code IN OUT NOCOPY cn_utils.code_type,
1025 body_code IN OUT NOCOPY cn_utils.code_type) IS
1026
1027 X_rowid ROWID;
1028
1029 BEGIN
1030 cn_debug.print_msg('proc_init>>', 1);
1031
1032 -- Generate boilerplate comments
1033 cn_utils.proc_init_boilerplate(spec_code, procedure_name, description);
1034 cn_utils.proc_init_boilerplate(body_code, procedure_name, description);
1035
1036 -- Generate procedure header and parameters in both spec and body
1037 IF (procedure_type = 'P') THEN
1038 cn_utils.appind(spec_code, 'PROCEDURE ' || procedure_name);
1039 cn_utils.appind(body_code, 'PROCEDURE ' || procedure_name);
1040 ELSIF (procedure_type = 'F') THEN
1041 cn_utils.appind(spec_code, 'FUNCTION ' || procedure_name);
1042 cn_utils.appind(body_code, 'FUNCTION ' || procedure_name);
1043 END IF;
1044
1045 IF (parameter_list IS NOT NULL) THEN
1046 cn_utils.append(spec_code, ' (' || parameter_list || ')');
1047 cn_utils.append(body_code, ' (' || parameter_list || ')');
1048 END IF;
1049
1050 IF (procedure_type = 'F') THEN
1051 cn_utils.append(spec_code, ' RETURN ' || return_type);
1052 cn_utils.append(body_code, ' RETURN ' || return_type);
1053 END IF;
1054
1055 cn_utils.appendcr(spec_code, ';');
1056 cn_utils.appendcr(spec_code);
1057 cn_utils.appendcr(body_code, ' IS');
1058
1059 cn_debug.print_msg('proc_init<<', 1);
1060 END proc_init;
1061
1062 --for clob
1063 PROCEDURE proc_init (
1064 procedure_name cn_obj_procedures_v.name%TYPE,
1065 description cn_obj_procedures_v.description%TYPE,
1066 parameter_list cn_obj_procedures_v.parameter_list%TYPE,
1067 procedure_type cn_obj_procedures_v.procedure_type%TYPE,
1068 return_type cn_obj_procedures_v.return_type%TYPE,
1069 package_id cn_obj_procedures_v.package_id%TYPE,
1070 repository_id cn_obj_procedures_v.repository_id%TYPE,
1071 spec_code IN OUT NOCOPY cn_utils.clob_code_type,
1072 body_code IN OUT NOCOPY cn_utils.clob_code_type) IS
1073
1074 X_rowid ROWID;
1075
1076 BEGIN
1077 cn_debug.print_msg('proc_init>>', 1);
1078
1079 -- Generate boilerplate comments
1080 cn_utils.proc_init_boilerplate(spec_code, procedure_name, description);
1081 cn_utils.proc_init_boilerplate(body_code, procedure_name, description);
1082
1083 -- Generate procedure header and parameters in both spec and body
1084 IF (procedure_type = 'P') THEN
1085 cn_utils.appind(spec_code, 'PROCEDURE ' || procedure_name);
1086 cn_utils.appind(body_code, 'PROCEDURE ' || procedure_name);
1087 ELSIF (procedure_type = 'F') THEN
1088 cn_utils.appind(spec_code, 'FUNCTION ' || procedure_name);
1089 cn_utils.appind(body_code, 'FUNCTION ' || procedure_name);
1090 END IF;
1091
1092 IF (parameter_list IS NOT NULL) THEN
1093 cn_utils.append(spec_code, ' (' || parameter_list || ')');
1094 cn_utils.append(body_code, ' (' || parameter_list || ')');
1095 END IF;
1096
1097 IF (procedure_type = 'F') THEN
1098 cn_utils.append(spec_code, ' RETURN ' || return_type);
1099 cn_utils.append(body_code, ' RETURN ' || return_type);
1100 END IF;
1101
1102 cn_utils.appendcr(spec_code, ';');
1103 cn_utils.appendcr(spec_code);
1104 cn_utils.appendcr(body_code, ' IS');
1105
1106 cn_debug.print_msg('proc_init<<', 1);
1107 END proc_init;
1108
1109 --end clob
1110
1111
1112 PROCEDURE proc_begin (
1113 procedure_name cn_obj_procedures_v.name%TYPE,
1114 generate_debug_pipe VARCHAR2,
1115 code IN OUT NOCOPY cn_utils.code_type) IS
1116
1117 BEGIN
1118 cn_debug.print_msg('proc_begin>>', 1);
1119
1120 -- Generate begin procedure statement
1121 cn_utils.appindcr(code, 'BEGIN');
1122 cn_utils.indent(code, 1);
1123
1124 IF (generate_debug_pipe = 'Y') THEN
1125 -- Generate code to initialize a debug pipe if a pipename has been specified
1126 cn_utils.appindcr(code, 'IF (debug_pipe IS NOT NULL) THEN');
1127 cn_utils.appindcr(code, ' cn_debug.init_pipe(debug_pipe, debug_level);');
1128 cn_utils.appindcr(code, 'END IF;');
1129 END IF;
1130
1131 cn_utils.appendcr(code);
1132
1133 cn_debug.print_msg('proc_begin<<', 1);
1134 END proc_begin;
1135
1136 --for clob
1137 PROCEDURE proc_begin (
1138 procedure_name cn_obj_procedures_v.name%TYPE,
1139 generate_debug_pipe VARCHAR2,
1140 code IN OUT NOCOPY cn_utils.clob_code_type) IS
1141 BEGIN
1142 cn_debug.print_msg('proc_begin>>', 1);
1143
1144 -- Generate begin procedure statement
1145 cn_utils.appindcr(code, 'BEGIN');
1146 cn_utils.indent(code, 1);
1147
1148 IF (generate_debug_pipe = 'Y') THEN
1149 -- Generate code to initialize a debug pipe if a pipename has been specified
1150 cn_utils.appindcr(code, 'IF (debug_pipe IS NOT NULL) THEN');
1151 cn_utils.appindcr(code, ' cn_debug.init_pipe(debug_pipe, debug_level);');
1152 cn_utils.appindcr(code, 'END IF;');
1153 END IF;
1154
1155 cn_utils.appendcr(code);
1156
1157 cn_debug.print_msg('proc_begin<<', 1);
1158 END proc_begin;
1159 --end clob
1160
1161
1162 PROCEDURE proc_end (
1163 procedure_name cn_obj_procedures_v.name%TYPE,
1164 exception_flag VARCHAR2,
1165 code IN OUT NOCOPY cn_utils.code_type) IS
1166
1167 BEGIN
1168 cn_debug.print_msg('proc_end>>', 1);
1169
1170 cn_utils.appendcr(code);
1171 cn_utils.appendcr(code);
1172 IF (exception_flag = 'Y') THEN
1173 cn_utils.record_process_exception(procedure_name, NULL, code);
1174 END IF;
1175
1176 -- Generate end of procedure statement
1177 cn_utils.appendcr(code);
1178 cn_utils.unindent(code, 1);
1179 cn_utils.appindcr(code, 'END ' || procedure_name || ';');
1180 cn_utils.appendcr(code);
1181
1182 cn_debug.print_msg('proc_end<<', 1);
1183 END proc_end;
1184
1185 --for clob
1186 PROCEDURE proc_end (
1187 procedure_name cn_obj_procedures_v.name%TYPE,
1188 exception_flag VARCHAR2,
1189 code IN OUT NOCOPY cn_utils.clob_code_type) IS
1190
1191 BEGIN
1192 cn_debug.print_msg('proc_end>>', 1);
1193
1194 cn_utils.appendcr(code);
1195 cn_utils.appendcr(code);
1196 IF (exception_flag = 'Y') THEN
1197 cn_utils.record_process_exception(procedure_name, NULL, code);
1198 END IF;
1199
1200 -- Generate end of procedure statement
1201 cn_utils.appendcr(code);
1202 cn_utils.unindent(code, 1);
1203 cn_utils.appindcr(code, 'END ' || procedure_name || ';');
1204 cn_utils.appendcr(code);
1205
1206 cn_debug.print_msg('proc_end<<', 1);
1207 END proc_end;
1208
1209 --end clob
1210
1211 -- AE 05-02-95. gen_create_xxxxxx procedures commented out. not used.
1212 /*
1213 --+
1214 -- Procedure Name
1215 -- gen_create_table
1216 -- Purpose
1217 -- generates the code for creating a table
1218 -- History
1219 -- 18-NOV-93 Devesh Khatu Created
1220 --+
1221 PROCEDURE gen_create_table (
1222 X_table_id cn_obj_tables_v.table_id%TYPE,
1223 code IN OUT NOCOPY cn_utils.code_type) IS
1224
1225 -- Declare cursor for getting columns that need to be created
1226 CURSOR columns_cursor IS
1227 SELECT cocv.name column_name, data_type, data_length, data_scale,
1228 primary_key, nullable
1229 FROM cn_obj_columns_v cocv
1230 WHERE cocv.table_id = X_table_id
1231 ORDER BY cocv.position;
1232
1233 table_name cn_obj_tables_v.name%TYPE;
1234
1235 BEGIN
1236 cn_debug.print_msg('gen_create_table>>', 1);
1237
1238 table_name := cn_utils.get_object_name(X_table_id);
1239
1240 cn_utils.init_code( X_table_id, code);
1241
1242 appendcr(code, 'CREATE TABLE ' || LOWER(table_name) || ' (');
1243 FOR c IN columns_cursor LOOP
1244 cn_utils.append(code, ' ' || LOWER(c.column_name) || ' ' || UPPER(c.data_type));
1245 IF (c.data_length IS NOT NULL) THEN
1246 IF (c.data_scale IS NOT NULL) THEN
1247 cn_utils.append(code, '(' || c.data_length || ',' || c.data_scale || ') ');
1248 ELSE
1249 cn_utils.append(code, '(' || c.data_length || ') ');
1250 END IF;
1251 END IF;
1252 IF (c.primary_key = 'Y') THEN
1253 cn_utils.append(code, ' PRIMARY KEY');
1254 END IF;
1255 IF (c.nullable = 'N') THEN
1256 cn_utils.appendcr(code, ' NOT NULL,');
1257 ELSE
1258 cn_utils.appendcr(code, ' NULL,');
1259 END IF;
1260 END LOOP;
1261 IF (SQL%FOUND) THEN
1262 strip(code, 2); -- remove trailing comma
1263 END IF;
1264 cn_utils.appendcr(code, ')');
1265
1266 -- Note: How does one get storage parameters for the table?
1267 cn_debug.print_msg('gen_create_table<<', 1);
1268 END gen_create_table;
1269
1270
1271 --+
1272 -- Procedure Name
1273 -- gen_create_index
1274 -- Purpose
1275 -- generates the code for creating an index
1276 -- History
1277 -- 02-FEB-94 Devesh Khatu Created
1278 --+
1279 PROCEDURE gen_create_index (
1280 X_index_id cn_obj_indexes_v.index_id%TYPE,
1281 code IN OUT NOCOPY cn_utils.code_type) IS
1282
1283 CURSOR index_columns IS
1284 SELECT cocv.name column_name
1285 FROM cn_obj_columns_v cocv, cn_column_ind_maps ccim
1286 WHERE cocv.column_id = ccim.column_id
1287 AND ccim.index_id = X_index_id;
1288 X_name cn_obj_indexes_v.name%TYPE;
1289 X_table_name cn_obj_tables_v.name%TYPE;
1290 X_unique_flag cn_obj_indexes_v.unique_flag%TYPE;
1291
1292 BEGIN
1293 cn_debug.print_msg('gen_create_index>>', 1);
1294
1295 SELECT coiv.name, cotv.name, unique_flag
1296 INTO X_name, X_table_name, X_unique_flag
1297 FROM cn_obj_indexes_v coiv, cn_obj_tables_v cotv
1298 WHERE cotv.table_id = coiv.table_id
1299 AND coiv.index_id = X_index_id;
1300
1301 cn_utils.init_code( X_index_id, code);
1302 cn_utils.appind(code, 'CREATE ');
1303 IF (X_unique_flag = 'Y') THEN
1304 cn_utils.appind(code, 'UNIQUE ');
1305 END IF;
1306 cn_utils.appindcr(code, 'INDEX ' || LOWER(X_name) || ' ON ' || LOWER(X_table_name) || '(');
1307 FOR ic IN index_columns LOOP
1308 cn_utils.appindcr(code, LOWER(ic.column_name) || ',');
1309 END LOOP;
1310 cn_utils.strip(code, 2); -- remove trailing comma
1311 cn_utils.appindcr(code, ')');
1312 -- Shouldn't the next statement be customizable? -- Devesh
1313 cn_utils.appindcr(code, 'PCTFREE 10');
1314
1315 cn_debug.print_msg('gen_create_index<<', 1);
1316 END gen_create_index;
1317
1318
1319 --+
1320 -- Procedure Name
1321 -- gen_create_sequence
1322 -- Purpose
1323 -- generates the code for creating a sequence
1324 -- History
1325 -- 02-FEB-94 Devesh Khatu Created
1326 --+
1327 PROCEDURE gen_create_sequence (
1328 X_sequence_id cn_obj_sequences_v.sequence_id%TYPE,
1329 code IN OUT NOCOPY cn_utils.code_type) IS
1330 X_name cn_obj_sequences_v.name%TYPE;
1331 X_start_value cn_obj_sequences_v.start_value%TYPE;
1332 X_increment_value cn_obj_sequences_v.increment_value%TYPE;
1333 BEGIN
1334 cn_debug.print_msg('gen_create_sequence>>', 1);
1335
1336 SELECT name, start_value, increment_value
1337 INTO X_name, X_start_value, X_increment_value
1338 FROM cn_obj_sequences_v cosv
1339 WHERE cosv.sequence_id = X_sequence_id;
1340
1341 cn_utils.init_code( X_sequence_id, code);
1342 cn_utils.appindcr(code, 'CREATE SEQUENCE ' || LOWER(X_name));
1343 cn_utils.indent(code, 1);
1344
1345 IF (X_increment_value IS NOT NULL) THEN
1346 cn_utils.appindcr(code, 'INCREMENT BY ' || X_increment_value);
1347 END IF;
1348 IF (X_start_value IS NOT NULL) THEN
1349 cn_utils.appindcr(code, 'START WITH ' || X_start_value);
1350 END IF;
1351
1352 cn_utils.appindcr(code, 'NOMINVALUE');
1353 cn_utils.appindcr(code, 'NOMAXVALUE');
1354 cn_utils.appindcr(code, 'NOCYCLE');
1355 cn_utils.appindcr(code, 'CACHE 20');
1356 cn_utils.appindcr(code, 'NOORDER');
1357 cn_utils.unindent(code, 1);
1358
1359 cn_debug.print_msg('gen_create_sequence<<', 1);
1360 END gen_create_sequence;
1361
1362
1363 --+
1364 -- Procedure Name
1365 -- gen_create_dblink
1366 -- Purpose
1367 -- generates the code for creating a dblink
1368 -- History
1369 -- 11-JUN-94 Devesh Khatu Created
1370 --+
1371 PROCEDURE gen_create_dblink (
1372 X_dblink_id cn_obj_dblinks_v.dblink_id%TYPE,
1373 code IN OUT NOCOPY cn_utils.code_type) IS
1374 X_name cn_obj_dblinks_v.name%TYPE;
1375 X_connect_to_username cn_obj_dblinks_v.connect_to_username%TYPE;
1376 X_connect_to_password cn_obj_dblinks_v.connect_to_password%TYPE;
1377 X_connect_to_host cn_obj_dblinks_v.connect_to_host%TYPE;
1378 BEGIN
1379 cn_debug.print_msg('gen_create_dblink>>', 1);
1380
1381 SELECT name, connect_to_username, connect_to_password, connect_to_host
1382 INTO X_name, X_connect_to_username, X_connect_to_password, X_connect_to_host
1383 FROM cn_obj_dblinks_v codv
1384 WHERE codv.dblink_id = X_dblink_id;
1385
1386 cn_utils.init_code( X_dblink_id, code);
1387 cn_utils.appindcr(code, 'CREATE DATABASE LINK ' || LOWER(X_name));
1388 cn_utils.indent(code, 1);
1389 cn_utils.appind(code, 'CONNECT TO ' || LOWER(X_connect_to_username));
1390 cn_utils.appendcr(code, ' IDENTIFIED BY ' || LOWER(X_connect_to_password));
1391 cn_utils.appindcr(code, 'USING ''' || LOWER(X_connect_to_host) || '''');
1392 cn_utils.unindent(code, 1);
1393
1394 cn_debug.print_msg('gen_create_dblink<<', 1);
1395 END gen_create_dblink;
1396
1397
1398 --+
1399 -- Procedure Name
1400 -- gen_create_subprogram
1401 -- Purpose
1402 -- generates the code for creating a subprogram
1403 -- History
1404 -- 18-NOV-93 Devesh Khatu Created
1405 --+
1406 PROCEDURE gen_create_subprogram (
1407 X_object_id cn_objects.object_id%TYPE,
1408 code IN OUT NOCOPY cn_utils.code_type) IS
1409
1410 X_statement_text cn_obj_packages_v.statement_text%TYPE;
1411 BEGIN
1412 cn_debug.print_msg('gen_create_subprogram>>', 1);
1413
1414 SELECT statement_text
1415 INTO X_statement_text
1416 FROM cn_objects co
1417 WHERE co.object_id = X_object_id;
1418
1419 cn_utils.init_code( X_object_id, code);
1420 cn_utils.appendcr(code, 'CREATE OR REPLACE ' || X_statement_text);
1421
1422 cn_debug.print_msg('gen_create_subprogram<<', 1);
1423 END gen_create_subprogram;
1424
1425
1426 --+
1427 -- Procedure Name
1428 -- gen_create_package
1429 -- Purpose
1430 -- generates the code for creating a package, or a package body
1431 -- History
1432 -- 18-NOV-93 Devesh Khatu Created
1433 -- 16-MAR-94 Devesh Khatu Modified
1434 --+
1435 PROCEDURE gen_create_package (
1436 X_object_id cn_objects.object_id%TYPE,
1437 dump_to_file VARCHAR2,
1438 code IN OUT NOCOPY cn_utils.code_type) IS
1439
1440 X_statement_text cn_obj_packages_v.statement_text%TYPE;
1441 X_object_type cn_obj_packages_v.object_type%TYPE;
1442 X_name cn_obj_packages_v.name%TYPE;
1443 X_description cn_obj_packages_v.description%TYPE;
1444
1445 BEGIN
1446 cn_debug.print_msg('gen_create_package>>', 1);
1447
1448 SELECT statement_text, object_type, name, description
1449 INTO X_statement_text, X_object_type, X_name, X_description
1450 FROM cn_objects co
1451 WHERE co.object_id = X_object_id;
1452
1453 cn_utils.init_code( X_object_id, code);
1454
1455 IF (dump_to_file = 'N') THEN
1456 IF (X_object_type = 'PKS') THEN
1457 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE ' || LOWER(X_name) || ' AS');
1458 ELSE
1459 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY ' || LOWER(X_name) || ' AS');
1460 END IF;
1461
1462 cn_utils.appendcr(code, X_statement_text);
1463 cn_utils.appendcr(code, 'END ' || LOWER(X_name) || ';');
1464
1465 ELSIF (dump_to_file = 'Y') THEN
1466 cn_utils.pkg_init_boilerplate(code, X_name, X_description, X_object_type);
1467 cn_utils.appendcr(code, X_statement_text);
1468 cn_utils.pkg_end_boilerplate(code, X_object_type);
1469 END IF;
1470
1471 cn_debug.print_msg('gen_create_package<<', 1);
1472 END gen_create_package;
1473
1474
1475 --+
1476 -- Procedure Name
1477 -- gen_create_trigger
1478 -- Purpose
1479 -- generates the code for creating a trigger
1480 -- History
1481 -- 14-DEC-93 Devesh Khatu Created
1482 --+
1483 PROCEDURE gen_create_trigger (
1484 X_trigger_id cn_obj_triggers_v.trigger_id%TYPE,
1485 code IN OUT NOCOPY cn_utils.code_type) IS
1486
1487 row cn_obj_triggers_v%ROWTYPE;
1488 table_name cn_obj_tables_v.name%TYPE;
1489
1490 CURSOR triggering_columns IS
1491 SELECT cocv.name
1492 FROM cn_obj_columns_v cocv, cn_column_trg_maps cctm
1493 WHERE cocv.column_id = cctm.column_id
1494 AND cctm.trigger_id = X_trigger_id;
1495
1496 BEGIN
1497 cn_debug.print_msg('gen_create_trigger>>', 1);
1498 cn_utils.init_code( X_trigger_id, code);
1499
1500 row.trigger_id := X_trigger_id;
1501 cn_obj_triggers_v_pkg.select_row(row);
1502 cn_utils.appendcr(code, 'CREATE OR REPLACE TRIGGER ' || LOWER(row.name));
1503 cn_utils.append(code, 'AFTER ');
1504
1505 IF (row.triggering_event = 'I') THEN
1506 cn_utils.append(code, 'INSERT OF ');
1507 ELSIF (row.triggering_event = 'U') THEN
1508 cn_utils.append(code, 'UPDATE OF ');
1509 ELSIF (row.triggering_event = 'D') THEN
1510 cn_utils.append(code, 'DELETE OF ');
1511 END IF;
1512
1513 FOR tc IN triggering_columns LOOP
1514 cn_utils.append(code, tc.name || ', ');
1515 END LOOP;
1516
1517 strip(code, 2); -- remove trailing comma
1518 cn_utils.append(code, ' ON ');
1519
1520 SELECT name INTO table_name
1521 FROM cn_obj_tables_v
1522 WHERE table_id = row.table_id;
1523
1524 cn_utils.appendcr(code, LOWER(table_name));
1525 cn_utils.appendcr(code, 'FOR EACH ROW');
1526
1527 IF (row.when_clause IS NOT NULL) THEN
1528 cn_utils.appendcr(code, 'WHEN ' || row.when_clause);
1529 END IF;
1530
1531 cn_utils.appendcr(code, row.statement_text);
1532 cn_debug.print_msg('gen_create_trigger<<', 1);
1533 END gen_create_trigger;
1534
1535
1536 PROCEDURE gen_instantiation_code (
1537 X_object_id cn_objects.object_id%TYPE,
1538 dump_to_file VARCHAR2,
1539 code_text IN OUT NOCOPY VARCHAR2) IS
1540
1541 code cn_utils.code_type;
1542 X_object_type cn_objects.object_type%TYPE;
1543
1544 BEGIN
1545 cn_debug.print_msg('gen_instantiation_code>>', 1);
1546 cn_debug.print_msg('gen_instantiation_code: X_object_id = ' || X_object_id, 1);
1547
1548 SELECT object_type INTO X_object_type
1549 FROM cn_objects co
1550 WHERE co.object_id = X_object_id;
1551
1552 IF (X_object_type = 'TBL') THEN -- table
1553 gen_create_table(X_object_id, code);
1554 ELSIF (X_object_type = 'PKS') THEN -- package spec
1555 gen_create_package(X_object_id, dump_to_file, code);
1556 ELSIF (X_object_type = 'PKB') THEN -- package body
1557 gen_create_package(X_object_id, dump_to_file, code);
1558 ELSIF (X_object_type = 'PRC') THEN -- procedure
1559 gen_create_subprogram(X_object_id, code);
1560 ELSIF (X_object_type = 'TRG') THEN -- trigger
1561 gen_create_trigger(X_object_id, code);
1562 ELSIF (X_object_type = 'SEQ') THEN -- sequence
1563 gen_create_sequence(X_object_id, code);
1564 ELSIF (X_object_type = 'DBL') THEN -- database link
1565 gen_create_dblink(X_object_id, code);
1566 ELSIF (X_object_type = 'IND') THEN -- index
1567 gen_create_index(X_object_id, code);
1568 END IF;
1569 code_text := code.text;
1570
1571 cn_debug.print_msg('gen_instantiation_code<<', 1);
1572 END gen_instantiation_code;
1573 */
1574 -- AE 05-02-95. gen_create_xxxxxx procedures commented out. not used.
1575
1576
1577
1578
1579
1580 FUNCTION get_proc_audit_id
1581 RETURN cn_process_audits.process_audit_id%TYPE IS
1582 x_process_audit_id cn_process_audits.process_audit_id%TYPE;
1583 BEGIN
1584 SELECT cn_process_audits_s.NEXTVAL
1585 INTO x_process_audit_id
1586 FROM sys.dual;
1587 RETURN x_process_audit_id;
1588 END get_proc_audit_id;
1589
1590
1591 FUNCTION get_object_id
1592 RETURN cn_objects.object_id%TYPE IS
1593 x_object_id cn_objects.object_id%TYPE;
1594 BEGIN
1595 SELECT cn_objects_s.NEXTVAL
1596 INTO x_object_id
1597 FROM sys.dual;
1598 RETURN x_object_id;
1599 END get_object_id;
1600
1601
1602 FUNCTION get_mod_obj_depends_id
1603 RETURN cn_mod_obj_depends.mod_obj_depends_id%TYPE IS
1604 x_mod_obj_depends_id cn_mod_obj_depends.mod_obj_depends_id%TYPE;
1605 BEGIN
1606 SELECT cn_mod_obj_depends_s.NEXTVAL
1607 INTO x_mod_obj_depends_id
1608 FROM sys.dual;
1609 RETURN x_mod_obj_depends_id;
1610 END get_mod_obj_depends_id;
1611
1612
1613 FUNCTION get_object_name (X_object_id cn_objects.object_id%TYPE, p_org_id IN NUMBER)
1614 RETURN cn_objects.name%TYPE IS
1615 X_name cn_objects.name%TYPE;
1616 BEGIN
1617 SELECT name INTO X_name
1618 FROM cn_objects
1619 WHERE object_id = X_object_id
1620 AND org_id = p_org_id;
1621 RETURN X_name;
1622 END get_object_name;
1623
1624
1625 FUNCTION get_repository (X_module_id cn_modules.module_id%TYPE, p_org_id IN NUMBER)
1626 RETURN cn_repositories.repository_id%TYPE IS
1627 X_repository_id cn_repositories.repository_id%TYPE;
1628 BEGIN
1629 SELECT repository_id INTO X_repository_id
1630 FROM cn_modules
1631 WHERE module_id = X_module_id
1632 AND org_id = p_org_id;
1633 RETURN X_repository_id;
1634 END get_repository;
1635
1636
1637
1638 FUNCTION get_event (X_module_id cn_modules.module_id%TYPE, p_org_id IN NUMBER)
1639 RETURN cn_events.event_id%TYPE IS
1640 X_event_id cn_events.event_id%TYPE;
1641 BEGIN
1642 SELECT event_id INTO X_event_id
1643 FROM cn_modules
1644 WHERE module_id = X_module_id
1645 AND org_id = p_org_id;
1646 RETURN X_event_id;
1647 END get_event;
1648
1649
1650 PROCEDURE find_object (
1651 x_name cn_objects.name%TYPE,
1652 x_object_type cn_objects.object_type%TYPE,
1653 x_object_id IN OUT NOCOPY cn_objects.object_id%TYPE,
1654 x_description IN OUT NOCOPY cn_objects.description%TYPE,
1655 p_org_id IN NUMBER ) IS
1656
1657 BEGIN
1658 SELECT object_id, description
1659 INTO x_object_id, x_description
1660 FROM cn_objects
1661 WHERE name = x_name
1662 AND object_type = x_object_type
1663 AND org_id = p_org_id ;
1664 END find_object;
1665
1666
1667
1668 PROCEDURE compute_hierarchy_levels (
1669 X_dim_hierarchy_id cn_dim_hierarchies.dim_hierarchy_id%TYPE) IS
1670 BEGIN
1671 cn_debug.print_msg('compute_hierarchy_levels>>', 1);
1672
1673 -- DELETE FROM cn_tmp_hierarchy_levels;
1674 -- INSERT INTO cn_tmp_hierarchy_levels
1675 -- SELECT value_id, MAX(LEVEL) FROM cn_hierarchy_edges
1676 -- WHERE dim_hierarchy_id = X_dim_hierarchy_id
1677 -- CONNECT BY PRIOR parent_value_id = value_id
1678 -- AND dim_hierarchy_id = X_dim_hierarchy_id
1679 -- GROUP BY value_id;
1680 -- UPDATE cn_hierarchy_nodes chn
1681 -- SET hierarchy_level = (
1682 -- SELECT hierarchy_level FROM cn_tmp_hierarchy_levels cthl
1683 -- WHERE chn.value_id = cthl.value_id)
1684 -- WHERE chn.dim_hierarchy_id = X_dim_hierarchy_id;
1685 -- DELETE FROM cn_tmp_hierarchy_levels;
1686
1687 -- cn_debug.print_msg('compute_hierarchy_levels<<', 1);
1688 END compute_hierarchy_levels;
1689
1690 --+
1691 -- Procedure Name
1692 -- next_period
1693 -- Purpose
1694 -- get the next period
1695 -- History
1696 -- 24-Nov-98 Angela Chung Created
1697 --+
1698 FUNCTION next_period (x_period_id NUMBER, p_org_id NUMBER)
1699 RETURN cn_periods.period_id%TYPE IS
1700 l_next_period_id cn_periods.period_id%TYPE;
1701 BEGIN
1702 SELECT MIN(period_id)
1703 INTO l_next_period_id
1704 FROM cn_period_statuses_all
1705 WHERE period_id > x_period_id
1706 AND period_status IN ('F', 'O')
1707 AND org_id = p_org_id;
1708
1709 RETURN l_next_period_id;
1710
1711 EXCEPTION
1712 WHEN no_data_found THEN
1713 RETURN NULL;
1714 END next_period;
1715
1716 END cn_utils;