Team:
I am SQLCL 17.3 connecting to Oracle 11g database.
demo@ORA11G> create table t(aa xmltype,bb varchar2(100),c date);
Table T created.
demo@ORA11G> show ddl
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON
demo@ORA11G> drop table t purge;
Table T dropped.
demo@ORA11G> create table t(aa xmltype,bb varchar2(100),c date);
Table T created.
demo@ORA11G> show ddl
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON
demo@ORA11G> set ddl specification off
DDL Option SPECIFICATION off
demo@ORA11G> set ddl storage off
DDL Option STORAGE off
demo@ORA11G> set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off
demo@ORA11G> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "DEMO"."T"
( "AA" "XMLTYPE",
"BB" VARCHAR2(100),
"C" DATE
)
you can see the SQL Terminator is missing in the above output, though we have set SQLTERMINATOR to ON (by default).
If we set SQLTERMINATOR to ON explicitly again, we could see the SQL Terminator in the output. Kindly advice.
demo@ORA11G> show ddl
STORAGE : OFF
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : OFF
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : OFF
demo@ORA11G> set ddl sqlterminator on
DDL Option SQLTERMINATOR on
demo@ORA11G> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "DEMO"."T"
( "AA" "XMLTYPE",
"BB" VARCHAR2(100),
"C" DATE
) ;
demo@ORA11G>
Script used for this demo:
drop table t purge;
create table t(aa xmltype,bb varchar2(100),c date);
show ddl
set ddl specification off
set ddl storage off
set ddl segment_attributes off
select dbms_metadata.get_ddl('TABLE','T') from dual;
show ddl
set ddl sqlterminator on
select dbms_metadata.get_ddl('TABLE','T') from dual;
October 18, 2017 - 4:43 am UTC
sqlterminator is about the terminator to be used in SQLcl the *tool*, just like it is i sqlplus.
It is *unrelated* to the concept of "do I want to add a sqlterminator to the DDL I generate" which is "ddl sqlterminator".
Hope this helps.