AnsweredAssumed Answered

How do single quotes work in SQL with patch 13 for 6.9.1?

Question asked by Paul Duer on May 24, 2016
Latest reply on May 2, 2017 by Sabthami S
  • Comment • 4

Okay, we have a request worflow with the SQL below as it's first node:

 

SELECT  NVL(supervisor_id,0) AS supervisor_id, CASE

  WHEN supervisor_id is null THEN '311'  ELSE ''

  END IS_SUP_UPDATED  from           T_MASTER_ENTERPRISE_USERS

  WHERE user_id = '${access_request_cri_meu_userId}'

 

Before patch 13, everything worked FINE. Now we get this error:

 

nested exception is: java.sql.SQLException: ORA-00933: SQL command not properly ended Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 170625:WPDS, Process Ref = 'WF_RR_8900', Node Name = 'Null Supervisor', WorkItem = 1034344:WPDS:1

 

 

What are the NEW rules around single quotes?

 

We know that USER_ID is a NUMBER in the master users table. So WHY when I remove the single quotes from the where clause does it break?

 

Is it because of the VIA parameter right there? Will the SQL processor just plain not save that?

 

What gives?

Reuben Fisher
Reuben Fisher Employee May 24, 2016 1:28 PM
Correct Answer

I believe the issue is that we now keep the quotes as literal quotes that are concatenated to the variable value.

So - If we start with ‘${x}’

  1. For execution, we change it to ‘’ || ? || ‘’ and then execute a prepared statement
  2. For design-time validation, we are executing a static sql statement so  we change it to ‘’ || 0 || ‘’

I am working on changing the processing as follows:

  1. Remove the quotes around the variable name so they are ignored. This is the original intent of the sql.
  2. Use a prepared statement for design-time validation
See the reply in context
No one else had this question

Outcomes