PeopleTools and Lifecycle Management - PSFT (MOSC)

MOSC Banner

SQR: How to handle Dynamic SQL in Where Clause when values could contain a single quote?

edited Aug 17, 2020 7:55AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 8 commentsAnswered ✓

Hi SQR Programmers!

I am trying to include some Dynamic SQL in my Select statement so I don't have to write the same SQL 3 different times.  One of my conditions is to check this table for the employee's city.  A few of the cities contain a single quote.  This is causing an issue when the SQR runs.

Example Dynamic condition:

!* Check City

let $Dyn_Cond = 'and a.city = '''|| $city || ''''

I get this error:

SQR 5528) ORACLE OCIStmtPrepare error 1756 in cursor 13:

   ORA-01756: quoted string not properly terminated

The city it came across is this.

:1) AND ft.eff_status = 'A' AND ft.state      = :2 and ft.city =

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center