Forum Stats

  • 3,758,210 Users
  • 2,251,354 Discussions
  • 7,870,110 Comments

Discussions

SQLCL Bridge syntax error sql

Bill S3
Bill S3 Member Posts: 19
edited Mar 26, 2019 1:56AM in SQLcl

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,'~') <> '~');

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jan 6, 2019 9:58PM Accepted Answer

    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.

Answers

  • Bill S3
    Bill S3 Member Posts: 19
    edited Jan 4, 2019 9:46AM

    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
    Bill S3 Member Posts: 19
    edited Jan 4, 2019 9:50AM

    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
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jan 6, 2019 9:58PM Accepted Answer

    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
    Bill S3 Member Posts: 19
    edited Jan 8, 2019 5:28PM

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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jan 8, 2019 7:04PM
    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
    2773669 Member Posts: 5
    edited Mar 25, 2019 10:34PM

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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Mar 26, 2019 1:56AM

    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>