| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: problem using long raw in trigger
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
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
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;
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;
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;
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;
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;
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
 |
 |