Re: Creating a trigger that calls a stored procedure

higuey77_at_yahoo.com (Elie) wrote in message news:<4cea796.0212221931.96143b8_at_posting.google.com>...
> Hi,
>
> Using JAVA (JDBC), I'm trying to create a trigger that calls a stored
> procedure. However there seems to be something wrong with my syntax
> because when I go to the database and check the status of the trigger
> it's invalid and as a result I am unable to call it. The stored
> procedure that the trigger is trying to call is valid and exist in the
> db.
>
>
> Stored Procedure Code (which I believe is correct)is listed below.
>
> String proc= "CREATE OR REPLACE PROCEDURE stored_proc
> (parameter1 IN NUMBER, parameter2 IN NUMBER)
> AS LANGUAGE JAVA NAME 'myClass.method_one(java.lang.Integer,
> java.lang.Double)';";
>
> Trigger Code is listed below
>
> String trig= "CREATE OR REPLACE TRIGGER myTrigg
> AFTER INSERT ON student_billings
> FOR EACH ROW
> BEGIN
> call stored_proc(:new.parameter1, :new.parameter2);
> END;";
>
> Does anyone notice something wrong.
>
> Thanks in Advance

Have you tried to create your trigger from SQL*Plus to see if it compiles OK?

On a side note...why are you trying to create server stored objects from java instead of SQL*Plus? If the goal is to "Create once and use many times" then it is much easier to create stored objects from SQL*Plus where you get all error messages and exactly know if your object compiled or not then call them from java. Remember, all DDL statements (CREATE something ....) from java are treated as dynamic PLSQL in Oracle.... and if you are familiar with dynamic PLSQL...you would know that it is not the best way to create your stored objects.

Unless my assumption is incorrect... this to me looks like a classic java overkill.

Regards
/Rauf Sarwar Received on Thu Dec 26 2002 - 04:21:18 CST