This discussion is archived
13 Replies Latest reply: Mar 5, 2012 7:20 AM by 921802 RSS

LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.

454631 Newbie
Currently Being Moderated
Hi,

My source data contains a char data in LONG RAW column. I have to convert it to Char(CLOB). The problem is that i can not access the LONG RAW data for some columns for which the size exceeding above 32k. I have wriiten the function using DBMS_SQL package to retrieve only 32K LONG RAW data & then i am converting it to char(CLOB). But as the LONG RAW size is exceeding for some columns, i want to retrieve the data upto 64K insted of 32 K.

I am using the SP to retieve the data from LONGRAW.
Is it possible to retrive whole data if instead of LONG RAW, the column data type is BLOB

Its very urgent

Thx in advance!

Shilpa
  • 1. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some c
    311178 Newbie
    Currently Being Moderated
    Hi

    Did you take a look at askTom's implementation LONG2CLOB (use this to search on http://asktom.oracle.com/ ) ? I found it very usefull, not sure if it's capable of 32K+.

    Furthermore, if you have 9i+ maybe you can use:
    select TO_LOB(long_col) from table
    hope this helps,
    Michiel
  • 2. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some c
    445741 Newbie
    Currently Being Moderated
    Here's the link referred to in the previous post:
    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1037232794454
  • 3. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    454631 Newbie
    Currently Being Moderated
    The link specified code for LONG to CLOB conversion ...not LONG RAW to CLOB.
    My sorce data contains the pure text data but it is stored in LONG RAW datatype.
    I need to UPDATE the target table column whose data type is CLOB. But the problem is i am unable to fetch those source rows whose LONG RAW size is exceeding 32k size.

    The current scenario
    Source col : LONG RAW target Col : CLOB

    I am first converting the LONG RAW data into CLOB using SP. Then this CLOB data gets appended to TARGET CLOB in the same SP. But this works upto 32K
    LONG RAW data. If i need to convert the LONG RAW data above 32K size
    then currently i am truncating the LONG RAW data if it exceeds 32k using function.

    I need to convert whole LONG RAW data( atleast upto 64k) to CLOB(CHAR).
    Cud any one guide me the way to solve this?

    Shilpa
  • 4. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    27876 Newbie
    Currently Being Moderated
    what you can try is to convert the long raw into a BLOB and then drive your conversion from this BLOB to the final destination CLOB. You can use a temp table to temporarily convert the long raw to a BLOB to be able to use DBMS_LOB package on it to get the contents. Others can suggest better alternatives.
    SQL> create table long_raw(lng long raw) ; /* This is your current data table */

    Table created.

    SQL> create global temporary table temp_data(lob BLOB) ; /* temp table to convert long raw to BLOB */

    Table created.

    SQL> insert into long_raw values (utl_raw.cast_to_raw('This is pure text')) ; /* insert some data to the original table */

    1 row created.

    SQL> insert into temp_data select to_lob(lng) from long_raw ; /* transfer the data into the temp table converting it to BLOB */

    1 row created.

    SQL> /* now use temp_data table to qwuery your data and use DBMS_LOB to get the contents */
  • 5. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some c
    4125 Newbie
    Currently Being Moderated
    I would go with something very similar to what Kamal suggested.

    1. Insert LONG RAW into temp table with BLOB using TO_LOB.

    2. Convert BLOB to CLOB using this function.
    Re: BLOB to CLOB?
  • 6. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    454631 Newbie
    Currently Being Moderated
    But this handles only 32K of data. Is there any way to split up the long raw into two? So that in first i read 32k chunk data & in the second another 32k
  • 7. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    27876 Newbie
    Currently Being Moderated
    But this handles only 32K of data.
    Which part of the process outlined in above responses are you referring to?

    The TO_LOB should convert the entire RAW or LONG RAW. The other conversion function utilizes DBMS_LOB pacakge so should be able to handle the entire LOB.

    Are you still having issues with data > 32K with this approach?
  • 8. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    454631 Newbie
    Currently Being Moderated
    Kamal,
    My yesterdays reply was in reference to user user112. I can not apply ur approach. I have to handle it through SP as i am calling it through informatica(ETL tool) and also my source & target data base are different. I dont have access link to connect between two databases. I load the Source LONG RAW data in temporary target table using informatica. Then appending the target table with this data by first converting it to char using SP. Using Sp i am able to convert only 32k of long raw data to char. But what to do with the remaining chunk?. I have only two source rows which excceding this capacity. My target DB table column datatype is CLOB.

    Target column has some description up to 1000 char. I have to append it with some remark field( 500 char) and then with 64k of converted long raw data based one some condition .

    I cannot use direct insert statement

    Can u suggest me a way to do this?
    Even if i am able to split the long raw in two chunks that will also do for me.

    Thanks in advance!

    Shilpa
  • 9. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    475971 Newbie
    Currently Being Moderated
    Can u give me an idea about the sp to convert blob to clob
  • 10. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    593523 Newbie
    Currently Being Moderated
    A Example, the field CONTENTHTML is LOW RAW.


    CREATE OR REPLACE PROCEDURE Replace_Text_From_BLOB (
    numReg IN INTEGER,
    replaceStr IN VARCHAR2,
    replaceWith IN VARCHAR2) IS
              
    vBuffer VARCHAR2 (32767);
    l_amount BINARY_INTEGER := 32767;
    l_pos PLS_INTEGER := 1;
    l_clob_len PLS_INTEGER;
    newClob CLOB := EMPTY_CLOB;
         srcClob CLOB;
         vBuffer1 VARCHAR2 (32767);
         cont PLS_INTEGER := 1;
         
         
         
         pos PLS_INTEGER := 1;
    buffer LONG RAW( 32767 );
         

         
         CURSOR c_tempBlob IS
              SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,
    CONTENTCREATIONDATE, LOBCOL, CONTENTRSSURL,
    CONTENTPAGENUMBER
    FROM ADMCMS.TEMP_BLOB ORDER BY CONTENTCODE;
              
    BEGIN
         
         
         DELETE ADMCMS.TEMP_T_CONTENT;
         
         DELETE ADMCMS.TEMP_BLOB;
         
         
         INSERT INTO TEMP_BLOB SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,CONTENTCREATIONDATE,TO_LOB( CONTENTHTML) LOBCOL ,CONTENTRSSURL,
    CONTENTPAGENUMBER FROM T_CONTENT;

         FOR tmp IN c_tempBlob LOOP
         
         -- initalize the new clob
              newClob:=null;
              dbms_lob.createtemporary(newClob,TRUE);
              
              l_clob_len := dbms_lob.getlength(tmp.LOBCOL);
              vBuffer1 :=UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(tmp.LOBCOL, l_clob_len, 1));
              
              srcClob := TO_CLOB(vBuffer1);
    l_pos := 1;
              --Recorre el texto y lo modifica
              WHILE l_pos < l_clob_len
              LOOP
              dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

              IF vBuffer IS NOT NULL THEN
                   -- reemplaza el texto
                   vBuffer := replace(vBuffer, replaceStr, replaceWith);
                   -- escribe al new clob
                   dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
         END IF;
              l_pos := l_pos + l_amount;
              END LOOP;
              

                                       
              buffer := UTL_RAW.CAST_TO_RAW (newClob);                                        
                        
              INSERT INTO ADMCMS.TEMP_T_CONTENT (
    CONTENTCODE, NEWSCODE, CONTENTTYPECODE,
    CONTENTCREATIONDATE, CONTENTHTML, CONTENTRSSURL,
    CONTENTPAGENUMBER)
    VALUES (tmp.CONTENTCODE ,tmp.NEWSCODE ,tmp.CONTENTTYPECODE ,tmp.CONTENTCREATIONDATE
                   , buffer ,tmp.CONTENTRSSURL ,tmp.CONTENTPAGENUMBER );     
                        
                        
              COMMIT;     
                        
         cont := cont + 1;
              
              IF cont> numReg THEN
              EXIT;
              END IF;
              
         END LOOP;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END Replace_Text_From_BLOB;
    /
  • 11. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some c
    479343 Newbie
    Currently Being Moderated
    It was Nov 17, 2005 when the OP last replied and you are posting a reply now?
  • 12. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some c
    677794 Newbie
    Currently Being Moderated
    This is useful to me, so what the post is much later, he just worked on it recently, n'est c pas?
  • 13. Re: LONG RAW to CLOB conversion --- LONG RAW exceeding  32K SIZE for some col.
    921802 Newbie
    Currently Being Moderated
    can you give me the syntax for the SP you used for this conversion?