| URGENT: 'FRM-40505: unable to perform query error [message #85200] |
Wed, 09 June 2004 02:00  |
amit sharma
Messages: 9
Registered: October 2002
|
Junior Member |
|
|
Hi,
I am using Oracle forms 6i (But it is not deployed on web)
I am having a database block which has database items and also certain non-database items.
The block name is -BLK_CLA_COLUMBO_WORKBUFFER
I am populating that block dynamically by setting its WHERE clause at the Block level by using following code in PRE-QUERY trigger of the block BLK_CLA_COLUMBO_WORKBUFFER -
-----------------------
DECLARE
l_query varchar2(200);
BEGIN
l_query := 'ctry_cd = '||''''||RTrim(form_global.ctry_cd)||'''';
l_query := l_query||' AND part_nr = '||''''||rtrim(form_global.part_nr)||'''';
set_block_property('BLK_CLA_COLUMBO_WORKBUFFER', DEFAULT_WHERE, l_query);
EXCEPTION
WHEN OTHERS THEN
show_error('Cannot query the Columbo WB:- PROC_GET_COLUMBO_WB');
END;
------------------------
The PROBLEM i am facing is - sometimes we get a RANDOM error 'FRM-40505- Unable to perform query' while querying this block The code that gets fired for querying data from this database block (i.e querying the source Table)
------------------
go_block('BLK_CLA_COLUMBO_WORKBUFFER');
execute_query;
-----------------
I looked for the description in form builder help, it says -
The 'FRM-40505: ORACLE error: unable to perform query' error has following description -
"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."
But if I query for the SAME DATA AGAIN in the same session on this block then same code is fired again i.e
------------------
go_block('BLK_CLA_COLUMBO_WORKBUFFER');
execute_query;
-----------------
But this TIME it QUERIES the table successfully and populates the data block properly.
There is no access priveleges issue with this table.
Kindly let me know why this is happening ???
Is it a known problem in Oracle forms 6i which can be solved by use of any patch ?
Kindly respond at the earliest.
Thanks,
Amit Sharma
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #85205 is a reply to message #85200] |
Wed, 09 June 2004 03:56  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
From where your variable form_global.part_nr & form_global.ctry_cd.
It seems that at first execution they are null.
So the trin formed will be as follows:
'Where ctry_cd= AND part_nr = '
which is an erroneous string.
You can simply press Shift+F1 to see what is the Query your Form builder has fired.
HTH
Regards
Himanshu
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #85206 is a reply to message #85205] |
Wed, 09 June 2004 04:53 |
amit sharma
Messages: 9
Registered: October 2002
|
Junior Member |
|
|
Hi Himanshu,
Thanks for replying !!
The variable form_global.part_nr & form_global.ctry_cd are never Null because i assign them before firing query over this erroroneous block and i debugged the query, it always hold the string with ctry_cd and part_nr so the string 'Where ctry_cd= AND part_nr = ' is always proper.
what i saw it RANDOMLY fails (rarely) before this code
----------------------
set_block_property('BLK_CLA_COLUMBO_WORKBUFFER', DEFAULT_WHERE, l_query);
----------------------
It seems sometime the set_block_property is not able to find the database table assigned to this block and so raises the error 'FRM-40505: ORACLE error: unable to perform query'
This error has following description -
"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."
My question is why it says that 'The table associated with the current block of the form might not exist' because when i query that part next time IT FIRES the query sucessfully for same part_nr and ctry_cd.
{{IRRESPECTIVE OF WHETHER IT IS IN SAME SESSION (OR) IN DIFFERENT SESSION }}
This is quite strange and I am unable to understand WHY this happens rarely because if something is wrong with coding then it should always happen for that part_nr and ctry_cd ???
Kindly advise.
Amit
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #85218 is a reply to message #85206] |
Wed, 09 June 2004 22:56 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi Amit,
This is a Generic message followed by other Error Messages in the Stack.
As I suggested to you as sson as you get this error Press Shift+F1.
This will open a window with the query which form tried to fire.
Also below it you will find another error message like "Invalid column" or "nvalid No" etc.
Copy the query from the Pop-up window and execute it on SQL prompt.
You will find out the cause of your problem.
If you are still unable to find out then post your Form & Table structure at himan_bharadwaj@hotmail.com for investigation.
HTH
Regards
Himanshu
|
|
|
|
|
|
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #158170 is a reply to message #85200] |
Wed, 08 February 2006 16:19 |
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
|
Senior Member |
|
|
Amit,
You put in your set_block_property(...) as where clause the variable l_query.
As Himanshu already suggested, it could be that in some cases it would be something like '... = '; (A NULL value after one of the = signs)
I would strongly suggest that you change your trigger as follows:
Declare
...
Begin
...
...
LOG( l_query );
Set_property....
...
EXCEPTION
when others then
LOG( l_query );
...
END;
LOG(...) is of course your own standard logging procedure, as I assume you have one.
The point is, that at this moment you have too little information to get the real error cause. You're making the assumption that both form_global.ctry_cd and form_global.part_nr are never NULL.
(And never in 'programming-time' sometimes is remarkebly short...)
The saying 'it works when I put it in sql*plus' doesn't prove a thing to me, unless you can prove that what you put in slq*plus is EXACTLY what Forms is trying to process. And I might be a very old and mistrusting programmer, but as long as I do not see the l_query in a logfile, I do not believe in the l_query 'you are sure of what it must be'.
The only proof is seeing the values for exactly what they are, so logging and more logging...
Please let us know what your solution will be.
Regards,
Rob Zijlstra
|
|
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #218631 is a reply to message #158193] |
Fri, 09 February 2007 01:23 |
sanjay_oracle@hotmail.com
Messages: 2
Registered: February 2007
|
Junior Member |
|
|
Hi,
I am facing the same error i.e. 'FRM-40505: unable to perform query error'.
After going thru the entire discussion now I can see the exact error message after pressing Shift+F1. It shows
"ORA-00904: invalid column name" and that is correct.
But, I am not sure where to effect that change (rectify column name), which is not matching with one of my database table column names.
My schema name is G_HRS, Table Name - HRS_MSTEMPLOYEE. I am using Forms 6i.
If you need any further info to solve this problem, plz revert.
Thanks in Advance for any helpful suggestions.
Sanjay
|
|
|
|
| Re: URGENT: 'FRM-40505: unable to perform query error [message #218643 is a reply to message #218631] |
Fri, 09 February 2007 02:19 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
|
If you choose Help - Show Errors (or whichever its name it is), it should open a window. In its bottom part you should be able to scroll through the whole statement that raised an error and compare columns with actual columns in your table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
