14 Replies Latest reply: Jul 15, 2009 8:40 AM by 49905 RSS



      How to find the corresponding ORA message for FRM-40505 in forms? I guess when i receive FRM-40505, then how to see the ORA message to see the issue. I'm using refcursor to populate the multiblock and some time
      EXECUTE_QUERY fails and i get FRM-40505. I wanted see ORA message to see why it fails.

        • 1. Re: FRM-40505
          • 2. Re: FRM-40505
            it didn't work for me. i tried shift+F1 but no luck.
            • 3. Re: FRM-40505
              FRM-40505: ORACLE error: unable to perform query.

              Cause:     Processing error encountered. The table associated with the current block of the form might not exist, or your username might not have authority to perform the specified action on the table.

              Action:     Pressing [Display Error] provides more information, if it is available. You can also try to update or delete this record later. If necessary, contact your DBA.

              Level:     >25

              Type:     Error

              Please mark as sloved if it helps
              • 4. Re: FRM-40505
                If your query in a different block. try this
                Put GO_BLOCK , before EXECUTE_QUERY
                or put the query button to block you are quring.

                • 5. Re: FRM-40505
                  You didn't mention your version. Try Shift+Ctrl+E
                  • 6. Re: FRM-40505
                    Hi Slava, Sorry about that. We're on 10g (web forms). I tried Shift+Ctrl+E after FRM-40505 and it says FRM-42100.

                    KK , thx. i know FRM-40505: ORACLE rror: unable to perform query. The table does exists and my username have authority to perform the action on the table. It works 98% of the time but some time EXECUTE_QUERY fails and i get 40505. So i'm trying to get the corresponding ORA message so i should know the real cause of failing EXECUTE_QUERY.

                    Manoj. - I've a GO_BLOCK before EXECUTE_QUERY

                    Thank you all
                    • 7. Re: FRM-40505
                      check the datatype of columns in the table and items in the form is matching.

                      sometimes the dataype of the data stored in the table is not matching with the datatype of the items in the table, this can happen.

                      mark if it helps
                      • 8. Re: FRM-40505
                        Try to trap this in on-error trigger:
                        if error_code=40505 then     
                             message(dbms_error_code ||' ' ||dbms_error_text);
                             message(error_code ||' ' ||error_text);
                             raise form_trigger_failure;
                             message(error_code ||' ' ||error_text);
                             raise form_trigger_failure;
                        end if;     
                        The fact that execute_query fails just sometime - points that the error may be data related.
                        For example your forms item datatype is number, but corresponding table column is varchar that contains numeric data in 98%
                        This will raise "Invalid Number" only in when non-numeric data queried.
                        As KK suggested make sure datatype of your items and corresponding table columns are matching
                        • 9. Re: FRM-40505
                          Thanks Slava and KK:

                          I have made sure that items datatype and corresponding table columns are matching.

                          I tried your code in on-error trigger and i get this: FRM-40733: PL/SQL builtin DBMS_ERROR_CODE failed and

                          FRM-40733: PL/SQL builtin DBMS_ERROR_TEXT failed.
                          • 10. Re: FRM-40505
                            You mentioned you are using ref cursor to populate block.
                            Can you share the code in the query procedure that block based on?
                            Is it local forms procedure or DB?
                            Can you run this form in debug mode?

                            Sound like after your query fails - form diisconnects from DB.
                            You can try sql*net trace for more clues. Modify sqlnet.ora add

                            May be a good idea also to check Database alert.log for ORA-600 errors.
                            • 11. Re: FRM-40505
                              Hi Slava,

                              Yeap i'm using back end procedure (spkg_tst.sp_grid_details) to populate the grid. It has a very simple select statments etc.

                              where is the database alert.log located?

                              • 12. Re: FRM-40505
                                Malik has been emailing me on this problem as well.

                                I too suspect there may be an Oracle disconnect somewhere, although I cannot imagine why the login-retry ( as stated in his multi block ) would trigger the problem.

                                I have given him some pointers, such as making sure :System.Message_level is always set to zero, and find and remove any "Exception when others then null;" if coded anywhere.

                                Malik, how are you switching from form to form and back? Maybe that causes the issue.

                                When your form is supposed to query the block, maybe insert some code to do a pl/sql SELECT Count(*) prior to the query and report back via a message, the row count it should find.

                                And lastly, Malik, I'd prefer you posted here rather than use email, because there are many more eyes to look at your problem. And there is no way I could help with a phone call. Words flow, and are hardly can be absorbed. When you write them, we can refer back, and maybe come up with new ideas. With a phone conversation, it is all lost.
                                • 13. Re: FRM-40505
                                  I got your answer that there is no problems with :System.message_level or Exceptions.

                                  Now... in your original post, you wrote:
                                  -> and then go back to the 2nd form then multi block doesn't show the data at all

                                  In the second form, your when-new-form-instance must do a go_block and Execute_Query to that Multi-row block.

                                  So just before that, insert some debugging lines. Try the Select Count(*) and display the result at that point. Is there any chance the block's default where clause has been changed? IF so, try displaying that. If the block is based on a stored procedure, then use pl/sql to make calls to it and see what it returns.

                                  It would be helpful if you downloaded my ZDebug form, which writes debugging messages you create into a record group, so you can then view the results of your debugging process whenever you want. You can download ZDebug here:

                                    ZDebug - Forms Debugging Message Tool  

                                  I would sure be using it if I were in your shoes.

                                  Edit: And maybe even before you add debugging code, try cleaning up the fmb code in your forms. Use the compile All function: Ctrl-Shift-K

                                  Or better yet, do the tried and true semicolon-semicolon replace all:

                                      Re: FMB size shrinks dramatically
                                      Re: Why does this happen - find ';', replace with ';'?

                                  Edited by: Steve Cosner on Jul 15, 2009 9:22 AM
                                  more suggestions
                                  • 14. Re: FRM-40505
                                    Steve, it is obvious, you know much more details about this issue than I do, but after reading the original post about custom logon and navigation between forms - I would say that form disconnected even before query is executed. I suspect any "select count(*)" in WNFI will fail too.
                                    Malik, what do you use to navigate between forms? CALL_FORM, NEW_FORM, OPEN_FORM (session/no_session) and how do you exit from forms? Just exit_form or may be you use some call/new/open again?

                                    P.S. alert.log location depend on DB version and OS. But you can: select value from v$parameter where name = 'background_dump_dest'