Results 1 to 3 of 3
\r\n \r\n\r\n \r\n \r\n
\r\n
\r\n
\r\n I have an Oracle procedure that has potentially twenty-something parameters; the procedure is used in a Portal application.
\r\nMost of the parameters are optional.
\r\n
\r\nIn 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.
\r\nIs it possible to use dynamic SQL in this scenario?
\r\n
\r\nThis is my (failed) attempt at it:
\r\nDefine a variable to contain the SQL:
\r\n l_query_applied long;
\r\n
\r\nI created a view: fw_ticket_settle_applied_v to contain the source data.
\r\n
\r\nI created a Global temporary table to contain the results: gtt_ticket_settle_query_detail
\r\n
\r\nHere is my SQL:
\r\n l_query_applied := \'
\r\n INSERT INTO gtt_ticket_settle_query_detail
\r\n SELECT advance_amount,
\r\n advance_date,
\r\n advance_epr_price,
\r\n <column list>
\r\n FROM fw_ticket_settle_applied_v
\r\n WHERE vendor_id IN
\r\n (SELECT name_and_address_id
\r\n FROM gtt_get_name_address_list
\r\n )
\r\n \';
\r\n
\r\nFor each parameter that can be passed to the procedure I did this:
\r\n
\r\n IF (as_name_address_filter IS NOT NULL) THEN
\r\n l_query_applied := l_query_applied || \'
\r\n AND (vendor_id = \'\'\' || as_name_address_filter || \'\'\')
\r\n \';
\r\n END IF;
\r\n
\r\nIn the above example \'as_name_address_filter" is a passed parameter which may be populated or may be null.
\r\n
\r\nAfter processing all of the parameters I ran this SQL:
\r\n
\r\n EXECUTE IMMEDIATE \'l_query_applied\';
\r\n
\r\n
\r\nHere is a more complete example:
\r\nCREATE OR REPLACE procedure test
\r\n (ac_date_type IN char,
\r\n ad_from_date IN date,
\r\n ad_to_date IN date,
\r\n an_ticket_no IN number,
\r\n an_page_no IN number,
\r\n an_page_size IN number,
\r\n result_set IN
\r\n OUT PagingUtility.PagedResultType,
\r\n order_by IN varchar2
\r\n )
\r\nAS
\r\n lc_date_type char(1);
\r\n lv_name_and_address_id varchar2(10);
\r\n ld_from_date date;
\r\n ld_to_date date;
\r\n str_from_date varchar2(10);
\r\n str_to_date varchar2(10);
\r\n
\r\n initstmt varchar2(32760);
\r\n startpos number;
\r\n endpos number;
\r\n query varchar2(32760);
\r\n lv_get_naa s1_name_and_address.name_and_address_id%TYPE;
\r\n lv_full_name s1_name_and_address.full_name%TYPE;
\r\n lv_parent_record_id s1_name_and_address.parent_record_id%TYPE;
\r\n lv_pass number;
\r\n naa_result_set SYS_REFCURSOR;
\r\n
\r\n l_query_applied varchar2(32760);
\r\n l_query_unapplied varchar2(32760);
\r\n
\r\nBEGIN
\r\n
\r\n lc_date_type := ac_date_type;
\r\n-' + '- ac_date_type := \'T\';
\r\n-' + '- as_commodity_id := \'CORN\';
\r\n
\r\n naa_result_set := f_get_name_address_list (lv_name_and_address_id);
\r\n
\r\n WHILE TRUE LOOP
\r\n
\r\n FETCH naa_result_set INTO lv_get_naa,
\r\n lv_full_name,
\r\n lv_parent_record_id,
\r\n lv_pass;
\r\n
\r\n EXIT WHEN naa_result_set%NOTFOUND;
\r\n
\r\n INSERT INTO gtt_get_name_address_list
\r\n VALUES (lv_get_naa,lv_full_name,lv_parent_record_id,lv_pa ss);
\r\n
\r\n END LOOP;
\r\n
\r\n ld_from_date := ad_from_date;
\r\n ld_to_date := ad_to_date;
\r\n
\r\n IF ld_to_date IS NOT NULL THEN
\r\n ld_to_date := ld_to_date + 1;
\r\n END IF;
\r\n
\r\n str_from_date := TO_CHAR(ld_from_date,\'DD-MON-YY\');
\r\n str_to_date := TO_CHAR(ld_to_date,\'DD-MON-YY\');
\r\n
\r\n l_query_applied := \'
\r\n INSERT INTO gtt_ticket_settle_query_detail
\r\n SELECT *
\r\n FROM fw_ticket_settle_applied_v
\r\n WHERE vendor_id IN
\r\n (SELECT name_and_address_id
\r\n FROM gtt_get_name_address_list
\r\n )
\r\n \';
\r\n
\r\n IF (ad_from_date IS NOT NULL AND
\r\n lc_date_type = \'T\'
\r\n )
\r\n THEN
\r\n l_query_applied := l_query_applied ||
\r\n \' AND ((direct_ship_flag = \'\'Y\'\' AND ship_date >= TO_DATE(\'\'\' || str_from_date || \'\'\'))
\r\n OR (direct_ship_flag = \'\'N\'\' AND ticket_date >= TO_DATE(\'\'\' || str_from_date || \'\'\'))
\r\n )
\r\n \';
\r\n ELSE
\r\n l_query_applied := l_query_applied ||
\r\n \' and (1=1 OR
\r\n ad_from_date IS NULL
\r\n ) \';
\r\n END IF;
\r\n
\r\n IF (ad_from_date IS NOT NULL AND
\r\n lc_date_type = \'A\'
\r\n )
\r\n THEN
\r\n l_query_applied := l_query_applied ||
\r\n \' AND advance_date >= TO_DATE(\'\'\' || str_from_date || \'\'\')
\r\n \';
\r\n ELSE
\r\n l_query_applied := l_query_applied ||
\r\n \' and (1=1 OR
\r\n TO_DATE(\'\'\' || str_from_date || \'\'\') IS NULL
\r\n ) \';
\r\n END IF;
\r\n
\r\n IF (ad_from_date IS NOT NULL AND
\r\n lc_date_type = \'S\'
\r\n )
\r\n THEN
\r\n l_query_applied := l_query_applied ||
\r\n \' AND settlement_date >= TO_DATE(\'\'\' || str_from_date || \'\'\')
\r\n \';
\r\n ELSE
\r\n l_query_applied := l_query_applied ||
\r\n \' and (1=1 OR
\r\n TO_DATE(\'\'\' || str_from_date || \'\'\') IS NULL
\r\n ) \';
\r\n END IF;
\r\n
\r\n dbms_output.put_line(l_query_applied);
\r\n
\r\n EXECUTE IMMEDIATE l_query_applied;
\r\n
\r\n IF (an_ticket_no IS NOT NULL) THEN
\r\n DELETE FROM gtt_ticket_settle_query_detail
\r\n WHERE settlement_status IN (\'Not Adv-Unsettled\',\'Unapplied\');
\r\n END IF;
\r\n
\r\n query := \'SELECT * FROM gtt_ticket_settle_query_detail\';
\r\n
\r\n endpos := (an_page_no * an_page_size);
\r\n startpos := (endpos - an_page_size) + 1;
\r\n
\r\n IF (an_page_no = 0) OR
\r\n (an_page_size = 0) THEN
\r\n
\r\n initstmt := \'SELECT *
\r\n FROM (SELECT a.*,
\r\n ROWNUM row_no
\r\n FROM (SELECT *
\r\n FROM (\'|| query ||\'
\r\n ORDER BY rdr
\r\n ),
\r\n (SELECT COUNT(*) AS tot_rows
\r\n FROM (\'|| query ||\')
\r\n )
\r\n )a
\r\n )
\r\n WHERE row_no BETWEEN :st AND :en\';
\r\n
\r\n OPEN RESULT_SET for initstmt USING order_by,
\r\n startpos,
\r\n endpos;
\r\n
\r\n ELSE
\r\n
\r\n initstmt := \'WITH group_data AS
\r\n (SELECT *
\r\n FROM (SELECT a.*,
\r\n ROWNUM row_no
\r\n FROM (SELECT *
\r\n FROM (\'|| query ||\'
\r\n ORDER BY rdr
\r\n ),
\r\n (SELECT COUNT(*) AS tot_rows
\r\n FROM (\'|| query ||\')
\r\n )
\r\n )a
\r\n )
\r\n WHERE row_no BETWEEN :st AND :en\';
\r\n
\r\n OPEN RESULT_SET for initstmt USING order_by,
\r\n startpos,
\r\n endpos;
\r\n
\r\n END IF;
\r\n
\r\nEND;
\r\n/
\r\n
\r\nThe Procedure compiles ok.
\r\n
\r\nI use this SQL to test the procedure:
\r\nset serveroutput on;
\r\nEXECUTE test
\r\n (\'T\', -' + '- dateType,
\r\n TO_DATE(\'20170101\',\'YYYYMMDD\'), -' + '- fromDate,
\r\n TO_DATE(\'20170128\',\'YYYYMMDD\'), -' + '- toDate,
\r\n NULL, -' + '- ticket_no
\r\n 0, -' + '- page_no
\r\n 10, -' + '- page_size,
\r\n NULL, -' + '- result_set
\r\n NULL -' + '- order_by
\r\n )
\r\n;
\r\n Here are the results:
\r\n
\r\nError starting at line : 2 in command -
\r\nBEGIN test; END;
\r\nError report -
\r\nORA-06550: line 1, column 7:
\r\nPLS-00306: wrong number or types of arguments in call to \'TEST\'
\r\nORA-06550: line 1, column 7:
\r\nPL/SQL: Statement ignored
\r\n06550. 00000 - "line %s, column %s:\\n%s"
\r\n*Cause: Usually a PL/SQL compilation error.
\r\n*Action:
\r\n
\r\nError starting at line : 3 in command -
\r\n (\'T\', -' + '- dateType,
\r\n TO_DATE(\'20170101\',\'YYYYMMDD\'), -' + '- fromDate,
\r\n TO_DATE(\'20170128\',\'YYYYMMDD\'), -' + '- toDate,
\r\n NULL, -' + '- ticket_no
\r\n 0, -' + '- page_no
\r\n 10, -' + '- page_size,
\r\n NULL, -' + '- result_set
\r\n NULL -' + '- order_by
\r\n )
\r\n
\r\nError at Command Line : 3 Column : 5
\r\nError report -
\r\nSQL Error: ORA-00928: missing SELECT keyword
\r\n00928. 00000 - "missing SELECT keyword"
\r\n*Cause:
\r\n*Action:
\r\n
\r\nThanks
\r\nMurray
\r\n
\r\n
\r\n\r\n \r\n\r\n\r\n
\r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n
\r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n\r\n'; pd[6650262] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #2\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n Littlefoot is offline\r\n\r\n \r\n \r\n Lost Boy\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Jan 2004
    \r\n
    Location
    Croatia, Europe
    \r\n \r\n
    Posts
    4,113
    \r\n \r\n
    \r\n \r\n
    Provided Answers: 5
    \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n 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.
    \n
    \nOn 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.
    \n
    \nUnless there is a huge amount of data you\'re dealing with, it *should* work rather fast, retrieve data and display it to the user.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6650269] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #3\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n flyboy is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Mar 2007
    \r\n \r\n \r\n
    Posts
    629
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n Firstly, let us have a look at the SQL*Plus EXECUTE command: https://docs.oracle.com/database/122...E.htm#SQPUG043
    \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).\r\n \r\n
    \r\n
    \r\n
    So effectively you call the procedure without parameters.
    \r\n
    \r\nAnyway, you declared the parameter RESULT_SET as IN OUT. You cannot put there a constant (NULL), you have to call it with variable of the same type.
    \r\nhttps://docs.oracle.com/database/122...s.htm#LNPLS659
    \r\nJust think. How can the procedure return anything to NULL?
    \r\n
    \r\nWhich demonstrates the difference between "calling" the procedure and meaningfully using it. I fail to understand its purpose, so I cannot asssist in that.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; // next/previous post info pn[6650235] = "6650269,6650262"; pn[0] = ",6650235"; pn[6650262] = "6650235,6650269"; pn[6650269] = "6650262,6650235"; // cached usernames pu[0] = guestphrase; pu[369008] = "MJSobol"; pu[55311] = "Littlefoot"; pu[163533] = "flyboy"; // -->

    Threaded View

    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

    Posting Permissions

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