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

Alex Poole · Accepted Answer · 2021-02-28 16:56:28Z

3

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.

answered Feb 28, 2021 at 16:56

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eaolson · Accepted Answer · 2021-02-28 17:05:42Z

0

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();

answered Feb 28, 2021 at 17:05

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

2 Comments

Kirill_Batutin Over a year ago

can you give me advice,what kind of query you would use for my case for Oracle?I need to use bind variables(?,?)

eaolson Over a year ago

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/…

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related