How can i use regexp_replace with 2 clob's where one clob is greater than 32K?

how to concatenate two clobs where one is >32K? i am currently using something like this:
lv_table := REGEXP_REPLACE(lv_text, '~~INSERT DATA~~',  lv_instance, 1, 1);

i get an error with this as lv_instance is greater than 32K
Rao_SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
First, why use regexp_replace?  '~~INSERT DATA~~' doesn't have a pattern for the regular expressions.

That said, I believe you will have to write your own.  Tom Kyte has sample code for a lob_replace function:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1533006062995

There are a few obscure bugs in it though.
0
sdstuberCommented:
try this...


CREATE OR REPLACE FUNCTION replaceinlob(
    p_original      IN CLOB,
    p_search        IN VARCHAR2,
    p_replace       IN CLOB,
    p_offset        IN INTEGER DEFAULT 1,
    p_occurrences   IN INTEGER DEFAULT 0
)
    RETURN CLOB
IS
    v_result        CLOB;
    v_counter       INTEGER := 1;
    v_next          INTEGER;
    v_index         INTEGER := 1;
    v_search_length INTEGER := LENGTH(p_search);
BEGIN
    IF p_original IS NULL
    THEN
        v_result := NULL;
    ELSIF p_search IS NULL
    THEN
        DBMS_LOB.createtemporary(v_result, TRUE, DBMS_LOB.call);
        DBMS_LOB.COPY(
            v_result,
            p_original,
            DBMS_LOB.lobmaxsize,
            1,
            1
        );
    ELSE
        DBMS_LOB.createtemporary(v_result, TRUE, DBMS_LOB.call);

        LOOP
            v_next :=
                DBMS_LOB.INSTR(
                    p_original,
                    p_search,
                    p_offset,
                    v_counter
                );

            IF v_next != 0
            THEN
                IF (v_next - v_index) > 0
                THEN
                    DBMS_LOB.COPY(
                        v_result,
                        p_original,
                        v_next - v_index,
                        DBMS_LOB.getlength(v_result) + 1,
                        v_index
                    );
                END IF;

                v_result := v_result || p_replace;
                v_index := v_next + v_search_length;
                v_counter := v_counter + 1;
            END IF;

            EXIT WHEN (v_counter > p_occurrences AND p_occurrences > 0) OR v_next = 0;
        END LOOP;

        DBMS_LOB.COPY(
            v_result,
            p_original,
            DBMS_LOB.lobmaxsize,
            DBMS_LOB.getlength(v_result) + 1,
            v_index
        );
    END IF;

    RETURN v_result;
END;

Open in new window



for my test case I have a  table with various texts.
I picked a couple of big ones.  Nothing special about them except they are both much larger than 32K

Then I picked one line out of War and Peace and replaced it with the entire contents of the Bible.

DECLARE
    lv_text        CLOB;
    lv_table       CLOB;
    lv_instance    CLOB;

    lv_search_text VARCHAR2(1000)
        := 'Yakov Alpatych heard without heeding.';
BEGIN
    SELECT text
      INTO lv_text
      FROM etexts
     WHERE name = 'Project Gutenberg: War and Peace';

    SELECT text
      INTO lv_instance
      FROM etexts
     WHERE name = 'King James Bible';

    DBMS_OUTPUT.put_line('War and Peace size...' || NVL(DBMS_LOB.getlength(lv_text), 0));
    DBMS_OUTPUT.put_line('Bible size...........' || NVL(DBMS_LOB.getlength(lv_instance), 0));

    lv_table :=
        replaceinlob(
            lv_text,
            lv_search_text,
            lv_instance            
        );

    DBMS_OUTPUT.put_line('Combined size........' || DBMS_LOB.getlength(lv_table));
    DBMS_OUTPUT.put_line(
           'Expected size........'
        || (  NVL(DBMS_LOB.getlength(lv_text), 0)
            + NVL(DBMS_LOB.getlength(lv_instance), 0)
            - NVL(LENGTH(lv_search_text), 0))
    );
END;

Open in new window


with results...
War and Peace size...3291621
Bible size...........4432662
Combined size........7724246
Expected size........7724246

Open in new window



replaceinlob includes 2 optional parameters I borrowed from the regexp_replace api.

You can specify a starting offset and the number of replacments to make if the search string occurs more than once.

By default it starts at the beginning and replaces all occurrences.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rao_SAuthor Commented:
hi sdstuber, thank you for the pl/sql code, i have not yet had a chance to test it, because i am working on another piece of code which has a higer priority, but will definately get to this issue this week.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rao_SAuthor Commented:
hi sdstuber,
i get the following error, when i try to select into a clob:
 -6502ORA-06502: PL/SQL: numeric or value error
SELECT  col1  INTO    lv_tab   FROM   table1    WHERE   col2 = 30;
i have a object typ with a method, and a associated table, the program collects data in a html format and stores into the object typ associated table. then the second part of the program reads the html clob from the table into local variable which is a clob and reads another table and stores a clob column into a another local clob variable, then i have to repalce a small text in the second clob with the html clob from the first table.
so i get the above error when i try to read the html clob into a local variable, the size of the html clob is 32,805.
the "into" clause does not allow a size greater than 32K?
0
sdstuberCommented:
post your code

I'm not sure how

"SELECT  col1  INTO    lv_tab   FROM   table1    WHERE   col2 = 30; "

relates to anything I posted above,  perhaps you're getting an error in an unrelated area.  If so, please open a new question about that, since it has nothing to do with a REPLACE action.

you can definitely select clob values INTO variables > 32K,  My example above shows this.
Both of the initial selects that populate the clob documents are of the form SELECT INTO
and are each well over 32K  (3mb and 4mb.)
0
Rao_SAuthor Commented:
you are correct! found the error.. it is not the "into", i was using ..
dbms_output.put_line (lv_instance);
where lv_instance is more than 32K, it is the "dbms_output.put_line (lv_instance);" which is giving the error.. i comented it out and now your code works fine!!
0
Rao_SAuthor Commented:
hi sdstuber, your soulution worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.