forked from OraMUC/table-api-generator
-
Notifications
You must be signed in to change notification settings - Fork 0
/
OM_TAPIGEN.pkb
4291 lines (3865 loc) · 187 KB
/
OM_TAPIGEN.pkb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
CREATE OR REPLACE PACKAGE BODY om_tapigen IS
-----------------------------------------------------------------------------
-- private global constants (c_*)
-----------------------------------------------------------------------------
c_generator_error_number CONSTANT PLS_INTEGER := -20000;
c_bulk_collect_limit CONSTANT NUMBER := 10000;
c_lf CONSTANT VARCHAR2(2 CHAR) := chr(10);
c_lflf CONSTANT VARCHAR2(3 CHAR) := chr(10) || chr(10);
c_list_delimiter CONSTANT VARCHAR2(3 CHAR) := ',' || c_lf;
c_custom_defaults_present_msg CONSTANT VARCHAR2(30) := 'SEE_END_OF_API_PACKAGE_SPEC';
c_spec_options_min_line CONSTANT NUMBER := 5;
c_spec_options_max_line CONSTANT NUMBER := 35;
c_debug_max_runs CONSTANT NUMBER := 1000;
-----------------------------------------------------------------------------
-- private record (t_rec_*) and collection (t_tab_*) types
-----------------------------------------------------------------------------
TYPE t_rec_params IS RECORD(
table_name all_objects.object_name%TYPE,
owner all_users.username%TYPE,
reuse_existing_api_params BOOLEAN,
enable_insertion_of_rows BOOLEAN,
enable_column_defaults BOOLEAN,
enable_update_of_rows BOOLEAN,
enable_deletion_of_rows BOOLEAN,
enable_parameter_prefixes BOOLEAN,
enable_proc_with_out_params BOOLEAN,
enable_getter_and_setter BOOLEAN,
col_prefix_in_method_names BOOLEAN,
return_row_instead_of_pk BOOLEAN,
enable_dml_view BOOLEAN,
enable_generic_change_log BOOLEAN,
api_name all_objects.object_name%TYPE,
sequence_name all_sequences.sequence_name%TYPE,
exclude_column_list VARCHAR2(4000 CHAR),
enable_custom_defaults BOOLEAN,
custom_default_values xmltype,
custom_defaults_serialized VARCHAR2(32767 CHAR));
TYPE t_rec_status IS RECORD(
pk_is_multi_column BOOLEAN,
column_prefix all_tab_cols.column_name%TYPE,
xmltype_column_present BOOLEAN,
generator_action VARCHAR2(30 CHAR),
api_exists BOOLEAN,
rpad_columns INTEGER,
rpad_pk_columns INTEGER,
rpad_uk_columns INTEGER);
--
TYPE t_tab_columns IS TABLE OF t_rec_columns INDEX BY BINARY_INTEGER; -- record type is public beacause of util_view_columns_array
--
TYPE t_tab_columns_index IS TABLE OF INTEGER INDEX BY user_tab_columns.column_name%TYPE;
--
TYPE t_rec_constraints IS RECORD(
constraint_name user_constraints.constraint_name%TYPE);
TYPE t_tab_constraints IS TABLE OF t_rec_constraints INDEX BY BINARY_INTEGER;
--
TYPE t_rec_cons_columns IS RECORD(
constraint_name all_cons_columns.constraint_name%TYPE,
position all_cons_columns.position%TYPE,
column_name all_cons_columns.column_name%TYPE,
column_name_length INTEGER,
data_type all_tab_cols.data_type%TYPE,
r_owner all_users.username%TYPE,
r_table_name all_objects.object_name%TYPE,
r_column_name all_tab_cols.column_name%TYPE);
TYPE t_tab_cons_columns IS TABLE OF t_rec_cons_columns INDEX BY BINARY_INTEGER;
--
TYPE t_rec_code_blocks IS RECORD(
template VARCHAR2(32767 CHAR),
api_spec CLOB,
api_spec_varchar_cache VARCHAR2(32767 CHAR),
api_body CLOB,
api_body_varchar_cache VARCHAR2(32767 CHAR),
dml_view CLOB,
dml_view_varchar_cache VARCHAR2(32767 CHAR),
dml_view_trigger CLOB,
dml_view_trigger_varchar_cache VARCHAR2(32767 CHAR));
--
TYPE t_rec_template_options IS RECORD(
use_column_defaults BOOLEAN,
hide_identity_columns BOOLEAN,
padding INTEGER);
--
TYPE t_tab_vc2_5k IS TABLE OF VARCHAR2(5000) INDEX BY BINARY_INTEGER;
--
TYPE t_rec_iterator IS RECORD(
column_name all_tab_cols.column_name%TYPE,
method_name all_tab_cols.column_name%TYPE,
parameter_name all_tab_cols.column_name%TYPE,
column_compare VARCHAR2(512 CHAR),
old_value VARCHAR2(512 CHAR),
new_value VARCHAR2(512 CHAR),
current_uk_constraint all_objects.object_name%TYPE);
--
TYPE t_rec_debug_details IS RECORD(
step INTEGER(4),
module st_session_module,
action st_session_action,
start_time TIMESTAMP(6),
stop_time TIMESTAMP(6));
TYPE t_tab_debug_details IS TABLE OF t_rec_debug_details INDEX BY BINARY_INTEGER;
--
TYPE t_rec_debug IS RECORD(
run INTEGER(4),
owner all_users.username%TYPE,
table_name all_objects.object_name%TYPE,
start_time TIMESTAMP(6),
stop_time TIMESTAMP(6),
details t_tab_debug_details);
TYPE t_tab_debug IS TABLE OF t_rec_debug INDEX BY BINARY_INTEGER;
-----------------------------------------------------------------------------
-- private global variables (g_*)
-----------------------------------------------------------------------------
--variables
g_debug_enabled BOOLEAN;
g_debug_run INTEGER;
g_debug_step INTEGER;
g_debug_module st_session_module;
-- records
g_params t_rec_params;
g_params_existing_api t_rec_existing_apis;
g_iterator t_rec_iterator;
g_code_blocks t_rec_code_blocks;
g_status t_rec_status;
g_template_options t_rec_template_options;
-- collections
g_columns t_tab_columns;
g_columns_reverse_index t_tab_columns_index;
g_uk_constraints t_tab_constraints;
g_fk_constraints t_tab_constraints;
g_pk_columns t_tab_cons_columns;
g_uk_columns t_tab_cons_columns;
g_fk_columns t_tab_cons_columns;
g_debug t_tab_debug;
-----------------------------------------------------------------------------
-- private global cursors (g_cur_*)
-----------------------------------------------------------------------------
CURSOR g_cur_columns IS
WITH not_null_columns AS
(SELECT CASE
WHEN instr(column_name_nn, '"') = 0 THEN
upper(column_name_nn)
ELSE
TRIM(both '"' FROM column_name_nn)
END AS column_name_nn
FROM (SELECT regexp_substr(
$IF dbms_db_version.ver_le_11_1 $THEN om_tapigen.util_get_cons_search_condition(p_owner => USER,
p_constraint_name => constraint_name)
$ELSE
$IF dbms_db_version.ver_le_11_2 $THEN
om_tapigen.util_get_cons_search_condition(p_owner => USER,
p_constraint_name => constraint_name)
$ELSE search_condition_vc
$END
$END,
'^\s*("[^"]+"|[a-zA-Z0-9_#$]+)\s+is\s+not\s+null\s*$',
1,
1,
'i',
1) AS column_name_nn
FROM all_constraints
WHERE owner = g_params.owner
AND table_name = g_params.table_name
AND constraint_type = 'C'
AND status = 'ENABLED')
WHERE column_name_nn IS NOT NULL),
excluded_columns AS
(SELECT column_value AS column_name_excluded
FROM TABLE(om_tapigen.util_split_to_table(g_params.exclude_column_list))),
identity_columns AS
(
$IF dbms_db_version.ver_le_11_1 $THEN
SELECT 'DUMMY_COLUMN_NAME' AS column_name_identity, NULL AS identity_type
FROM dual
$ELSE
$IF dbms_db_version.ver_le_11_2 $THEN
SELECT 'DUMMY_COLUMN_NAME' AS column_name_identity, NULL AS identity_type
FROM dual
$ELSE
SELECT column_name AS column_name_identity, generation_type AS identity_type
FROM all_tab_identity_cols
WHERE owner = g_params.owner
AND table_name = g_params.table_name
$END
$END
),
t AS
(SELECT DISTINCT column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale,
char_length,
identity_type,
CASE
WHEN data_default IS NOT NULL THEN
(SELECT om_tapigen.util_get_column_data_default(p_owner => g_params.owner,
p_table_name => table_name,
p_column_name => column_name)
FROM dual)
ELSE
NULL
END AS data_default,
virtual_column,
CASE
WHEN column_name_nn IS NOT NULL THEN
'N'
ELSE
'Y'
END AS is_nullable_yn,
CASE
WHEN (virtual_column = 'YES' AND data_type != 'XMLTYPE') OR
excluded_columns.column_name_excluded IS NOT NULL THEN
'Y'
ELSE
'N'
END AS is_excluded_yn
FROM all_tab_cols
LEFT JOIN not_null_columns ON all_tab_cols.column_name = not_null_columns.column_name_nn
LEFT JOIN excluded_columns ON all_tab_cols.column_name = excluded_columns.column_name_excluded
LEFT JOIN identity_columns ON all_tab_cols.column_name = identity_columns.column_name_identity
WHERE owner = g_params.owner
AND table_name = g_params.table_name
AND hidden_column = 'NO'
ORDER BY column_id)
SELECT column_name,
data_type,
data_length,
data_precision,
data_scale,
data_default,
char_length,
NULL AS data_custom_default,
NULL AS custom_default_source,
identity_type,
'N' AS is_pk_yn,
'N' AS is_uk_yn,
'N' AS is_fk_yn,
is_nullable_yn,
is_excluded_yn,
NULL AS r_owner,
NULL AS r_table_name,
NULL AS r_column_name
FROM t;
-----------------------------------------------------------------------------
-- util_execute_sql is a private helper procedure that parses and executes
-- generated code with the help of DBMS_SQL package. Execute immediate is not
-- used here directly, because of the missing possibility of parsing a
-- statement in a performant way. Executing immediate and catching
-- the error is more expensive than parsing the statement and catching the
-- error.
-----------------------------------------------------------------------------
PROCEDURE util_execute_sql(p_sql IN OUT NOCOPY CLOB) IS
v_cursor NUMBER;
v_exec_result PLS_INTEGER;
BEGIN
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, p_sql, dbms_sql.native);
v_exec_result := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(v_cursor);
RAISE;
END util_execute_sql;
-----------------------------------------------------------------------------
-- util_string_to_bool is a private helper function to deliver a
-- boolean representation of an string value. True is returned,if:
-- true,yes,y,1
-- is given. False is returned when:
-- false,no,n,0
-- is given.
-----------------------------------------------------------------------------
FUNCTION util_string_to_bool(p_string IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN CASE WHEN lower(p_string) IN('true', 'yes', 'y', '1') THEN TRUE WHEN lower(p_string) IN('false',
'no',
'n',
'0') THEN FALSE ELSE NULL END;
END util_string_to_bool;
-----------------------------------------------------------------------------
-- util_bool_to_string is a private helper function to deliver a
-- varchar2 representation of an boolean value. 'TRUE' is returned,if
-- boolean value is true. 'FALSE' is returned when boolean value is false.
-----------------------------------------------------------------------------
FUNCTION util_bool_to_string(p_bool IN BOOLEAN) RETURN VARCHAR2 IS
BEGIN
RETURN CASE WHEN p_bool THEN 'TRUE' WHEN NOT p_bool THEN 'FALSE' ELSE NULL END;
END util_bool_to_string;
-----------------------------------------------------------------------------
-- util_get_table_column_prefix is a private helper function to find out the
-- column prefixes of a table. We understand everything before the first
-- underscore "_" within the columnname as prefix. If columns have different
-- prefixes within a table,null will be returned.
-----------------------------------------------------------------------------
FUNCTION util_get_table_column_prefix(p_table_name IN VARCHAR2) RETURN VARCHAR2 IS
v_return VARCHAR2(128 CHAR);
v_count PLS_INTEGER := 0;
BEGIN
FOR i IN (SELECT DISTINCT substr(column_name,
1,
CASE
WHEN instr(column_name, '_') = 0 THEN
length(column_name)
ELSE
instr(column_name, '_') - 1
END) AS prefix
FROM all_tab_cols
WHERE owner = g_params.owner
AND table_name = p_table_name
AND hidden_column = 'NO') LOOP
v_count := v_count + 1;
IF v_count > 1 THEN
v_return := NULL;
EXIT;
END IF;
v_return := i.prefix;
END LOOP;
RETURN v_return;
END util_get_table_column_prefix;
-----------------------------------------------------------------------------
-- util_get_attribute_surrogate is a private helper function to find out a
-- datatype dependent surrogate. This is required for comparing two
-- values of a column e.g. old value and new value. There is the special case
-- of null comparisison in Oracle,what means null compared with null is
-- never true. That is the reason to compare:
-- coalesce(old value,surrogate) = coalesce(new value,surrogate)
-- that is true,if both sides are null.
-----------------------------------------------------------------------------
FUNCTION util_get_attribute_surrogate(p_data_type IN user_tab_cols.data_type%TYPE) RETURN VARCHAR2 IS
v_return VARCHAR2(100 CHAR);
BEGIN
v_return := CASE
WHEN p_data_type = 'NUMBER' THEN
'-999999999999999.999999999999999'
WHEN p_data_type LIKE '%CHAR%' THEN
'''@@@@@@@@@@@@@@@'''
WHEN p_data_type = 'DATE' THEN
'TO_DATE(''01.01.1900'',''DD.MM.YYYY'')'
WHEN p_data_type LIKE 'TIMESTAMP%' THEN
'TO_TIMESTAMP(''01.01.1900'',''dd.mm.yyyy'')'
WHEN p_data_type = 'CLOB' THEN
'TO_CLOB(''@@@@@@@@@@@@@@@'')'
WHEN p_data_type = 'BLOB' THEN
'TO_BLOB(UTL_RAW.cast_to_raw(''@@@@@@@@@@@@@@@''))'
WHEN p_data_type = 'XMLTYPE' THEN
'XMLTYPE(''<NULL/>'')'
ELSE
'''@@@@@@@@@@@@@@@'''
END;
RETURN v_return;
END util_get_attribute_surrogate;
-----------------------------------------------------------------------------
-- util_get_attribute_compare is a private helper function to deliver the
-- described (take a look at function util_get_attribute_surrogate) compare
-- code for two attributes. In addition to that,the compare operation must
-- be dynamically,because e.g. "=" or "<>" or other operations are required.
-----------------------------------------------------------------------------
FUNCTION util_get_attribute_compare
(
p_data_type IN user_tab_cols.data_type%TYPE,
p_nullable IN BOOLEAN,
p_first_attribute IN VARCHAR2,
p_second_attribute IN VARCHAR2,
p_compare_operation IN VARCHAR2 DEFAULT '<>'
) RETURN VARCHAR2 IS
v_return VARCHAR2(1000 CHAR);
FUNCTION get_coalesce(p_attribute VARCHAR2) RETURN VARCHAR2 IS
v_return VARCHAR2(1000 CHAR);
BEGIN
v_return := CASE
WHEN NOT p_nullable THEN
p_attribute
ELSE
'COALESCE(' || p_attribute || ', ' || util_get_attribute_surrogate(p_data_type) || ')'
END;
RETURN v_return;
END;
BEGIN
v_return := CASE
WHEN p_data_type = 'XMLTYPE' THEN
'util_xml_compare( ' || get_coalesce(p_first_attribute) || ', ' || get_coalesce(p_second_attribute) || ') ' ||
p_compare_operation || ' 0'
WHEN p_data_type IN ('BLOB', 'CLOB') THEN
'DBMS_LOB.compare( ' || get_coalesce(p_first_attribute) || ',' || get_coalesce(p_second_attribute) || ') ' ||
p_compare_operation || ' 0'
ELSE
get_coalesce(p_first_attribute) || ' ' || p_compare_operation || ' ' ||
get_coalesce(p_second_attribute)
END;
RETURN v_return;
END util_get_attribute_compare;
-----------------------------------------------------------------------------
-- util_get_vc2_4000_operation is a private helper function to deliver a
-- varchar2 representation of an attribute in dependency of its datatype.
-----------------------------------------------------------------------------
FUNCTION util_get_vc2_4000_operation
(
p_data_type IN all_tab_cols.data_type%TYPE,
p_attribute_name IN VARCHAR2
) RETURN VARCHAR2 IS
v_return VARCHAR2(1000 CHAR);
BEGIN
v_return := CASE
WHEN p_data_type IN ('NUMBER', 'FLOAT', 'INTEGER') THEN
'to_char(' || p_attribute_name || ')'
WHEN p_data_type = 'DATE' THEN
'to_char(' || p_attribute_name || ',''yyyy.mm.dd hh24:mi:ss'')'
WHEN p_data_type LIKE 'TIMESTAMP%' THEN
'to_char(' || p_attribute_name || ',''yyyy.mm.dd hh24:mi:ss.ff'')'
WHEN p_data_type = 'BLOB' THEN
'''Data type "BLOB" is not supported for generic change log'''
WHEN p_data_type = 'XMLTYPE' THEN
'substr( CASE WHEN ' || p_attribute_name || ' IS NULL THEN NULL ELSE ' || p_attribute_name ||
'.getStringVal() END,1,4000)'
ELSE
'substr(' || p_attribute_name || ',1,4000)'
END;
RETURN v_return;
END util_get_vc2_4000_operation;
-----------------------------------------------------------------------------
-- util_get_user_name is a private helper function to deliver the current
-- username. If a valid APEX session exists,then the APEX application user
-- is taken,otherwise the current connected operation system user.
-----------------------------------------------------------------------------
FUNCTION util_get_user_name RETURN all_users.username%TYPE IS
v_return all_users.username%TYPE;
BEGIN
v_return := upper(coalesce(v('APP_USER'), sys_context('USERENV', 'OS_USER'), USER));
RETURN v_return;
END util_get_user_name;
-----------------------------------------------------------------------------
-- util_get_parameter_name is a private helper function to deliver a cleaned
-- normalized parameter name.
-----------------------------------------------------------------------------
FUNCTION util_get_parameter_name
(
p_column_name VARCHAR2,
p_rpad INTEGER
) RETURN VARCHAR2 IS
v_return user_objects.object_name%TYPE;
BEGIN
v_return := regexp_replace(lower(p_column_name), '[^a-z0-9_]', NULL);
IF g_params.enable_parameter_prefixes THEN
v_return := 'p_' || substr(v_return, 1, c_ora_max_name_len - 2);
END IF;
IF p_rpad IS NOT NULL THEN
v_return := rpad(v_return,
CASE
WHEN g_params.enable_parameter_prefixes THEN
p_rpad + 2
ELSE
p_rpad
END);
END IF;
RETURN v_return;
END util_get_parameter_name;
-----------------------------------------------------------------------------
-- util_get_method_name is a private helper function to deliver a cleaned
-- normalized method name for the getter and setter functions/procedures.
-----------------------------------------------------------------------------
FUNCTION util_get_method_name(p_column_name VARCHAR2) RETURN VARCHAR2 IS
v_return user_objects.object_name%TYPE;
BEGIN
v_return := regexp_replace(lower(p_column_name), '[^a-z0-9_]', NULL);
v_return := CASE
WHEN g_params.col_prefix_in_method_names THEN
substr(v_return, 1, c_ora_max_name_len - 4)
ELSE
substr(v_return, length(g_status.column_prefix) + 2, c_ora_max_name_len - 4)
END;
RETURN v_return;
END;
-----------------------------------------------------------------------------
FUNCTION util_get_substituted_name(p_name_template VARCHAR2) RETURN VARCHAR2 IS
v_return all_objects.object_name%TYPE;
v_base_name all_objects.object_name%TYPE;
v_replace_string all_objects.object_name%TYPE;
v_position PLS_INTEGER;
v_length PLS_INTEGER;
BEGIN
-- Get replace string
v_replace_string := regexp_substr(p_name_template, '#[A-Za-z0-9_-]+#', 1, 1);
-- Check,if we have to do a replacement
IF v_replace_string IS NULL THEN
-- Without replacement we return simply the input
v_return := p_name_template;
ELSE
-- Replace possible placeholders in name template
v_base_name := rtrim(regexp_substr(upper(v_replace_string), '[A-Z_]+', 1, 1), '_');
-- logger.log('v_base_name: ' || v_base_name);
-- Check,if we have a valid base name
IF v_base_name NOT IN ('TABLE_NAME', 'PK_COLUMN', 'COLUMN_PREFIX') THEN
-- Without a valid base name we return simply the input
v_return := p_name_template;
ELSE
-- Search for start and stop positions
v_position := regexp_substr(v_replace_string, '-?\d+', 1, 1);
v_length := regexp_substr(v_replace_string, '\d+', 1, 2);
-- 1. To be backward compatible we have to support things like this TABLE_NAME_26.
-- 2. If someone want to use the substr version he has always to provide position and length.
-- 3. Negative position is supported like this #TABLE_NAME_-15_15# (the second number can not be omitted like in substr,see 1.)
IF v_position IS NULL AND v_length IS NULL THEN
v_length := 200;
v_position := 1;
ELSIF v_position IS NOT NULL AND v_length IS NULL THEN
v_length := v_position;
v_position := 1;
END IF;
v_return := REPLACE(p_name_template,
v_replace_string,
substr(CASE v_base_name
WHEN 'TABLE_NAME' THEN
g_params.table_name
WHEN 'PK_COLUMN' THEN
g_pk_columns(1).column_name
WHEN 'COLUMN_PREFIX' THEN
g_status.column_prefix
END,
v_position,
v_length));
END IF;
END IF;
RETURN v_return;
END util_get_substituted_name;
-----------------------------------------------------------------------------
FUNCTION util_get_column_data_default
(
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_owner VARCHAR2 DEFAULT USER
) RETURN VARCHAR2 AS
v_return LONG;
CURSOR c_utc IS
SELECT data_default
FROM all_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name
AND column_name = p_column_name;
BEGIN
OPEN c_utc;
FETCH c_utc
INTO v_return;
CLOSE c_utc;
RETURN substr(v_return, 1, 4000);
END;
--------------------------------------------------------------------------------
FUNCTION util_get_cons_search_condition
(
p_constraint_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER
) RETURN VARCHAR2 AS
v_return LONG;
CURSOR c_search_condition IS
SELECT search_condition
FROM all_constraints
WHERE owner = p_owner
AND constraint_name = p_constraint_name;
BEGIN
OPEN c_search_condition;
FETCH c_search_condition
INTO v_return;
CLOSE c_search_condition;
RETURN substr(v_return, 1, 4000);
END;
-----------------------------------------------------------------------------
FUNCTION util_get_ora_max_name_len RETURN INTEGER IS
BEGIN
RETURN c_ora_max_name_len;
END;
-----------------------------------------------------------------------------
FUNCTION util_split_to_table
(
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN t_tab_vc2_4k
PIPELINED IS
v_offset PLS_INTEGER := 1;
v_index PLS_INTEGER := instr(p_string, p_delimiter, v_offset);
v_delimiter_length PLS_INTEGER := length(p_delimiter);
v_string_length CONSTANT PLS_INTEGER := length(p_string);
BEGIN
WHILE v_index > 0 LOOP
PIPE ROW(TRIM(substr(p_string, v_offset, v_index - v_offset)));
v_offset := v_index + v_delimiter_length;
v_index := instr(p_string, p_delimiter, v_offset);
END LOOP;
IF v_string_length - v_offset + 1 > 0 THEN
PIPE ROW(TRIM(substr(p_string, v_offset, v_string_length - v_offset + 1)));
END IF;
RETURN;
END util_split_to_table;
-----------------------------------------------------------------------------
FUNCTION util_serialize_xml(p_xml xmltype) RETURN VARCHAR2 IS
v_return VARCHAR2(32767);
BEGIN
SELECT xmlserialize(document p_xml no indent) INTO v_return FROM dual;
RETURN v_return;
END util_serialize_xml;
--------------------------------------------------------------------------------
PROCEDURE util_set_debug_on IS
BEGIN
g_debug_enabled := TRUE;
g_debug_run := 0;
g_debug_step := 0;
g_debug.delete;
END;
--------------------------------------------------------------------------------
PROCEDURE util_set_debug_off IS
BEGIN
g_debug_enabled := FALSE;
END;
PROCEDURE util_debug_start_one_run
(
p_generator_action VARCHAR2,
p_table_name all_objects.object_name%TYPE,
p_owner all_users.username%TYPE
) IS
BEGIN
g_debug_module := c_generator || ' v' || c_generator_version || ': ' || p_generator_action;
IF g_debug_enabled THEN
g_debug_run := g_debug_run + 1;
IF g_debug_run <= c_debug_max_runs THEN
g_debug_step := 0;
g_debug(g_debug_run).run := g_debug_run;
g_debug(g_debug_run).owner := p_owner;
g_debug(g_debug_run).table_name := p_table_name;
g_debug(g_debug_run).start_time := systimestamp;
END IF;
END IF;
END;
-----------------------------------------------------------------------------
PROCEDURE util_debug_stop_one_run IS
BEGIN
IF g_debug_enabled AND g_debug_run <= c_debug_max_runs THEN
g_debug(g_debug_run).stop_time := systimestamp;
END IF;
END;
-----------------------------------------------------------------------------
PROCEDURE util_debug_start_one_step(p_action VARCHAR2) IS
BEGIN
dbms_application_info.set_module(module_name => g_debug_module, action_name => p_action);
IF g_debug_enabled AND g_debug_run <= c_debug_max_runs THEN
g_debug_step := g_debug_step + 1;
g_debug(g_debug_run).details(g_debug_step).step := g_debug_step;
g_debug(g_debug_run).details(g_debug_step).module := g_debug_module;
g_debug(g_debug_run).details(g_debug_step).action := p_action;
g_debug(g_debug_run).details(g_debug_step).start_time := systimestamp;
END IF;
END;
-----------------------------------------------------------------------------
PROCEDURE util_debug_stop_one_step IS
BEGIN
dbms_application_info.set_module(module_name => NULL, action_name => NULL);
IF g_debug_enabled AND g_debug_run <= c_debug_max_runs THEN
g_debug(g_debug_run).details(g_debug_step).stop_time := systimestamp;
END IF;
END;
-----------------------------------------------------------------------------
FUNCTION util_view_debug_log RETURN t_tab_debug_data
PIPELINED IS
v_return t_rec_debug_data;
BEGIN
FOR i IN 1 .. g_debug.count LOOP
v_return.run := g_debug(i).run;
v_return.run_time := round(SYSDATE + ((g_debug(i).stop_time - g_debug(i).start_time) * 86400) - SYSDATE, 6);
v_return.owner := g_debug(i).owner;
v_return.table_name := g_debug(i).table_name;
FOR j IN 1 .. g_debug(i).details.count LOOP
v_return.step := g_debug(i).details(j).step;
v_return.elapsed := round(SYSDATE + ((g_debug(i).details(j).stop_time - g_debug(i).start_time) * 86400) -
SYSDATE,
6);
v_return.execution := round(SYSDATE +
((g_debug(i).details(j).stop_time - g_debug(i).details(j).start_time) * 86400) -
SYSDATE,
6);
v_return.action := g_debug(i).details(j).action;
v_return.start_time := g_debug(i).details(j).start_time;
--sysdate + (interval_difference * 86400) - sysdate
--https://stackoverflow.com/questions/10092032/extracting-the-total-number-of-seconds-from-an-interval-data-type
PIPE ROW(v_return);
END LOOP;
END LOOP;
END;
-----------------------------------------------------------------------------
FUNCTION util_view_columns_array RETURN t_tab_debug_columns
PIPELINED IS
v_return t_rec_columns;
BEGIN
FOR i IN 1 .. g_columns.count LOOP
v_return.column_name := g_columns(i).column_name;
v_return.data_type := g_columns(i).data_type;
v_return.data_length := g_columns(i).data_length;
v_return.data_precision := g_columns(i).data_precision;
v_return.data_scale := g_columns(i).data_scale;
v_return.data_default := g_columns(i).data_default;
v_return.char_length := g_columns(i).char_length;
v_return.data_custom_default := g_columns(i).data_custom_default;
v_return.custom_default_source := g_columns(i).custom_default_source;
v_return.identity_type := g_columns(i).identity_type;
v_return.is_pk_yn := g_columns(i).is_pk_yn;
v_return.is_uk_yn := g_columns(i).is_uk_yn;
v_return.is_fk_yn := g_columns(i).is_fk_yn;
v_return.is_nullable_yn := g_columns(i).is_nullable_yn;
v_return.is_excluded_yn := g_columns(i).is_excluded_yn;
v_return.r_owner := g_columns(i).r_owner;
v_return.r_table_name := g_columns(i).r_table_name;
v_return.r_column_name := g_columns(i).r_column_name;
PIPE ROW(v_return);
END LOOP;
END util_view_columns_array;
--------------------------------------------------------------------------------
FUNCTION util_get_ddl
(
p_object_type VARCHAR2,
p_object_name VARCHAR2,
p_owner VARCHAR2 DEFAULT USER
) RETURN CLOB IS
v_return CLOB;
v_count PLS_INTEGER;
BEGIN
IF p_object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'TRIGGER') THEN
SELECT COUNT(*)
INTO v_count
FROM all_objects
WHERE owner = p_owner
AND object_type = p_object_type
AND object_name = p_object_name;
IF v_count = 1 THEN
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', TRUE);
CASE p_object_type
WHEN 'PACKAGE' THEN
v_return := dbms_metadata.get_ddl(object_type => p_object_type, NAME => p_object_name, SCHEMA => p_owner);
v_return := ltrim(substr(v_return, 1, instr(v_return, 'CREATE OR REPLACE PACKAGE BODY') - 1),
' ' || chr(10));
WHEN 'PACKAGE BODY' THEN
v_return := dbms_metadata.get_ddl(object_type => 'PACKAGE', NAME => p_object_name, SCHEMA => p_owner);
v_return := substr(v_return, instr(v_return, 'CREATE OR REPLACE PACKAGE BODY'));
WHEN 'VIEW' THEN
v_return := ltrim(regexp_replace(regexp_replace(dbms_metadata.get_ddl(object_type => p_object_type,
NAME => p_object_name,
SCHEMA => p_owner),
'\(.*\) ', -- remove additional column list from the compiler
NULL,
1,
1),
'^ SELECT', -- remove additional whitespace from the compiler
'SELECT',
1,
1,
'im'),
' ' || chr(10));
WHEN 'TRIGGER' THEN
v_return := ltrim(dbms_metadata.get_ddl(object_type => p_object_type,
NAME => p_object_name,
SCHEMA => p_owner),
' ' || chr(10));
ELSE
NULL;
END CASE;
END IF;
ELSE
v_return := 'ERROR: unsupported object type "' || p_object_type || '"';
END IF;
RETURN v_return;
END;
-----------------------------------------------------------------------------
FUNCTION util_get_fk_value
(
p_table_name VARCHAR2,
p_column_name VARCHAR2,
p_owner VARCHAR2 DEFAULT USER
) RETURN VARCHAR2 IS
v_cur SYS_REFCURSOR;
v_return VARCHAR2(4000);
v_column_expression VARCHAR2(4000);
BEGIN
FOR i IN (SELECT data_type
FROM all_tab_columns
WHERE owner = p_owner
AND table_name = p_table_name
AND column_name = p_column_name) LOOP
v_column_expression := CASE
WHEN i.data_type LIKE '%CHAR%' THEN
'''''''''||substr(' || p_column_name || ',1,4000)||'''''''''
WHEN i.data_type IN ('NUMBER', 'INTEGER', 'FLOAT') THEN
'to_char(' || p_column_name || ')'
ELSE
NULL
END;
IF v_column_expression IS NOT NULL THEN
OPEN v_cur FOR 'SELECT ' || v_column_expression || ' FROM ' || p_table_name;
FETCH v_cur
INTO v_return;
CLOSE v_cur;
END IF;
END LOOP;
RETURN(v_return);
END util_get_fk_value;
-----------------------------------------------------------------------------
FUNCTION util_generate_list(p_list_name VARCHAR2) RETURN t_tab_vc2_5k IS
-----------------------------------------------------------------------------
-- Columns as flat list for insert - without p_column_exclude_list:
-- {% LIST_INSERT_COLUMNS %}
-- Example:
-- col1,
-- col2,
-- col3,
-- ...
-----------------------------------------------------------------------------
FUNCTION list_insert_columns RETURN t_tab_vc2_5k IS
v_result t_tab_vc2_5k;
BEGIN
FOR i IN g_columns.first .. g_columns.last LOOP
IF g_columns(i).is_excluded_yn = 'N' AND
NOT (g_template_options.hide_identity_columns AND
nvl(g_columns(i).identity_type, 'NULL') IN ('ALWAYS', 'BY DEFAULT')) THEN
v_result(v_result.count + 1) := ' ' || '"' || g_columns(i).column_name || '"' || CASE
WHEN g_columns(i).is_pk_yn = 'Y' THEN
' /*PK*/'
END || CASE
WHEN g_columns(i).is_uk_yn = 'Y' THEN
' /*UK*/'
END || CASE
WHEN g_columns(i).is_fk_yn = 'Y' THEN
' /*FK*/'
END || c_list_delimiter;
END IF;
END LOOP;
v_result(v_result.first) := ltrim(v_result(v_result.first));
v_result(v_result.last) := rtrim(v_result(v_result.last), c_list_delimiter);
RETURN v_result;
END list_insert_columns;
-----------------------------------------------------------------------------
-- Columns as flat list for insert - without p_column_exclude_list:
-- {% LIST_INSERT_PARAMS %}
-- Example:
-- p_col2,
-- p_col3,
-- p_col4,
-- ...
-----------------------------------------------------------------------------
FUNCTION list_insert_params RETURN t_tab_vc2_5k IS
v_result t_tab_vc2_5k;
BEGIN
FOR i IN g_columns.first .. g_columns.last LOOP
IF g_columns(i).is_excluded_yn = 'N' AND
NOT (g_template_options.hide_identity_columns AND
nvl(g_columns(i).identity_type, 'NULL') IN ('ALWAYS', 'BY DEFAULT')) THEN
v_result(v_result.count + 1) := ' ' || CASE
WHEN g_columns(i).is_pk_yn = 'Y' AND NOT g_status.pk_is_multi_column AND g_params.sequence_name IS NOT NULL THEN
'COALESCE( ' || util_get_parameter_name(g_columns(i).column_name, NULL) || ', "' || g_params.sequence_name ||
'".nextval )'
ELSE
util_get_parameter_name(g_columns(i).column_name, NULL)
END || c_list_delimiter;
END IF;
END LOOP;
v_result(v_result.first) := ltrim(v_result(v_result.first));
v_result(v_result.last) := rtrim(v_result(v_result.last), c_list_delimiter);
RETURN v_result;
END list_insert_params;
-----------------------------------------------------------------------------
-- Columns as flat list - with p_column_exclude_list:
-- {% LIST_COLUMNS_W_PK_FULL %}
-- Example:
-- col1,
-- col2,
-- col3,
-- ...
-----------------------------------------------------------------------------
FUNCTION list_columns_w_pk_full RETURN t_tab_vc2_5k IS
v_result t_tab_vc2_5k;
BEGIN
FOR i IN g_columns.first .. g_columns.last LOOP
v_result(v_result.count + 1) := ' ' || '"' || g_columns(i).column_name || '"' || CASE
WHEN g_columns(i).is_pk_yn = 'Y' THEN
' /*PK*/'
END || CASE
WHEN g_columns(i).is_uk_yn = 'Y' THEN
' /*UK*/'
END || CASE
WHEN g_columns(i).is_fk_yn = 'Y' THEN
' /*FK*/'
END || c_list_delimiter;
END LOOP;