SQLException: ORA-00911: invalid character

[Simon Knight]

Hi - I've got the following code in respect of an SQL query:

update = "select PARSING_USER_ID from v$sql where SQL_TEXT like '" + SQLtext + "'";

Which when run produces the following (for a specific instance of the query):

select PARSING_USER_ID from v$sql where SQL_TEXT like 'select LOW_OPTIMAL_SIZE, HIGH_OPTIMAL_SIZE, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS, TOTAL_EXECUTIONS from GV$SQL_WORKAREA_HISTOGRAM where INST_ID = USERENV('Instance')'

This also has the unfortunate result of causing a "java.sql.SQLException: ORA-00933: SQL command not properly ended" exception. I don't understand this as the query looks fine to me, and when cut and paste into SQLDeveloper works fine...

Can anyone tell me, why won't it work in JDBC and is there a work around?

[Pavel Cherkashin]

The point is in preparation of statement. Avoid using string concatination, when You put params into query.
For instance:

    
PreparedStatement stmt = jdbcConnection.prepareStatement("select PARSING_USER_ID from v$sql where SQL_TEXT like ?");
stmt.setString(1, SQLtext);

if SQLtext contains some invalid characters, jdbc driver translates
them to correct SQL escape symbols. But in your case all characters will be pasted into query as is.