Re: Transferring LONG RAW from TAB1 to TAB2

[Quoted] In article <37E654BC.57470EF9_at_theleme.com>,   Yann Chevriaux <chevriaux_at_theleme.com> wrote:
> Many thanks: it works ... until 32K !
>
> I've just learned that in PL/SQL, long variables can't execed 32K !
> As the documents users want to store are often over 50K large, I must
find
> another solution.
>
> Yann.
>
> michael_bialik_at_my-deja.com a écrit :
>
> > In article <37E10FD1.F00D0715_at_theleme.com>,
> > Yann Chevriaux <chevriaux_at_theleme.com> wrote:
> > > I would to know how to copy a LONG RAW column from a table to
another.
> > >
> > > The following based function failed with:
> > > ORA-00997: illegal use of LONG datatype
> > >
> > > function ArchiveRawData(dataId in number)
> > > return number
> > > is
> > > newArcId number;
> > > begin
> > > newArcId := NewId; -- NewId is a based function that return an
UID
> > >
> > > insert into rawArchive (id, rawdata)
> > > select newArcId, rawdata
> > > from rawCurrent
> > > where id = dataId;
> > >
> > > return newArcId;
> > >
> > > end;
> > >
> > > I thought it could be a PL/SQL limitation but even with a simple
SQL
> > > statement it fail as well.
> > >
> > > Configuration: NTServer; Oracle 7.3.2
> > >
> > > Please help !!!
> >
> > >
> > >
> >
> > Use PL/SQL instead :
> >
> > function ArchiveRawData(dataId in number)
> > return number
> > is
> > newArcId number;
> > newData rawCurrent.rawdata%TYPE;
> > begin
> > newArcId := NewId; -- NewId is a based function that return an
UID
> >
> > select rawdata into newData from rawCurrent
> > where id = dataId;
> >
> > insert into rawArchive (id, rawdata) values(newArcId,newData);
> >
> > return newArcId;
> >
> > end;
> >
> > Good luck. Michael.
> >
If you are version 8, look at using the LOB data types and the dbms_lob package. The package provides piece-wise fetching so you can grab or insert a chunck at a time.

[Quoted] If you are 7.3, Thomas Kyte of Oracle has posted several times in the past about how to do piece-wise fetching via pl/sql. Try the newsgroup archives.

--
[Quoted] Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.