This content has been marked as final. Show 14 replies
it didn't work for me. i tried shift+F1 but no luck.
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.
Please mark as sloved if it helps
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.
You didn't mention your version. Try Shift+Ctrl+E
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
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
Try to trap this in on-error trigger:
The fact that execute_query fails just sometime - points that the error may be data related.
if error_code=40505 then display_error; message(dbms_error_code ||' ' ||dbms_error_text); message(error_code ||' ' ||error_text); raise form_trigger_failure; else message(error_code ||' ' ||error_text); raise form_trigger_failure; end if;
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
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.
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.
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?
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.
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
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'