Technology For Your Business For Your Life

Friday, May 2, 2014

Oracle PL/SQL - How to embed an apostrophe inside a string in dynamic sql

I was asked to make some enhancements to an existing .Net Application that uses an Oracle database.
Most of the logic used by the application resides in the database in packages. These packages make use of loads of dynamic sql.

So one of the issues encountered was having a list of branch descriptions built up on the fly. The problem was that some branch descriptions had an apostrophe in the description thereby terminating the string and breaking the dynamic sql.

So the branch description variable was something like this...   'Test store 1, Test store 2,Test store's'
"Test Store's" was breaking the dynamic sql  because of the apostrophe.

The solution without having to double on apostrophes is to use User Defined Delimeters...
q'(Test store 1, Test store 2,Test store's)'

Start the literal with "q" to mark your delimiter, and surround your delimited expression with single quotes is basically what is happening above. The user defined delimiter is the round bracket, so everything in between round brackets is taken as is.

Web Designer Cape Town
Alwin

No comments:

Post a Comment