How to run REPLACE function in Oracle9i with strings larger than 4000 characters

Question

I have the following block of PLSQL that succeeds when ln_length is 4000 characters or less but fails with "ORA-01460: unimplemented or unreasonable conversion requested" when ln_length is > 4000 characters.

The block is:

DECLARE
    ls_string VARCHAR2(32767);
    ls_temp VARCHAR2(32767);
    ln_length NUMBER := 4000;
BEGIN
    ls_string := '';
    FOR i IN 1..ln_length LOOP
      ls_string := ls_string || 'x';
    END LOOP;

    SELECT REPLACE(ls_string,'bob')
    INTO ls_temp FROM dual;
END;

How would I write an equivalent piece of code that caters for strings up to 32k in length?

Answer 1

Don't have 9i to test with, but perhaps this will get around it:

replace:

SELECT REPLACE(ls_string,'bob')
INTO ls_temp FROM dual;

with:

ls_temp := REPLACE(ls_string,'bob');

The RDBMS engine only supports VARCHAR2's up to 4000 in length, while PL/SQL supports up to 32767. Avoid going to the database for the REPLACE operation (it's unnecessary anyway) and it seems to work (at least w/ 10g).