Checking query posted to server for record fetch
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Checking query posted to server for record fetch

  1. #1
    Join Date
    Jul 2005
    Location
    Sheffield
    Posts
    2

    Checking query posted to server for record fetch

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Trace it from the server.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2005
    Location
    Sheffield
    Posts
    2
    I'll see what I can do. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width