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

    Unanswered: A Database Trigger Calling an Oracle Procedure which in turn calls a Java function

    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
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    it must be
    szApplData := '123456789012345678901234567890abcdefghijklmn10FE1

    in line 11

    Hope this helps.

    Greetings

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

  3. #3
    Join Date
    Feb 2002
    Posts
    17
    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.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

  5. #5
    Join Date
    Feb 2002
    Posts
    17
    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.

  6. #6
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    You are welcome

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

Posting Permissions

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