execute_query failure [message #124957] |
Wed, 22 June 2005 09:07 |
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 |
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 #125099 is a reply to message #124957] |
Thu, 23 June 2005 05:26 |
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 #125133 is a reply to message #124957] |
Thu, 23 June 2005 07:36 |
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 |
|
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 |
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 |
|
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 #125328 is a reply to message #124957] |
Fri, 24 June 2005 09:09 |
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 #664660 is a reply to message #664646] |
Wed, 26 July 2017 07:51 |
cookiemonster
Messages: 13943 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 |
|
Littlefoot
Messages: 21818 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.
|
|
|