I have a vague recollection of having a similar problem some time ago. If
memory serves me, the only way round it at the time was to do a raw to hex
and pipe it to another procedure that did a hex to raw for insert. Probably
useless and I'm sure somebody could do better.
Jim
Thomas J. Kyte <tkyte_at_us.oracle.com> wrote in message
news:8he2g9$eh2$1_at_nnrp1.deja.com...
> In article <8hdcil$1q9v$1_at_storm.comstar.ru>,
> "Oleg Fedorenkov" <ofed_at_com2com.ru> wrote:
> > This is a multi-part message in MIME format.
> >
> > ------=_NextPart_000_0174_01BFCE35.1AFC63C0
> > Content-Type: text/plain;
> > charset="iso-8859-1"
> > Content-Transfer-Encoding: 7bit
> >
> > Hi
> > I had the same problem. I could only find in docs that it is the
restriction
> > for longs in triggers.
> > May be you should try another datatypes. If you find a solution
please mail
> > me.
> >
> > Oleg Fedorenkov
> >
> > Minicooper News <Minicooper_at_quadrat.be> wrote in message
> > news:Q3TS4.88$Qt4.2294_at_nreader2.kpnqwest.net...
> > I need to write a trigger that copies a value out of one table into
> > another and i'm experiencing great troubles in copying the LONG RAW
field.
> >
> > for example:
> >
> > table: Y ( Y1 integer, Y2 varchar2(20), Y3 long raw)
> > table: X ( X1 integer, X2 varchar2(20), X3 long raw)
> >
> > trigger: Y_to_X before insert or update on Y referencing new as new
and
> > old as old for each row
> > begin
> > insert into X (X1,X2,X3) values (:new.Y1, :new.Y2,
> > :new.Y3);
> > end;
> >
> > now i only get the first 2 values in the table without the long
raw !!
> >
> > Can any1 help me here !!!!!??????
> >
> > mailto:jurgen_at_quadrat.be
> > from the application developers guide:
>
>
> http://technet.oracle.com/doc/server73x/ADG73/ch9.htm#toc093
>
> <quote>
>
> LONG and LONG RAW Datatypes
>
> LONG and LONG RAW datatypes in triggers are subject to the
> following restrictions:
>
> o A SQL statement within a trigger can insert data into a column
> of LONG or LONG RAW datatype.
>
> o If data from a LONG or LONG RAW column can be converted to a
> constrained datatype (such as CHAR and VARCHAR2), a LONG or LONG
> RAW column can be referenced in a SQL statement within a
> trigger. Note that the maximum length for these datatypes is
> 32000 bytes.
>
> o Variables cannot be declared using the LONG or LONG RAW
> datatypes.
>
> o :NEW and :OLD cannot be used with LONG or LONG RAW columns.
> </quote>
>
> So, you cannot use your approach to copy a LONG RAW from one
> table to another. In fact, if the LONG RAW exceeds 32k, you
> simply will not be able to do this via a trigger at all.
>
> What you can do, if the LONG RAW is less then 32k, is to use an
> AFTER trigger (not for each row) to process the copy. A FOR
> EACH ROW trigger would be used to capture the primary keys of
> the newly inserted rows and the AFTER trigger would read the
> long raw out into a temporary variable (upto 32k) and insert it
> into the other table. See
>
>
> http://osi.oracle.com/~tkyte/Mutate/index.html
>
> for the details. Your particular implementation, if the long
> raws are 32k or less, would look like this:
>
> tkyte_at_ORA734.WORLD> create table images
> 2 ( image_id int PRIMARY KEY,
> 3 image_filepath int,
> 4 image_filename varchar2(12),
> 5 image_filetype varchar2(10),
> 6 image_data long raw
> 7 );
>
> Table created.
>
> tkyte_at_ORA734.WORLD> create table test_images
> 2 ( image_id int,
> 3 image_filepath int,
> 4 image_filename varchar2(12),
> 5 image_filetype varchar2(10),
> 6 image_data long raw
> 7 );
>
> Table created.
>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD> create or replace package state_pkg
> 2 as
> 3 type numArray is table of number
> 4 index by binary_integer;
> 5
> 5 modified numArray;
> 6 empty numArray;
> 7 end;
> 8 /
>
> Package created.
>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD> create or replace trigger images_BIU
> 2 before insert or update on images
> 3 begin
> 4 state_pkg.modified := state_pkg.empty;
> 5 end;
> 6 /
>
> Trigger created.
>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD> create or replace trigger images_AIUfer
> 2 after insert or update on images
> 3 for each row
> 4 begin
> 5 state_pkg.modified(state_pkg.modified.count+1) :=
> 6 :new.image_id;
> 7 end;
> 8 /
>
> Trigger created.
>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD> create or replace trigger images_AIU
> 2 after insert or update on images
> 3 begin
> 4 for i in 1 .. state_pkg.modified.count
> 5 loop
> 6 for x in ( select *
> 7 from images
> 8 where image_id =
> 9 state_pkg.modified(i) )
> 10 loop
> 11 insert into test_images
> 12 ( image_id, image_filepath,
> 13 image_filename,
> 14 image_filetype, image_data )
> 15 values
> 16 ( x.image_id, x.image_filepath,
> 17 x.image_filename,
> 18 x.image_filetype, x.image_data );
> 19 end loop;
> 20 end loop;
> 21 state_pkg.modified := state_pkg.empty;
> 22 exception
> 23 when others then
> 24 state_pkg.modified := state_pkg.empty;
> 25 raise;
> 26 end;
> 27 /
>
> Trigger created.
>
>
> Now, to test this out, we will insert a 10k raw and see if it
> gets copied correctly:
>
> tkyte_at_ORA734.WORLD> insert into images values
> 2 ( 1, 1, 'x', 'y', hextoraw( rpad( 'A', 4000, 'A') ) );
>
> 1 row created.
>
> tkyte_at_ORA734.WORLD>
> tkyte_at_ORA734.WORLD> set serveroutput on
> tkyte_at_ORA734.WORLD> declare
> 2 x images%rowtype;
> 3 y images%rowtype;
> 4 begin
> 5
> 5 select * into x from images;
> 6 select * into y from test_images;
> 7
> 7 dbms_output.put_line( 'length(x) = ' ||
> 8 length( x.image_data ) );
> 9 dbms_output.put_line( 'length(y) = ' ||
> 10 length( y.image_data ) );
> 11
> 11 if ( x.image_data = y.image_data ) then
> 12 dbms_output.put_line( 'Are equal' );
> 13 else
> 14 dbms_output.put_line( 'Are NOT equal' );
> 15 end if;
> 16 end;
> 17 /
> length(x) = 2000
> length(y) = 2000
> Are equal
>
> PL/SQL procedure successfully completed.
>
>
> sure enough -- this works. Again, this will only work upto
> 32k at which point it will no longer work as the largest PLSQL
> variable is 32k in size. At that point, given that you are
> using Oracle7.3.4, your choices are very limited -- PLSQL is out
> of the question. The best answer at that point is really to use
> Oracle8 and use the BLOB datatype which does not have this
> restriction -- the entire blob can be manipulated using plsql or
> any language for that matter.
>
>
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Jun 04 2000 - 00:00:00 CDT
