Re: Convert LONG (not RAW) to BLOB?

From: Tim Arnold <timkarnold_at_comcast.net>
Date: Sat, 2 Aug 2008 09:21:09 -0400
Message-ID: <KOqdnQXZz-PXwgnVnZ2dnUVZ_ofinZ2d@comcast.com>

"Spiggy Topes" <ubik_at_shaw.ca> wrote in message news:7e70f9ef-070c-404c-b9a2-bd500cc169ec_at_t1g2000pra.googlegroups.com... On Jul 30, 8:44 pm, "Tim Arnold" <timkarn..._at_comcast.net> wrote:
> "Spiggy Topes" <u..._at_shaw.ca> wrote in message
>
> news:6b9a4b00-6352-4812-a9f1-0c392af84dc0_at_a21g2000prf.googlegroups.com...
>
>
>
> >I have an Oracle database containing several tables with LONGs - not
> > LONG RAW, just LONG. These tables were accessed by a VB application
> > using ADO and Microsoft's MSDATASHAPE provider. It's been perfectly
> > happily socking away and retrieving binary data to/from those LONGs
> > for several years now - archived Word documents, Outlook message files
> > and so forth.
>
> > Recently, I found out what happens when language settings at the
> > server and the desktop are different; character translation eats all
> > the binary data leaving useless mush.
>
> > So it behooves me to replace all the LONGs with BLOBs, before disaster
> > strikes.
>
> > Trouble is, I can't find a way of converting that doesn't require
> > reading each record using MSDATASHAPE as a LONG and writing it using
> > ORAOLEDB as a BLOB - the MS provider doesn't do BLOBs. All the simple
> > SQL suggestions I've found so far don't work, as they either won't
> > allow for conversion of a field defined as LONG to any binary format,
> > or as soon as they hit non-character data they die.
>
> > Anyone have a workable method to get around this?
>
> Metalink Note:1012454.7
>
> he following example shows an alternate method for converting LONG
> columns into BLOBs using PL/SQL.
>
> REM long2lob.sql
> REM Version 1.0, last updated 8/8/97
> REM This procedure copies LONG data into a CLOB, as described in
> REM Chapter 21 of _Oracle8 PL/SQL Programming_ by Scott Urman.
>
> CREATE OR REPLACE PROCEDURE Long2Lob(
> -- Uses DBMS_SQL to select a LONG column identified by p_LongQuery, and
> -- returns it in p_CLob.
> p_LongQuery IN VARCHAR2,
> p_CLob IN OUT CLOB) AS
>
> c_ChunkSize CONSTANT INTEGER := 100;
>
> v_CursorID INTEGER;
> v_RC INTEGER;
> v_Chunk VARCHAR2(100);
> v_ChunkLength INTEGER;
> v_Offset INTEGER := 0;
> BEGIN
> -- Open the cursor, define, execute, and fetch.
> v_CursorID := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(v_CursorID, p_LongQuery, DBMS_SQL.V7);
> DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1);
> v_RC := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
>
> -- Loop over the LONG, fetching c_ChunkSize characters at a time from
> -- the LONG and adding them to the LOB.
> LOOP
> DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, c_ChunkSize, v_Offset,
> v_Chunk, v_ChunkLength);
> DBMS_LOB.WRITE(p_CLob, v_ChunkLength, v_Offset + 1, v_Chunk);
> IF v_ChunkLength < c_ChunkSize THEN
> EXIT;
> ELSE
> v_Offset := v_Offset + v_ChunkLength;
> END IF;
> END LOOP;
>
> DBMS_SQL.CLOSE_CURSOR(v_CursorID);
> EXCEPTION
> WHEN OTHERS THEN
> -- Clean up, and reraise the error.
> DBMS_SQL.CLOSE_CURSOR(v_CursorID);
> RAISE;
> END Long2Lob;
> /

Thanks, that sounds very promising. Ummm... how do I use it? Assuming I can get the necessary privileges to create stored procedures, how do I use this to transcribe column C1 on table T to column C2 on the same (or different, I'm not fussy) table?

Slight variation, but here goes

 SQL> desc y
 Name Null? Type

• -------- ------------ NO NUMBER(1) NAME CLOB

SQL> desc z
 Name Null? Type

• -------- ------------ NO NUMBER(1) NAME LONG

Here is the procedure to accomplish this task.

CREATE OR REPLACE PROCEDURE LONG_TO_LOB IS

    Lob_loc        CLOB;
    Buffer         VARCHAR2(32767);
    Amount         BINARY_INTEGER := 32;
    Position       INTEGER := 1 ;
    i              INTEGER;
    v_err        varchar2(1000) ;

BEGIN
    /* Select the LOB: */
    SELECT name INTO Lob_loc

        FROM Y WHERE no = 1
        FOR UPDATE;

    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE) ;

    /* Fill the Buffer with data to be written. */     SELECT name INTO buffer FROM Z WHERE no = 2 ;

    /* If the length of LONG data is more than 32767, then LOOP through to capture all the data */

• FOR i IN 1..3
• LOOP

     /* Write data: */
        DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);

--        Position := Position + Amount;

-- END LOOP;     /* Closing the LOB is mandatory if you have opened it: */     DBMS_LOB.CLOSE (Lob_loc);

EXCEPTION
   WHEN OTHERS THEN

      v_err := SQLERRM ;
      DBMS_OUTPUT.PUT_LINE('Operation failed');
      DBMS_OUTPUT.PUT_LINE(v_err) ;
      DBMS_LOB.CLOSE (Lob_loc);

END;
/ Received on Sat Aug 02 2008 - 08:21:09 CDT