Generates DDL for a given foreign table, stream or view.
You can call this function using the following code:
SELECT STATEMENT FROM
TABLE(SYS_BOOT.MGMT.GENERATE_DDL_FOR_TABLE(schema_name,table_name));
SELECT STATEMENT FROM
TABLE(GENERATE_DDL_FOR_TABLE(schema_name,view_name));
where schema_name is the name of the current schema and table_name is the name of the foreign table, stream or view for which you want to generate DDL.
NOTE:
Getting DDL for a stream:
0: jdbc:sqlstream:sdp://drew-VirtualBox> SELECT STATEMENT FROM TABLE(GENERATE_DDL_FOR_TABLE('SALES', 'BIDS'));
'STATEMENT'
'CREATE OR REPLACE STREAM SALES.BIDS ('
' "time" BIGINT,'
' "ticker" VARCHAR(5),'
' "shares" INTEGER,'
' "price" REAL,'
' "expiryMs" BIGINT,'
' "comment" VARCHAR(1024)'
');'
8 rows selected (0.56 seconds)
You can also invoke this function for a view:
0: jdbc:sqlstream:sdp://drew-VirtualBox> SELECT STATEMENT FROM TABLE(SYS_BOOT.MGMT.GENERATE_DDL_FOR_TABLE('SALES', 'TEMPSVIEW'));
'STATEMENT'
'SET SCHEMA 'SALES';'
'CREATE OR REPLACE VIEW SALES.TEMPSVIEW AS'
'select empno, name from temps;'
3 rows selected (0.279 seconds)
Or for a foreign stream:
select * from table(generate_ddl_for_table('StreamLab_Output_buses','source_1_fs'));
'STATEMENT'
'CREATE OR REPLACE FOREIGN STREAM "StreamLab_Output_buses"."source_1_fs" ('
' "id" BIGINT,'
' "reported_at" TIMESTAMP,'
' "speed" INTEGER,'
' "driver_no" BIGINT,'
' "prescribed" BOOLEAN,'
' "gps" VARCHAR(128),'
' "highway" VARCHAR(8)'
')'
'SERVER FILE_SERVER'
'OPTIONS ('
' CHARACTER_ENCODING 'UTF-8','
' DIRECTORY '/tmp','
' FILENAME_PATTERN 'buses\.log','
' PARSER 'XML','
' PARSER_XML_ROW_TAGS '/Table1','
' PARSER_XML_USE_ATTRIBUTES 'false','
' "driver_no_XPATH" '/Table1/driver_no','
' "gps_XPATH" '/Table1/gps','
' "highway_XPATH" '/Table1/highway','
' "id_XPATH" '/Table1/id','
' "prescribed_XPATH" '/Table1/prescribed','
' "reported_at_XPATH" '/Table1/reported_at','
' "speed_XPATH" '/Table1/speed''
');'
25 rows selected (0.28 seconds)