SQLCL Bridge syntax error sql

Bill S3

The sql works fine when run with sqldeveloper on the server; are there any sql limitations?

BRIDGE ODS_ORDKEYS as "jdbc:oracle:thin:username/[email protected]/service.world"(WITH GETDATES AS ( SELECT to_date('2018-12-22','yyyy-mm-dd') - 7 start_dt, (to_date('2018-12-22','yyyy-mm-dd') + 3) + 86399 / 86400 end_dt FROM DUAL ) SELECT SLS.SLS_4_PART_KEY sls4pk_ods, cast(to_number( Trim(To_char(AUD.RTL_STOR_ID, '0009')) ||Trim(To_char(AUD.BSNS_DAY_DT, 'YYYYMMDD')) ||Trim(To_char(AUD.WRKSTN_ID, '00009')) ||Trim(To_char(AUD.REG_TRANS_NBR, '0000000009'))) as number(30)) sls4pk_fdm, LN_.LN_ITEM_NBR, LN_.VOID_FLG, ln_.item_id item_sku, LN_.XTND_AMT, AUD.RTL_STOR_ID, AUD.WRKSTN_ID, AUD.BSNS_DAY_DT, AUD.REG_TRANS_NBR, TPT.ORD_NBR, TPT.ORD_LN_KEY, SLS.SLS_AUDIT_RVSN_NBR, SLS.SLS_AUDIT_ACTN_CDE FROM SLS_ODS_SCH01.AUDIT_SLS_TRANS AUD inner join getdates g on 1=1 inner join SLS_ODS_SCH01.SLS_TRANS_ENC SLS on AUD.SLS_4_PART_KEY=SLS.SLS_4_PART_KEY inner join SLS_ODS_SCH01.LN_ITEM_DTL_ENC LN_ on SLS.SLS_TRANS_KEY=LN_.SLS_TRANS_KEY inner join SLS_ODS_SCH01.TPTY_ORD TPT on LN_.LN_ITEM_DTL_KEY=TPT.LN_ITEM_DTL_KEY WHERE AUD.partition_Col between g.start_dt and g.end_dt and sls.partition_col between g.start_dt and g.end_dt and ln_.partition_col between g.start_dt and g.end_dt and tpt.partition_col between g.start_dt and g.end_dt and nvl(tpt.ord_nbr,'~') <> '~');

Gaz in Oz

A simple test case with "extra" parens works:

SQL> show versionOracle SQLDeveloper Command-Line (SQLcl) version: 18.1.0.0SQL> bridge new_table#1 as "jdbc:oracle:thin:gaz/[email protected]:1512/xe"(with x as (select * from dual) select * from x);Created table new_table#1 and inserted 1 rowsSQL> desc new_table#1Name  Null? Type----- ----- -----------DUMMY       VARCHAR2(1)SQL>

You don't show any error.

Bill S3

It seems to get a syntax error as soon as I include additional parens in the sql

for example

works BRIDGE NEW_TAB as "jdbc:oracle:thin:username/[email protected]/service.world"(select * from sls_ods_sch01.INTL_BSNS_HIER);

does not work BRIDGE NEW_TAB as "jdbc:oracle:thin:username/[email protected]/service.world"(with test as (select * from sls_ods_sch01.INTL_BSNS_HIER) select * from test);

Bill S3

Version 18.3

This also fails

does not work BRIDGE NEW_TAB as "jdbc:oracle:thin:username/[email protected]/service.world"("with test as (select * from sls_ods_sch01.INTL_BSNS_HIER) select * from test");

Gaz in Oz

A simple test case with "extra" parens works:

SQL> show versionOracle SQLDeveloper Command-Line (SQLcl) version: 18.1.0.0SQL> bridge new_table#1 as "jdbc:oracle:thin:gaz/[email protected]:1512/xe"(with x as (select * from dual) select * from x);Created table new_table#1 and inserted 1 rowsSQL> desc new_table#1Name  Null? Type----- ----- -----------DUMMY       VARCHAR2(1)SQL>

You don't show any error.

Bill S3

turns out the syntax error was caused when the fully qualified database name.

Gaz in Oz

turns out the syntax error was caused when the fully qualified database name.

Thanks for posting back with the actual issue.

Good to hear hear you got it sorted.

2773669

This looks cut off.  Can you post the full explanation of what the issue was?  Thanks.

Gaz in Oz

OP specified "service_name.world" as part of connection string. sqlcl doesn't like trailing dot after service_name.

Example:

SQL> bridge new_table#1 as "jdbc:oracle:thin:gaz/[email protected]:1521/xe."(with x as (select * from dual) select * from x);BRIDGE Command has invalid syntaxBRIDGE----Used mainly to script data move between two connections/schemasIt also includes functionality to dynamically create Oracle tables which "fit" the data being received through JDBCThe following functionality is availableA) query tables in other connectionsB) query tables in multiple connections in the same statementC) insert data from one connection into anotherD) create a table and insert data into it from another connectionSyntax:BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>);Example:BRIDGE table1 as "jdbc:oracle:thin:scott/[email protected]:1521:orcl"(select * from dept);In the above example table1 is created in the current connection.Table1 is defined using the metadata from the query run against the database connection defined using a SID.Example:BRIDGE table1 as "jdbc:oracle:thin:scott/[email protected]:1521/orcl"(select * from dept);In the above example table1 is created in the current connection.Table1 is defined using the metadata from the query run against the database connection defined using a Service Name.The JDBC URL specified has to conform to the format defined by the driver.BRIDGE new_table  as "jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID"(select * from scott.emp);BRIDGE new_table  as "jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]/SERVICE"(select * from scott.dept);SQL>