2

I'm connecting to an Oracle database 12c which uses Oracle Workspace Manager from a java application. I'm trying to execute the following command from my java application

EXECUTE DBMS_WM.GotoWorkspace('WORKSPACE');

using this code

String setWorkspace = "EXECUTE DBMS_WM.GotoWorkspace(?);";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.registerOutParameter(1, OracleTypes.CHAR);
cs.setString(1, "'WORKSPACE'");
cs.execute();

and I'm receiving this exception

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:210)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:53)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5617)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)

I can execute this command from SQL Developer, and I can execute a regular query like this

ResultSet rs = stmt.executeQuery(query);

from my java application. I get the same exception trying to execute the above command with executeQuery()

I've done some reading and found out that DBMS_WM is a public synonym. The code I'm using to run the execute statement is used for stored procedures. However I haven't been able to find any examples of executing a public synonym from a java application. How do I call a function from a public synonym from java?

Update

I changed my java code to

    String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE');";
    CallableStatement cs = conn.prepareCall(setWorkspace);
    cs.execute();

and now I get

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
1

I was receiving the

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

because I was using the EXECUTE command rather than the CALL command. In SQL Developer either EXECUTE or CALL works.
I was getting

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

because of the semicolon at the end of the SQL statement. The following

String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE')";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.execute();

works.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.