Checking query posted to server for record fetch

[Lee Sutton]

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

[marist89]

Trace it from the server.

[Lee Sutton]

I'll see what I can do. Thanks.