Stored Procedure to Copy Long Raw

From: Thomas Sommerfeld <Thomas.Sommerfeld_at_T-Online.de>
Date: 1997/10/19
Message-ID: <344A2225.55576829@T-Online.de>#1/1

Dies ist eine mehrteilige Nachricht im MIME-Format.

--------------9B25D58DF3F21CB0F7FB372B
Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

Im trying to develop a stored proc with Oracle 7.3 that will copy an existing row to a new row. The table contains a LONG RAW column. The stored proc I use looks like this one:

procedure copyrow (OldKey in number,NewKey out number) as begin
declare
Buffer long;

begin
savepoint BeforeCoy;

Get new KeyValue select SequenceXY.Nextval into Newkey from dual;
Create new Row with new KeyValue insert into TableXY (KeyField, Field1) select NewKey, Field1 from TableXY where KeyField = OldKey;
Get LONG field into buffer select TextField into Buffer from TableXY where KeyField = OldKey;
Set LONG field with buffer update TableXY set TextField = Buffer where KeyField = NewKey; end; exception when others then rollback to BeforeCopy; NewKey := -1; end; /

This works fine while the content of TextField is smaller than 32 K, because that's maximum length of the LONG variable in the proc. Nevertheless I need a way to copy a row with fields containing data up to 10 MB and more.

Has anybody a solution or workaround for this problem?

Thanks a lot

Thomas Sommerfeld

--------------9B25D58DF3F21CB0F7FB372B
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Visitenkarte für Thomas Sommerfeld
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Thomas Sommerfeld
n:              Sommerfeld;Thomas
org:            Softwareentwicklung Sommerfeld

email;internet: Thomas.Sommerfeld_at_T-Online.de x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard

--------------9B25D58DF3F21CB0F7FB372B-- Received on Sun Oct 19 1997 - 00:00:00 CDT