Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Java Trigger and SP

Java Trigger and SP

From: Cristina Bulfon <cristina.bulfon_at_roma1.infn.it>
Date: Sat, 16 Apr 2005 10:41:45 +0200
Message-ID: <4260CFC9.50703@roma1.infn.it>


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 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)';    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 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 Received on Sat Apr 16 2005 - 03:41:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US