| Oracle trigger calling java static method. [message #41369] |
Mon, 23 December 2002 05:25  |
Tejasvi
Messages: 1
Registered: December 2002
|
Junior Member |
|
|
Hi,
I have written a trigger that works after insert in the 'DISTRIBUTION' table, creates an XML message and calls a static java method post(String, String, String) in the class ServletConnector. The trigger is:
CREATE OR REPLACE TRIGGER "HILLROM"."NEW_WORK_ORDER" BEFORE
INSERT ON "HILLROM"."DISTRIBUTION"
FOR EACH ROW
DECLARE
msg VARCHAR2(1024);
url VARCHAR2(1024);
BEGIN
msg := '<FISEvent name="NewWorkOrder">' ||
'<Property name="Call_Number">' || :New."CALL_NUMBER"
||
||
'</Property>' ||
'<Property name="Employee_ID">' || :New."EMPLOYEE_ID"
||
||
'</Property>' ||
'</FISEvent>';
Select "VALUE"
into url
from HILLROM_CONFIG
where "PROPERTY" = 'FISEventListenerUrl';
SERVLETCONNECTOR_POST('NEW_WORK_ORDER', msg, url);
END;
We are experiencing some problems with this:
1) On insert into the 'DISTRIBUTION' table, the trigger calls the java method (which posts the XML on some URL) and then hangs...a lock is created on the row in the table and the insertion is actually never committed (though the trigger is AFTER insert).
2) When I try to run a SQL script that contains multiple inserts into the DISTRIBUTION table, it hangs after the first insert statement. Here too, the java methos is called successfully...but the INSERT never takes place and a lock is created.
Can anyone let me know what is the problem here?
Thanks in advance
Tejasvi.
|
|
|
|
| Re: Oracle trigger calling java static method. [message #41381 is a reply to message #41369] |
Tue, 24 December 2002 20:53  |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You say that the trigger is AFTER insert, but your code says BEFORE insert.
In the following section:
msg := '' ||
'' || :New."CALL_NUMBER"
||
||
'' ||
'' || :New."EMPLOYEE_ID"
||
||
'' ||
'';
it is not clear what you are trying to do. You have two places where you have two || ( that is || || ) in a row. When you compile the trigger that will cause errors. If you change it to:
msg := '' ||
'' || :New."CALL_NUMBER"
||
'' ||
'' || :New."EMPLOYEE_ID"
||
'' ||
'';
it will compile, but I am not sure if that is what you want. It will assign a value to msg like:
value_of_new_call_number value_of_new_employee_id
If you are trying to concatenate single quotes with values, then you need to put an extra single quote in front of each single quote that you want to be part of the msg and place single quotes around them. For example:
msg := '''';
assigns a value to msg consisting of one single quote (').
So, for example, the following:
msg := '''' || :NEW."CALL_NUMBER" || ''''
|| '''' || :NEW."EMPLOYEE_ID" || '''';
would assign a value to msg like:
'value_of_new_call_number' 'value_of_new_employee_id'
You can experiment with the value of msg, test it, and display it, by setting serveroutput on, replacing the call to servletconnector_post with:
DBMS_OUTPUT.PUT_LINE (msg);
and performing an insert.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
