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?

"Spiggy Topes" <ubik (AT) shaw (DOT) ca> wrote

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

"Spiggy Topes" <ubik (AT) shaw (DOT) ca> wrote

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

"Spiggy Topes" <ubik (AT) shaw (DOT) ca> wrote

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

"Spiggy Topes" <ubik (AT) shaw (DOT) ca> wrote

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

On Jul 30, 8:44*pm, "Tim Arnold" <timkarn... (AT) comcast (DOT) net> wrote:
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?

On Jul 30, 8:44*pm, "Tim Arnold" <timkarn... (AT) comcast (DOT) net> wrote:
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?

On Jul 30, 8:44*pm, "Tim Arnold" <timkarn... (AT) comcast (DOT) net> wrote:
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?

On Jul 30, 8:44*pm, "Tim Arnold" <timkarn... (AT) comcast (DOT) net> wrote:
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?

"Spiggy Topes" <ubik (AT) shaw (DOT) ca> wrote

On Jul 30, 8:44 pm, "Tim Arnold" <timkarn... (AT) comcast (DOT) net> wrote:
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;
/