Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unhappy Unanswered: A Trigger Calling a Procedure which in turn calls a Java Function

    Hi,
    I am having a error while writing trigger calling an oracle procedure which in turn calls a java function. Please advise

    Following is my Java Function:
    import java.lang.*;
    import java.sql.*;
    import oracle.jdbc.driver.*;

    public class DBTrigger
    {
    public static void send(String receiver) throws SQLException
    {
    Connection con=DriverManager.getConnection("jdbc:default:conn ection:");
    try
    {
    String mov="",temp="";
    String comma=",",status="send";

    Statement stat=con.createStatement();
    ResultSet rs=stat.executeQuery("select * from movie");
    Boolean more=rs.next();
    while(more)
    { //display all movies
    temp=rs.getString(2);
    mov+=temp;
    mov+=comma;
    more=rs.next();
    }
    rs.close();
    stat.close();
    pstat=con.prepareStatement("insert into ozekimessageout(receiver,msg,status) values (?,?,?)");
    pstat.setString(1,receiver);
    pstat.setString(2,mov);
    pstat.setString(3,status);
    pstat.executeUpdate();
    pstat.close();
    }//try
    catch(Exception e)
    {
    System.out.println(e);
    }


    }
    }

    And Here is my Oracle Procedure which calls the java program:
    SQL> create or replace procedure sen(receiver varchar2)
    2 as language java
    3 name 'DBTrigger.send(java.lang.string)';
    4 /

    Procedure created.

    And this is the trigger with the error

    SQL> create or replace trigger AUTO_SEND
    2 AFTER INSERT ON OZEKIMESSAGEIN
    3 FOR EACH ROW
    4 declare
    5 rec varchar2(30);
    6 Begin
    7 rec:='123456';
    8 call sen(rec);
    9 END;
    10 /

    With this Error

    5/6 PLS-00103: Encountered the symbol "SEN" when expecting one of the
    following:
    := . ( @ % ;
    The symbol ":=" was substituted for "SEN" to continue.

    Please Help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.orafaq.com/forum/m/297718/74940/#msg_297718

    How do you invoke a procedure in PL/SQL?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Posts
    4

    Thumbs up Figured it out.

    I figured the problem. I was using a invalid syntax while writing a trigger the trigger should have been:

    create or replace trigger auto_send
    on ozekimessagein
    for each row
    call sen(:new.receiver)
    /

    Glad it worked

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, if that's the whole (PL/SQL trigger) code, I'm afraid it won't work either. There's no trigger type, BEGIN - END keywords, what does CALL do here?

  5. #5
    Join Date
    Feb 2008
    Posts
    4
    Oops is should have written as:

    create or replace trigger auto_send
    after insert on ozekimessagein
    for each row
    call sen:new.receiver)
    /

    Mah bad! The call is supposed to call the oracle procedure which calls the java stored procedure and yes it worked. See o/p

    SQL> create or replace trigger auto_send
    2 after insert on ozekimessagein
    3 for each row
    4 call sen(:new.receiver)
    5 /

    Trigger created.

  6. #6
    Join Date
    Feb 2008
    Posts
    4
    dang with the typos!

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, true (?!?). It is a whole new syntax for me. I'm used to use a more conventional one, but - never mind me. I apologize for being suspicious.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •