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

7.0 vs 2000?

I hope I can explain this correctly...

I have a developer that runs a batch file to delete the current parts from the database. The batch file calls a prorietary Java class file (Trilogy) to delete the parts and it simply returns a "null". I don't know whats making it return null.

Note: We have the same DB on a 7.0 Instance and a 2000 Instance. The batch file works against the 7.0 Instance and not against the 2000 Instance.

I'm wondering if possibly it's a Server setting? I'm not sure where to begin with this one.The first thing I'd check would be the logs. Any output (logfile, console, or other) from the Java app would be first, then the SQL Server log file, the NT logs, and anything else that seems appropriate.

The next thing I'd do would be to sic the SQL Profiler on the little beastie, and watch what it is doing. The exact steps will vary a lot depending on your configuration, but the process isn't too complex.

If none of those give you any clues, then you have a real puzzle! Those can still be solved, but they are a lot harder.

-PatP