Problems with creating virtual schema

[gcekol]

I would like to create a virtual schema for Oracle database.

So far I've done this:

CREATE SCHEMA ADAPTER;

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS %scriptclass com.exasol.adapter.RequestDispatcher; %jar /buckets/bfsdefault/jars/virtualschema-jdbc-adapter-dist-3.1.2.jar; %jar /buckets/bfsdefault/jars/ojdbc6.jar; / ;

I have uploaded all the files on BucketFS. I have created all the connections and they work.

When I try to create virtual schema:

CREATE VIRTUAL SCHEMA VIRTUAL_JDBC_SCHEMA USING ADAPTER.JDBC_ADAPTER WITH SQL_DIALECT = 'ORACLE' CONNECTION_NAME = 'TGTTEST_JDBC' SCHEMA_NAME = 'schema name' ;

In this step I get an error.

[Code: 0, SQL State: 22002] VM error: End of %scriptclass statement not found (Session: 1663479284189193382)

I'm using Exasol 6.2.5 version.

Thank you in advance!

[AnastasiiaSergienko]

Hi! Try to add a start of the SQL Block --/ before a CREATE OR REPLACE JAVA ADAPTER SCRIPT line:

--/ CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS

This is a necessary part of syntax if you use DBVisualizer, for example. You can find more information about DBVisualizer and scripting here: https://docs.exasol.com/connect_exasol/sql_clients/db_visualizer.htm

Let me know if it solves your problem.

Best Regards, Anastasiia Sergienko

[gcekol]

I have tried that, but I still have the same problem.

Thank you for the link.

[AnastasiiaSergienko]

I just copied your CREATE SCRIPT statement and used it to create a virtual schema in DBVisualizer. It worked after I added --/. Please check your syntax one more time and re-create the script.

You can also try to use another database tool (DBeaver/DataGrip/etc) and see if the problem persists there too.

[gcekol]

Can you please send me your create virtual schema syntax?

I will try with DBeaver as soon as possible (need to download it).

[AnastasiiaSergienko]

Here is a working example of creating Oracle virtual schema:

CREATE SCHEMA schema_oracle;

--/ CREATE JAVA ADAPTER SCRIPT schema_oracle.adapter_script_oracle AS %scriptclass com.exasol.adapter.RequestDispatcher; %jar /buckets/bfsdefault/virtualschemas/virtualschema-jdbc-adapter-dist-3.1.2.jar; %jar /buckets/bfsdefault/oracle/ojdbc7.jar; / ;

CREATE CONNECTION jdbc_connection_oracle TO ' .. ' USER ' .. ' IDENTIFIED BY ' ... ';

CREATE VIRTUAL SCHEMA virtual_schema_oracle USING schema_oracle.adapter_script_oracle WITH SQL_DIALECT = 'ORACLE' CONNECTION_NAME = 'jdbc_connection_oracle' SCHEMA_NAME = 'MY_USER';

[gcekol]

On DBeaver I got this error message:

SQL Error [22002]: VM error: End of %scriptclass statement not found (Session: 1664129752338340659)

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [22002]: VM error: End of %scriptclass statement not found (Session: 1664129752338340659) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:491) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:426) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:418) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:778) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2942) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:111) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:109) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$17.run(ResultSetViewer.java:3478) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:103) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: com.exasol.jdbc.DataException: VM error: End of %scriptclass statement not found (Session: 1664129752338340659) at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:33) at com.exasol.jdbc.EXASQLException.getSQLExceptionIntern(EXASQLException.java:50) at com.exasol.jdbc.AbstractEXAStatement.execute(AbstractEXAStatement.java:468) at com.exasol.jdbc.EXAStatement.execute(EXAStatement.java:278) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) ... 12 more

[gcekol]

Instead of virtualschema-jdbc-adapter-dist-3.1.2.jar I used virtual-schema-dist-5.0.4-bundle-4.0.3.jar and now everything works fine.