Re: Convert LONG (not RAW) to BLOB?

"Spiggy Topes" <ubik_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;

• 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;

• Clean up, and reraise the error. DBMS_SQL.CLOSE_CURSOR(v_CursorID); RAISE; END Long2Lob; /