Home » Developer & Programmer » Forms » URGENT: 'FRM-40505: unable to perform query error
URGENT: 'FRM-40505: unable to perform query error [message #85200] Wed, 09 June 2004 02:00 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #158099 is a reply to message #85218] Wed, 08 February 2006 08:38 Go to previous messageGo to next message
Praseeja
Messages: 12
Registered: February 2006
Junior Member
Hi,

when i clicked the Shift+F1, it displayed the query, but when i exe the same query in sql prompt it is working fine.
Re: URGENT: 'FRM-40505: unable to perform query error [message #158101 is a reply to message #158099] Wed, 08 February 2006 08:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And what ORA error message did it display?

MHE
Re: URGENT: 'FRM-40505: unable to perform query error [message #158139 is a reply to message #85206] Wed, 08 February 2006 11:24 Go to previous messageGo to next message
karan.shandilya
Messages: 64
Registered: July 2005
Location: mumbai
Member
Hi,
Post your Form and Table structure.

Karan.
Re: URGENT: 'FRM-40505: unable to perform query error [message #158170 is a reply to message #85200] Wed, 08 February 2006 16:19 Go to previous messageGo to next message
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 #158193 is a reply to message #85200] Wed, 08 February 2006 22:34 Go to previous messageGo to next message
M0nst3r
Messages: 38
Registered: February 2006
Location: Wherever the Money Is
Member
I can't remember if Oracle forms prevents SQL injection. Embarassed

For those of you who don't know, SQL injection is the manipulation of field data to trick the application into executing an extra statement.

Review all of the possible values that can be assigned to form_global.ctry_cd and form_global.part_nr. Maybe one of the values passed to your parameters has a single apostrophe embedded in it.
Re: URGENT: 'FRM-40505: unable to perform query error [message #218631 is a reply to message #158193] Fri, 09 February 2007 01:23 Go to previous messageGo to next message
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 Go to previous message
Littlefoot
Messages: 20900
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.
Previous Topic: pl/sql looping
Next Topic: About Image
Goto Forum:
  


Current Time: Thu Dec 08 02:11:20 CST 2016

Total time taken to generate the page: 0.13371 seconds