-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathplex_install.sql
3749 lines (3346 loc) · 143 KB
/
plex_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
whenever sqlerror exit sql.sqlcode rollback
prompt
prompt Installing PL/SQL Export Utilities
prompt ============================================================
prompt Set compiler flags
DECLARE
v_apex_installed VARCHAR2(5) := 'FALSE'; -- Do not change (is set dynamically).
v_ords_installed VARCHAR2(5) := 'FALSE'; -- Do not change (is set dynamically).
v_java_installed VARCHAR2(5) := 'FALSE'; -- Do not change (is set dynamically).
v_utils_public VARCHAR2(5) := 'FALSE'; -- Make utilities public available (for testing or other usages).
v_debug_on VARCHAR2(5) := 'FALSE'; -- Object DDL: extract only one object per type to find problematic ones and save time in big schemas like APEX_XXX.
BEGIN
FOR i IN (SELECT 1
FROM all_objects
WHERE object_type = 'SYNONYM'
AND object_name = 'APEX_EXPORT') LOOP
v_apex_installed := 'TRUE';
END LOOP;
FOR i IN (SELECT 1
FROM all_objects
WHERE object_type = 'SYNONYM'
AND object_name = 'ORDS_EXPORT') LOOP
v_ords_installed := 'TRUE';
END LOOP;
FOR i IN (SELECT 1
FROM all_objects
WHERE object_type = 'SYNONYM'
AND object_name = 'DBMS_JAVA') LOOP
v_java_installed := 'TRUE';
END LOOP;
-- Show unset compiler flags as errors (results for example in errors like "PLW-06003: unknown inquiry directive '$$UTILS_PUBLIC'")
EXECUTE IMMEDIATE 'alter session set plsql_warnings = ''ENABLE:6003''';
-- Finally set compiler flags
EXECUTE IMMEDIATE 'alter session set plsql_ccflags = '''
|| 'apex_installed:' || v_apex_installed || ','
|| 'ords_installed:' || v_ords_installed || ','
|| 'java_installed:' || v_java_installed || ','
|| 'utils_public:' || v_utils_public || ','
|| 'debug_on:' || v_debug_on || '''';
END;
/
prompt Compile package plex (spec)
CREATE OR REPLACE PACKAGE PLEX AUTHID current_user IS
c_plex_name CONSTANT VARCHAR2(30 CHAR) := 'PLEX - PL/SQL Export Utilities';
c_plex_version CONSTANT VARCHAR2(10 CHAR) := '2.4.2';
c_plex_url CONSTANT VARCHAR2(40 CHAR) := 'https://github.com/ogobrecht/plex';
c_plex_license CONSTANT VARCHAR2(10 CHAR) := 'MIT';
c_plex_license_url CONSTANT VARCHAR2(60 CHAR) := 'https://github.com/ogobrecht/plex/blob/master/LICENSE.txt';
c_plex_author CONSTANT VARCHAR2(20 CHAR) := 'Ottmar Gobrecht';
/**
PL/SQL Export Utilities
=======================
PLEX was created to be able to quickstart version control for existing Oracle DB projects and has currently two main functions called **BackApp** and **Queries_to_CSV**. Queries_to_CSV is used by BackApp as a helper function, but its functionality is also useful standalone.
Also see this resources for more information:
- [Blog post on how to getting started](https://ogobrecht.github.io/posts/2018-08-26-plex-plsql-export-utilities)
- [PLEX project page on GitHub](https://github.com/ogobrecht/plex)
- [Changelog](https://github.com/ogobrecht/plex/blob/master/CHANGELOG.md)
- [Give feedback](https://github.com/ogobrecht/plex/issues/new)
DEPENDENCIES
The package itself is independend, but functionality varies on the following conditions:
- For APEX app export: APEX >= 5.1.4 installed
- For ORDS modules export: ORDS >= 18.3 installed (I think package ords_export is included since this version, but I don't know it)
- ATTENTION: There seems to be a [bug in ORDS 19.2](https://community.oracle.com/thread/4292776) which prevents you to export ORDS modules via the package ords_export. Please see plex_error_log.md, if you miss your ORDS modules after an export - this is no problem of PLEX.
INSTALLATION
- Download the [latest version](https://github.com/ogobrecht/plex/releases/latest)
- Unzip it, open a shell and go into the root directory
- Start SQL*Plus (or another tool which can run SQL scripts)
- To install PLEX run the provided install script `plex_install.sql` (script provides compiler flags)
- To uninstall PLEX run the provided script `plex_uninstall.sql` or drop the package manually
**/
--------------------------------------------------------------------------------------------------------------------------------
-- CONSTANTS, TYPES
--------------------------------------------------------------------------------------------------------------------------------
c_app_info_length CONSTANT PLS_INTEGER := 64;
SUBTYPE app_info_text IS VARCHAR2(64 CHAR);
SUBTYPE file_path IS VARCHAR2(256 CHAR);
TYPE rec_error_log IS RECORD (
time_stamp TIMESTAMP,
file_name VARCHAR2(255),
error_text VARCHAR2(200),
call_stack VARCHAR2(500));
TYPE tab_error_log IS TABLE OF rec_error_log;
TYPE rec_runtime_log IS RECORD (
overall_start_time TIMESTAMP,
overall_run_time NUMBER,
step INTEGER,
elapsed NUMBER,
execution NUMBER,
module app_info_text,
action app_info_text);
TYPE tab_runtime_log IS TABLE OF rec_runtime_log;
TYPE rec_export_file IS RECORD (
name VARCHAR2(255),
contents CLOB);
TYPE tab_export_files IS TABLE OF rec_export_file;
TYPE tab_vc32k IS TABLE OF varchar2(32767);
TYPE tab_vc1k IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
--------------------------------------------------------------------------------------------------------------------------------
-- MAIN METHODS
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION backapp (
$if $$apex_installed $then
-- APEX App:
p_app_id IN NUMBER DEFAULT null, -- If null, we simply skip the APEX app export.
p_app_date IN BOOLEAN DEFAULT true, -- If true, include export date and time in the result.
p_app_public_reports IN BOOLEAN DEFAULT true, -- If true, include public reports that a user saved.
p_app_private_reports IN BOOLEAN DEFAULT false, -- If true, include private reports that a user saved.
p_app_notifications IN BOOLEAN DEFAULT false, -- If true, include report notifications.
p_app_translations IN BOOLEAN DEFAULT true, -- If true, include application translation mappings and all text from the translation repository.
p_app_pkg_app_mapping IN BOOLEAN DEFAULT false, -- If true, export installed packaged applications with references to the packaged application definition. If FALSE, export them as normal applications.
p_app_original_ids IN BOOLEAN DEFAULT false, -- If true, export with the IDs as they were when the application was imported.
p_app_subscriptions IN BOOLEAN DEFAULT true, -- If true, components contain subscription references.
p_app_comments IN BOOLEAN DEFAULT true, -- If true, include developer comments.
p_app_supporting_objects IN VARCHAR2 DEFAULT null, -- If 'Y', export supporting objects. If 'I', automatically install on import. If 'N', do not export supporting objects. If null, the application's include in export deployment value is used.
p_app_include_single_file IN BOOLEAN DEFAULT false, -- If true, the single sql install file is also included beside the splitted files.
p_app_build_status_run_only IN BOOLEAN DEFAULT false, -- If true, the build status of the app will be overwritten to RUN_ONLY.
$end
$if $$ords_installed $then
-- ORDS Modules:
p_include_ords_modules IN BOOLEAN DEFAULT false, -- If true, include ORDS modules of current user/schema.
$end
-- Schema Objects:
p_include_object_ddl IN BOOLEAN DEFAULT false, -- If true, include DDL of current user/schema and all its objects.
p_object_type_like IN VARCHAR2 DEFAULT null, -- A comma separated list of like expressions to filter the objects - example: '%BODY,JAVA%' will be translated to: ... from user_objects where ... and (object_type like '%BODY' escape '\' or object_type like 'JAVA%' escape '\').
p_object_type_not_like IN VARCHAR2 DEFAULT null, -- A comma separated list of not like expressions to filter the objects - example: '%BODY,JAVA%' will be translated to: ... from user_objects where ... and (object_type not like '%BODY' escape '\' and object_type not like 'JAVA%' escape '\').
p_object_name_like IN VARCHAR2 DEFAULT null, -- A comma separated list of like expressions to filter the objects - example: 'EMP%,DEPT%' will be translated to: ... from user_objects where ... and (object_name like 'EMP%' escape '\' or object_name like 'DEPT%' escape '\').
p_object_name_not_like IN VARCHAR2 DEFAULT null, -- A comma separated list of not like expressions to filter the objects - example: 'EMP%,DEPT%' will be translated to: ... from user_objects where ... and (object_name not like 'EMP%' escape '\' and object_name not like 'DEPT%' escape '\').
p_object_view_remove_col_list IN BOOLEAN DEFAULT true, -- If true, the outer column list, added by Oracle on views during compilation, is removed
-- Table Data:
p_include_data IN BOOLEAN DEFAULT false, -- If true, include CSV data of each table.
p_data_as_of_minutes_ago IN NUMBER DEFAULT 0, -- Read consistent data with the resulting timestamp(SCN).
p_data_max_rows IN NUMBER DEFAULT 1000, -- Maximum number of rows per table.
p_data_table_name_like IN VARCHAR2 DEFAULT null, -- A comma separated list of like expressions to filter the tables - example: 'EMP%,DEPT%' will be translated to: where ... and (table_name like 'EMP%' escape '\' or table_name like 'DEPT%' escape '\').
p_data_table_name_not_like IN VARCHAR2 DEFAULT null, -- A comma separated list of not like expressions to filter the tables - example: 'EMP%,DEPT%' will be translated to: where ... and (table_name not like 'EMP%' escape '\' and table_name not like 'DEPT%' escape '\').
p_data_format IN VARCHAR2 DEFAULT 'csv', -- A comma separated list of formats - currently supported formats are CSV and INSERT - example: 'csv,insert' will export for each table a csv file and a sql file with insert statements. For insert you can also give the number of rows per "insert all" statement (defaults to 20) - example: 'csv,insert:10' or 'insert:5'.
-- General Options:
p_include_templates IN BOOLEAN DEFAULT true, -- If true, include templates for README.md, export and install scripts.
p_include_runtime_log IN BOOLEAN DEFAULT true, -- If true, generate file plex_runtime_log.md with detailed runtime infos.
p_include_error_log IN BOOLEAN DEFAULT true, -- If true, generate file plex_error_log.md with detailed error messages.
p_base_path_backend IN VARCHAR2 DEFAULT 'app_backend', -- The base path in the project root for the Schema objects.
p_base_path_frontend IN VARCHAR2 DEFAULT 'app_frontend', -- The base path in the project root for the APEX app.
p_base_path_web_services IN VARCHAR2 DEFAULT 'app_web_services', -- The base path in the project root for the ORDS modules.
p_base_path_data IN VARCHAR2 DEFAULT 'app_data', -- The base path in the project root for the table data.
p_base_path_docs IN VARCHAR2 DEFAULT 'docs', -- The base path in the project root for the docs.
p_base_path_tests IN VARCHAR2 DEFAULT 'tests', -- The base path in the project root for the tests.
p_base_path_scripts IN VARCHAR2 DEFAULT 'scripts', -- The base path in the project root for the generated deploy scripts.
p_base_path_script_logs IN VARCHAR2 DEFAULT 'scripts/logs', -- The base path in the project root for the deploy script log files.
p_scripts_working_directory IN VARCHAR2 DEFAULT 'scripts') -- The working directory of the shell (relative to the project root) where deploy scripts will be called. Set this to null if you run the deploy scripts from the project root.
RETURN tab_export_files;
/**
Get a file collection of an APEX application (or the current user/schema only) including:
- The app export SQL files splitted ready to use for version control and deployment
- Optional the DDL scripts for all objects and grants
- Optional the data in CSV files (this option was implemented to track catalog tables, can be used as logical backup, has the typical CSV limitations...)
- Everything in a (hopefully) nice directory structure
EXAMPLE BASIC USAGE
```sql
DECLARE
l_file_collection plex.tab_export_files;
BEGIN
l_file_collection := plex.backapp(
p_app_id => 100, -- parameter only available when APEX is installed
p_include_ords_modules => true, -- parameter only available when ORDS is installed
p_include_object_ddl => false,
p_include_data => false,
p_include_templates => false);
-- do something with the file collection
FOR i IN 1..l_file_collection.count LOOP
dbms_output.put_line(i || ' | '
|| lpad(round(length(l_file_collection(i).contents) / 1024), 3) || ' kB' || ' | '
|| l_file_collection(i).name);
END LOOP;
END;
{{/}}
```
EXAMPLE ZIP FILE PL/SQL
```sql
DECLARE
l_zip_file BLOB;
BEGIN
l_zip_file := plex.to_zip(plex.backapp(
p_app_id => 100, -- parameter only available when APEX is installed
p_include_ords_modules => true, -- parameter only available when ORDS is installed
p_include_object_ddl => true,
p_include_data => false,
p_include_templates => true));
-- do something with the zip file
-- Your code here...
END;
{{/}}
```
EXAMPLE ZIP FILE SQL
```sql
-- Inline function because of boolean parameters (needs Oracle 12c or higher).
-- Alternative create a helper function and call that in a SQL context.
WITH
FUNCTION backapp RETURN BLOB IS
BEGIN
RETURN plex.to_zip(plex.backapp(
p_app_id => 100, -- parameter only available when APEX is installed
p_include_ords_modules => true, -- parameter only available when ORDS is installed
p_include_object_ddl => true,
p_include_data => false,
p_include_templates => true));
END backapp;
SELECT backapp FROM dual;
```
EXAMPLE ZIP FILE SQL*Plus
```sql
-- SQL*Plus can only handle CLOBs, no BLOBs - so we are forced to create a CLOB
-- for spooling the content to the client disk. You need to decode the base64
-- encoded file before you are able to unzip the content. Also see this blog
-- post how to do this on different operating systems:
-- https://www.igorkromin.net/index.php/2017/04/26/base64-encode-or-decode-on-the-command-line-without-installing-extra-tools-on-linux-windows-or-macos/
-- Example Windows: certutil -decode app_100.zip.base64 app_100.zip
-- Example Mac: base64 -D -i app_100.zip.base64 -o app_100.zip
-- Example Linux: base64 -d app_100.zip.base64 > app_100.zip
set verify off feedback off heading off
set trimout on trimspool on pagesize 0 linesize 5000 long 100000000 longchunksize 32767
whenever sqlerror exit sql.sqlcode rollback
variable contents clob
BEGIN
:contents := plex.to_base64(plex.to_zip(plex.backapp(
p_app_id => 100, -- parameter only available when APEX is installed
p_include_ords_modules => true, -- parameter only available when ORDS is installed
p_include_object_ddl => true,
p_include_data => false,
p_include_templates => true)));
END;
{{/}}
set termout off
spool "app_100.zip.base64"
print contents
spool off
set termout on
```
**/
PROCEDURE add_query (
p_query IN VARCHAR2, -- The query itself
p_file_name IN VARCHAR2, -- File name like 'Path/to/your/file-without-extension'.
p_max_rows IN NUMBER DEFAULT 1000); -- The maximum number of rows to be included in your file.
/**
Add a query to be processed by the method queries_to_csv. You can add as many queries as you like.
EXAMPLE
```sql
BEGIN
plex.add_query(
p_query => 'select * from user_tables',
p_file_name => 'user_tables');
END;
{{/}}
```
**/
FUNCTION queries_to_csv (
p_delimiter IN VARCHAR2 DEFAULT ',', -- The column delimiter.
p_quote_mark IN VARCHAR2 DEFAULT '"', -- Used when the data contains the delimiter character.
p_header_prefix IN VARCHAR2 DEFAULT NULL, -- Prefix the header line with this text.
p_include_runtime_log IN BOOLEAN DEFAULT true, -- If true, generate file plex_runtime_log.md with runtime statistics.
p_include_error_log IN BOOLEAN DEFAULT true) -- If true, generate file plex_error_log.md with detailed error messages.
RETURN tab_export_files;
/**
Export one or more queries as CSV data within a file collection.
EXAMPLE BASIC USAGE
```sql
DECLARE
l_file_collection plex.tab_export_files;
BEGIN
--fill the queries array
plex.add_query(
p_query => 'select * from user_tables',
p_file_name => 'user_tables');
plex.add_query(
p_query => 'select * from user_tab_columns',
p_file_name => 'user_tab_columns',
p_max_rows => 10000);
-- process the queries
l_file_collection := plex.queries_to_csv;
-- do something with the file collection
FOR i IN 1..l_file_collection.count LOOP
dbms_output.put_line(i || ' | '
|| lpad(round(length(l_file_collection(i).contents) / 1024), 3) || ' kB' || ' | '
|| l_file_collection(i).name);
END LOOP;
END;
{{/}}
```
EXAMPLE EXPORT ZIP FILE PL/SQL
```sql
DECLARE
l_zip_file BLOB;
BEGIN
--fill the queries array
plex.add_query(
p_query => 'select * from user_tables',
p_file_name => 'user_tables');
plex.add_query(
p_query => 'select * from user_tab_columns',
p_file_name => 'user_tab_columns',
p_max_rows => 10000);
-- process the queries
l_zip_file := plex.to_zip(plex.queries_to_csv);
-- do something with the zip file
-- Your code here...
END;
{{/}}
```
EXAMPLE EXPORT ZIP FILE SQL
```sql
WITH
FUNCTION queries_to_csv_zip RETURN BLOB IS
v_return BLOB;
BEGIN
plex.add_query(
p_query => 'select * from user_tables',
p_file_name => 'user_tables');
plex.add_query(
p_query => 'select * from user_tab_columns',
p_file_name => 'user_tab_columns',
p_max_rows => 10000);
v_return := plex.to_zip(plex.queries_to_csv);
RETURN v_return;
END queries_to_csv_zip;
SELECT queries_to_csv_zip FROM dual;
```
EXAMPLE ZIP FILE SQL*Plus
```sql
-- SQL*Plus can only handle CLOBs, no BLOBs - so we are forced to create a CLOB
-- for spooling the content to the client disk. You need to decode the base64
-- encoded file before you are able to unzip the content. Also see this blog
-- post how to do this on the different operating systems:
-- https://www.igorkromin.net/index.php/2017/04/26/base64-encode-or-decode-on-the-command-line-without-installing-extra-tools-on-linux-windows-or-macos/
-- Example Windows: certutil -decode metadata.zip.base64 metadata.zip
-- Example Mac: base64 -D -i metadata.zip.base64 -o metadata.zip
-- Example Linux: base64 -d metadata.zip.base64 > metadata.zip
set verify off feedback off heading off termout off
set trimout on trimspool on pagesize 0 linesize 5000 long 100000000 longchunksize 32767
whenever sqlerror exit sql.sqlcode rollback
variable contents clob
BEGIN
--fill the queries array
plex.add_query(
p_query => 'select * from user_tables',
p_file_name => 'user_tables');
plex.add_query(
p_query => 'select * from user_tab_columns',
p_file_name => 'user_tab_columns',
p_max_rows => 10000);
-- process the queries
:contents := plex.to_base64(plex.to_zip(plex.queries_to_csv));
END;
{{/}}
spool "metadata.zip.base64"
print contents
spool off
```
**/
FUNCTION to_zip (
p_file_collection IN tab_export_files) -- The file collection to zip.
RETURN BLOB;
/**
Convert a file collection to a zip file.
EXAMPLE
```sql
DECLARE
l_zip BLOB;
BEGIN
l_zip := plex.to_zip(plex.backapp(
p_app_id => 100,
p_include_object_ddl => true));
-- do something with the zip file...
END;
```
**/
FUNCTION to_base64(
p_blob IN BLOB) -- The BLOB to convert.
RETURN CLOB;
/**
Encodes a BLOB into a Base64 CLOB for transfers over a network (like with SQL*Plus). For encoding on the client side see [this blog article](https://www.igorkromin.net/index.php/2017/04/26/base64-encode-or-decode-on-the-command-line-without-installing-extra-tools-on-linux-windows-or-macos/).
```sql
DECLARE
l_clob CLOB;
BEGIN
l_clob := plex.to_base64(plex.to_zip(plex.backapp(
p_app_id => 100,
p_include_object_ddl => true)));
-- do something with the clob...
END;
```
**/
FUNCTION view_error_log RETURN tab_error_log PIPELINED;
/**
View the error log from the last plex run. The internal array for the error log is cleared on each call of BackApp or Queries_to_CSV.
EXAMPLE
```sql
SELECT * FROM TABLE(plex.view_error_log);
```
**/
FUNCTION view_runtime_log RETURN tab_runtime_log PIPELINED;
/**
View the runtime log from the last plex run. The internal array for the runtime log is cleared on each call of BackApp or Queries_to_CSV.
EXAMPLE
```sql
SELECT * FROM TABLE(plex.view_runtime_log);
```
**/
--------------------------------------------------------------------------------------------------------------------------------
-- UTILITIES (only available when v_utils_public is set to 'true' in install script plex_install.sql)
--------------------------------------------------------------------------------------------------------------------------------
$if $$utils_public $then
FUNCTION util_bool_to_string (p_bool IN BOOLEAN) RETURN VARCHAR2;
FUNCTION util_string_to_bool (
p_bool_string IN VARCHAR2,
p_default IN BOOLEAN)
RETURN BOOLEAN;
FUNCTION util_split (
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN tab_vc32k;
FUNCTION util_join (
p_array IN tab_vc32k,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2;
FUNCTION util_clob_to_blob (p_clob CLOB) RETURN BLOB;
/*
ZIP UTILS
- The following four zip utilities are copied from this article:
- Blog: https://technology.amis.nl/2010/03/13/utl_compress-gzip-and-zlib/
- Source: https://technology.amis.nl/wp-content/uploads/2010/06/as_zip10.txt
- Copyright (c) 2010, 2011 by Anton Scheffer (MIT license)
- Thank you for sharing this Anton :-)
*/
FUNCTION util_zip_blob_to_num (
p_blob IN BLOB,
p_len IN INTEGER,
p_pos IN INTEGER)
RETURN NUMBER;
FUNCTION util_zip_little_endian (
p_big IN NUMBER,
p_bytes IN PLS_INTEGER := 4)
RETURN RAW;
PROCEDURE util_zip_add_file (
p_zipped_blob IN OUT BLOB,
p_name IN VARCHAR2,
p_content IN BLOB);
PROCEDURE util_zip_finish (p_zipped_blob IN OUT BLOB);
FUNCTION util_multi_replace (
p_source_string VARCHAR2,
p_01_find VARCHAR2 DEFAULT NULL, p_01_replace VARCHAR2 DEFAULT NULL,
p_02_find VARCHAR2 DEFAULT NULL, p_02_replace VARCHAR2 DEFAULT NULL,
p_03_find VARCHAR2 DEFAULT NULL, p_03_replace VARCHAR2 DEFAULT NULL,
p_04_find VARCHAR2 DEFAULT NULL, p_04_replace VARCHAR2 DEFAULT NULL,
p_05_find VARCHAR2 DEFAULT NULL, p_05_replace VARCHAR2 DEFAULT NULL,
p_06_find VARCHAR2 DEFAULT NULL, p_06_replace VARCHAR2 DEFAULT NULL,
p_07_find VARCHAR2 DEFAULT NULL, p_07_replace VARCHAR2 DEFAULT NULL,
p_08_find VARCHAR2 DEFAULT NULL, p_08_replace VARCHAR2 DEFAULT NULL,
p_09_find VARCHAR2 DEFAULT NULL, p_09_replace VARCHAR2 DEFAULT NULL,
p_10_find VARCHAR2 DEFAULT NULL, p_10_replace VARCHAR2 DEFAULT NULL,
p_11_find VARCHAR2 DEFAULT NULL, p_11_replace VARCHAR2 DEFAULT NULL,
p_12_find VARCHAR2 DEFAULT NULL, p_12_replace VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
FUNCTION util_set_build_status_run_only (p_app_export_sql IN CLOB) RETURN CLOB;
FUNCTION util_calc_data_timestamp (p_as_of_minutes_ago IN NUMBER) RETURN TIMESTAMP;
PROCEDURE util_setup_dbms_metadata (
p_pretty IN BOOLEAN DEFAULT true,
p_constraints IN BOOLEAN DEFAULT true,
p_ref_constraints IN BOOLEAN DEFAULT false,
p_partitioning IN BOOLEAN DEFAULT true,
p_tablespace IN BOOLEAN DEFAULT false,
p_storage IN BOOLEAN DEFAULT false,
p_segment_attributes IN BOOLEAN DEFAULT false,
p_sqlterminator IN BOOLEAN DEFAULT true,
p_constraints_as_alter IN BOOLEAN DEFAULT false,
p_emit_schema IN BOOLEAN DEFAULT false);
FUNCTION util_to_xlsx_datetime (
p_date IN DATE)
RETURN NUMBER;
--------------------------------------------------------------------------------------------------------------------------------
-- The following tools are working on the global private package variables g_clob, g_clob_varchar_cache, g_runlog and g_queries
--------------------------------------------------------------------------------------------------------------------------------
PROCEDURE util_clob_append (p_content IN VARCHAR2);
PROCEDURE util_clob_append (p_content IN CLOB);
PROCEDURE util_clob_replace (
p_search VARCHAR2,
p_replace VARCHAR2);
PROCEDURE util_clob_flush_cache;
PROCEDURE util_clob_add_to_export_files (
p_export_files IN OUT NOCOPY tab_export_files,
p_name IN VARCHAR2);
PROCEDURE util_clob_query_to_csv (
p_query IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 1000,
p_delimiter IN VARCHAR2 DEFAULT ',',
p_quote_mark IN VARCHAR2 DEFAULT '"',
p_header_prefix IN VARCHAR2 DEFAULT NULL);
PROCEDURE util_clob_table_to_insert (
p_table_name IN VARCHAR2,
p_data_scn IN NUMBER,
p_max_rows IN NUMBER DEFAULT 1000,
p_insert_all_size IN NUMBER DEFAULT 10);
PROCEDURE util_clob_create_runtime_log (p_export_files IN OUT NOCOPY tab_export_files);
PROCEDURE util_clob_create_error_log (p_export_files IN OUT NOCOPY tab_export_files);
PROCEDURE util_ensure_unique_file_names (
p_export_files IN OUT NOCOPY tab_export_files,
p_path_to_scripts IN VARCHAR2 DEFAULT NULL);
PROCEDURE util_log_init (p_module IN VARCHAR2);
PROCEDURE util_log_start (p_action IN VARCHAR2);
PROCEDURE util_log_error (p_name VARCHAR2);
PROCEDURE util_log_stop;
FUNCTION util_log_get_runtime (
p_start IN TIMESTAMP,
p_stop IN TIMESTAMP)
RETURN NUMBER;
PROCEDURE util_log_calc_runtimes;
$end
END plex;
/
show errors
prompt Compile package plex (body)
CREATE OR REPLACE PACKAGE BODY plex IS
--------------------------------------------------------------------------------------------------------------------------------
-- CONSTANTS, TYPES, GLOBALS
--------------------------------------------------------------------------------------------------------------------------------
c_tab CONSTANT VARCHAR2(1) := chr(9);
c_cr CONSTANT VARCHAR2(1) := chr(13);
c_lf CONSTANT VARCHAR2(1) := chr(10);
c_crlf CONSTANT VARCHAR2(2) := chr(13) || chr(10);
c_space_crlf CONSTANT VARCHAR2(3) := ' ' || chr(13) || chr(10);
c_at CONSTANT VARCHAR2(1) := '@';
c_hash CONSTANT VARCHAR2(1) := '#';
c_slash CONSTANT VARCHAR2(1) := '/';
c_vc2_max_size CONSTANT PLS_INTEGER := 32767;
c_zip_local_file_header CONSTANT RAW(4) := hextoraw('504B0304');
c_zip_end_of_central_directory CONSTANT RAW(4) := hextoraw('504B0506');
-- numeric type identfiers
c_number CONSTANT PLS_INTEGER := 2; -- FLOAT
c_binary_float CONSTANT PLS_INTEGER := 100;
c_binary_double CONSTANT PLS_INTEGER := 101;
-- string type identfiers
c_char CONSTANT PLS_INTEGER := 96; -- NCHAR
c_varchar2 CONSTANT PLS_INTEGER := 1; -- NVARCHAR2
c_long CONSTANT PLS_INTEGER := 8;
c_clob CONSTANT PLS_INTEGER := 112; -- NCLOB
c_xmltype CONSTANT PLS_INTEGER := 109; -- ANYDATA, ANYDATASET, ANYTYPE, Object type, VARRAY, Nested table
c_rowid CONSTANT PLS_INTEGER := 69;
c_urowid CONSTANT PLS_INTEGER := 208;
-- binary type identfiers
c_raw CONSTANT PLS_INTEGER := 23;
c_long_raw CONSTANT PLS_INTEGER := 24;
c_blob CONSTANT PLS_INTEGER := 113;
c_bfile CONSTANT PLS_INTEGER := 114;
-- date type identfiers
c_date CONSTANT PLS_INTEGER := 12;
c_timestamp CONSTANT PLS_INTEGER := 180;
c_timestamp_tz CONSTANT PLS_INTEGER := 181;
c_timestamp_ltz CONSTANT PLS_INTEGER := 231;
-- interval type identfiers
c_interval_year_to_month CONSTANT PLS_INTEGER := 182;
c_interval_day_to_second CONSTANT PLS_INTEGER := 183;
-- cursor type identfiers
c_ref CONSTANT PLS_INTEGER := 111;
c_ref_cursor CONSTANT PLS_INTEGER := 102; -- same identfiers for strong and weak ref cursor
TYPE tab_errlog IS TABLE OF rec_error_log INDEX BY BINARY_INTEGER;
TYPE rec_runlog_step IS RECORD (
action app_info_text,
start_time TIMESTAMP(6),
stop_time TIMESTAMP(6),
elapsed NUMBER,
execution NUMBER);
TYPE tab_runlog_step IS TABLE OF rec_runlog_step INDEX BY BINARY_INTEGER;
TYPE rec_runlog IS RECORD (
module app_info_text,
start_time TIMESTAMP(6),
stop_time TIMESTAMP(6),
run_time NUMBER,
measured_time NUMBER,
unmeasured_time NUMBER,
data tab_runlog_step);
TYPE rec_queries IS RECORD (--
query VARCHAR2(32767 CHAR),
file_name VARCHAR2(256 CHAR),
max_rows NUMBER DEFAULT 100000);
TYPE tab_queries IS TABLE OF rec_queries INDEX BY BINARY_INTEGER;
TYPE tab_file_list_lookup IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(256);
TYPE rec_files IS RECORD (
ords_modules_ tab_vc1k,
sequences_ tab_vc1k,
tables_ tab_vc1k,
data_ tab_vc1k,
ref_constraints_ tab_vc1k,
indices_ tab_vc1k,
views_ tab_vc1k,
mviews_ tab_vc1k,
types_ tab_vc1k,
type_bodies_ tab_vc1k,
triggers_ tab_vc1k,
functions_ tab_vc1k,
procedures_ tab_vc1k,
packages_ tab_vc1k,
package_bodies_ tab_vc1k,
grants_ tab_vc1k,
other_objects_ tab_vc1k);
TYPE rec_path IS RECORD (
to_backend file_path,
to_frontend file_path,
to_web_services file_path,
to_data file_path,
to_docs file_path,
to_tests file_path,
to_scripts file_path,
to_script_logs file_path,
scripts_work_dir file_path,
from_scripts_to_project_root file_path);
TYPE rec_nls IS RECORD (
numeric_characters varchar2(100),
date_format varchar2(100),
timestamp_format varchar2(100),
timestamp_tz_format varchar2(100));
g_clob CLOB;
g_cache VARCHAR2(32767char);
g_errlog tab_errlog;
g_runlog rec_runlog;
g_queries tab_queries;
--------------------------------------------------------------------------------------------------------------------------------
-- UTILITIES (forward declarations, only compiled when not public)
--------------------------------------------------------------------------------------------------------------------------------
$if not $$utils_public $then
FUNCTION util_bool_to_string (p_bool IN BOOLEAN) RETURN VARCHAR2;
FUNCTION util_string_to_bool (
p_bool_string IN VARCHAR2,
p_default IN BOOLEAN)
RETURN BOOLEAN;
FUNCTION util_split (
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN tab_vc32k;
FUNCTION util_join (
p_array IN tab_vc32k,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2;
FUNCTION util_clob_to_blob (p_clob CLOB) RETURN BLOB;
/*
ZIP UTILS
- The following four zip utilities are copied from this article:
- Blog: https://technology.amis.nl/2010/03/13/utl_compress-gzip-and-zlib/
- Source: https://technology.amis.nl/wp-content/uploads/2010/06/as_zip10.txt
- Copyright (c) 2010, 2011 by Anton Scheffer (MIT license)
- Thank you for sharing this Anton :-)
*/
FUNCTION util_zip_blob_to_num (
p_blob IN BLOB,
p_len IN INTEGER,
p_pos IN INTEGER)
RETURN NUMBER;
FUNCTION util_zip_little_endian (
p_big IN NUMBER,
p_bytes IN PLS_INTEGER := 4)
RETURN RAW;
PROCEDURE util_zip_add_file (
p_zipped_blob IN OUT BLOB,
p_name IN VARCHAR2,
p_content IN BLOB);
PROCEDURE util_zip_finish (p_zipped_blob IN OUT BLOB);
FUNCTION util_multi_replace (
p_source_string VARCHAR2,
p_01_find VARCHAR2 DEFAULT NULL, p_01_replace VARCHAR2 DEFAULT NULL,
p_02_find VARCHAR2 DEFAULT NULL, p_02_replace VARCHAR2 DEFAULT NULL,
p_03_find VARCHAR2 DEFAULT NULL, p_03_replace VARCHAR2 DEFAULT NULL,
p_04_find VARCHAR2 DEFAULT NULL, p_04_replace VARCHAR2 DEFAULT NULL,
p_05_find VARCHAR2 DEFAULT NULL, p_05_replace VARCHAR2 DEFAULT NULL,
p_06_find VARCHAR2 DEFAULT NULL, p_06_replace VARCHAR2 DEFAULT NULL,
p_07_find VARCHAR2 DEFAULT NULL, p_07_replace VARCHAR2 DEFAULT NULL,
p_08_find VARCHAR2 DEFAULT NULL, p_08_replace VARCHAR2 DEFAULT NULL,
p_09_find VARCHAR2 DEFAULT NULL, p_09_replace VARCHAR2 DEFAULT NULL,
p_10_find VARCHAR2 DEFAULT NULL, p_10_replace VARCHAR2 DEFAULT NULL,
p_11_find VARCHAR2 DEFAULT NULL, p_11_replace VARCHAR2 DEFAULT NULL,
p_12_find VARCHAR2 DEFAULT NULL, p_12_replace VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
FUNCTION util_set_build_status_run_only (p_app_export_sql IN CLOB) RETURN CLOB;
FUNCTION util_calc_data_timestamp (p_as_of_minutes_ago IN NUMBER) RETURN TIMESTAMP;
PROCEDURE util_setup_dbms_metadata (
p_pretty IN BOOLEAN DEFAULT true,
p_constraints IN BOOLEAN DEFAULT true,
p_ref_constraints IN BOOLEAN DEFAULT false,
p_partitioning IN BOOLEAN DEFAULT true,
p_tablespace IN BOOLEAN DEFAULT false,
p_storage IN BOOLEAN DEFAULT false,
p_segment_attributes IN BOOLEAN DEFAULT false,
p_sqlterminator IN BOOLEAN DEFAULT true,
p_constraints_as_alter IN BOOLEAN DEFAULT false,
p_emit_schema IN BOOLEAN DEFAULT false);
FUNCTION util_to_xlsx_datetime (
p_date IN DATE)
RETURN NUMBER;
--------------------------------------------------------------------------------------------------------------------------------
-- The following tools are working on the global private package variables g_clob, g_clob_varchar_cache, g_runlog and g_queries
--------------------------------------------------------------------------------------------------------------------------------
PROCEDURE util_clob_append (p_content IN VARCHAR2);
PROCEDURE util_clob_append (p_content IN CLOB);
PROCEDURE util_clob_replace (
p_search VARCHAR2,
p_replace VARCHAR2);
PROCEDURE util_clob_flush_cache;
PROCEDURE util_clob_add_to_export_files (
p_export_files IN OUT NOCOPY tab_export_files,
p_name IN VARCHAR2);
PROCEDURE util_clob_query_to_csv (
p_query IN VARCHAR2,
p_max_rows IN NUMBER DEFAULT 1000,
p_delimiter IN VARCHAR2 DEFAULT ',',
p_quote_mark IN VARCHAR2 DEFAULT '"',
p_header_prefix IN VARCHAR2 DEFAULT NULL);
PROCEDURE util_clob_table_to_insert (
p_table_name IN VARCHAR2,
p_data_scn IN NUMBER,
p_max_rows IN NUMBER DEFAULT 1000,
p_insert_all_size IN NUMBER DEFAULT 10);
PROCEDURE util_clob_create_runtime_log (p_export_files IN OUT NOCOPY tab_export_files);
PROCEDURE util_clob_create_error_log (p_export_files IN OUT NOCOPY tab_export_files);
PROCEDURE util_ensure_unique_file_names (
p_export_files IN OUT NOCOPY tab_export_files,
p_path_to_scripts IN VARCHAR2 DEFAULT NULL);
PROCEDURE util_log_init (p_module IN VARCHAR2);
PROCEDURE util_log_start (p_action IN VARCHAR2);
PROCEDURE util_log_error (p_name VARCHAR2);
PROCEDURE util_log_stop;
FUNCTION util_log_get_runtime (
p_start IN TIMESTAMP,
p_stop IN TIMESTAMP)
RETURN NUMBER;
PROCEDURE util_log_calc_runtimes;
$end
--------------------------------------------------------------------------------------------------------------------------------
-- UTILITIES MAIN CODE
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION util_bool_to_string (p_bool IN BOOLEAN) RETURN VARCHAR2 IS
BEGIN
RETURN CASE WHEN p_bool THEN 'TRUE' ELSE 'FALSE' END;
END util_bool_to_string;
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION util_string_to_bool (
p_bool_string IN VARCHAR2,
p_default IN BOOLEAN)
RETURN BOOLEAN IS
v_bool_string VARCHAR2(1 CHAR);
v_return BOOLEAN;
BEGIN
v_bool_string := upper(substr(p_bool_string, 1, 1));
v_return :=
CASE
WHEN v_bool_string IN ('1', 'Y', 'T') THEN
true
WHEN v_bool_string IN ('0', 'N', 'F') THEN
false
ELSE p_default
END;
RETURN v_return;
END util_string_to_bool;
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION util_split (
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN tab_vc32k IS
v_return tab_vc32k := tab_vc32k();
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);
v_count PLS_INTEGER := 1;
PROCEDURE add_value (p_value VARCHAR2) IS
BEGIN
v_return.extend;
v_return(v_count) := p_value;
v_count := v_count + 1;
END add_value;
BEGIN
WHILE v_index > 0 LOOP
add_value(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
add_value(trim(substr(p_string, v_offset, v_string_length - v_offset + 1)));
END IF;
RETURN v_return;
END util_split;
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION util_join (
p_array IN tab_vc32k,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
v_return VARCHAR2(32767);
BEGIN
IF p_array IS NOT NULL AND p_array.count > 0 THEN
v_return := p_array(1);
FOR i IN 2 ..p_array.count LOOP
v_return := v_return || p_delimiter || p_array(i);
END LOOP;
END IF;
RETURN v_return;
EXCEPTION
WHEN value_error THEN
RETURN v_return;
END util_join;
--------------------------------------------------------------------------------------------------------------------------------
FUNCTION util_clob_to_blob (p_clob CLOB) RETURN BLOB IS
v_blob BLOB;
v_lang_context INTEGER := dbms_lob.default_lang_ctx;
v_warning INTEGER := dbms_lob.warn_inconvertible_char;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
BEGIN
IF p_clob IS NOT NULL THEN
dbms_lob.createtemporary(v_blob, true);
dbms_lob.converttoblob(
dest_lob => v_blob,
src_clob => p_clob,
amount => dbms_lob.lobmaxsize,
dest_offset => v_dest_offset,
src_offset => v_src_offset,
blob_csid => nls_charset_id('AL32UTF8'),
lang_context => v_lang_context,
warning => v_warning);
END IF;
RETURN v_blob;
END util_clob_to_blob;
--------------------------------------------------------------------------------------------------------------------------------
-- copyright by Anton Scheffer (MIT license, see https://technology.amis.nl/2010/03/13/utl_compress-gzip-and-zlib/)
FUNCTION util_zip_blob_to_num (
p_blob IN BLOB,
p_len IN INTEGER,
p_pos IN INTEGER)
RETURN NUMBER IS
rv NUMBER;
BEGIN
rv := utl_raw.cast_to_binary_integer(
dbms_lob.substr(p_blob, p_len, p_pos),
utl_raw.little_endian);
IF rv < 0 THEN