GENERATE_DDL_FOR_TABLE

Generates DDL for a given foreign table, stream or view.

Syntax

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:

  • It is no longer necessary to specify SYS_BOOT.MGMT as part of the function name, as the PATH already includes it.

Example

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)