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

    SQLCL Bridge syntax error sql

    Bill S3
    Bill S3 Jan 4, 2019 1:30 PM

      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
          Bill S3 Jan 4, 2019 2:46 PM (in response to 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
            Bill S3 Jan 4, 2019 2:50 PM (in response to 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
              Gaz in Oz Jan 7, 2019 2:58 AM (in response to Bill S3)

              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
                Bill S3 Jan 8, 2019 10:28 PM (in response to 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
                  Gaz in Oz Jan 9, 2019 12:04 AM (in response to Bill S3)

                  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
                    2773669 Mar 26, 2019 2:34 AM (in response to Bill S3)

                    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
                      Gaz in Oz Mar 26, 2019 5:56 AM (in response to 2773669)

                      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>