Error calling stored procedure from trigger in Oracle Database 11g

Question

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to know what I'm doing wrong.

I've created the following Java source:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED TryJavaHelperSource AS
import java.io.*;
public class TryJavaHelper {
    public static void Run(String arg1, String arg2) throws Exception
    {
        // …
        return;
    }
}
/

And I created a stored procedure that calls this Java source:

CREATE OR REPLACE PROCEDURE TryJavaHelperRun (arg1 IN VARCHAR2, arg2 IN VARCHAR2)
AS LANGUAGE JAVA NAME 'TryJavaHelper.Run (java.lang.String, java.lang.String)';
/

I'm trying to create a trigger on this table:

create table TryTable ( 
    arg1 varchar2(20), 
    arg2 varchar2(100) 
);

I get an compile error when I attempt to create the following trigger:

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    CALL TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Here's the error I'm getting:

SQL> sho err
Errors for TRIGGER TRYTABLE_BEF_UPD_ROW:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/6      PLS-00103: Encountered the symbol "TRYJAVAHELPERRUN" when
         expecting one of the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "TRYJAVAHELPERRUN" to
         continue.

Why is this error occurring, and how do I fix it?

UPDATE:

It looks like it works when I take out the CALL statement. What are the rules governing when to use CALL and when to just type the name of the function/procedure?

Answer 1

Your trigger doesn't need the CALL keyword.

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ancient version of Oracle. It allows you to replace the entire PL/SQL block that is the trigger body with a single CALL statement, i.e.

create or replace trigger TRYTABLE_BEF_UPD_ROW
  before update or insert on TryTable
  for each row
  call TryJavaHelperRun(:new.arg1, :new.arg2)
/

Personally, I've never come across a case where there was any benefit to ditching the "normal" PL/SQL syntax in favor of using CALL in a trigger.

This assumes that you can call the TryJavaHelperRun procedure successfully in general.