sethu
02-15-02, 01:12
| 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. |