Re: trigger -> stored proc -> Java code

In article <f73db37a.0110101137.7dc27c54_at_posting.google.com>, emwalter_at_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:

1. 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.
2. In INSTEAD OF triggers on views, you can read both the :new and :old values.
3. 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).
4. 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