Re: Java Trigger and SP

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 Received on Sat Apr 16 2005 - 19:28:27 CDT