Re: Creating a trigger that calls a stored procedure

higuey77_at_yahoo.com (Elie) writes:

> 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.
>

I notice you have not included the interface definition for your stored procedure to map between the java data types and the SQL types. I assume you have done this, but thought I'd mention it anyway.

Also, is your java procedure defined as public static?

Have you tried calling your java stored procedure from within a test procedure within sqlplus to make sure it works? Doing something like

declare
  value1 INTEGER;
  value2 NUMBER;
begin
  my_java_proc(value1, value2);
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
/

might give you an insight into what is wrong.

I assume your use of java is because you need to do something you can't do in plsql (like execute and external utility or OS command), otherwise I would not be using it - use plsql - its a lot easier and more robust IMO.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!