CODEWITHSUNDEEP
sqloracle-database
user1612078
user1612078

Reputation: 585

Alternative to enquote_literal for string containing single quote

I am bound to use dbms_assert.enquote_literal to enquote string. The string is schema name which is unknown to me as it is coming as a parameter to my function. The only thing I know is that a schema name may contain single quote. For such strings enquote_literal fails with ORA-06502: PL/SQL: numeric or value error. Is there any alternative that I can use in place of enquote_literal that gives the same output as enquote_literal.

Upvotes: 0

Views: 943

Answers (2)

XING
XING

Reputation: 9886

Try 'q' quotes Read here.

select q'['<you string>']' from dual;

Demo

SQL> select q'['Hello'workddl']' Col from dual;

COL
--------------
'Hello'workddl'

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

Not a good solution, but an easy solution is 

REPLACE(dbms_assert.enquote_literal(REPLACE(text,'''','''''')),
    '''''','''');

Input Text 

hello'world

Output Text

'hello'world'

If you don't need the quote to appear even once

dbms_assert.enquote_literalreplace(text,'''',''));

Upvotes: 2

Related Questions