Re: Java Trigger and SP

Cristina Bulfon wrote:
> Hello,
>
> thanks for the answer, I follow your advice
>
> - remove Connection argument frm the Java code
>
> - create at the same way stored procedure and trigger
>
> and I still got the same error
>
> cristina
>
> Rauf Sarwar wrote:
> > Comments embedded.
> >
> > Cristina Bulfon wrote:
> >
> >>Hello,
> >>
> >>I am newby with Oracle and I'd like to implement a Stored Procedure
> >
> > or
> >
> >>Trigger.
> >>PC is running Fedora Core 2 and Oracle 9.2.0.4
> >>
> >>First of all I tried to ATTACH a Java class directly to the table
> >>
> >>[bulfon_at_localhost ~]$ sqlplus /nolog
> >>
> >>SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 16 10:22:14
2005
> >>
> >>Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >>
> >>SQL> connect system/<password>@InvSicr_localhost
> >>Connected.
> >>SQL> alter table Referente attach java class "DeleteReferente" in
> >>'/home/cristina';
> >>alter table Referente attach java class "DeleteReferente" in
> >>'/home/cristina'
> >> *
> >>ERROR at line 1:
> >>ORA-01735: invalid ALTER TABLE option
> >>
> >>it seems that my Oracle version doesn't support Java Trigger
> >>
> >>then create a stored procedure, following the steps below
> >>
> >>1) my Java code
> >>
> >>import java.sql.*;
> >>import java.io.*;
> >>import oracle.jdbc.*;
> >>
> >>public class DeleteReferente {
> >>
> >> public static void delete (Connection con, int id ) throws
> >
> >
> > Your java procedure accepts two arguments (More about this few
lines
> > below). How are you going to pass a Connection object from PLSQL?
> > Remove the Connection argument and do a default connection like,
> >
> > Connection con
> > = new oracle.jdbc.OracleDriver().defaultConnection();
> >
> >
> >>SQLException
> >>{
> >>
> >> Statement st = con.createStatement();
> >> ResultSet rs;
> >> rs = st.executeQuery("select * from referente where
> >
> > idRef=id");
> >
> >> System.out.println("cancella");
> >> st.close();
> >> }
> >>}
> >>
> >>2) The Java class is in my Home Dir and the path is in the
CLASSPATH
> >>
> >>
> >>3)
> >>
> >>javac DeleteReferente.java
> >>loadjava -u system/<password>@InvSicr_localhost
DeleteReferente.class
> >>
> >>4) Procedure
> >>
> >>SQL> create or replace procedure test(id NUMBER)
> >> 2 as language java name
> >> 3 'DeleteReferente.delete(oracle.sql.NUMBER)';
> >
> >
> > Your java stored procedure above accepts two arguments. You are
passing
> > only one. See comments few lines above.
> >
> >
> >> 4 /
> >>
> >>Procedure created.
> >>
> >>5) Trigger
> >>
> >>SQL> CREATE OR REPLACE TRIGGER deltrg before delete on referente
for
> >>each row
> >> 2 call test(:old.idref)
> >> 3 /
> >>
> >>Trigger created.
> >>
> >>6) Test
> >>
> >>SQL> delete referente where idref=4;
> >>delete referente where idref=4
> >> *
> >>ERROR at line 1:
> >>ORA-29531: no method delete in class DeleteReferente
> >
> >
> > Java thinks you are calling an overloaded procedure which accepts
only
> > one argument and it cannot find it.
> >
> >
> >>ORA-04088: error during execution of trigger 'SYSTEM.DELTRG'
> >>
> >>
> >>Am I missing something or doing something wrong ?
> >>
> >>any help, hint, suggestions are very appreciated
> >>
> >>
> >>Thanks in advance
> >>
> >>cristina
> >
> >
> > Watch out for any table mutation in your java code.
> >
> > Regards
> > /Rauf
> >

Please do not top post or you won't get a reply again.

Try with int. Change the following line in your plsql procedure, 'DeleteReferente.delete(oracle­.sql.NUMBER)'; to
'DeleteReferente.delete(int)';

Also, you need to close ResultSet in your java code before closing the statement i.e. rs.close() and close the connection also at the end i.e. con.close().

Your java code will most likely raise a mutating table error (see note in my previous post). You cannot do select on the table where trigger is fired from.

I would suggest a good understanding of Oracle Triggers and JDBC would be helpfull. Visit http://tahiti.oracle.com

Regards
/Rauf Received on Sun Apr 17 2005 - 18:31:44 CDT