Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    17

    Unanswered: 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: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.

  2. #2
    Join Date
    Dec 2001
    Posts
    4
    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.

  3. #3
    Join Date
    Jun 2001
    Posts
    2
    uhh, it looks like you just forgot the : in the assignment

    szApplData = '123456789012345678901234567890abcdefghijklmn10FE1 '

    try

    szApplData := '123456789012345678901234567890abcdefghijklmn10FE'

  4. #4
    Join Date
    Feb 2002
    Posts
    17

    IT IS WORKING FINE NOW

    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •