Home » SQL & PL/SQL » SQL & PL/SQL » A DB Trigger calling a Oracle Procedure Which in Turn Calls a Java Function
icon9.gif  A DB Trigger calling a Oracle Procedure Which in Turn Calls a Java Function [message #297717] Sat, 02 February 2008 22:23 Go to next message
karmod
Messages: 2
Registered: February 2008
Junior Member
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 Sad

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

Please Help.
Re: A DB Trigger calling a Oracle Procedure Which in Turn Calls a Java Function [message #297718 is a reply to message #297717] Sat, 02 February 2008 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.dbforums.com/showthread.php?t=1627033

How do you invoke a procedure in PL/SQL?
Re: A DB Trigger calling a Oracle Procedure Which in Turn Calls a Java Function [message #297722 is a reply to message #297717] Sun, 03 February 2008 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition for your next posts please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
icon14.gif  Figured the Problem. [message #297823 is a reply to message #297717] Sun, 03 February 2008 22:57 Go to previous message
karmod
Messages: 2
Registered: February 2008
Junior Member
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 Smile
Previous Topic: regarding date
Next Topic: How to generate a spool file without an extension name (.lst,.txt etc...)
Goto Forum:
  


Current Time: Thu Dec 08 14:28:46 CST 2016

Total time taken to generate the page: 0.04170 seconds