Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
I copied the "call" syntax from the book "Oracle Database 10g: The Complete Reference" in the section titled "Calling Procedures Within Triggers", where it does exactly this. I assume this changed since 10g, as I am using 11g-r2. –  notfed Jun 19 '12 at 15:36
1  
@notfed - The CALL syntax hasn't changed in forever-- what works in 10.1 works in 11.2 just as well. You don't have a BEGIN or an END when you are using the CALL syntax (see my second example). In general, though, I'd strongly suggest avoiding the CALL syntax entirely. –  Justin Cave Jun 19 '12 at 15:41
    
+1 Thanks for the advice, I will certainly avoid it from here on. (I think it looks ugly anyway.) –  notfed Jun 19 '12 at 15:58
2  
@notfed - Putting the actual logic in a stored procedure rather than having lots of code in trigger bodies is definitely a good idea. Using the paleolithic CALL syntax rather than the standard PL/SQL block to actually execute the stored procedure is where I have a dispute. CALL is valid, it's just extremely unusual and sufficiently different from every other PL/SQL block you'll ever encounter that I can't see any benefit to using it (well, it does save typing 4 characters, so I guess there is some benefit). –  Justin Cave Jun 19 '12 at 16:13
1  
@notfed - In the normal case, you have to type both 'BEGIN' and 'END' (8 characters). In the CALL case, you just have to type CALL (4 characters) so you save 4 characters. Not, of course, that avoiding typing 4 characters is a meaningful benefit. But the CALL syntax is marginally shorter. –  Justin Cave Jun 19 '12 at 16:20
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.