Set oracle workspace from java application

javaoracle

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

Best Answer

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.

Related Question