How to automatically bulk create virtual tables dynamically in code?

[ozmike]

Hi we are accessing many tables and views from a remote system and wondered how to bulk create virtual tables?

[ozmike]

Hi this code will create bulk VT to the remote system.

first manually create virtual table locally to the SYS.table_columns system catalog in the remote. Assuming someone in admin has set up a remote source in your local system and you have access to system objects. 

eg in this example we are using table_columns to source table names.

CREATE VIRTUAL TABLE "MY_LOCAL_SCHEMA"."vt_table_columns_remote_db"
AT "MY_Remote_source_name"."MYDb"."SYS"."TABLE_COLUMNS";

This below select will generate the SQL code for all the VT create statements you need.

SELECT DISTINCT 

'CREATE VIRTUAL TABLE ' || '"' || schema_name || '"."VT_' || table_name || 

'" AT ' || '"MY_Remote_source_name"."MYDb".' || 

'"' || schema_name || '"."' || table_name || '";'

FROM 

(

SELECT DISTINCT 

schema_name,

table_name

FROM vt_table_columns_remote_db

)

Just cut and paste this code and run. Note, it is not possible (currently) to exec immediate the code generated from this SQL you have to cut and paste and run the output. FYI.

You can also create VT to views by using the SYS.views similarly.

You can add filters as required. 

WHERE  table_name LIKE '%MYTABLES%'

AND schema_name LIKE '%MYSCHEMAS%'

Note any tables with geometry are not supported so you can ad this filter.

AND table_name NOT IN

(SELECT DISTINCT table_name

FROM vt_table_columns_remote_db

WHERE DATA_TYPE_NAME LIKE 'ST%'

To work around this issue see my post here