Home » Developer & Programmer » Forms » execute_query failure
execute_query failure [message #124957] Wed, 22 June 2005 09:07 Go to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Hello,

I am creating 2 'events'. The key here is that I am querying data from a table using a default_where clause in the pre-query trigger on the block.
For the first event, 9 rows are returned (as required) and the event can be processed. However, 0 rows are returned when processing the second event. I have trapped the value of the default_where clause and they are identical.
For further proof, I debugged the post-query trigger. A key variable was set when creating event 1, but is still null for event 2.

Therefore, I can only presume the execute_query procedure (itself) has failed. Has anyone seen this happen before? Can anything be done about it?

Note: If I exit the application and then re-enter to create the second event I have no problems.

Thankyou for your time.
Regards

Lee Sutton

Re: execute_query failure [message #124974 is a reply to message #124957] Wed, 22 June 2005 10:31 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Correction. execute_query does not fire. Having messaged the post-query I can confirm that this is not called when trying to create Event 2.
Any help as to why execute-query would not fire would be much appreciated.

Many thanks.

Lee Sutton
Re: execute_query failure [message #124994 is a reply to message #124957] Wed, 22 June 2005 12:04 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Please also note that record_status is 'NEW' and system_mode is 'Enter-Query' for both events.

Many thanks

Lee Sutton
Re: execute_query failure [message #125045 is a reply to message #124994] Wed, 22 June 2005 21:29 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Okay, you have two blocks - a control block and a data block.

You enter data into the control block fields and press a button which contains:
go_block('data');
execute_query;


You have a pre-query on the data block which populates a variable that you apply to the data block using a default_where command.

Is this close?

David
Re: execute_query failure [message #125099 is a reply to message #124957] Thu, 23 June 2005 05:26 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Yes djmartin - you are close.

By performing a when-mouse-doubleclick I am navigating to a package HCI_FRM.wizard_options. Within this we do a
GO_BLOCK('V_CV_EVENT_TYPES1');
DO_KEY('EXECUTE_QUERY');

The pre-query trigger is as follows:

:SYSTEM.MESSAGE_LEVEL := 15;

IF :B1.FIND IS NOT NULL THEN -- IGNORE THIS - SEE ELSE --
SET_BLOCK_PROPERTY('V_CV_EVENT_TYPES1',DEFAULT_WHERE,
'V_CV_EVENT_TYPES.FUNCTION = ''SUM07304''' ||
' AND V_CV_EVENT_TYPES.SOCIETY = ' || :B0.SOC_SEQNO ||
' AND V_CV_EVENT_TYPES.CUSTOMER_CONTACT = ''Y''' ||
' AND V_CV_EVENT_TYPES.EVENT_GROUP = ''' || :tk_ctrl.event_type || '''' ||
' AND V_CV_EVENT_TYPES.EFFECTIVE_FROM < ''' || :B1.WCAL_DATE || '''' ||
' AND ( V_CV_EVENT_TYPES.EFFECTIVE_TO > ''' || :B1.WCAL_DATE || '''' ||
' OR V_CV_EVENT_TYPES.EFFECTIVE_TO IS NULL )' ||
' AND V_CV_EVENT_TYPES.SUB_FUNCTION_1 = ''' || :V_DIS_CV_EVENT_TYPES.SUB_FUNCTION_1 || '''' ||
' AND V_CV_EVENT_TYPES.EVENT_CODE LIKE ''' || :B1.FIND || '%''');
ELSE -- NOTE: THIS IS THE ONE WE USE --
SET_BLOCK_PROPERTY('V_CV_EVENT_TYPES1',DEFAULT_WHERE,
'V_CV_EVENT_TYPES.FUNCTION = ''SUM07304''' ||
' AND V_CV_EVENT_TYPES.SOCIETY = ' || :B0.SOC_SEQNO ||
' AND V_CV_EVENT_TYPES.CUSTOMER_CONTACT = ''Y''' ||
' AND V_CV_EVENT_TYPES.EVENT_GROUP = ''' || :tk_ctrl.event_type || '''' ||
' AND V_CV_EVENT_TYPES.EFFECTIVE_FROM < ''' || :B1.WCAL_DATE || '''' ||
' AND ( V_CV_EVENT_TYPES.EFFECTIVE_TO > ''' || :B1.WCAL_DATE || '''' ||
' OR V_CV_EVENT_TYPES.EFFECTIVE_TO IS NULL )' ||
' AND V_CV_EVENT_TYPES.SUB_FUNCTION_1 = ''' || :V_DIS_CV_EVENT_TYPES.SUB_FUNCTION_1 || '''');
END IF;

The forms variables are identical for both Event 1 and 2 - therefore the default_where clause is identical.
Unfortunately, execute-query does not fire second time round.

Thanks for your input so far.

Kind regards

Lee
Re: execute_query failure [message #125100 is a reply to message #124957] Thu, 23 June 2005 05:26 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
Ah. just noticed your name is David......
Thanks.
Re: execute_query failure [message #125133 is a reply to message #124957] Thu, 23 June 2005 07:36 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
By adding a post-select I could determine that Forms Builder has constructed and issued the block SELECT statement (using default_where) , but it has not fetched the records.

Lee
Re: execute_query failure [message #125226 is a reply to message #125133] Thu, 23 June 2005 20:20 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
You don't need quotes around your date fields unless they are in character format and then I would recommend that you MANUALLY convert them to DATE type with a TO_DATE and NOT let the system do it for you automatically as you MAY have a difference in NSL_DATE_FORMAT, etc, between your AS and DB, especially between development, test, and production.

Upd: I assume that EFFECTIVE_FROM and EFFECTIVE_TO are of type 'DATE' ... yes?

David

Update: You are using double-click to get out of the field, maybe there is validation pending ... try putting an 'ENTER' command at the beginning of your double-click procedure.

PS For readability I would normally put the two where clauses as local variables. This makes the 'if' test a bit easier to read.

[Updated on: Fri, 24 June 2005 00:38]

Report message to a moderator

Re: execute_query failure [message #125242 is a reply to message #125045] Fri, 24 June 2005 00:11 Go to previous messageGo to next message
yarlagadda_01
Messages: 13
Registered: June 2005
Location: india
Junior Member
can u give me the answer for your question clearly.
i.e., what will happen if we write code under a button in the controlblock like go_block('data');execute_query; will it execute query in the data block.
Re: execute_query failure [message #125243 is a reply to message #125242] Fri, 24 June 2005 00:26 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Short answer: yes

Long answer: Let's say you have one block in your form and it is a 'data' block.

You write code, for example, in the When-New-Form-Instance trigger, you do an 'enter-query' command, then put a value in some of the fields, and then do the 'execute-query'. Assuming that there is nothing in the 'Default Where' of the block, you will retrieve those records selected by the data in the field as per Query-by-example. This will happen every time you run the form.

Often we present the user with just the 'data' block and get then to press the 'enter-query' button, place data into fields, and then get them to press 'execute-query'. Again they get data retrieved.

Unfortunately, most users can not handle this 'dual' nature of screen fields. One mode for data entry, and one for data retrieval. I usually do something obvious like change the colour of the menu bar, or have a label that says 'Enter mode' or 'Query mode' to act as a visual hint.

But more often they want the screen 'idiot-proofed' and we have a 'control' block with fields at the top of the screen into which they add data and then we define those fields as part of the 'data' block's 'default where' property.

But now we want to do something more complicated and we have to join our 'data' table to different tables depending on whether a particular 'search' field has been populated. This is where we must redefine the 'default where' for the 'data' block before we can run the query.

So we put data in the 'control' fields, press a button which does
go_block('data');
redefine_where_clause;
execute_query
then we have a new 'where' clause and have the data retrieved.

Hope this helps.

David
Re: execute_query failure [message #125299 is a reply to message #124957] Fri, 24 June 2005 05:23 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
David,
Thanks so far - I'll check out your suggestions.
Lee
Re: execute_query failure [message #125328 is a reply to message #124957] Fri, 24 June 2005 09:09 Go to previous messageGo to next message
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
Junior Member
I've tried this too David.

This is weird - I've checked and re-checked the statement passed to the Oracle Server and when I run the statement through sqlplus 9 rows are returned.
* It is identical to the statement passed first time round too *.

Obviously post-query is not fired because no rows are fetched. I just can't see how this is the case.
Thanks for your input.

Regards

Lee
Re: execute_query failure [message #125483 is a reply to message #125328] Sun, 26 June 2005 20:16 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
What version of forms are you running? IF 9i/10g then use the Forms Builder debug facility to follow the flow of execution in your form.

Alternatively, lots and lots of "message; pause;" pairs to follow your code, line by line.

Look at running the form with 'debug_messages=yes' which will tell you the name of the trigger as it is executed.

David
Re: execute_query failure [message #664646 is a reply to message #125483] Wed, 26 July 2017 03:45 Go to previous messageGo to next message
shashwat0805
Messages: 1
Registered: July 2017
Junior Member
Hi Lee, I am facing exactly the same problem as you've described.

I have a form which diplays customer data in query mode only which is called from a different form.
The form works fine the first time, but if an user tries searches a different customer and then enters this form the second time, it just says No data found. I've debugged the post select also, the query being fired is exaclty same except for the customer id which is the primary key, still the block is not populated. And to my suprise if I exit the previous form and try for the second customer, it works!

All in all the execute_query does not works the second time.

did you find a solution for this?
Re: execute_query failure [message #664660 is a reply to message #664646] Wed, 26 July 2017 07:51 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're highly unlikely to get a response from Lee - he last visited the forum in 2005.

It would help if you described the relevant code in more detail.
Also try using get_block_property(<block name>, last_query) after the 2nd query has been executed to see if it makes sense.
Re: execute_query failure [message #664670 is a reply to message #664660] Thu, 27 July 2017 01:02 Go to previous message
Littlefoot
Messages: 21690
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Looks as if the data block has a DEFAULT WHERE clause set to a customer acquired from the "different form". Search PL/SQL units in that form (the one you use to query data) for "set_block_property" and/or "default_where". Possible locations might be WHEN-NEW-FORM-INSTANCE or PRE-QUERY triggers.

If you find it, the simplest solution would be to modify DEFAULT_WHERE to ONETIME_WHERE.
Previous Topic: Oracle Forms Newbie question :)
Next Topic: Decimal issue in currency (merged 2)
Goto Forum:
  


Current Time: Wed Dec 01 21:34:00 CST 2021