Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2017
    Posts
    2

    Unanswered: Dynamic SQL question

    I have an Oracle procedure that has potentially twenty-something parameters; the procedure is used in a Portal application.
    Most of the parameters are optional.

    In the Portal, a user may select only a few of the parameters for the data they wish to retrieve; I thought that if I could construct a SQL statement containing only those parameters that the execution time would much quicker.
    Is it possible to use dynamic SQL in this scenario?

    This is my (failed) attempt at it:
    Define a variable to contain the SQL:
    l_query_applied long;

    I created a view: fw_ticket_settle_applied_v to contain the source data.

    I created a Global temporary table to contain the results: gtt_ticket_settle_query_detail

    Here is my SQL:
    l_query_applied := '
    INSERT INTO gtt_ticket_settle_query_detail
    SELECT advance_amount,
    advance_date,
    advance_epr_price,
    <column list>
    FROM fw_ticket_settle_applied_v
    WHERE vendor_id IN
    (SELECT name_and_address_id
    FROM gtt_get_name_address_list
    )
    ';

    For each parameter that can be passed to the procedure I did this:

    IF (as_name_address_filter IS NOT NULL) THEN
    l_query_applied := l_query_applied || '
    AND (vendor_id = ''' || as_name_address_filter || ''')
    ';
    END IF;

    In the above example 'as_name_address_filter" is a passed parameter which may be populated or may be null.

    After processing all of the parameters I ran this SQL:

    EXECUTE IMMEDIATE 'l_query_applied';


    Here is a more complete example:
    CREATE OR REPLACE procedure test
    (ac_date_type IN char,
    ad_from_date IN date,
    ad_to_date IN date,
    an_ticket_no IN number,
    an_page_no IN number,
    an_page_size IN number,
    result_set IN
    OUT PagingUtility.PagedResultType,
    order_by IN varchar2
    )
    AS
    lc_date_type char(1);
    lv_name_and_address_id varchar2(10);
    ld_from_date date;
    ld_to_date date;
    str_from_date varchar2(10);
    str_to_date varchar2(10);

    initstmt varchar2(32760);
    startpos number;
    endpos number;
    query varchar2(32760);
    lv_get_naa s1_name_and_address.name_and_address_id%TYPE;
    lv_full_name s1_name_and_address.full_name%TYPE;
    lv_parent_record_id s1_name_and_address.parent_record_id%TYPE;
    lv_pass number;
    naa_result_set SYS_REFCURSOR;

    l_query_applied varchar2(32760);
    l_query_unapplied varchar2(32760);

    BEGIN

    lc_date_type := ac_date_type;
    -- ac_date_type := 'T';
    -- as_commodity_id := 'CORN';

    naa_result_set := f_get_name_address_list (lv_name_and_address_id);

    WHILE TRUE LOOP

    FETCH naa_result_set INTO lv_get_naa,
    lv_full_name,
    lv_parent_record_id,
    lv_pass;

    EXIT WHEN naa_result_set%NOTFOUND;

    INSERT INTO gtt_get_name_address_list
    VALUES (lv_get_naa,lv_full_name,lv_parent_record_id,lv_pa ss);

    END LOOP;

    ld_from_date := ad_from_date;
    ld_to_date := ad_to_date;

    IF ld_to_date IS NOT NULL THEN
    ld_to_date := ld_to_date + 1;
    END IF;

    str_from_date := TO_CHAR(ld_from_date,'DD-MON-YY');
    str_to_date := TO_CHAR(ld_to_date,'DD-MON-YY');

    l_query_applied := '
    INSERT INTO gtt_ticket_settle_query_detail
    SELECT *
    FROM fw_ticket_settle_applied_v
    WHERE vendor_id IN
    (SELECT name_and_address_id
    FROM gtt_get_name_address_list
    )
    ';

    IF (ad_from_date IS NOT NULL AND
    lc_date_type = 'T'
    )
    THEN
    l_query_applied := l_query_applied ||
    ' AND ((direct_ship_flag = ''Y'' AND ship_date >= TO_DATE(''' || str_from_date || '''))
    OR (direct_ship_flag = ''N'' AND ticket_date >= TO_DATE(''' || str_from_date || '''))
    )
    ';
    ELSE
    l_query_applied := l_query_applied ||
    ' and (1=1 OR
    ad_from_date IS NULL
    ) ';
    END IF;

    IF (ad_from_date IS NOT NULL AND
    lc_date_type = 'A'
    )
    THEN
    l_query_applied := l_query_applied ||
    ' AND advance_date >= TO_DATE(''' || str_from_date || ''')
    ';
    ELSE
    l_query_applied := l_query_applied ||
    ' and (1=1 OR
    TO_DATE(''' || str_from_date || ''') IS NULL
    ) ';
    END IF;

    IF (ad_from_date IS NOT NULL AND
    lc_date_type = 'S'
    )
    THEN
    l_query_applied := l_query_applied ||
    ' AND settlement_date >= TO_DATE(''' || str_from_date || ''')
    ';
    ELSE
    l_query_applied := l_query_applied ||
    ' and (1=1 OR
    TO_DATE(''' || str_from_date || ''') IS NULL
    ) ';
    END IF;

    dbms_output.put_line(l_query_applied);

    EXECUTE IMMEDIATE l_query_applied;

    IF (an_ticket_no IS NOT NULL) THEN
    DELETE FROM gtt_ticket_settle_query_detail
    WHERE settlement_status IN ('Not Adv-Unsettled','Unapplied');
    END IF;

    query := 'SELECT * FROM gtt_ticket_settle_query_detail';

    endpos := (an_page_no * an_page_size);
    startpos := (endpos - an_page_size) + 1;

    IF (an_page_no = 0) OR
    (an_page_size = 0) THEN

    initstmt := 'SELECT *
    FROM (SELECT a.*,
    ROWNUM row_no
    FROM (SELECT *
    FROM ('|| query ||'
    ORDER BY rdr
    ),
    (SELECT COUNT(*) AS tot_rows
    FROM ('|| query ||')
    )
    )a
    )
    WHERE row_no BETWEEN :st AND :en';

    OPEN RESULT_SET for initstmt USING order_by,
    startpos,
    endpos;

    ELSE

    initstmt := 'WITH group_data AS
    (SELECT *
    FROM (SELECT a.*,
    ROWNUM row_no
    FROM (SELECT *
    FROM ('|| query ||'
    ORDER BY rdr
    ),
    (SELECT COUNT(*) AS tot_rows
    FROM ('|| query ||')
    )
    )a
    )
    WHERE row_no BETWEEN :st AND :en';

    OPEN RESULT_SET for initstmt USING order_by,
    startpos,
    endpos;

    END IF;

    END;
    /

    The Procedure compiles ok.

    I use this SQL to test the procedure:
    set serveroutput on;
    EXECUTE test
    ('T', -- dateType,
    TO_DATE('20170101','YYYYMMDD'), -- fromDate,
    TO_DATE('20170128','YYYYMMDD'), -- toDate,
    NULL, -- ticket_no
    0, -- page_no
    10, -- page_size,
    NULL, -- result_set
    NULL -- order_by
    )
    ;
    Here are the results:

    Error starting at line : 2 in command -
    BEGIN test; END;
    Error report -
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'TEST'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    Error starting at line : 3 in command -
    ('T', -- dateType,
    TO_DATE('20170101','YYYYMMDD'), -- fromDate,
    TO_DATE('20170128','YYYYMMDD'), -- toDate,
    NULL, -- ticket_no
    0, -- page_no
    10, -- page_size,
    NULL, -- result_set
    NULL -- order_by
    )

    Error at Command Line : 3 Column : 5
    Error report -
    SQL Error: ORA-00928: missing SELECT keyword
    00928. 00000 - "missing SELECT keyword"
    *Cause:
    *Action:

    Thanks
    Murray

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,113
    Provided Answers: 5
    You should display all variables to the screen (INITSTMT is the one you missed) so that you could see what Oracle is going to execute. It appears that it contains an error.

    On the other hand, I'd say that you should discard that approach and simply "SELECT *" from the view ("fw_ticket_settle_applied_v"). As it (the view) is just a stored query, make sure it is correctly written. Don't forget to index all apropriate columns if there are several tables you join. See its SELECT statement's explain plan.

    Unless there is a huge amount of data you're dealing with, it *should* work rather fast, retrieve data and display it to the user.

Posting Permissions

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