| Checking query posted to server for record fetch [message #126218] |
Fri, 01 July 2005 11:22  |
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
|
Junior Member |
|
|
Hello,
Can you trap the true sql statement posted to the Oracle Server from Forms when no rows are returned?
No data is returned for a sql statement using a DEFAULT_WHERE clause on the pre-query trigger. Two rows ahould be returned.
To investigate, I created a POST-QUERY trigger and output a message knowing that this should fire for every row that is returned. Unfortunately, it does not fire.
I manually ran the sql statement produced by the Default_where clause in SQLPlus and 2 rows are indeed retrieved. My only thought then is that for some reason the sql statement is not being posted. So I created a POST_SELECT trigger. This calls a procedure that determines the where clause in :system.last_query and goes like this:
PROCEDURE last_where_clause IS
tmp_lstqry VARCHAR2(1000) := :System.Last_Query;
tmp_curblk VARCHAR2(40);
tmp_index NUMBER;
tmp_where VARCHAR2(2000);
where1 VARCHAR2(150);
where2 VARCHAR2(150);
where3 VARCHAR2(150);
BEGIN
message('last_where_clause called');
tmp_index:= INSTR(tmp_lstqry,'WHERE');
IF tmp_index > 0 THEN
message('where clause found');
tmp_where := SUBSTR(tmp_lstqry, tmp_index + 6);
where1 := substr(tmp_where,1,150);
where2 := substr(tmp_where,151,150);
where3 := substr(tmp_where,301,150);
message('1='||tmp_where);
message('2='||where1);
message('3='||where2);
message('4='||where3);
END IF;
EXCEPTION WHEN OTHERS THEN
message('E1='||tmp_where);
message('E2='||where1);
message('E3='||where2);
message('E4='||where3);
END;
Again, using the messages output I run the sql again manually in SQLPlus - 2 rows are returned.
To be doubly sure, I call another procedure from POST_SELECT to determine the data held on the table at the time the sql is posted. The rows returned in the cursor are based on the query performed.
PACKAGE BODY LEE_TEST IS
PROCEDURE DUMMY_PROCEDURE IS
CURSOR c_res is
SELECT function,
society,
customer_contact,
event_group,
effective_from,
effective_to,
sub_function_1
FROM v_cv_event_types
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 to_date(V_CV_EVENT_TYPES.EFFECTIVE_FROM, 'DD-MON-YY') < :B1.WCAL_DATE
AND ( to_date(V_CV_EVENT_TYPES.EFFECTIVE_TO, 'DD-MON-YY') > :B1.WCAL_DATE
OR to_date(V_CV_EVENT_TYPES.EFFECTIVE_TO, 'DD-MON-YY') IS NULL )
AND V_CV_EVENT_TYPES.SUB_FUNCTION_1 = :V_DIS_CV_EVENT_TYPES.SUB_FUNCTION_1;
BEGIN
FOR cv_res in c_res LOOP
message('dummy:v_func='||cv_res.function);
message('dummy:v_soc='||cv_res.society);
message('dummy:v_cust='||cv_res.customer_contact);
message('dummy:v_event='||cv_res.event_group);
message('dummy:v_from='||cv_res.effective_from);
message('dummy:v_to='||cv_res.effective_to);
message('dummy:v_subfunc='||cv_res.sub_function_1);
END LOOP;
END;
END;
The data held on table is output on screen and again the variables on the default_where clause fall within the parameters.
I just cannot see why data is not returned. Please note: there is no confirmation no rows are returned. Am I truly returning the last sql statement to be posted to the server. Is there anything else I can do? Why are no rows returned?
Any help appreciated. Thankyou for your time.
Kind regards
Lee Sutton
|
|
|
|
| Re: Checking query posted to server for record fetch [message #126267 is a reply to message #126218] |
Sat, 02 July 2005 09:58  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
To see which query is executed do the following in the application:
Enter query
Enter an ampersand (&) in one of the queryable fields.
Execute query
Enter rubbish in the popped-up box (e.g. just 1 character)
Press Ok.
Now an error appears stating that there was an error.
Confirm this.
Press key 'Show last error' (See help-menu which key to use; default is CTRL-F8 on Windows I think).
Now it shows the exact query as was executed.
hth
|
|
|
|
| Re: Checking query posted to server for record fetch [message #128446 is a reply to message #126267] |
Mon, 18 July 2005 11:09 |
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
|
Junior Member |
|
|
Hi Frank,
Thanks for your input. I did this and it was indeed identical to :system.last_query. That's great (in a way). Unfortunately, I have no idea why the Oracle Server is not fetching records.
For anyone reading this - do you think a rewrite of the form is required. I've heard people say that using alternative methods to perform the same action will produce different results - ie there are just quirks in the system. Does this ring true with anyone??
Regards
Lee
|
|
|
|
|
|
| Re: Checking query posted to server for record fetch [message #128785 is a reply to message #128477] |
Wed, 20 July 2005 10:39 |
Lee Sutton
Messages: 20
Registered: June 2005
Location: Sheffield
|
Junior Member |
|
|
Hello again David.
I'd already thought of checking the user, but thanks for the tip about trapping the last query. I've tried 3-4 methods now and they all identify the same statement.
However, this is no longer an issue. I have amended the view (only called by this one form), trapping as many of the conditional clauses as possible within it. I then removed the now redundant clauses from the Default_Where in the pre-query.
Although it is still doing the same thing, data is returned for both 'Events', not just for the first. I guess this is just one of Forms 6i features.
Thanks again
Regards
Lee
|
|
|
|
| Re: Checking query posted to server for record fetch [message #128839 is a reply to message #128785] |
Wed, 20 July 2005 20:30 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I ran across this yesterday. I was building a current item into the default_where but as I had it on the screen, forms was also putting it into the where clause if I searched by that screen item. My problem was that I had manually put the code as 'column = :item' but as I was using '%' in the query in the form, forms was using 'column like :item' and, of course, I got nothing back.
Try this, manually add to your default_where a column that does not exist in your table, the query will fail with an 'unknown column', then press Ctrl-E to get the error, check to see if the same column is being searched for twice.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
