Replace function not working for long query in ORACLE/PLSQL?

Question

I have tried running replace Function for long query probably length greater than 4000, the function returns an error of

ORA-01704:String literal too long.

Is there any other alternative of using Replace function to replace a character in Oracle SQL?

Query:

Select REPLACE('VERY_LONG_QUERY','''','''''') FROM DUAL;

Separating the long query into smaller subqueries and then putting them together again after the replacement? — Geshode
Oracle has a hard limit of 4000 bytes for a character literal in SQL. Inside a PL/SQL block (which is not what you show in your question - that's plain SQL) you can handle character literals up to 32k — a_horse_with_no_name
Please check my answer and accept it if it helped you or give a relevant feedback(comments) for the given solution. Please read : stackoverflow.com/help/someone-answers to understand why it's important. — Kaushik Nayak

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-10-01T06:01:43

I'm not sure if it works in 11g or which is your Oracle version, but this works for me in 12c (Tried from Sql developer latest version ). To use BIND variables to assign the long string and then run your sql query

VARIABLE VERY_LONG_QUERY VARCHAR2(32767);
EXEC :VERY_LONG_QUERY := 'select * from something...........greater than 4000';
Select REPLACE(:VERY_LONG_QUERY,'''','''''') FROM DUAL;

Similarly it should work fine if I am doing this in PL/SQL.

Replace function not working for long query in ORACLE/PLSQL?

1 Answers