Re: Convert LONG (not RAW) to BLOB?

"Tim Arnold" <timkarnold_at_comcast.net> schreef in bericht news:KOqdnQXZz-PXwgnVnZ2dnUVZ_ofinZ2d_at_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;
> /
>

And don't forget to SAVE your result somewhere (table...)

Shakespeare Received on Sun Aug 03 2008 - 05:16:35 CDT