Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18

    Unanswered: How do I capture FRM-40505 when executing query?

    Am working in Designer 6i Forms. After entering a Query, then pressing
    F8 to execute, I get the FRM-40505: ORACLE error: Unable to perform
    query. I have tried coding the Application Logic Events for ON-ERROR
    and ON-MESSAGE at the form level and module component level to capture this. But, have had no luck. Can you help me capture this
    error/message/alert so that the user will not see it? Thanks, Maggie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Does the query succeed when run from SQL*Plus as the same user who is running the form?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How do I capture FRM-40505 when executing query?

    Your ON-ERROR trigger should look something like this:
    PHP Code:
    DECLARE
      
    v_error NUMBER      := ERROR_CODE;
      
    v_type VARCHAR2(3)  := ERROR_TYPE;
      
    v_text VARCHAR2(80) := ERROR_TEXT;
    BEGIN
      
    IF v_type 'FRM' AND v_error 40505 THEN
        message
    ('never mind');
      ELSE
        
    Message(v_type||'-'||to_char(v_error)||': '||v_text);
        
    RAISE Form_Trigger_Failure;
      
    END IF;
    END
    However, bear in mind that this error should not occur as it indicates a bug in your form. You should prevent it from happening in the first place rather than try to hide the fact that it has happened!

  4. #4
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18
    It happens when the Parent table is queried in the beginning of the form. I do not know what could be wrong in the parent/child relationship between the tables or blocks. Everything looks okay. After finding the existing record I want in the parent table (first block) I go to the child table (second block) and then select a child that I want to be with that parent. So I'm not asking it to query the child when I query the parent. But I feel that it is trying to do that when I receive this message in the first block.

    I can go into the Database and see that the correct relationship exists.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What do you see when you select Help - Display Error from the form menu just after you get the FRM-40505?

  6. #6
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18
    This is what I get when I select Help Display Error right after the message in the form.

    The SQL Statement in Error is:

    SELECT ROWID,
    PIG_BASIS_ACCOUNTS.FY,
    PIG_BASIS_ACCOUNTS.ACCOUNT,
    PIG_BASIS_ACCOUNTS.ID,
    PIG_BASIS_ACCOUNTS.PIG_ID,
    PIG_BASIS_ACCOUNTS.CREATE_DATE,
    PIG_BASIS_ACCOUNTS.CREATED_BY
    FROM PIG_BASIS_ACCOUNTS PIG_BASIS_ACCOUNTS
    WHERE ( SELECT PIG_BASIS_ACCOUNTS.ACCOUNT
    FROM BASIS_ACCOUNTS_VW, PIG_BASIS_ACCOUNTS
    WHERE BASIS_ACCOUNTS_VW.FY = PIG_BASIS_ACCOUNTS.FY ) and (PIG_BASIS_ACCOUNTS.PIG_ID=:1)

    and then below this is the Error:

    ORA-00936: missing expression

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well that SQL is certainly invalid:

    SELECT ROWID,
    PIG_BASIS_ACCOUNTS.FY,
    PIG_BASIS_ACCOUNTS.ACCOUNT,
    PIG_BASIS_ACCOUNTS.ID,
    PIG_BASIS_ACCOUNTS.PIG_ID,
    PIG_BASIS_ACCOUNTS.CREATE_DATE,
    PIG_BASIS_ACCOUNTS.CREATED_BY
    FROM PIG_BASIS_ACCOUNTS PIG_BASIS_ACCOUNTS
    WHERE ( SELECT PIG_BASIS_ACCOUNTS.ACCOUNT
    FROM BASIS_ACCOUNTS_VW, PIG_BASIS_ACCOUNTS
    WHERE BASIS_ACCOUNTS_VW.FY = PIG_BASIS_ACCOUNTS.FY )

    and (PIG_BASIS_ACCOUNTS.PIG_ID=:1)

    It might be OK if the subquery was being compared to something, e.g.

    SELECT ROWID,
    PIG_BASIS_ACCOUNTS.FY,
    PIG_BASIS_ACCOUNTS.ACCOUNT,
    PIG_BASIS_ACCOUNTS.ID,
    PIG_BASIS_ACCOUNTS.PIG_ID,
    PIG_BASIS_ACCOUNTS.CREATE_DATE,
    PIG_BASIS_ACCOUNTS.CREATED_BY
    FROM PIG_BASIS_ACCOUNTS PIG_BASIS_ACCOUNTS
    WHERE ( SELECT PIG_BASIS_ACCOUNTS.ACCOUNT
    FROM BASIS_ACCOUNTS_VW, PIG_BASIS_ACCOUNTS
    WHERE BASIS_ACCOUNTS_VW.FY = PIG_BASIS_ACCOUNTS.FY ) > 100
    and (PIG_BASIS_ACCOUNTS.PIG_ID=:1)

  8. #8
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18
    Okay, that SQL is being generated from the system based on, I guess, form design. So I need to take this information to a team member and see what we can do.

    I'm not sure what we will do, but will hash it out and get back with actions taken.

    Thanks much for your help.
    Maggie

  9. #9
    Join Date
    Oct 2003
    Location
    Missouri, USA
    Posts
    18

    Talking

    Well, I said I'd let you know the outcome. Happily the FRM-40505 is gone! The problem was with the code you, Tony, identified. The validation that was going on in the WHERE clause was also being done in two other places in the form. One was a validation at table level and the other... Well anyhow, we got rid of the other validations and now we're running smoothly.

    Thanks much for helping me get this straightened out.
    Maggie

Posting Permissions

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