I can't set parameters in java using Oracle Sql query

Question

I have a database Oracle 12 and query 'ALTER USER GISDPFB SET DEFAULT TABLESPACE DEV_MDS' Query is correct and i can execute it manually,but I can't to set parameters dynamically. My Code:

 callableStatement = connection.prepareCall("ALTER USER ? DEFAULT TABLESPACE ?");
 callableStatement.setString(1,"John");
 callableStatement.setString(2,"SYSAUX");
 callableStatement.execute();

I got 'java.sql.SQLSyntaxErrorException: ORA-01935: missing user or role name'

I guess this exception because of wrong syntax,but i have success when do it in console

Answer 1

You can only use bind variables for values, not identifiers - which both the user name and tablespace name are. You'll need to embed them in the statement:

callableStatement = connection.prepareCall("ALTER USER \"John\" DEFAULT TABLESPACE \"SYSAUX\"");
callableStatement.execute();

or if the values are coming from Java variables:

callableStatement = connection.prepareCall("ALTER USER \"" + userName
 + "\" DEFAULT TABLESPACE \"" + tablespaceName + "\"");
callableStatement.execute();

If they are from variables you might want to look at adding dbms_assert checks.

Answer 2

I'm pretty sure ALTER USER is considered DLL and doesn't take bind variables. You'll probably have to just create a string for it. Try:

callableStatement = connection.prepareCall("ALTER USER john DEFAULT TABLESPACE SYSAUX");
callableStatement.execute();

• Kirill_Batutin: can you give me advice,what kind of query you would use for my case for Oracle?I need to use bind variables(?,?)
• eaolson: You could check out this answer, by essentially wrapping the ALTER USER in EXECUTE IMMEDIATE. I haven't verified that it works. stackoverflow.com/questions/10315170/…