Execute Oracle SQL query containing @ from Broadway or Java

[AFOYQqhZ55j0hwYnWQTrk6rnwfNONSEeP6uheHC]

I'm trying to execute an Oracel SQL statement which contains @...@ substring. It seems that Fabric is trying to parse this part as a Global, and it is not possible to execute the command.

Running the command directly in Oracle SQL console is executed with no issue:

copy from BANK_SOURCE/BANK_SOURCE@82.76.7.229:1521/XE to BANK_TARGET/BANK_TARGET@localhost:1521/XE create table_name using select * from BANK_SOURCE.ACCOUNT

• when executed from Broadway DbCommand actor, getting error: WARN 2024-06-18 10:21:19,455 [LIDf30b00000000005e] [JdbcEx Server-23/127.0.0.1:52566] c.k.c.s.Utils - Global '82.76.7.229:1521/XE to BANK_TARGET/BANK_TARGET' not found WARN 2024-06-18 10:21:19,464 [LIDf30b00000000005e] [JdbcEx Server-23/127.0.0.1:52566] c.k.f.j.k.s.JdbcExServerConnection - java.sql.SQLException: com.k2view.broadway.exception.StageException: Flow: test Level: 1 Stage: Stage 1 Actor: DbCommand2 Missing IN or OUT parameter at index:: 1 copy from BANK_SOURCE/BANK_SOURCE@82.76.7.229:1521/XE to BANK_TARGET/BANK_TARGET@localhost:1521/XE create table_name using select * from BANK_SOURCE.ACCOUNT class java.sql.SQLException Cause: Missing IN or OUT parameter at index:: 1 at com.k2view.fabric.common.Util.wrapCause(Util.java:186) at com.k2view.fabric.common.jdbc.JdbcUtil.sqlException(JdbcUtil.java:183) at com.k2view.fabric.session.sql.FabricStatement.doExecute(FabricStatement.java:244) at com.k2view.fabric.session.sql.FabricPreparedStatement.lambda$execute$1(FabricPreparedStatement.java:169) at com.k2view.fabric.common.jdbc.JdbcUtil.sqlException(JdbcUtil.java:181) ...

• when trying to execute the same via Java function - db("Interface").execute(sql); - getting this error: ORA-00900: invalid SQL statement WARN 2024-06-18 10:28:55,255 [LIDf30b000000000063] [JdbcEx Server-26/127.0.0.1:52970] c.k.c.s.Utils - Global '82.76.7.229:1521/XE to BANK_TARGET/BANK_TARGET' not found WARN 2024-06-18 10:28:55,361 [LIDf30b000000000063] [JdbcEx Server-26/127.0.0.1:52970] c.k.f.j.k.s.JdbcExServerConnection - java.sql.SQLException: com.k2view.broadway.exception.StageException: Flow: test Level: 1 Stage: Stage 1 Actor: LuFunction1 Exception in temp(): java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement class oracle.jdbc.OracleDatabaseException Cause: ORA-00900: invalid SQL statement

  at com.k2view.fabric.common.Util.wrapCause(Util.java:186) at com.k2view.fabric.common.jdbc.JdbcUtil.sqlException(JdbcUtil.java:183) at com.k2view.fabric.session.sql.FabricStatement.doExecute(FabricStatement.java:244) at com.k2view.fabric.session.sql.FabricPreparedStatement.lambda$execute$1(FabricPreparedStatement.java:169) at com.k2view.fabric.common.jdbc.JdbcUtil.sqlException(JdbcUtil.java:181) at com.k2view.fabric.session.sql.FabricPreparedStatement.execute(FabricPreparedStatement.java:169) at com.k2view.fabric.common.jdbc.delegates.PreparedStatementDelegate.execute(PreparedStatementDelegate.java:216) at com.k2view.fabric.session.sql.SessionStatementWrapper$PrepareStatementDelegateWrapper.lambda$execute$0(SessionStatementWrapper.java:191) at com.k2view.fabric.session.sql.SessionStatementWrapper$StatementDelegateWrapper.postExecute(SessionStatementWrapper.java:105) at com.k2view.fabric.session.sql.SessionStatementWrapper$PrepareStatementDelegateWrapper.execute(SessionStatementWrapper.java:191) at com.k2view.fabric.jdbcex.k2driver.server.JdbcExServerConnection.executePreparedStatement(JdbcExServerConnection.java:286) at com.k2view.fabric.jdbcex.k2driver.server.JdbcExServerConnection.lambda$hookActions$9(JdbcExServerConnection.java:104) ...

[F78aPg0zjWnRKWkYrBd6lShvmAYl8doniMtEnFN]

@dotants as far as I know, copy is not a valid SQL command in Oracle. BTW, you are getting an invalid statement error from Oracle (ORA-00900). After some research I found this in Stack Overflow. So, if this command is part of sqlplus, then you cannot use DbCommand actor for that.

[tZajFGR0CidT8AVERBHw8puD36HY6oWViykmIIb]

The SQL query you provided is not valid as it combines SQL DML (Data Manipulation Language) and DDL (Data Definition Language) operations with database-specific syntax that doesn't fully conform to SQL standards. SQL does not have a standardized "copy" command like what is suggested here. Instead, you might need to use specific tools or utilities provided by the database management system (DBMS) or a series of SQL statements to achieve the desired action. To copy data from one database to another (assuming both are Oracle databases):

1. Create the Table in the Destination Database
2. Insert Data into the Target Table