How to escape value in parameter passed to Oracle SQL script

Question

I have an sql script which is executed using sql plus. It reads input parameters and the beginning looks like this:

SET DEFINE ON

DEFINE PARAM1 = '&1'
DEFINE PARAM2 = '&2'

DECLARE
...

Now I would like to use this script with the parameters, but I need to use some special characters, particularly '

@@./update.sql 'value of first param' 'Doesn't work'
                                            ^
--------------------------------------------| Here's the problem
commit;

When I do the usual way of concatenation strings like this:

'Doesn'||chr(39)||'t work'

only Doesn appear in the PARAM2. Is there some way to escape the character in a way that the sqlplus will read it as a single string?

Srini V · Accepted Answer · 2014-03-11 05:15:36Z

You need to use escape characters to achieve this.

{} Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query.

\ Use the backslash character to escape a single character or symbol. Only the character immediately following the backslash is escaped.

Some examples on single character escape

SELECT 'Frank''s site' AS text FROM DUAL;

TEXT
--------------------
Franks's site

Read more here

For escaping & in SQL*Plus

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

OR

SET SCAN OFF
SELECT '&ABC' x FROM dual;

Escaping wild card

SELECT name FROM emp 
   WHERE id LIKE '%/_%' ESCAPE '/';

SELECT name FROM emp 
   WHERE id LIKE '%\%%' ESCAPE '\';

Thanks for an answer, but when I do SELECT 'Frank''s site' AS text FROM DUAL;, then only SELECT is read in the DEFINE PARAM2 = '&2'

fred727 · Accepted Answer · 2016-12-14 09:23:32Z

1

From a shell you should call the script like that:

sqlplus user@db/passwd @update 'value of first param' "Doesn't work"

(the word @update refer to your script which is named update.sql)

Then you have to use literal quoting in your script:

DEFINE PARAM2 = q'[&2]';

Documentation for literals can be found here.

answered Dec 14, 2016 at 9:23

fred727

2,8841 gold badge24 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to escape value in parameter passed to Oracle SQL script

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related