0

I created an unquoted table my_table under a quoted schema "Quoted_User".

When executing the statement

INSERT INTO "Quoted_User".my_table (custom_id) VALUES (DEFAULT)

using the Intellij Oracle browser it works, but when I'm performing the same statement through a PreparedStatement in Java Oracle is complaining that the schema is not existing. Here's the prepared statement Java line:

statement = connection.prepareStatement(insertSQL, new String[]{custom_id});

and here is insertSQL as shown in the inspector:

INSERT INTO \"Quoted_User\".my_table (custom_id) VALUES (DEFAULT) 

It's like Oracle is removing the quotes, and I can say this because I noticed the uppercase letters in the error message which are always returned when the object is considered to be case insensitive.

Error returned by Oracle:

java.sql.SQLSyntaxErrorException: ORA-04043: object "QUOTED_USER" does not exist

There should be a way to use quoted schema names with prepared statements I guess.

Thanks for your hints,

Mattia

1 Answer 1

0

Are you connecting to the database using the credentials of "Quoted_User"? In this case I see no reason to specify the schema name in your SQL statements. On the contrary, do you have the required privileges on the "Quoted_User" schema?

By the way, using mixed case identifiers is really NOT a good idea.

4
  • I don't work with java so don't have a solution. But lest you think @Massimo Pasquini is just an outlier in his opinion, I'll just add that I've been working with Oracle for about 25 years and while using mixed-case identifiers is not the worst thing you can do, it surely rates high on the list. And you've just demonstrated another reason why. Oracle defaults to, and expects upper-case names. Oracle is not just MSSQL with a different label. When in Rome (Oracle), do as the Romans.
    – EdStevens
    Aug 24, 2019 at 18:13
  • @MassimoPasquini sure, I'm connected using the "Quoted_User" credentials and I have all the needed privileges (as I said the query is properly working from the Intellij SQL console). I have to specify the schema because the user's default schema can potentially be changed by another statement executed in another thread that shares the same data source connection. Aug 24, 2019 at 22:44
  • @EdStevens the user and schema are the only information provided as parameter to the Java code I'm dealing with (i.e. arbitrary values), everything that is created inside that schema is fully managed by the application itself and therefore there is no need to provide the quoted/unquoted option, and as far as I know the unquoted one is the most common one Aug 24, 2019 at 22:44
  • From sqlplus or SQL Developer, issue a simple 'select username from dba_users order by username'. Examine the results. If you have any usernames that are not ALL_CAPS, then any time, every time you reference those users, the name MUST be enclosed in double-quotes AND the exact same case as shown from DBA_USERS. If intellij SQL is perhaps stripping double-quotes, then oracle is the victim, notthe culprit. You could do appropriate auditing at the databse level and see exactly what the database is getting from the client.
    – EdStevens
    Aug 25, 2019 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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