A Database Trigger Calling an Oracle Procedure which in turn calls a Java functi

A Database Trigger Calling an Oracle Procedure which in turn calls a Java function
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:connection:");

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.substring(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('"+szApplData.substring(67,75)+"','DD/MM/YYYY'),'"+ szApplData.substring(75,125)+"','"+ szApplData.substring(125,225)+"','"+ szApplData.substring(225,226)+"',TO_DATE('"+szApplData.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.Hello,

it must be
szApplData := '123456789012345678901234567890abcdefghijklmn10FE1

in line 11

Hope this helps.

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hello,

it must be
szApplData := '123456789012345678901234567890abcdefghijklmn10FE1

in line 11

Hope this helps.

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Hello ,

Thanks for the Reply..It was rectified..and Trigger created successfully.
when i try to insert a record for testing purpose..I am getting as below
iN THE DB TRIGGER I have given messages and when executing the procedure it is failing ...need help Pls ...Thanks

SQL> @.insert
Inside Begin
After Assigning SZApplData
Oracle Error Message :ORA-29531: no method putsData in class Insert

1 row created.
:)|||Hello,

could it be, that the problem is the prototype
public String putsData(String szApplData)throws SQLException{

You define a return value, but you use a procedure for the java function, which can not send back variables

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

In my opionion, Oracle is looking for a method

void Insert.putsData(String)

Is that your problem ?

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||It is working fine now ...I have not changed anything in database Trigger but changed the procedure and java function as below.

Two reasons for not working:
(1)The way we call the java function thro oracle procedure.
(2)In the Java parameter declaration change from vector to String

The Corrected Procedure Calling Java:

OLD:

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

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


Thanks for the Quick Responses from MANFRED PETER and BRICKLEN thro db forum for looking it in other angle in finding the solution.|||You are welcome

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com