-
Notifications
You must be signed in to change notification settings - Fork 15
/
Copy pathom_tapigen_install.sql
6233 lines (5586 loc) · 252 KB
/
om_tapigen_install.sql
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
set define off feedback off serveroutput on
whenever sqlerror exit sql.sqlcode rollback
prompt
prompt Install github.com/OraMUC/table-api-generator
prompt ============================================================
prompt Set compiler flags
declare
v_db_version varchar2(10);
begin
select replace(regexp_substr(min(version), '\d+\.\d+'), '.', null) as db_version
into v_db_version
from product_component_version
where product like 'Oracle Database%';
if to_number(v_db_version) < 121 then
raise_application_error (-20000, 'Unsupported DB version detected: Sorry, you need to have 12.1 or higher for our table API generator :-(');
end if;
if to_number(v_db_version) >= 180 then
execute immediate q'[
select replace(regexp_substr(min(version_full), '\d+\.\d+'), '.', null) as db_version
from product_component_version
where product like 'Oracle Database%' ]'
into v_db_version;
end if;
-- Show unset compiler flags as errors (results for example in errors like "PLW-06003: unknown inquiry directive '$$DB_VERSION'")
execute immediate q'[alter session set plsql_warnings = 'ENABLE:6003']';
-- Finally set compiler flags
execute immediate replace(
q'[alter session set plsql_ccflags = 'db_version:#DB_VERSION#']',
'#DB_VERSION#',
v_db_version);
end;
/
prompt Compile package om_tapigen (spec)
CREATE OR REPLACE PACKAGE om_tapigen AUTHID CURRENT_USER IS
c_generator CONSTANT VARCHAR2(10 CHAR) := 'OM_TAPIGEN';
c_generator_version CONSTANT VARCHAR2(10 CHAR) := '0.6.3';
/**
Oracle PL/SQL Table API Generator
=================================
_This table API generator needs an Oracle DB version 12.1 or higher and can be
integrated in the Oracle SQL-Developer with an additional wrapper package
for the [SQL Developer extension oddgen](https://www.oddgen.org/)._
The effort of generated API's is to reduce your PL/SQL code by calling standard
procedures and functions for usual DML operations on tables. So the generated
table APIs work as a logical layer between your business logic and the data. And
by the way this logical layer enables you to easily separate the data schema and
the UI schema for your applications to improve security by granting only execute
rights on table APIs to the application schema. In addition to that table APIs
will speed up your development cycles because developers are able to set the
focal point to the business logic instead of wasting time by manual creating
boilerplate code for your tables.
> Get Rid of Hard-Coding in PL/SQL ([Steven Feuerstein](https://www.youtube.com/playlist?list=PL0mkplxCP4ygQo3zAvhYrrU6hIQ0JtYTA))
FEATURES
- Generates small wrappers around your tables
- Highly configurable
- You can enable or disable separately insert, update and delete functionality
- Standard CRUD methods (column and row type based) and an additional create or update method
- Set based methods for high performance DML processing
- For each unique constraint a read method and a getter to fetch the primary key
- Functions to check if a row exists (primary key based, returning boolean or varchar2)
- Support for audit columns
- Support for a row version column
- Optional getter and setter for each column
- Optional 1:1 view to support the separation of concerns (also known as ThickDB/SmartDB/PinkDB paradigm)
- Optional DML view with an instead of trigger to support low code tools like APEX
PREREQUISITES
- Oracle 12.1 or higher
LICENSE
- [The MIT License (MIT)](https://github.com/OraMUC/table-api-generator/blob/master/LICENSE.txt)
- Copyright (c) 2015-2020 André Borngräber, Ottmar Gobrecht
We give our best to produce clean and robust code, but we are NOT responsible,
if you loose any code or data by using this API generator. By using it you
accept the MIT license. As a best practice test the generator first in your
development environment and decide after your tests, if you want to use it in
production. If you miss any feature or find a bug, we are happy to hear from you
via the GitHub [issues](https://github.com/OraMUC/table-api-generator/issues)
functionality.
DOCS
- [Changelog](https://github.com/OraMUC/table-api-generator/blob/master/docs/changelog.md)
- [Getting started](https://github.com/OraMUC/table-api-generator/blob/master/docs/getting-started.md)
- [Detailed parameter descriptions](https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md)
- [Bulk processing](https://github.com/OraMUC/table-api-generator/blob/master/docs/bulk-processing.md)
- [Example API](https://github.com/OraMUC/table-api-generator/blob/master/docs/example-api.md)
- [SQL Developer integration](https://github.com/OraMUC/table-api-generator/blob/master/docs/sql-developer-integration.md)
LINKS
- [Project home page](https://github.com/OraMUC/table-api-generator)
- [Download the latest version](https://github.com/OraMUC/table-api-generator/releases/latest)
- [Issues](https://github.com/OraMUC/table-api-generator/issues)
**/
--------------------------------------------------------------------------------
-- Public constants (c_*) and subtypes (t_*)
--------------------------------------------------------------------------------
c_ora_max_name_len CONSTANT INTEGER := $IF $$db_version < 121 $THEN 30 $ELSE ora_max_name_len $END;
SUBTYPE t_ora_max_name_len IS VARCHAR2(c_ora_max_name_len CHAR); -- 30 or 128 depending on the system
SUBTYPE t_vc2_1 IS VARCHAR2(1 CHAR);
SUBTYPE t_vc2_2 IS VARCHAR2(2 CHAR);
SUBTYPE t_vc2_5 IS VARCHAR2(5 CHAR);
SUBTYPE t_vc2_10 IS VARCHAR2(10 CHAR);
SUBTYPE t_vc2_20 IS VARCHAR2(20 CHAR);
SUBTYPE t_vc2_30 IS VARCHAR2(30 CHAR);
SUBTYPE t_vc2_64 IS VARCHAR2(64 CHAR);
SUBTYPE t_vc2_100 IS VARCHAR2(100 CHAR);
SUBTYPE t_vc2_128 IS VARCHAR2(128 CHAR);
SUBTYPE t_vc2_200 IS VARCHAR2(200 CHAR);
SUBTYPE t_vc2_4k IS VARCHAR2(4000 CHAR);
SUBTYPE t_vc2_16K IS VARCHAR2(16000 CHAR);
SUBTYPE t_vc2_32K IS VARCHAR2(32767 CHAR);
c_audit_user_expression CONSTANT t_vc2_200 := q'[coalesce(sys_context('apex$session','app_user'), sys_context('userenv','os_user'), sys_context('userenv','session_user'))]';
--------------------------------------------------------------------------------
-- Public global constants c_*
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- Public record (t_rec_*) and collection (t_tab_*) types
--------------------------------------------------------------------------------
TYPE t_rec_existing_apis IS RECORD(
errors t_vc2_4k,
owner all_users.username%TYPE,
table_name all_objects.object_name%TYPE,
package_name all_objects.object_name%TYPE,
spec_status all_objects.status%TYPE,
spec_last_ddl_time all_objects.last_ddl_time%TYPE,
body_status all_objects.status%TYPE,
body_last_ddl_time all_objects.last_ddl_time%TYPE,
generator t_vc2_10,
generator_version t_vc2_10,
generator_action t_vc2_30,
generated_at DATE,
generated_by all_users.username%TYPE,
p_owner all_users.username%TYPE,
p_table_name all_objects.object_name%TYPE,
p_enable_insertion_of_rows t_vc2_5,
p_enable_column_defaults t_vc2_5,
p_enable_update_of_rows t_vc2_5,
p_enable_deletion_of_rows t_vc2_5,
p_enable_parameter_prefixes t_vc2_5,
p_enable_proc_with_out_params t_vc2_5,
p_enable_getter_and_setter t_vc2_5,
p_col_prefix_in_method_names t_vc2_5,
p_return_row_instead_of_pk t_vc2_5,
p_double_quote_names t_vc2_5,
p_default_bulk_limit INTEGER,
p_enable_dml_view t_vc2_5,
p_dml_view_name all_objects.object_name%TYPE,
p_dml_view_trigger_name all_objects.object_name%TYPE,
p_enable_one_to_one_view t_vc2_5,
p_one_to_one_view_name all_objects.object_name%TYPE,
p_api_name all_objects.object_name%TYPE,
p_sequence_name all_objects.object_name%TYPE,
p_exclude_column_list t_vc2_4k,
p_audit_column_mappings t_vc2_4k,
p_audit_user_expression t_vc2_4k,
p_row_version_column_mapping t_vc2_4k,
p_tenant_column_mapping t_vc2_4k,
p_enable_custom_defaults t_vc2_5,
p_custom_default_values t_vc2_30);
TYPE t_tab_existing_apis IS TABLE OF t_rec_existing_apis;
--
TYPE t_rec_naming_conflicts IS RECORD(
object_name all_objects.object_name%TYPE,
object_type all_objects.object_type%TYPE,
status all_objects.status%TYPE,
last_ddl_time all_objects.last_ddl_time%TYPE);
TYPE t_tab_naming_conflicts IS TABLE OF t_rec_naming_conflicts;
--
TYPE t_rec_debug_data IS RECORD(
run INTEGER,
run_time NUMBER,
owner all_users.username%TYPE,
table_name all_objects.object_name%TYPE,
step INTEGER,
elapsed NUMBER,
execution NUMBER,
action t_vc2_64,
start_time TIMESTAMP(6));
TYPE t_tab_debug_data IS TABLE OF t_rec_debug_data;
--
TYPE t_rec_columns IS RECORD(
column_name all_tab_cols.column_name%TYPE,
data_type all_tab_cols.data_type%TYPE,
char_length all_tab_cols.char_length%TYPE,
data_length all_tab_cols.data_length%TYPE,
data_precision all_tab_cols.data_precision%TYPE,
data_scale all_tab_cols.data_scale%TYPE,
data_default t_vc2_4k,
data_custom_default t_vc2_4k,
custom_default_source t_vc2_20,
identity_type t_vc2_20,
default_on_null_yn t_vc2_1,
is_pk_yn t_vc2_1,
is_uk_yn t_vc2_1,
is_fk_yn t_vc2_1,
is_nullable_yn t_vc2_1,
is_hidden_yn t_vc2_1,
is_virtual_yn t_vc2_1,
is_excluded_yn t_vc2_1,
audit_type t_vc2_20,
row_version_expression t_vc2_4k,
tenant_expression t_vc2_4k,
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_debug_columns IS TABLE OF t_rec_columns;
/* We use t_tab_debug_columns as a private array/collection inside the package body
indexed by binary_integer. For the pipelined function util_view_columns_array
we need this additional table type. */
--
TYPE t_rec_package_state IS RECORD(
package_status_key t_vc2_30,
value t_vc2_200);
TYPE t_tab_package_state IS TABLE OF t_rec_package_state;
/* For debugging we can view some global package state
variables with the pipelined function util_view_package_state. */
--
TYPE t_rec_clob_line_by_line IS RECORD(
text t_vc2_4k);
TYPE t_tab_clob_line_by_line IS TABLE OF t_rec_clob_line_by_line;
--
TYPE t_tab_vc2_4k IS TABLE OF t_vc2_4k;
--------------------------------------------------------------------------------
-- Public table API generation methods
--------------------------------------------------------------------------------
PROCEDURE compile_api
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER, -- The schema, in which the API should be generated.
p_enable_insertion_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, create methods are generated.
p_enable_column_defaults IN BOOLEAN DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
p_enable_update_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, update methods are generated.
p_enable_deletion_of_rows IN BOOLEAN DEFAULT FALSE, -- If true, delete methods are generated.
p_enable_parameter_prefixes IN BOOLEAN DEFAULT TRUE, -- If true, the param names of methods will be prefixed with 'p_'.
p_enable_proc_with_out_params IN BOOLEAN DEFAULT TRUE, -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
p_enable_getter_and_setter IN BOOLEAN DEFAULT TRUE, -- If true, getter and setter methods are created for each column.
p_col_prefix_in_method_names IN BOOLEAN DEFAULT TRUE, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names.
p_return_row_instead_of_pk IN BOOLEAN DEFAULT FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
p_double_quote_names IN BOOLEAN DEFAULT TRUE, -- If true, object names (owner, table, columns) are placed in double quotes.
p_default_bulk_limit IN INTEGER DEFAULT 1000, -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
p_enable_dml_view IN BOOLEAN DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
p_dml_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_dml_view_trigger_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_enable_one_to_one_view IN BOOLEAN DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
p_one_to_one_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_api_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_sequence_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible.
p_exclude_column_list IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded).
p_audit_column_mappings IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
p_audit_user_expression IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
p_row_version_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
p_tenant_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
p_enable_custom_defaults IN BOOLEAN DEFAULT FALSE, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions).
p_custom_default_values IN XMLTYPE DEFAULT NULL -- Custom values in XML format for the previous option, if the generator provided defaults are not ok.
);
/**
Generates the code and compiles it. When the defaults are used you need only
to provide the table name.
```sql
BEGIN
om_tapigen.compile_api (p_table_name => 'EMP');
END;
```
**/
FUNCTION compile_api_and_get_code
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER, -- The schema, in which the API should be generated.
p_enable_insertion_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, create methods are generated.
p_enable_column_defaults IN BOOLEAN DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
p_enable_update_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, update methods are generated.
p_enable_deletion_of_rows IN BOOLEAN DEFAULT FALSE, -- If true, delete methods are generated.
p_enable_parameter_prefixes IN BOOLEAN DEFAULT TRUE, -- If true, the param names of methods will be prefixed with 'p_'.
p_enable_proc_with_out_params IN BOOLEAN DEFAULT TRUE, -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
p_enable_getter_and_setter IN BOOLEAN DEFAULT TRUE, -- If true, getter and setter methods are created for each column.
p_col_prefix_in_method_names IN BOOLEAN DEFAULT TRUE, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names.
p_return_row_instead_of_pk IN BOOLEAN DEFAULT FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
p_double_quote_names IN BOOLEAN DEFAULT TRUE, -- If true, object names (owner, table, columns) are placed in double quotes.
p_default_bulk_limit IN INTEGER DEFAULT 1000, -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
p_enable_dml_view IN BOOLEAN DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
p_dml_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_dml_view_trigger_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_enable_one_to_one_view IN BOOLEAN DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
p_one_to_one_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_api_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_sequence_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible.
p_exclude_column_list IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded).
p_audit_column_mappings IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
p_audit_user_expression IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
p_row_version_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
p_tenant_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
p_enable_custom_defaults IN BOOLEAN DEFAULT FALSE, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions).
p_custom_default_values IN XMLTYPE DEFAULT NULL -- Custom values in XML format for the previous option, if the generator provided defaults are not ok.
) RETURN CLOB;
/**
Generates the code, compiles and returns it as a CLOB. When the defaults are used you need only
to provide the table name.
```sql
DECLARE
l_api_code CLOB;
BEGIN
l_api_code := om_tapigen.compile_api_and_get_code (p_table_name => 'EMP');
--> do something with the API code
END;
```
**/
FUNCTION get_code
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
p_table_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER, -- The schema, in which the API should be generated.
p_enable_insertion_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, create methods are generated.
p_enable_column_defaults IN BOOLEAN DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
p_enable_update_of_rows IN BOOLEAN DEFAULT TRUE, -- If true, update methods are generated.
p_enable_deletion_of_rows IN BOOLEAN DEFAULT FALSE, -- If true, delete methods are generated.
p_enable_parameter_prefixes IN BOOLEAN DEFAULT TRUE, -- If true, the param names of methods will be prefixed with 'p_'.
p_enable_proc_with_out_params IN BOOLEAN DEFAULT TRUE, -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
p_enable_getter_and_setter IN BOOLEAN DEFAULT TRUE, -- If true, getter and setter methods are created for each column.
p_col_prefix_in_method_names IN BOOLEAN DEFAULT TRUE, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names.
p_return_row_instead_of_pk IN BOOLEAN DEFAULT FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
p_double_quote_names IN BOOLEAN DEFAULT TRUE, -- If true, object names (owner, table, columns) are placed in double quotes.
p_default_bulk_limit IN INTEGER DEFAULT 1000, -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
p_enable_dml_view IN BOOLEAN DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
p_dml_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_dml_view_trigger_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_enable_one_to_one_view IN BOOLEAN DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
p_one_to_one_view_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_api_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
p_sequence_name IN VARCHAR2 DEFAULT NULL, -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible.
p_exclude_column_list IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded).
p_audit_column_mappings IN VARCHAR2 DEFAULT NULL, -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
p_audit_user_expression IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
p_row_version_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
p_tenant_column_mapping IN VARCHAR2 DEFAULT NULL, -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
p_enable_custom_defaults IN BOOLEAN DEFAULT FALSE, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions).
p_custom_default_values IN XMLTYPE DEFAULT NULL -- Custom values in XML format for the previous option, if the generator provided defaults are not ok.
) RETURN CLOB;
/**
Generates the code and returns it as a CLOB. When the defaults are used you
need only to provide the table name.
This function is called by the oddgen wrapper for the SQL Developer integration.
```sql
DECLARE
l_api_code CLOB;
BEGIN
l_api_code := om_tapigen.get_code (p_table_name => 'EMP');
--> do something with the API code
END;
```
**/
--------------------------------------------------------------------------------
-- Public helper methods
--------------------------------------------------------------------------------
FUNCTION view_existing_apis(
p_table_name VARCHAR2 DEFAULT NULL,
p_owner VARCHAR2 DEFAULT USER)
RETURN t_tab_existing_apis PIPELINED;
/**
Helper function (pipelined) to list all APIs generated by om_tapigen.
```sql
SELECT * FROM TABLE (om_tapigen.view_existing_apis);
```
**/
FUNCTION view_naming_conflicts(
p_owner VARCHAR2 DEFAULT USER)
RETURN t_tab_naming_conflicts PIPELINED;
/**
Helper to check possible naming conflicts before the very first usage of the API generator.
Also see the [naming conventions](https://github.com/OraMUC/table-api-generator/blob/master/docs/naming-conventions.md) of the generator.
```sql
SELECT * FROM TABLE (om_tapigen.view_naming_conflicts);
-- No rows expected. After you generated some APIs there will be results ;-)
```
**/
FUNCTION util_get_column_data_default(
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_owner VARCHAR2 DEFAULT USER)
RETURN VARCHAR2;
/*
Helper to read a column data default from the dictionary.
[Working with long columns](http://www.oracle-developer.net/display.php?id=430).
*/
FUNCTION util_get_cons_search_condition(
p_constraint_name IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER)
RETURN VARCHAR2;
/*
Helper to read a constraint search condition from the dictionary (not needed
in 12cR1 and above, there we have a column search_condition_vc in
user_constraints).
*/
FUNCTION util_split_to_table(
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN t_tab_vc2_4k PIPELINED;
/*
Helper function to split a string to a selectable table.
```sql
SELECT column_value FROM TABLE (om_tapigen.util_split_to_table('1,2,3,test'));
```
*/
FUNCTION util_get_ora_max_name_len
RETURN INTEGER;
/*
Helper function to determine the maximum length for an identifier name (e.g.
column name). Returns the package constant c_ora_max_name_len, which is
determined by a conditional compilation.
*/
PROCEDURE util_set_debug_on;
/*
Enable (and reset) the debugging (previous debug data will be lost)
```sql
BEGIN
om_tapigen.util_set_debug_on;
END;
```
*/
PROCEDURE util_set_debug_off;
/*
Disable the debugging
```sql
BEGIN
om_tapigen.util_set_debug_off;
END;
```
*/
FUNCTION util_view_debug_log
RETURN t_tab_debug_data PIPELINED;
/*
View the debug details. Maximum 999 API creations are captured for memory
reasons. You can reset the debugging by calling `om_tapigen.util_set_debug_on`.
```sql
SELECT * FROM TABLE(om_tapigen.util_view_debug_log);
```
*/
FUNCTION util_view_columns_array
RETURN t_tab_debug_columns PIPELINED;
/*
View the internal columns array from the last API generation. This view is
independend from the debug mode, because this array is resetted for each API
generation.
```sql
SELECT * FROM TABLE(om_tapigen.util_view_columns_array);
```
*/
FUNCTION util_view_package_state
RETURN t_tab_package_state PIPELINED;
/*
View some informations from the internal package state for debug purposes.
```sql
SELECT * FROM TABLE(om_tapigen.util_view_package_state);
```
*/
END om_tapigen;
/
show errors
prompt Compile package om_tapigen (body)
CREATE OR REPLACE PACKAGE BODY om_tapigen IS
-----------------------------------------------------------------------------
-- Private global constants (c_*)
-----------------------------------------------------------------------------
c_generator_error_number CONSTANT PLS_INTEGER := -20000;
c_lf CONSTANT t_vc2_1 := chr(10);
c_lflf CONSTANT t_vc2_2 := chr(10) || chr(10);
c_list_delimiter CONSTANT t_vc2_2 := ',' || chr(10);
c_custom_defaults_present_msg CONSTANT t_vc2_30 := 'SEE_END_OF_API_PACKAGE_SPEC';
c_spec_options_min_line CONSTANT INTEGER := 5;
c_spec_options_max_line CONSTANT INTEGER := 42;
c_debug_max_runs CONSTANT INTEGER := 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,
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,
double_quote_names BOOLEAN,
default_bulk_limit INTEGER,
enable_dml_view BOOLEAN,
dml_view_name all_objects.object_name%TYPE,
dml_view_trigger_name all_objects.object_name%TYPE,
enable_one_to_one_view BOOLEAN,
one_to_one_view_name all_objects.object_name%TYPE,
api_name all_objects.object_name%TYPE,
sequence_name all_sequences.sequence_name%TYPE,
exclude_column_list t_vc2_4k,
audit_column_mappings t_vc2_4k,
audit_user_expression t_vc2_4k,
row_version_column_mapping t_vc2_4k,
tenant_column_mapping t_vc2_4k,
enable_custom_defaults BOOLEAN,
custom_default_values XMLTYPE,
custom_defaults_serialized t_vc2_32k);
TYPE t_rec_status IS RECORD(
generator_action t_vc2_30,
column_prefix all_tab_cols.column_name%TYPE,
pk_is_multi_column BOOLEAN,
identity_column all_tab_cols.column_name%TYPE,
identity_type t_vc2_30,
xmltype_column_present BOOLEAN,
number_of_data_columns INTEGER,
number_of_pk_columns INTEGER,
number_of_uk_columns INTEGER,
number_of_fk_columns INTEGER,
rpad_columns INTEGER,
rpad_pk_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 t_vc2_32k,
api_spec CLOB,
api_spec_varchar_cache t_vc2_32k,
api_body CLOB,
api_body_varchar_cache t_vc2_32k,
dml_view CLOB,
dml_view_varchar_cache t_vc2_32k,
dml_view_trigger CLOB,
dml_view_trigger_varchar_cache t_vc2_32k,
one_to_one_view CLOB,
one_to_one_view_varchar_cache t_vc2_32k
);
--
TYPE t_rec_template_options IS RECORD(
use_column_defaults BOOLEAN,
respect_g_iterator BOOLEAN,
crud_mode t_vc2_10,
padding INTEGER);
--
TYPE t_rec_list IS RECORD(
col1 varchar2(200 char),
col2 varchar2(2000 char));
TYPE t_tab_list IS TABLE OF t_rec_list INDEX BY BINARY_INTEGER;
--
TYPE t_rec_iterator IS RECORD(
column_name_compare all_tab_cols.column_name%TYPE,
column_name all_tab_cols.column_name%TYPE,
method_name all_tab_cols.column_name%TYPE,
parameter_name all_tab_cols.column_name%TYPE,
current_uk_constraint all_objects.object_name%TYPE);
--
TYPE t_rec_debug_details IS RECORD(
step INTEGER(4),
module t_vc2_64,
action t_vc2_64,
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 t_vc2_64;
-- records
g_params t_rec_params;
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(
search_condition_vc,
'^\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
(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),
t AS
(SELECT DISTINCT column_id,
column_name,
data_type,
char_length,
data_length,
data_precision,
data_scale,
identity_type,
CASE WHEN default_on_null = 'YES' THEN 'Y' ELSE 'N' END AS default_on_null_yn,
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,
CASE WHEN column_name_nn IS NULL THEN 'Y' ELSE 'N' END AS is_nullable_yn,
CASE WHEN hidden_column = 'YES' THEN 'Y' ELSE 'N' END AS is_hidden_yn,
CASE WHEN virtual_column = 'YES' THEN 'Y' ELSE 'N' END AS is_virtual_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 user_generated = 'YES'
ORDER BY column_id)
SELECT column_name,
data_type,
char_length,
data_length,
data_precision,
data_scale,
data_default,
NULL AS data_custom_default,
NULL AS custom_default_source,
identity_type,
default_on_null_yn,
'N' AS is_pk_yn,
'N' AS is_uk_yn,
'N' AS is_fk_yn,
is_nullable_yn,
is_hidden_yn,
is_virtual_yn,
is_excluded_yn,
NULL AS audit_type,
NULL AS row_version_expression,
NULL AS tenant_expression,
NULL AS r_owner,
NULL AS r_table_name,
NULL AS r_column_name
FROM t;
-----------------------------------------------------------------------------
PROCEDURE util_execute_sql(p_sql IN OUT NOCOPY CLOB) IS
v_cursor PLS_INTEGER;
v_exec_result PLS_INTEGER;
BEGIN
v_cursor := sys.dbms_sql.open_cursor;
sys.dbms_sql.parse(v_cursor, p_sql, sys.dbms_sql.native);
v_exec_result := sys.dbms_sql.execute(v_cursor);
sys.dbms_sql.close_cursor(v_cursor);
EXCEPTION
WHEN OTHERS THEN
sys.dbms_sql.close_cursor(v_cursor);
RAISE;
END util_execute_sql;
-----------------------------------------------------------------------------
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;
-----------------------------------------------------------------------------
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;
FUNCTION util_double_quote(p_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN CASE WHEN g_params.double_quote_names THEN '"'||p_name||'"' ELSE p_name END;
END util_double_quote;
-----------------------------------------------------------------------------
/*
util_get_attribute_surrogate is a private helper function to get 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 comparison in
Oracle: null compared with null is never true. That is the reason to compare
it in this way:
coalesce(old_value, surrogate) = coalesce(new_value, surrogate)
For a string this could be:
coalesce(old_value, '@@@@@@@@@@@@@@@') = coalesce(new_value, '@@@@@@@@@@@@@@@')
This will be evaluated to true if both values are null.
*/
FUNCTION util_get_attribute_surrogate(p_data_type IN user_tab_cols.data_type%TYPE) RETURN VARCHAR2 IS
v_return t_vc2_100;
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 = 'RAW' THEN
'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
--
FUNCTION get_coalesce(p_attribute IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN
CASE
WHEN NOT p_nullable THEN
p_attribute
ELSE
'COALESCE(' || p_attribute || ', ' || util_get_attribute_surrogate(p_data_type) || ')'
END;
END get_coalesce;
--
BEGIN
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
'sys.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;
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
BEGIN
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;
END util_get_vc2_4000_operation;
-----------------------------------------------------------------------------
FUNCTION util_get_user_name RETURN all_users.username%TYPE IS
BEGIN
RETURN upper (coalesce(
sys_context('apex$session', 'app_user'),
sys_context('userenv', 'os_user'),
sys_context('userenv', 'session_user')));
END util_get_user_name;