7 Replies Latest reply on Mar 26, 2019 5:56 AM by Gaz in Oz

    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/password@server/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,'~') <> '~');

        • 1. Re: SQLCL Bridge syntax error sql
          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/password@server/service.world"(select * from sls_ods_sch01.INTL_BSNS_HIER);

          does not work BRIDGE NEW_TAB as "jdbc:oracle:thin:username/password@server/service.world"(with test as (select * from sls_ods_sch01.INTL_BSNS_HIER) select * from test);

          • 2. Re: SQLCL Bridge syntax error sql
            Bill S3

            Version 18.3

            This also fails

            does not work BRIDGE NEW_TAB as "jdbc:oracle:thin:username/password@server/service.world"("with test as (select * from sls_ods_sch01.INTL_BSNS_HIER) select * from test");

            • 3. Re: SQLCL Bridge syntax error sql
              Gaz in Oz

              A simple test case with "extra" parens works:

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

              You don't show any error.

              • 4. Re: SQLCL Bridge syntax error sql
                Bill S3

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

                • 5. Re: SQLCL Bridge syntax error sql
                  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.

                  • 6. Re: SQLCL Bridge syntax error sql
                    2773669

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

                    • 7. Re: SQLCL Bridge syntax error sql
                      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/gaz@localhost:1521/xe."(with x as (select * from dual) select * from x);
                      BRIDGE Command has invalid syntax
                      BRIDGE
                      ----
                      
                      Used mainly to script data move between two connections/schemas
                      It also includes functionality to dynamically create Oracle tables which "fit" the data being received through JDBC
                      The following functionality is available
                      A) query tables in other connections
                      B) query tables in multiple connections in the same statement
                      C) insert data from one connection into another
                      D) create a table and insert data into it from another connection
                      
                      Syntax:
                      BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>);
                      
                      Example:
                      BRIDGE table1 as "jdbc:oracle:thin:scott/tiger@localhost: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/tiger@localhost: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>