-
Notifications
You must be signed in to change notification settings - Fork 31
/
ora_migrator--1.1.0.sql
1851 lines (1708 loc) · 73.7 KB
/
ora_migrator--1.1.0.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
/* tools for Oracle to PostgreSQL migration */
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION ora_migrator" to load this file. \quit
CREATE FUNCTION create_oraviews(
server name,
schema name DEFAULT NAME 'public',
options jsonb DEFAULT NULL
) RETURNS void
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
old_msglevel text;
v_max_long integer := 32767;
sys_schemas text :=
E'''''ANONYMOUS'''', ''''APEX_PUBLIC_USER'''', ''''APEX_030200'''', ''''APEX_040000'''',\n'
' ''''APEX_050000'''', ''''APPQOSSYS'''', ''''AUDSYS'''', ''''AURORA$JIS$UTILITY$'''',\n'
' ''''AURORA$ORB$UNAUTHENTICATED'''', ''''CTXSYS'''', ''''DBSFWUSER'''', ''''DBSNMP'''',\n'
' ''''DIP'''', ''''DMSYS'''', ''''DVSYS'''', ''''DVF'''', ''''EXFSYS'''',\n'
' ''''FLOWS_30000'''', ''''FLOWS_FILES'''', ''''GDOSYS'''', ''''GGSYS'''',\n'
' ''''GSMADMIN_INTERNAL'''', ''''GSMCATUSER'''', ''''GSMUSER'''', ''''LBACSYS'''',\n'
' ''''MDDATA'''', ''''MDSYS'''', ''''MGMT_VIEW'''', ''''ODM'''', ''''ODM_MTR'''',\n'
' ''''OJVMSYS'''', ''''OLAPSYS'''', ''''ORACLE_OCM'''', ''''ORDDATA'''',\n'
' ''''ORDPLUGINS'''', ''''ORDSYS'''', ''''OSE$HTTP$ADMIN'''', ''''OUTLN'''',\n'
' ''''PDBADMIN'''', ''''REMOTE_SCHEDULER_AGENT'''', ''''SI_INFORMTN_SCHEMA'''',\n'
' ''''SPATIAL_WFS_ADMIN_USR'''', ''''SPATIAL_CSW_ADMIN_USR'''', ''''SPATIAL_WFS_ADMIN_USR'''',\n'
' ''''SYS'''', ''''SYS$UMF'''', ''''SYSBACKUP'''', ''''SYSDG'''', ''''SYSKM'''',\n'
' ''''SYSMAN'''', ''''SYSRAC'''', ''''SYSTEM'''', ''''TRACESRV'''',\n'
' ''''MTSSYS'''', ''''OASPUBLIC'''', ''''OLAPSYS'''', ''''OWBSYS'''', ''''OWBSYS_AUDIT'''',\n'
' ''''PERFSTAT'''', ''''WEBSYS'''', ''''WK_PROXY'''', ''''WKSYS'''', ''''WK_TEST'''',\n'
' ''''WMSYS'''', ''''XDB'''', ''''XS$NULL''''';
tables_sql text := E'CREATE FOREIGN TABLE %I.tables (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name\n'
'FROM dba_tables\n'
'WHERE temporary = ''''N''''\n'
' AND secondary = ''''N''''\n'
' AND nested = ''''NO''''\n'
' AND dropped = ''''NO''''\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)\n'
' AND owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
table_comments text := E'CREATE FOREIGN TABLE %I.table_comments (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' table_comment text\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name,\n'
' c.comments table_comment\n'
'FROM dba_tables t\n'
'JOIN dba_tab_comments c\n'
' USING (owner, table_name)\n'
'WHERE t.temporary = ''''N''''\n'
' AND t.secondary = ''''N''''\n'
' AND t.nested = ''''NO''''\n'
' AND t.dropped = ''''NO''''\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)\n'
' AND owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
columns_sql text := E'CREATE FOREIGN TABLE %I.columns (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' column_name text NOT NULL,\n'
' position integer NOT NULL,\n'
' type_name text NOT NULL,\n'
' length integer NOT NULL,\n'
' precision integer,\n'
' scale integer,\n'
' nullable boolean NOT NULL,\n'
' default_value text\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name,\n'
' col.column_name,\n'
' col.column_id,\n'
' CASE WHEN col.data_type_owner IS NULL\n'
' THEN col.data_type\n'
' ELSE col.data_type_owner || ''''.'''' || col.data_type\n'
' END data_type,\n'
' col.char_length,\n'
' col.data_precision,\n'
' col.data_scale,\n'
' CASE WHEN col.nullable = ''''Y'''' THEN 1 ELSE 0 END AS nullable,\n'
' col.data_default\n'
'FROM dba_tab_columns col\n'
' JOIN (SELECT owner, table_name\n'
' FROM dba_tables\n'
' WHERE owner NOT IN (' || sys_schemas || E')\n'
' AND temporary = ''''N''''\n'
' AND secondary = ''''N''''\n'
' AND nested = ''''NO''''\n'
' AND dropped = ''''NO''''\n'
' UNION SELECT owner, view_name\n'
' FROM dba_views\n'
' WHERE owner NOT IN (' || sys_schemas || E')\n'
' ) tab\n'
' USING (owner, table_name)\n'
'WHERE (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)'
')'', max_long ''%s'', readonly ''true'')';
column_comments text := E'CREATE FOREIGN TABLE %I.column_comments (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' column_name text NOT NULL,\n'
' comment text\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name,\n'
' column_name,\n'
' cc.comments\n'
'FROM dba_tab_columns col\n'
' JOIN dba_col_comments cc\n'
' USING (owner, table_name, column_name)\n'
' JOIN (SELECT owner, table_name\n'
' FROM dba_tables\n'
' WHERE owner NOT IN (' || sys_schemas || E')\n'
' AND temporary = ''''N''''\n'
' AND secondary = ''''N''''\n'
' AND nested = ''''NO''''\n'
' AND dropped = ''''NO''''\n'
' UNION SELECT owner, view_name\n'
' FROM dba_views\n'
' WHERE owner NOT IN (' || sys_schemas || E')\n'
' ) tab\n'
' USING (owner, table_name)\n'
'WHERE (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)'
')'', max_long ''%s'', readonly ''true'')';
checks_sql text := E'CREATE FOREIGN TABLE %I.checks (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' constraint_name text NOT NULL,\n'
' "deferrable" boolean NOT NULL,\n'
' deferred boolean NOT NULL,\n'
' condition text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT con.owner,\n'
' con.table_name,\n'
' con.constraint_name,\n'
' CASE WHEN con.deferrable = ''''DEFERRABLE'''' THEN 1 ELSE 0 END deferrable,\n'
' CASE WHEN con.deferred = ''''DEFERRED'''' THEN 1 ELSE 0 END deferred,\n'
' con.search_condition\n'
'FROM dba_constraints con\n'
' JOIN dba_tables tab\n'
' ON tab.owner = con.owner AND tab.table_name = con.table_name\n'
'WHERE tab.temporary = ''''N''''\n'
' AND tab.secondary = ''''N''''\n'
' AND tab.nested = ''''NO''''\n'
' AND tab.dropped = ''''NO''''\n'
' AND con.constraint_type = ''''C''''\n'
' AND con.status = ''''ENABLED''''\n'
' AND con.validated = ''''VALIDATED''''\n'
' AND con.invalid IS NULL\n'
' AND con.owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
foreign_keys_sql text := E'CREATE FOREIGN TABLE %I.foreign_keys (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' constraint_name text NOT NULL,\n'
' "deferrable" boolean NOT NULL,\n'
' deferred boolean NOT NULL,\n'
' delete_rule text NOT NULL,\n'
' column_name text NOT NULL,\n'
' position integer NOT NULL,\n'
' remote_schema text NOT NULL,\n'
' remote_table text NOT NULL,\n'
' remote_column text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT con.owner,\n'
' con.table_name,\n'
' con.constraint_name,\n'
' CASE WHEN con.deferrable = ''''DEFERRABLE'''' THEN 1 ELSE 0 END deferrable,\n'
' CASE WHEN con.deferred = ''''DEFERRED'''' THEN 1 ELSE 0 END deferred,\n'
' con.delete_rule,\n'
' col.column_name,\n'
' col.position,\n'
' r_col.owner AS remote_schema,\n'
' r_col.table_name AS remote_table,\n'
' r_col.column_name AS remote_column\n'
'FROM dba_constraints con\n'
' JOIN dba_cons_columns col\n'
' ON con.owner = col.owner AND con.table_name = col.table_name AND con.constraint_name = col.constraint_name\n'
' JOIN dba_cons_columns r_col\n'
' ON con.r_owner = r_col.owner AND con.r_constraint_name = r_col.constraint_name AND col.position = r_col.position\n'
'WHERE con.constraint_type = ''''R''''\n'
' AND con.status = ''''ENABLED''''\n'
' AND con.validated = ''''VALIDATED''''\n'
' AND con.owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
keys_sql text := E'CREATE FOREIGN TABLE %I.keys (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' constraint_name text NOT NULL,\n'
' "deferrable" boolean NOT NULL,\n'
' deferred boolean NOT NULL,\n'
' column_name text NOT NULL,\n'
' position integer NOT NULL,\n'
' is_primary boolean NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT con.owner,\n'
' con.table_name,\n'
' con.constraint_name,\n'
' CASE WHEN deferrable = ''''DEFERRABLE'''' THEN 1 ELSE 0 END deferrable,\n'
' CASE WHEN deferred = ''''DEFERRED'''' THEN 1 ELSE 0 END deferred,\n'
' col.column_name,\n'
' col.position,\n'
' CASE WHEN con.constraint_type = ''''P'''' THEN 1 ELSE 0 END is_primary\n'
'FROM dba_tables tab\n'
' JOIN dba_constraints con\n'
' ON tab.owner = con.owner AND tab.table_name = con.table_name\n'
' JOIN dba_cons_columns col\n'
' ON con.owner = col.owner AND con.table_name = col.table_name AND con.constraint_name = col.constraint_name\n'
'WHERE (con.owner, con.table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND con.constraint_type IN (''''P'''', ''''U'''')\n'
' AND con.status = ''''ENABLED''''\n'
' AND con.validated = ''''VALIDATED''''\n'
' AND tab.temporary = ''''N''''\n'
' AND tab.secondary = ''''N''''\n'
' AND tab.nested = ''''NO''''\n'
' AND tab.dropped = ''''NO''''\n'
' AND con.owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
views_sql text := E'CREATE FOREIGN TABLE %I.views (\n'
' schema text NOT NULL,\n'
' view_name text NOT NULL,\n'
' definition text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' view_name,\n'
' text\n'
'FROM dba_views\n'
'WHERE owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
func_src_sql text := E'CREATE FOREIGN TABLE %I.func_src (\n'
' schema text NOT NULL,\n'
' function_name text NOT NULL,\n'
' is_procedure boolean NOT NULL,\n'
' line_number integer NOT NULL,\n'
' line text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT pro.owner,\n'
' pro.object_name,\n'
' CASE WHEN pro.object_type = ''''PROCEDURE'''' THEN 1 ELSE 0 END is_procedure,\n'
' src.line,\n'
' src.text\n'
'FROM dba_procedures pro\n'
' JOIN dba_source src\n'
' ON pro.owner = src.owner\n'
' AND pro.object_name = src.name\n'
' AND pro.object_type = src.type\n'
'WHERE pro.object_type IN (''''FUNCTION'''', ''''PROCEDURE'''')\n'
' AND pro.owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
functions_sql text := E'CREATE VIEW %I.functions AS\n'
'SELECT schema,\n'
' function_name,\n'
' is_procedure,\n'
' string_agg(line, TEXT '''' ORDER BY line_number) AS source\n'
'FROM %I.func_src\n'
'GROUP BY schema, function_name, is_procedure';
sequences_sql text := E'CREATE FOREIGN TABLE %I.sequences (\n'
' schema text NOT NULL,\n'
' sequence_name text NOT NULL,\n'
' min_value numeric(28),\n'
' max_value numeric(28),\n'
' increment_by numeric(28) NOT NULL,\n'
' cyclical boolean NOT NULL,\n'
' cache_size integer NOT NULL,\n'
' last_value numeric(28) NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT sequence_owner,\n'
' sequence_name,\n'
' min_value,\n'
' max_value,\n'
' increment_by,\n'
' CASE WHEN cycle_flag = ''''Y'''' THEN 1 ELSE 0 END cyclical,\n'
' cache_size,\n'
' last_number\n'
'FROM dba_sequences\n'
'WHERE sequence_owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
index_exp_sql text := E'CREATE FOREIGN TABLE %I.index_exp (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' index_name text NOT NULL,\n'
' uniqueness boolean NOT NULL,\n'
' position integer NOT NULL,\n'
' descend boolean NOT NULL,\n'
' col_name text NOT NULL,\n'
' col_expression text\n'
') SERVER %I OPTIONS (table ''('
'SELECT ic.table_owner,\n'
' ic.table_name,\n'
' ic.index_name,\n'
' CASE WHEN i.uniqueness = ''''UNIQUE'''' THEN 1 ELSE 0 END uniqueness,\n'
' ic.column_position,\n'
' CASE WHEN ic.descend = ''''DESC'''' THEN 1 ELSE 0 END descend,\n'
' ic.column_name,\n'
' ie.column_expression\n'
'FROM dba_indexes i,\n'
' dba_tables t,\n'
' dba_ind_columns ic,\n'
' dba_ind_expressions ie\n'
'WHERE i.table_owner = t.owner\n'
' AND i.table_name = t.table_name\n'
' AND i.owner = ic.index_owner\n'
' AND i.index_name = ic.index_name\n'
' AND i.table_owner = ic.table_owner\n'
' AND i.table_name = ic.table_name\n'
' AND ic.index_owner = ie.index_owner(+)\n'
' AND ic.index_name = ie.index_name(+)\n'
' AND ic.table_owner = ie.table_owner(+)\n'
' AND ic.table_name = ie.table_name(+)\n'
' AND ic.column_position = ie.column_position(+)\n'
' AND t.temporary = ''''N''''\n'
' AND t.secondary = ''''N''''\n'
' AND t.nested = ''''NO''''\n'
' AND t.dropped = ''''NO''''\n'
' AND i.index_type NOT IN (''''LOB'''', ''''DOMAIN'''')\n'
' AND coalesce(i.dropped, ''''NO'''') = ''''NO''''\n'
' AND NOT EXISTS (SELECT 1 /* exclude constraint indexes */\n'
' FROM dba_constraints c\n'
' WHERE c.owner = i.table_owner\n'
' AND c.table_name = i.table_name\n'
' AND COALESCE(c.index_owner, i.owner) = i.owner\n'
' AND c.index_name = i.index_name)\n'
' AND NOT EXISTS (SELECT 1 /* exclude materialized views */\n'
' FROM dba_mviews m\n'
' WHERE m.owner = i.table_owner\n'
' AND m.mview_name = i.table_name)\n'
' AND NOT EXISTS (SELECT 1 /* exclude materialized views logs */\n'
' FROM dba_mview_logs ml\n'
' WHERE ml.log_owner = i.table_owner\n'
' AND ml.log_table = i.table_name)\n'
' AND ic.table_owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
index_columns_sql text := E'CREATE VIEW %I.index_columns AS\n'
'SELECT schema,\n'
' table_name,\n'
' index_name,\n'
' position,\n'
' descend,\n'
' col_expression IS NOT NULL\n'
' AND (NOT descend OR col_expression !~ ''^"[^"]*"$'') AS is_expression,\n'
' coalesce(\n'
' CASE WHEN descend AND col_expression ~ ''^"[^"]*"$''\n'
' THEN replace (col_expression, ''"'', '''')\n'
' ELSE col_expression\n'
' END,\n'
' col_name) AS column_name\n'
'FROM %I.index_exp';
indexes_sql text := E'CREATE VIEW %I.indexes AS\n'
'SELECT DISTINCT\n'
' schema,\n'
' table_name,\n'
' index_name,\n'
' uniqueness,\n'
' NULL::text AS where_clause\n'
'FROM %I.index_exp';
partition_cols_sql text := E'CREATE FOREIGN TABLE %I.partition_columns (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' partition_name text NOT NULL,\n'
' column_name text NOT NULL,\n'
' column_position integer NOT NULL,\n'
' type text NOT NULL,\n'
' position integer NOT NULL,\n'
' values text\n'
') SERVER %I OPTIONS (table ''('
'SELECT part.table_owner,\n'
' part.table_name,\n'
' part.partition_name,\n'
' cols.column_name,\n'
' cols.column_position,\n'
' info.partitioning_type,\n'
' part.partition_position,\n'
' part.high_value\n'
'FROM dba_tab_partitions part\n'
' JOIN dba_part_tables info\n'
' ON part.table_owner = info.owner\n'
' AND part.table_name = info.table_name\n'
' JOIN dba_part_key_columns cols\n'
' ON part.table_owner = cols.owner\n'
' AND part.table_name = cols.name\n'
'WHERE part.table_owner NOT IN (' || sys_schemas || E')\n'
' AND cols.object_type = ''''TABLE''''\n'
' AND info.partitioning_type IN (''''LIST'''', ''''RANGE'''', ''''HASH'''')\n'
')'', max_long ''%s'', readonly ''true'')';
partitions_sql text := E'CREATE VIEW %1$I.partitions AS\n'
'WITH catalog AS (\n'
' SELECT schema,\n'
' table_name,\n'
' partition_name,\n'
' string_agg(column_name, TEXT '', '' ORDER BY column_position) AS key,\n'
' type, position, values,\n'
' count(column_name) AS colcount\n'
' FROM %1$I.partition_columns\n'
' GROUP BY schema, table_name, partition_name,\n'
' type, position, values\n'
'), list_partitions AS (\n'
' SELECT schema, table_name, partition_name, type, key,\n'
' coalesce(values = ''DEFAULT'', FALSE) AS is_default,\n'
' string_to_array(values, '','') AS values\n'
' FROM catalog\n'
' WHERE type = ''LIST''\n'
' AND colcount = 1\n'
'), range_partitions AS (\n'
' SELECT schema, table_name, partition_name, type, key, FALSE,\n'
' ARRAY[\n'
' lag(values, 1, ''MINVALUE'')\n'
' OVER (PARTITION BY schema, table_name\n'
' ORDER BY position),\n'
' values\n'
' ] AS values\n'
' FROM catalog\n'
' WHERE type = ''RANGE''\n'
' AND colcount = 1\n'
'), hash_partitions AS (\n'
' SELECT schema, table_name, partition_name, type, key, FALSE,\n'
' ARRAY[(position - 1)::text] AS values\n'
' FROM catalog\n'
' WHERE type = ''HASH''\n'
')\n'
'SELECT * FROM list_partitions\n'
'UNION ALL SELECT * FROM range_partitions\n'
'UNION ALL SELECT * FROM hash_partitions';
subpartition_cols_sql text := E'CREATE FOREIGN TABLE %I.subpartition_columns (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' partition_name text NOT NULL,\n'
' subpartition_name text NOT NULL,\n'
' column_name text NOT NULL,\n'
' column_position integer NOT NULL,\n'
' type text NOT NULL,\n'
' position integer NOT NULL,\n'
' values text\n'
') SERVER %I OPTIONS (table ''('
'SELECT part.table_owner,\n'
' part.table_name,\n'
' part.partition_name,\n'
' part.subpartition_name,\n'
' cols.column_name,\n'
' cols.column_position,\n'
' info.subpartitioning_type,\n'
' part.subpartition_position,\n'
' part.high_value\n'
'FROM dba_tab_subpartitions part\n'
' JOIN dba_part_tables info\n'
' ON part.table_owner = info.owner\n'
' AND part.table_name = info.table_name\n'
' JOIN dba_subpart_key_columns cols\n'
' ON part.table_owner = cols.owner\n'
' AND part.table_name = cols.name\n'
'WHERE part.table_owner NOT IN (' || sys_schemas || E')\n'
' AND cols.object_type = ''''TABLE''''\n'
' AND info.partitioning_type IN (''''LIST'''', ''''RANGE'''', ''''HASH'''')\n'
' AND info.subpartitioning_type IN (''''LIST'''', ''''RANGE'''', ''''HASH'''')\n'
')'', max_long ''%s'', readonly ''true'')';
subpartitions_sql text := E'CREATE VIEW %1$I.subpartitions AS\n'
'WITH catalog AS (\n'
' SELECT schema,\n'
' table_name,\n'
' partition_name,\n'
' subpartition_name,\n'
' string_agg(column_name, TEXT '', '' ORDER BY column_position) AS key,\n'
' type, position, values,\n'
' count(column_name) AS colcount\n'
' FROM %1$I.subpartition_columns \n'
' GROUP BY schema, table_name, partition_name, subpartition_name,\n'
' type, position, values'
'), list_subpartitions AS (\n'
' SELECT schema, table_name, partition_name, subpartition_name, type, key,\n'
' coalesce(values = ''DEFAULT'', FALSE) AS is_default,\n'
' string_to_array(values, '','') AS values\n'
' FROM catalog\n'
' WHERE type = ''LIST''\n'
' AND colcount = 1\n'
'), range_subpartitions AS (\n'
' SELECT schema, table_name, partition_name, subpartition_name, type, key, FALSE,\n'
' ARRAY[\n'
' lag(values, 1, ''MINVALUE'')\n'
' OVER (PARTITION BY schema, table_name, partition_name\n'
' ORDER BY position),\n'
' values\n'
' ] AS values\n'
' FROM catalog\n'
' WHERE type = ''RANGE''\n'
' AND colcount = 1\n'
'), hash_subpartitions AS (\n'
' SELECT schema, table_name, partition_name, subpartition_name, type, key, FALSE,\n'
' ARRAY[(position - 1)::text] AS values\n'
' FROM catalog\n'
' WHERE type = ''HASH'''
')\n'
'SELECT * FROM list_subpartitions\n'
'UNION ALL SELECT * FROM range_subpartitions\n'
'UNION ALL SELECT * FROM hash_subpartitions';
schemas_sql text := E'CREATE FOREIGN TABLE %I.schemas (\n'
' schema text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT username\n'
'FROM dba_users\n'
'WHERE username NOT IN( ' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
trig_sql text := E'CREATE FOREIGN TABLE %I.trig (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' trigger_name text NOT NULL,\n'
' trigger_type text NOT NULL,\n'
' triggering_event text NOT NULL,\n'
' when_clause text,\n'
' referencing_names text NOT NULL,\n'
' trigger_body text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT table_owner,\n'
' table_name,\n'
' trigger_name,\n'
' trigger_type,\n'
' triggering_event,\n'
' when_clause,\n'
' referencing_names,\n'
' trigger_body\n'
'FROM dba_triggers\n'
'WHERE table_owner NOT IN( ' || sys_schemas || E')\n'
' AND base_object_type IN (''''TABLE'''', ''''VIEW'''')\n'
' AND status = ''''ENABLED''''\n'
' AND crossedition = ''''NO''''\n'
' AND trigger_type <> ''''COMPOUND'''''
')'', max_long ''%s'', readonly ''true'')';
triggers_sql text := E'CREATE VIEW %I.triggers AS\n'
'SELECT schema,\n'
' table_name,\n'
' trigger_name,\n'
' CASE WHEN trigger_type LIKE ''BEFORE %%''\n'
' THEN ''BEFORE''\n'
' WHEN trigger_type LIKE ''AFTER %%''\n'
' THEN ''AFTER''\n'
' ELSE trigger_type\n'
' END AS trigger_type,\n'
' triggering_event,\n'
' trigger_type LIKE ''%%EACH ROW'' AS for_each_row,\n'
' when_clause,\n'
' referencing_names,\n'
' trigger_body\n'
'FROM %I.trig';
pack_src_sql text := E'CREATE FOREIGN TABLE %I.pack_src (\n'
' schema text NOT NULL,\n'
' package_name text NOT NULL,\n'
' src_type text NOT NULL,\n'
' line_number integer NOT NULL,\n'
' line text NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT pro.owner,\n'
' pro.object_name,\n'
' src.type,\n'
' src.line,\n'
' src.text\n'
'FROM dba_procedures pro\n'
' JOIN dba_source src\n'
' ON pro.owner = src.owner\n'
' AND pro.object_name = src.name\n'
'WHERE pro.object_type = ''''PACKAGE''''\n'
' AND src.type IN (''''PACKAGE'''', ''''PACKAGE BODY'''')\n'
' AND procedure_name IS NULL\n'
' AND pro.owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
packages_sql text := E'CREATE VIEW %I.packages AS\n'
'SELECT schema,\n'
' package_name,\n'
' src_type = ''PACKAGE BODY'' AS is_body,\n'
' string_agg(line, TEXT '''' ORDER BY line_number) AS source\n'
'FROM %I.pack_src\n'
'GROUP BY schema, package_name, src_type';
table_privs_sql text := E'CREATE FOREIGN TABLE %I.table_privs (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' privilege text NOT NULL,\n'
' grantor text NOT NULL,\n'
' grantee text NOT NULL,\n'
' grantable boolean NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name,\n'
' p.privilege,\n'
' p.grantor,\n'
' p.grantee,\n'
' CASE WHEN p.grantable = ''''YES'''' THEN 1 ELSE 0 END grantable\n'
'FROM dba_tab_privs p\n'
' JOIN dba_tables t USING (owner, table_name)\n'
'WHERE t.temporary = ''''N''''\n'
' AND t.secondary = ''''N''''\n'
' AND t.nested = ''''NO''''\n'
' AND coalesce(t.dropped, ''''NO'''') = ''''NO''''\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)\n'
' AND owner NOT IN (' || sys_schemas || E')\n'
' AND p.grantor NOT IN (' || sys_schemas || E')\n'
' AND p.grantee NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
column_privs_sql text := E'CREATE FOREIGN TABLE %I.column_privs (\n'
' schema text NOT NULL,\n'
' table_name text NOT NULL,\n'
' column_name text NOT NULL,\n'
' privilege text NOT NULL,\n'
' grantor text NOT NULL,\n'
' grantee text NOT NULL,\n'
' grantable boolean NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' table_name,\n'
' c.column_name,\n'
' c.privilege,\n'
' c.grantor,\n'
' c.grantee,\n'
' CASE WHEN c.grantable = ''''YES'''' THEN 1 ELSE 0 END grantable\n'
'FROM dba_col_privs c\n'
' JOIN dba_tables t USING (owner, table_name)\n'
'WHERE t.temporary = ''''N''''\n'
' AND t.secondary = ''''N''''\n'
' AND t.nested = ''''NO''''\n'
' AND t.dropped = ''''NO''''\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT owner, mview_name\n'
' FROM dba_mviews)\n'
' AND (owner, table_name)\n'
' NOT IN (SELECT log_owner, log_table\n'
' FROM dba_mview_logs)\n'
' AND owner NOT IN (' || sys_schemas || E')\n'
' AND c.grantor NOT IN (' || sys_schemas || E')\n'
' AND c.grantee NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
segments_sql text := E'CREATE FOREIGN TABLE %I.segments (\n'
' schema text NOT NULL,\n'
' segment_name text NOT NULL,\n'
' segment_type text NOT NULL,\n'
' bytes bigint NOT NULL\n'
') SERVER %I OPTIONS (table ''('
'SELECT owner,\n'
' segment_name,\n'
' segment_type,\n'
' bytes\n'
'FROM dba_segments\n'
'WHERE owner NOT IN (' || sys_schemas || E')'
')'', max_long ''%s'', readonly ''true'')';
migration_cost_estimate_sql text := E'CREATE VIEW %I.migration_cost_estimate AS\n'
' SELECT schema,\n'
' ''tables''::text AS task_type,\n'
' count(*)::bigint AS task_content,\n'
' ''count''::text AS task_unit,\n'
' ceil(count(*) / 10.0)::integer AS migration_hours\n'
' FROM %I.tables\n'
' GROUP BY schema\n'
'UNION ALL\n'
' SELECT t.schema,\n'
' ''data_migration''::text,\n'
' sum(bytes)::bigint,\n'
' ''bytes''::text,\n'
' ceil(sum(bytes::float8) / 26843545600.0)::integer\n'
' FROM %I.segments AS s\n'
' JOIN %I.tables AS t\n'
' ON s.schema = t.schema\n'
' AND s.segment_name = t.table_name\n'
' WHERE s.segment_type = ''TABLE''\n'
' GROUP BY t.schema\n'
'UNION ALL\n'
' SELECT schema,\n'
' ''functions'',\n'
' coalesce(sum(octet_length(source)), 0),\n'
' ''characters''::text,\n'
' ceil(coalesce(sum(octet_length(source)), 0) / 512.0)::integer\n'
' FROM %I.functions\n'
' GROUP BY schema\n'
'UNION ALL\n'
' SELECT schema,\n'
' ''triggers'',\n'
' coalesce(sum(octet_length(trigger_body)), 0),\n'
' ''characters''::text,\n'
' ceil(coalesce(sum(octet_length(trigger_body)), 0) / 512.0)::integer\n'
' FROM %I.triggers\n'
' GROUP BY schema\n'
'UNION ALL\n'
' SELECT schema,\n'
' ''packages'',\n'
' coalesce(sum(octet_length(source)), 0),\n'
' ''characters''::text,\n'
' ceil(coalesce(sum(octet_length(source)), 0) / 512.0)::integer\n'
' FROM %I.packages\n'
' WHERE is_body\n'
' GROUP BY schema\n'
'UNION ALL\n'
' SELECT schema,\n'
' ''views'',\n'
' coalesce(sum(octet_length(definition)), 0),\n'
' ''characters''::text,\n'
' ceil(coalesce(sum(octet_length(definition)), 0) / 512.0)::integer\n'
' FROM %I.views\n'
' GROUP BY schema';
test_error_sql text := E'CREATE TABLE %I.test_error (\n'
' log_time timestamp with time zone NOT NULL DEFAULT current_timestamp,\n'
' schema name NOT NULL,\n'
' table_name name NOT NULL,\n'
' rowid text NOT NULL,\n'
' message text NOT NULL,\n'
' PRIMARY KEY (schema, table_name, log_time, rowid)\n'
')';
test_error_stats_sql text := E'CREATE TABLE %I.test_error_stats (\n'
' log_time timestamp with time zone NOT NULL,\n'
' schema name NOT NULL,\n'
' table_name name NOT NULL,\n'
' errcount bigint NOT NULL,\n'
' PRIMARY KEY (schema, table_name, log_time)\n'
')';
BEGIN
/* remember old setting */
old_msglevel := current_setting('client_min_messages');
/* make the output less verbose */
SET LOCAL client_min_messages = warning;
IF options ? 'max_long' THEN
v_max_long := (options->>'max_long')::integer;
END IF;
/* tables */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.tables', schema);
EXECUTE format(tables_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.tables IS ''Oracle tables on foreign server "%I"''', schema, server);
/* table comments */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.table_comments', schema);
EXECUTE format(table_comments, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.table_comments IS ''Oracle comments for tables on foreign server "%I"''', schema, server);
/* columns */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.columns', schema);
EXECUTE format(columns_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.columns IS ''columns of Oracle tables and views on foreign server "%I"''', schema, server);
/* column comments */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.column_comments', schema);
EXECUTE format(column_comments, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.column_comments IS ''Oracle comments for columns of tables and views on foreign server "%I"''', schema, server);
/* checks */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.checks', schema);
EXECUTE format(checks_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.checks IS ''Oracle check constraints on foreign server "%I"''', schema, server);
/* foreign_keys */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.foreign_keys', schema);
EXECUTE format(foreign_keys_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.foreign_keys IS ''Oracle foreign key columns on foreign server "%I"''', schema, server);
/* keys */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.keys', schema);
EXECUTE format(keys_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.keys IS ''Oracle primary and unique key columns on foreign server "%I"''', schema, server);
/* views */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.views', schema);
EXECUTE format(views_sql, schema, server, v_max_long);
/* func_src and functions */
EXECUTE format('DROP VIEW IF EXISTS %I.functions', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.func_src', schema);
EXECUTE format(func_src_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.func_src IS ''source lines for Oracle functions and procedures on foreign server "%I"''', schema, server);
EXECUTE format(functions_sql, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.functions IS ''Oracle functions and procedures on foreign server "%I"''', schema, server);
/* sequences */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.sequences', schema);
EXECUTE format(sequences_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.sequences IS ''Oracle sequences on foreign server "%I"''', schema, server);
/* index_exp and index_columns */
EXECUTE format('DROP VIEW IF EXISTS %I.index_columns', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.index_exp', schema);
EXECUTE format(index_exp_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.index_exp IS ''Oracle index columns on foreign server "%I"''', schema, server);
EXECUTE format(index_columns_sql, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.index_columns IS ''Oracle index columns on foreign server "%I"''', schema, server);
/* indexes */
EXECUTE format('DROP VIEW IF EXISTS %I.indexes', schema);
EXECUTE format(indexes_sql, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.indexes IS ''Oracle indexes on foreign server "%I"''', schema, server);
/* partitions and subpartitions */
EXECUTE format('DROP VIEW IF EXISTS %I.partitions', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.partition_columns', schema);
EXECUTE format(partition_cols_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.partition_columns IS ''Oracle partition columns on foreign server "%I"''', schema, server);
EXECUTE format(partitions_sql, schema);
EXECUTE format('COMMENT ON VIEW %I.partitions IS ''Oracle partitions on foreign server "%I"''', schema, server);
EXECUTE format('DROP VIEW IF EXISTS %I.subpartitions', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.subpartition_columns', schema);
EXECUTE format(subpartition_cols_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.subpartition_columns IS ''Oracle subpartition columns on foreign server "%I"''', schema, server);
EXECUTE format(subpartitions_sql, schema);
EXECUTE format('COMMENT ON VIEW %I.subpartitions IS ''Oracle subpartitions on foreign server "%I"''', schema, server);
/* schemas */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.schemas', schema);
EXECUTE format(schemas_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.schemas IS ''Oracle schemas on foreign server "%I"''', schema, server);
/* trig and triggers */
EXECUTE format('DROP VIEW IF EXISTS %I.triggers', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.trig', schema);
EXECUTE format(trig_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.trig IS ''Oracle triggers on foreign server "%I"''', schema, server);
EXECUTE format(triggers_sql, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.triggers IS ''Oracle triggers on foreign server "%I"''', schema, server);
/* pack_src and packages */
EXECUTE format('DROP VIEW IF EXISTS %I.packages', schema);
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.pack_src', schema);
EXECUTE format(pack_src_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.pack_src IS ''Oracle package source lines on foreign server "%I"''', schema, server);
EXECUTE format(packages_sql, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.packages IS ''Oracle packages on foreign server "%I"''', schema, server);
/* table_privs */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.table_privs', schema);
EXECUTE format(table_privs_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.table_privs IS ''Privileges on Oracle tables on foreign server "%I"''', schema, server);
/* column_privs */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.column_privs', schema);
EXECUTE format(column_privs_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.column_privs IS ''Privileges on Oracle table columns on foreign server "%I"''', schema, server);
/* segments */
EXECUTE format('DROP FOREIGN TABLE IF EXISTS %I.segments', schema);
EXECUTE format(segments_sql, schema, server, v_max_long);
EXECUTE format('COMMENT ON FOREIGN TABLE %I.segments IS ''Size of Oracle objects on foreign server "%I"''', schema, server);
/* migration_cost_estimate */
EXECUTE format('DROP VIEW IF EXISTS %I.migration_cost_estimate', schema);
EXECUTE format(migration_cost_estimate_sql, schema, schema, schema, schema, schema, schema, schema, schema);
EXECUTE format('COMMENT ON VIEW %I.migration_cost_estimate IS ''Estimate of the migration costs per schema and object type''', schema);
/* test_error */
EXECUTE format('DROP TABLE IF EXISTS %I.test_error', schema);
EXECUTE format(test_error_sql, schema);
EXECUTE format('COMMENT ON TABLE %I.test_error IS ''Errors from the last run of "oracle_migrate_test_data"''', schema);
/* test_error_stats */
EXECUTE format('DROP TABLE IF EXISTS %I.test_error_stats', schema);
EXECUTE format(test_error_stats_sql, schema);
EXECUTE format('COMMENT ON TABLE %I.test_error_stats IS ''Cumulative errors from previous runs of "oracle_migrate_test_data"''', schema);
/* reset client_min_messages */
EXECUTE 'SET LOCAL client_min_messages = ' || old_msglevel;
END;$$;
COMMENT ON FUNCTION create_oraviews(name, name, jsonb) IS
'create Oracle foreign tables for the metadata of a foreign server';
/* this will silently truncate anything exceeding 63 bytes ...*/
CREATE FUNCTION oracle_tolower(text) RETURNS name
LANGUAGE sql STABLE CALLED ON NULL INPUT SET search_path = pg_catalog AS
'SELECT CASE WHEN $1 = upper($1) THEN lower($1)::name ELSE $1::name END';
COMMENT ON FUNCTION oracle_tolower(text) IS
'helper function to fold Oracle names to lower case';
CREATE FUNCTION oracle_translate_expression(s text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT SET search_path = @extschema@ AS
$$DECLARE
r text;
BEGIN
/* translate identifiers to lower case */
FOR r IN
SELECT idents[1]
FROM regexp_matches(s, '"([^"]*)"', 'g') AS idents
LOOP
s := replace(s, '"' || r || '"', '"' || oracle_tolower(r) || '"' );
END LOOP;
/*
* Replace SYSDATE and SYSTIMESTAMP.
* For the latter, "clock_timestamp()" would be more accurate, but
* I think that "current_timestamp" is usually preferable. This is
* perhaps an unfounded personal opinion, but this function lays no
* claim for completeness and total accuracy anyway.
*/
s := regexp_replace(s, '\msysdate\M', 'current_date', 'gi');
s := regexp_replace(s, '\msystimestamp\M', 'current_timestamp', 'gi');
/*
* Translate NEXTVAL from a pseudo-column to a function call.
* We don't have to worry about a double quote in an identifier,
* because Oracle doesn't support that anyway.
*/
s := regexp_replace(s, '"([^"]*)"\."([^"]*)"\."nextval"',
'nextval(''"\1"."\2"'')', 'gi');
/*
* Retrieve only two first arguments from to_date()
* Excludes NLS options
*/
s := regexp_replace(s, 'to_date\(\s*(''[^,]*'')\s*,\s*(''[^,]*'')[^)]*\)',
'to_date(\1, \2)', 'i');
RETURN s;
END;$$;
COMMENT ON FUNCTION oracle_translate_expression(text) IS
'helper function to translate Oracle SQL expressions to PostgreSQL';
CREATE FUNCTION oracle_translate_datatype(
v_type text,
v_length integer,
v_precision integer,
v_scale integer
) RETURNS text
LANGUAGE plpgsql STABLE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
v_geom_type text;
BEGIN
/* get the postgis geometry type if it exists */
SELECT extnamespace::regnamespace::text || '.geometry' INTO v_geom_type
FROM pg_catalog.pg_extension
WHERE extname = 'postgis';
IF v_geom_type IS NULL THEN v_geom_type := 'text'; END IF;
/* get the PostgreSQL type */
CASE
WHEN v_type = 'VARCHAR2' THEN RETURN 'character varying(' || v_length || ')';
WHEN v_type = 'NVARCHAR2' THEN RETURN 'character varying(' || v_length || ')';
WHEN v_type = 'CHAR' THEN RETURN 'character(' || v_length || ')';
WHEN v_type = 'NCHAR' THEN RETURN 'character(' || v_length || ')';
WHEN v_type = 'CLOB' THEN RETURN 'text';
WHEN v_type = 'LONG' THEN RETURN 'text';
WHEN v_type = 'NUMBER' THEN
IF v_precision IS NULL THEN RETURN 'numeric';
ELSIF v_scale = 0 THEN
IF v_precision < 5 THEN RETURN 'smallint';
ELSIF v_precision < 10 THEN RETURN 'integer';
ELSIF v_precision < 19 THEN RETURN 'bigint';
ELSE RETURN 'numeric(' || v_precision || ')';
END IF;
ELSE RETURN 'numeric(' || v_precision || ', ' || v_scale || ')';
END IF;
WHEN v_type = 'FLOAT' THEN
IF v_precision < 54 THEN RETURN 'float(' || v_precision || ')';
ELSE RETURN 'numeric';
END IF;
WHEN v_type = 'BINARY_FLOAT' THEN RETURN 'real';
WHEN v_type = 'BINARY_DOUBLE' THEN RETURN 'double precision';
WHEN v_type = 'RAW' THEN RETURN 'bytea';
WHEN v_type = 'BLOB' THEN RETURN 'bytea';
WHEN v_type = 'BFILE' THEN RETURN 'bytea';
WHEN v_type = 'LONG RAW' THEN RETURN 'bytea';
WHEN v_type = 'DATE' THEN RETURN 'timestamp(0) without time zone';
WHEN substr(v_type, 1, 9) = 'TIMESTAMP' THEN
IF length(v_type) < 17 THEN RETURN 'timestamp(' || least(v_scale, 6) || ') without time zone';
ELSE RETURN 'timestamp(' || least(v_scale, 6) || ') with time zone';
END IF;
WHEN substr(v_type, 1, 8) = 'INTERVAL' THEN
IF substr(v_type, 10, 3) = 'DAY' THEN RETURN 'interval(' || least(v_scale, 6) || ')';
ELSE RETURN 'interval(0)';
END IF;
WHEN v_type = 'SYS.XMLTYPE' OR v_type = 'PUBLIC.XMLTYPE' THEN RETURN 'xml';