Path: news.easynews.com!easynews!feedwest.news.agis.net!us.telia.net!newsfeed.media.kyoto-u.ac.jp!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: trigger -> stored proc -> Java code Date: 10 Oct 2001 16:39:17 -0700 Organization: Oracle Lines: 103 Message-ID: <9q2m750h5b@drn.newsguy.com> References: NNTP-Posting-Host: p-915.newsdawg.com X-Newsreader: Direct Read News 2.90 Xref: easynews comp.databases.oracle.server:120880 X-Received-Date: Wed, 10 Oct 2001 17:32:16 MST (news.easynews.com) In article , emwalter@engin.umich.edu says... > >So, I am having a big problem. The problem is that I created a > trigger that fires after a insert into a table. That trigger then > calls a stored procedure which then refers to some Java code, please > refer to code below for more information. My problem is that when the > blob comes into the Java code the length of the blob is 0 and I can > not get any information out of it ...even though there is data in the > blob in the database. I REALLY need some help....I have been stuck > for 3 days and asked everyone, including Oracle...if you know > anything..PLEASE HELP!!! > what is the full definition of the trigger -- is is a BEFORE INSERT FOR EACH ROW or an AFTER INSERT FOR EACH row (see specifically a* below) .... n Trigger Body. You can use the LOB column or LOB attribute in a trigger body subject to the following conditions. In general, the :new and :old LOB values bound in the trigger are read-only which means that you cannot write to the LOB. More specifically: a. In before row and after row triggers -* you can read the :old value of a LOB in both the triggers. * you can read the :new value of the LOB only in an after-row trigger. b. In INSTEAD OF triggers on views, you can read both the :new and :old values. c. You cannot specify the LOB column in an OF clause (Note that a BFILE can be modified without updating the underlying tables on which it is based). d. If you use OCI functions or DBMS_LOB routines to update LOB values or LOB attributes on object columns, the functions or routines will not fire the triggers defined on the tables containing the columns or attributes. > The code: > > I have a trigger that happends after an insert into a table. The > trigger is: > > BEGIN > IF (:new.nReplacedID > 0) THEN > Replaced_Packet(:new.nOriginalPacketID, :new.nLoadID, > :new.tSCETTime, :new.nPacketType, :new.sChannel, :new.nReplacedID, > :new.oPacket); > ELSE > New_Packet(:new.nOriginalPacketID, :new.nLoadID, > :new.tSCETTime, :new.nPacketType, :new.sChannel, :new.nReplacedID, > :new.oPacket); > END IF; > END; > > Here's the stored Procedure: > > (nPacketID NUMBER, nLoadID NUMBER, tSCETTime DATE, nPacketType > NUMBER, > sChannel CHAR, nReplacedID NUMBER, oPacket BLOB) > AS LANGUAGE JAVA NAME > 'Expand.Packet(int, int, java.sql.Date, int, java.lang.String, int, > java.sql.Blob)'; > > Here the beginning of the Java code: > import java.io.*; > import java.sql.*; > import java.lang.*; > import oracle.jdbc.driver.*; > import oracle.jdbc.*; > public class Expand > { > //***************************************************************** > //***************************************************************** > //***********************Packet******************************** > //Summary: This function is called when it is a new packet. > //***************************************************************** > //**************************************************************** > public static void Packet(int nOriginalPacketID, int nLoadID, > java.sql.Date tSCETTime, int nPacketType, String sChannel, int > nReplacedID, java.sql.Blob oPacket) > { > int fill_in; > java.sql.Connection conn; > System.out.println("beginning"); > try{ > conn = new OracleDriver().defaultConnection(); >long nblobLength = oPacket.length();//the length of the blob > System.out.println("the blob length is "+ nblobLength); > System.out.println("after"); > }catch(SQL Exception e){} > }//end of the function > }//end of the class > > ANY HELP would be appreciated!!! Thank you for your time! -- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp