Home » SQL & PL/SQL » SQL & PL/SQL » Problem retrieving substring from a CLOB variable (Oracle 8i)
Problem retrieving substring from a CLOB variable [message #393145] Fri, 20 March 2009 09:14 Go to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Hello,

I have a package that does a lot of operations. Among them is concatenating varchar variables into a clob variable, defind locally in the procedure, this way:

xTexto   clob := EMPTY_CLOB();


After the "begin" statement, I placed these instructions:

    dbms_lob.CREATETEMPORARY(xTexto, true);
    dbms_lob.OPEN(xTexto, dbms_lob.lob_readwrite);


Inside the rest of the procedure, I have a lot of WriteAppend instructions, this way:

dbms_lob.WRITEAPPEND(xTexto, length(xTitulo), xTitulo);

where xTitulo is defined as:

xtitulo VARCHAR2(500);


After all the taks made inside the procedure, I have this block of instructions, in order to copy the CLOB content into a TABLE of VARCHAR2's:

    xCount := 0;
    xOffset := 1;
    xChunkSize := 4000;
    
    xSize := dbms_lob.GETLENGTH(xTexto);
    
    WHILE xString_Out IS NOT NULL AND xChunkSize > 0
    LOOP
      IF xOffset + xChunkSize > xSize THEN
        xChunkSize := xSize - xOffset + 1;
      END IF;
      IF xChunkSize > 0 THEN
        xString_Out := dbms_lob.SUBSTR(xTexto, xChunkSize, xOffset);
        dbms_output.put_line(to_char(xCount) || ' - ' || to_char(xChunkSize) || ' - ' || to_char(xOffset));
        -- dbms_lob.READ(xTexto, xChunkSize, xOffset, xString_Out);
        IF xString_Out IS NOT NULL THEN
          --dbms_output.put_line('---> ' || xString_Out);
          v_data(xCount) := xString_Out;
          xCount := xCount + 1;
          xOffset := xChunkSize + xOffset + 1;
        END IF;
      END IF;
    END LOOP;


If I uncomment dbms_output.put_line('---> ' || xString_Out); instruction, a value error occures:

ORA-06502: PL/SQL: numeric or value error


Any help will be greatly appreciated,

Thanks
Jaime
Re: Problem retrieving substring from a CLOB variable [message #393148 is a reply to message #393145] Fri, 20 March 2009 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: Problem retrieving substring from a CLOB variable [message #393149 is a reply to message #393145] Fri, 20 March 2009 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_output line limit is 255 bytes.

Regards
Michel
Re: Problem retrieving substring from a CLOB variable [message #393199 is a reply to message #393148] Fri, 20 March 2009 13:17 Go to previous messageGo to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
sorry, but when I posted the message, an error occured, so I thought post was not sent to the forum.

Jaime
Re: Problem retrieving substring from a CLOB variable [message #393200 is a reply to message #393149] Fri, 20 March 2009 13:19 Go to previous message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Thanks... it worked, the dbms_output.put_line at least.

Jaime
Previous Topic: Coalesce multiple columns (merged)
Next Topic: Help required on Select query
Goto Forum:
  


Current Time: Fri Dec 09 16:00:25 CST 2016

Total time taken to generate the page: 0.13604 seconds