Hi,

I am having an error in A Database Trigger Calling an Oracle Procedure which in turn calls a Java function.....any body can help.

JAVA FUNCTION:
import java.sql.*;
import java.io.*;


public class Insert{
public String putsData(String szApplData)throws SQLException{
{
System.out.println("Entering the function inside java");
Connection conn = DriverManager.getConnection("jdbc:default:connecti on:");

String szQry="INSERT INTO TESTUSER.TRN_APPL_REQUESTS@MAINLINK(ID_REQUEST, ID_RESULT, ST_REQUEST, DT_INSERT,ID_APPLICATION,ID_FE,FLAG_GET,ID_FUNCTIO N,NO_SEMCALL,SEQ_REQUEST) VALUES ("+"'"+szApplData.substring(0,1)+"','"+szApplData. substring(1,3)+"','000',SYSDATE,'"+(szApplData.sub string(30,46)).trim()+"','"+(szApplData.substring( 46,50)).trim()+"',"+"'"+ (szApplData.substring(53,54)).trim() +"'"+",'"+ (Double.valueOf(szApplData.substring(54,57))).doub leValue() +"'"+",'"+ (Double.valueOf(szApplData.substring(57,59))).doub leValue() +"'"+",SEQ_REQUEST.CURRVAL)";
try {
PreparedStatement pstmt = conn.prepareStatement(szQry);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {System.err.println(e.getMessage());}

return szQry;

}
}
public String overrideInsert(String szApplData) {

String szOverride ="INSERT INTO TRN_OVERRIDE (SEQ_REQUEST, ID_FE,DT_OVERRIDE,USER_OVERRIDE,DESC_REASON,FLAG_A CCEPTANCE,DT_ACCEPTANCE,SEQ_OVERRIDE ) VALUES ('"+szApplData.substring(4,14)+"','"+ szApplData.substring(63,67)+"',TO_DATE('"+szApplDa ta.substring(67,75)+"','DD/MM/YYYY'),'"+ szApplData.substring(75,125)+"','"+ szApplData.substring(125,225)+"','"+ szApplData.substring(225,226)+"',TO_DATE('"+szAppl Data.substring(226,234)+"','DD/MM/YYYY'),SEQ_OVERRIDE.NEXTVAL)";

return szOverride ;
}
}


ORACLE PROCDURE WHICH CALLS THE JAVA:
create or replace procedure TESTINSERT1(szApplData in varchar2) as language java
name 'Insert.putsData(String)';

DATABASE TRIGGER:

1 create or replace trigger TRIG_REMOTE
2 AFTER INSERT ON TRN_APPL_REQUESTS
3 FOR EACH ROW
4 declare
5 szApplData varchar2(5000);
6 Result varchar2(5000);
7 my_sqlerrm VARCHAR2(150);
8 PRAGMA AUTONOMOUS_TRANSACTION;
9 Begin
10 dbms_output.put_line('Inside Begin');
11 szApplData = '123456789012345678901234567890abcdefghijklmn10FE1 F111111221234567891234567891234567891234';
12 dbms_output.put_line('After Assigning SZApplData');
13 call TESTINSERT1(szApplData);
14 dbms_output.put_line('After Select Statement :'||result);
15 commit;
16 exception when others then
17 my_sqlerrm := SUBSTR(SQLERRM,1,150);
18 dbms_output.put_line('Oracle Error Message :'||my_sqlerrm);
19* End;

ERROR ENCOUNTERED:
Errors for TRIGGER TRIG_REMOTE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/13 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.

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

I have never used the java or anything like you are, but I found I had headache after headache from procedures getting called by triggers etc. Turned out that there were two ways of fixing the problem:
a). remove the pragam autonomous_transaction, or,
b). do some in-depth checking into the interrelationships around your code, so that pragma autonomous_transaction will work.

I'm not saying that that is the problem, but I couldn't solve mine either, until I fixed that aspect of it.

Then, all was well in my world.

uhh, it looks like you just forgot the : in the assignment

szApplData = '123456789012345678901234567890abcdefghijklmn10FE1 '

try

szApplData := '123456789012345678901234567890abcdefghijklmn10FE'

Thanks for the feedback and inputs.

The reason for not working is because of the the way procedure was called.


OLD CODE:
create or replace procedure TESTINSERT1(szApplData in varchar2) as language java name 'Insert.putsData(String)';


CORRECTED CODE:
create or replace procedure TESTINSERT1(szApplData in varchar2) as language java name 'Insert.putsData(Java.lang.String)';





Thanks for all....