Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Posts
    40

    Unanswered: pl/sql problem!

    I have a trigger that will not compile because of line 18. I am getting the following error:
    Last edited by israel05; 03-20-07 at 00:36.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This cursor is useless. All it does is SELECT COUNT(*). So, use it in trigger body.

    Although it doesn't matter, I can't understand why people bother writing REFERENCING clause ... I'd accept if someone references old and new values differently, but referencing old as old really gives no information.

  3. #3
    Join Date
    Aug 2005
    Posts
    40
    Thank you for your response. The developer eliminated the cursor but we are still getting the same error.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, I thought so ... Oracle error message isn't very helpful either:
    Quote Originally Posted by Oracle
    PLS-00801: internal error [string]

    Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.

    Action: Report this error as a bug to your Customer Support representative.
    I've got an idea: instead of running the procedure over a database link, try to create a synonym to it in your schema and call it simply as
    Code:
    ISSUE_SIGNS (:NEW.LOC_ISSUE_LOC, :NEW.DEPT_DEPT_CODE, 
                 :NEW.PART_PART_NO,  :NEW.COMMENT_AREA,
                 :NEW.QTY_ISSUED,    :NEW.ISSUE_DATE,
                 :NEW.UNIQUE_ID
                );

  5. #5
    Join Date
    Aug 2005
    Posts
    40
    Littlefoot,

    Excuse my ignorance but I am not getting an error on the db_link. Is there something I am missing?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by israel05
    Thank you for your response. The developer eliminated the cursor but we are still getting the same error.
    Since the line that generated the error is missing, please post the new line that you are failing on. Below is what the script should look like. Please post the EXACT error that is being generated.
    Code:
    CREATE OR REPLACE TRIGGER GUSER.SEND_ISSUE_TO_HMMS
    BEFORE INSERT
    ON EMSDBA.PTD_MAIN
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    DECLARE
    CNT_SGN NUMBER;
    BEGIN
    
    IF :NEW.LOC_ISSUE_LOC LIKE '%WG%' AND :NEW.COMMENT_AREA IS NOT NULL THEN
      SELECT COUNT(*) 
      into cnt_sgn
      FROM EMSDBA.PTS_MAIN WHERE DESCRIPTION_KEYWORD IN
      ('DEST/GA SIGNS', 'GA SIGNS', 'GUIDE SIGNS','INFO SIGNS','REG SIGNS', 'SCHOOL SIGNS',
      'SR SIGNS','US ROUTE SIGNS','WARNING SIGNS','DEST SIGNS','RED SIGNS', 'SPECIAL') AND
      PART_PART_NO=:NEW.PART_PART_NO;
    
      IF CNT_SGN>0 THEN
        FLEET_ANYWHERE_INTERFACE.ISSUE_SIGNS@HMMS_LINK
        (:NEW.LOC_ISSUE_LOC,
         :NEW.DEPT_DEPT_CODE,
         :NEW.PART_PART_NO,
         :NEW.COMMENT_AREA,
         :NEW.QTY_ISSUED,
         :NEW.ISSUE_DATE,
         :NEW.UNIQUE_ID);
    
      END IF;
     END IF;
    end;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle suggests to call Oracle Support. In the meantime (while you are waiting for their response), I'm telling you what I suspect might help. DB link is probably OK, but ... perhaps, if you rewrite the code in order not to use it, it suddenly *might* start working. It probably won't, but - you won't know it unless you try it.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I also suspect that trying to use EXECUTE in a sql block is not valid either. You can do "EXECUTE IMMEDIATE", but plain EXECUTE is not legal (AFAIK).
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2005
    Posts
    40
    The developer basically just commented out the cursor but it still errored on the first IF statement:

    IF :NEW.LOC_ISSUE_LOC LIKE '%WG%' AND :NEW.COMMENT_AREA IS NOT NULL THEN
    -- OPEN check_for_sign_part;
    -- FETCH check_for_sign_part INTO CNT_SGN;
    --CLOSE check_for_sign_part;

    IF CNT_SGN>0 THEN
    EXECUTE (ISSUE_SIGNS@HMMS_LINK)
    (:NEW.LOC_ISSUE_LOC,
    :NEW.DEPT_DEPT_CODE,
    :NEW.PART_PART_NO,
    :NEW.COMMENT_AREA,
    :NEW.QTY_ISSUED,
    :NEW.ISSUE_DATE,
    :NEW.UNIQUE_ID);

    END IF;

    END IF;
    end;
    /

    I advised that the code did not make sense to me as a DBA. She said it had worked in the past.

    I did put a call into Oracle support and they just advised to upgrade to the latest patchset.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > She said it had worked in the past.
    Then, what changed?

    I agree with Bill. I think the EXECUTE looks wrong.
    This is NOT how to invoke a PL/SQL procedure from inside PL/SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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