This discussion is archived
14 Replies Latest reply: Apr 7, 2013 8:41 AM by 769072 RSS

Dynamically assign values after USING clause

769072 Newbie
Currently Being Moderated
Hi All,

I have a strange requirement to achieve performance while using dynamic DML sql. I'm using oracle 11g version2

italics
create table CMT_IVIEW_SECTION_DETAILS
(
SOURCE_SYSTEM VARCHAR2(100) not null,
SECTION_NAME VARCHAR2(100) not null,
PARMS_QUERY VARCHAR2(4000),
SEARCH_PARAMS VARCHAR2(100),
TABLE_HEADER VARCHAR2(2000),
DATA_HEADER VARCHAR2(2000),
CURSOR_COLS VARCHAR2(4000) not null,
SECTION_TITLES VARCHAR2(4000),
IS_DISP_DETAILS VARCHAR2(1) not null,
SUB_CURSORS VARCHAR2(4000),
IS_DETAILS_SPLIT VARCHAR2(1),
SPLIT_CNT NUMBER,
SECTION_ORDER NUMBER
)
;

prompt Loading CMT_IVIEW_SECTION_DETAILS...
insert into CMT_IVIEW_SECTION_DETAILS (SOURCE_SYSTEM, SECTION_NAME, PARMS_QUERY, SEARCH_PARAMS, TABLE_HEADER, DATA_HEADER, CURSOR_COLS, SECTION_TITLES, IS_DISP_DETAILS, SUB_CURSORS, IS_DETAILS_SPLIT, SPLIT_CNT, SECTION_ORDER)
values ('a.xls', 'section1', null, '1,2', null, null, 'test', null, 'N', null, null, null, 1);
insert into CMT_IVIEW_SECTION_DETAILS (SOURCE_SYSTEM, SECTION_NAME, PARMS_QUERY, SEARCH_PARAMS, TABLE_HEADER, DATA_HEADER, CURSOR_COLS, SECTION_TITLES, IS_DISP_DETAILS, SUB_CURSORS, IS_DETAILS_SPLIT, SPLIT_CNT, SECTION_ORDER)
values ('a.xls', 'section2', null, '1', null, null, 'test1', null, 'N', null, null, null, 2);
commit;

create table TEMPLATE_QRY
(
XLS_TEMP_NAME VARCHAR2(100) not null,
XLS_SECT_NAME VARCHAR2(100) not null,
XLS_QRY CLOB not null,
UPDATE_DATE DATE not null,
INSERT_DATE DATE not null
);


INSERT INTO TEMPLATE_QRY
VALUES
('c.xls','section1','SELECT cust_id,cust_nm,src_unq_row_id FROM cust_tgt WHERE customer_branch =:1 AND cust_id =:2','01-Jan-2013','01-Jan-2013');
INSERT INTO TEMPLATE_QRY
VALUES
('c.xls','section2','SELECT cust_id,cust_nm,src_unq_row_id FROM cust_tgt WHERE customer_branch =:1','01-Jan-2013','01-Jan-2013');

italics

----------------------------

My requirement is as follows
1. for the passed source_system(ex:a.xls), Need to select the search_params and put in a array
2. Then I need to execute the dynamic DML present in the TEMPLATE_QRY table using the above array.

In this case, the array is dynamic. I'm facing issues in constructing variables after the USING clause.

In the above example, for section1 I need to append two variables (1,2) after using clause
and for Section2, I need to append 1 variable(1) after using clause

----------------------------

Thanks!
  • 1. Re: Dynamically assign values after USING clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Using EXECUTE IMMEDIATE with the USING clause is not the right way to achieve this.

    For a dynamic number of bind variables, you would be better using the DBMS_SQL package to run your dynamic queries.

    However, if you're getting into a situation where you have queries stored on the database with dynamic numbers of bind variables, then that's usually an indication that something is wrong in the design of the application/database, and often you'd be better to step back and look to see why on Earth you are trying to do things dynamically in the first place and whether the design can be reworked so that you eliminate dynamic processing.
  • 2. Re: Dynamically assign values after USING clause
    Karthick_Arp Guru
    Currently Being Moderated
    For a moment lets just drop your current design and lets talk about your requirement. So whats your requirement? SQL stored in table is not a very good idea. It will not scale well. And it will be maintenance nightmare.

    Basically what i can understand is you want to build a SELECT statement in which the select list and table list are going to be static and the WHERE condition is going to change based on the option selected by users.

    There are some optimal way of doing it. I would point you to a artical from ASKTOM which talks about his in a very detailed way.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908
  • 3. Re: Dynamically assign values after USING clause
    BEDE Explorer
    Currently Being Moderated
    If you absolutely have to have dynamic SQL that is executed via a single stored procedure in a package and do not have a fixed number of bind-variables, then you should just give up using bind-variables and use place-holders in the SQL code, and, before execution replace those place-holders with strings containing the nbeeded values.
    So, a place-holder may look like: to_date('$$$ref_date$$$','ddmmyyy') , and you will replace '$$$ref_date$$$' with the string that represents the actual value of that date.
    Performance will certainly be awful, but that's what you have to sacrifice for the sake of flexibility.
    This is good to do only in case you have complicated SQL, the execution of which takes definitely much more time than parsing and that is not run at the same time by lots of user.
    Still, better stand back and see if you can't redesign your application.

    Edited by: BEDE on Apr 2, 2013 10:28 AM
  • 4. Re: Dynamically assign values after USING clause
    769072 Newbie
    Currently Being Moderated
    Hi,

    They mentioned that there are plenty of SQL's for different sections of the xls that is passed as input. hence they came up with this design.

    Can you share the document details of using DBMS_SQL for this scenario so that I can give a try....

    Thanks
  • 5. Re: Dynamically assign values after USING clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Harinath Arasu wrote:
    They mentioned that there are plenty of SQL's for different sections of the xls that is passed as input. hence they came up with this design.
    Who are "they"? Did "they" also mention that dynamic SQL has performance and SQL injection issues? Did "they" mention that storing SQL on the database and using dynamic queries will render all code that wants to use the data as having to be dynamic? Did "they" mention that dynamic queries do not scale, and bugs or dependency issues will not be identified at design/compile time, but may only become apparent at run-time... and then maybe only certain circumstances that won't be found in testing? Did "they" mention that dynamic code is a pain in the @rse to maintain?

    Whoever "they" are... it would seem "they" don't really understand the implications of creating dynamic code, or even how to go about designing a robust application.
    Can you share the document details of using DBMS_SQL for this scenario so that I can give a try....
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm#ARPLS68176
  • 6. Re: Dynamically assign values after USING clause
    BEDE Explorer
    Currently Being Moderated
    SQL injection will not be an issue if the dynamic SQL is stored in a table where the access is granted only to certain persons and it is executed via a package, and, those writing the dynamic SQL take the precautions not to design that dynamic as to allow anything. So, it may be a select from certain tables, but not just any table one may pass as parameter.
    It would be best to hold the SQL "pieces" in a table and execute the dynamic SQL via a procedure that returns a ref cursor if the statement is a select, or that returns something like a sq%rowcount if it is an update or delete.
    No doubt, performance is likely to be an issue.
  • 7. Re: Dynamically assign values after USING clause
    Karthick_Arp Guru
    Currently Being Moderated
    BEDE wrote:
    If you absolutely have to have dynamic SQL that is executed via a single stored procedure in a package and do not have a fixed number of bind-variables, then you should just give up using bind-variables and use place-holders in the SQL code, and, before execution replace those place-holders with strings containing the nbeeded values.
    So, a place-holder may look like: to_date('$$$ref_date$$$','ddmmyyy') , and you will replace '$$$ref_date$$$' with the string that represents the actual value of that date.
    Performance will certainly be awful, but that's what you have to sacrifice for the sake of flexibility.
    This is good to do only in case you have complicated SQL, the execution of which takes definitely much more time than parsing and that is not run at the same time by lots of user.
    Still, better stand back and see if you can't redesign your application.

    Edited by: BEDE on Apr 2, 2013 10:28 AM
    A simple demonstration to show how your PLACE-HOLDER approach is a screw-up approach.
    SQL> exec execute immediate replace(q'[select ename from emp where to_date('$$$ref_date$$$','ddmmyyy') = sysdate]', '$$$ref_date$$$', q'[01012009', 'ddmmyyyy') = null union all select dummy from dual --]') into :val
     
    PL/SQL procedure successfully completed.
     
    SQL> print val
     
    VAL
    --------------------------------------------------------------------------------------------------------------------------------
    X
     
    SQL> 
  • 8. Re: Dynamically assign values after USING clause
    BluShadow Guru Moderator
    Currently Being Moderated
    BEDE wrote:
    SQL injection will not be an issue if ...
    and there's the important word.... "if"....

    Unfortunately, many such 'designs' lack the knowledge or understanding to take account of all the "ifs" and will often leave holes open that can be abused... and the majority of those designs would be far better off being codes and designed properly without any dynamic code.
  • 9. Re: Dynamically assign values after USING clause
    769072 Newbie
    Currently Being Moderated
    I'm unable to use DBMS_SQL for this requirement, can you share a sample code for this scenario so that I can extend it for the rest of the part...

    Thanks!
  • 10. Re: Dynamically assign values after USING clause
    rp0428 Guru
    Currently Being Moderated
    >
    A simple demonstration to show how your PLACE-HOLDER approach is a screw-up approach.
    >
    That would be a better 'demonstration' if you edit your post to explain what your code does and why OP is using a 'screw-up approach'.
  • 11. Re: Dynamically assign values after USING clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Harinath Arasu wrote:
    I'm unable to use DBMS_SQL for this requirement,
    Why not?
    can you share a sample code for this scenario so that I can extend it for the rest of the part...
    If you cannot use DBMS_SQL, then you cannot do what you want, so no point in sharing any code.
  • 12. Re: Dynamically assign values after USING clause
    769072 Newbie
    Currently Being Moderated
    Nope blueshadow, I mentioned I don't know how to use dbms_sql for this requirement. So was asking for some sample DBMS_SQL code for the requirement.
  • 13. Re: Dynamically assign values after USING clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Harinath Arasu wrote:
    Nope blueshadow, I mentioned I don't know how to use dbms_sql for this requirement. So was asking for some sample DBMS_SQL code for the requirement.
    But there are plenty of examples in the documentation showing how to use it...

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm#ARPLS68176

    And plenty of examples found on the web if you bother to look...

    http://www.morganslibrary.org/reference/pkgs/dbms_sql.html
    http://docstore.mik.ua/orelly/oracle/bipack/ch02_05.htm
    http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/PLS5K28/Default.aspx
    http://www.oracle-developer.net/display.php?id=505
  • 14. Re: Dynamically assign values after USING clause
    769072 Newbie
    Currently Being Moderated
    Finally,

    I completed the package with the help of my seniors and the code as below...Thanks All for your support!
    spool pkg_aml_data_extractor.log;
    set define off;
    
    create or replace type T_STRINGARRAY is table of VARCHAR2(32767);
    /
    
    CREATE OR REPLACE PACKAGE PKG_AML_DATA_EXTRACTOR IS
    
      PROCEDURE PR_DATA_EXTRACTOR(PTEMPLATE_NM  VARCHAR2,
                                  P_INPUT_ARRAY T_STRINGARRAY,
                                  REF_CUR1      OUT SYS_REFCURSOR,
                                  REF_CUR2      OUT SYS_REFCURSOR,
                                  REF_CUR3      OUT SYS_REFCURSOR,
                                  REF_CUR4      OUT SYS_REFCURSOR,
                                  REF_CUR5      OUT SYS_REFCURSOR,
                                  REF_CUR6      OUT SYS_REFCURSOR,
                                  REF_CUR7      OUT SYS_REFCURSOR,
                                  REF_CUR8      OUT SYS_REFCURSOR,
                                  REF_CUR9      OUT SYS_REFCURSOR,
                                  REF_CUR10     OUT SYS_REFCURSOR,
                                  REF_CUR11     OUT SYS_REFCURSOR,
                                  REF_CUR12     OUT SYS_REFCURSOR,
                                  REF_CUR13     OUT SYS_REFCURSOR,
                                  REF_CUR14     OUT SYS_REFCURSOR,
                                  REF_CUR15     OUT SYS_REFCURSOR,
                                  REF_CUR16     OUT SYS_REFCURSOR,
                                  REF_CUR17     OUT SYS_REFCURSOR,
                                  REF_CUR18     OUT SYS_REFCURSOR,
                                  REF_CUR19     OUT SYS_REFCURSOR,
                                  REF_CUR20     OUT SYS_REFCURSOR,
                                  REF_CUR21     OUT SYS_REFCURSOR,
                                  REF_CUR22     OUT SYS_REFCURSOR,
                                  REF_CUR23     OUT SYS_REFCURSOR,
                                  REF_CUR24     OUT SYS_REFCURSOR,
                                  REF_CUR25     OUT SYS_REFCURSOR);
    END PKG_AML_DATA_EXTRACTOR;
    /
    
    SHO ERR;
    
    CREATE OR REPLACE PACKAGE BODY PKG_AML_DATA_EXTRACTOR IS
    
      /**********************************************************************
        Procedure for UI to extract data based on input passed
      ***********************************************************************/
      
      PROCEDURE PR_DATA_EXTRACTOR(PTEMPLATE_NM  VARCHAR2,
                                  P_INPUT_ARRAY T_STRINGARRAY,
                                  REF_CUR1      OUT SYS_REFCURSOR,
                                  REF_CUR2      OUT SYS_REFCURSOR,
                                  REF_CUR3      OUT SYS_REFCURSOR,
                                  REF_CUR4      OUT SYS_REFCURSOR,
                                  REF_CUR5      OUT SYS_REFCURSOR,
                                  REF_CUR6      OUT SYS_REFCURSOR,
                                  REF_CUR7      OUT SYS_REFCURSOR,
                                  REF_CUR8      OUT SYS_REFCURSOR,
                                  REF_CUR9      OUT SYS_REFCURSOR,
                                  REF_CUR10     OUT SYS_REFCURSOR,
                                  REF_CUR11     OUT SYS_REFCURSOR,
                                  REF_CUR12     OUT SYS_REFCURSOR,
                                  REF_CUR13     OUT SYS_REFCURSOR,
                                  REF_CUR14     OUT SYS_REFCURSOR,
                                  REF_CUR15     OUT SYS_REFCURSOR,
                                  REF_CUR16     OUT SYS_REFCURSOR,
                                  REF_CUR17     OUT SYS_REFCURSOR,
                                  REF_CUR18     OUT SYS_REFCURSOR,
                                  REF_CUR19     OUT SYS_REFCURSOR,
                                  REF_CUR20     OUT SYS_REFCURSOR,
                                  REF_CUR21     OUT SYS_REFCURSOR,
                                  REF_CUR22     OUT SYS_REFCURSOR,
                                  REF_CUR23     OUT SYS_REFCURSOR,
                                  REF_CUR24     OUT SYS_REFCURSOR,
                                  REF_CUR25     OUT SYS_REFCURSOR) IS
      
        V_XLS_SECT_NAME_TABLE T_STRINGARRAY;
        V_SECTION_ORDER_TABLE T_STRINGARRAY;
        V_SEARCH_PARAMS_COMMA_TABLE T_STRINGARRAY;   
      
        TYPE T_TEMPLATE_QRY_TABLE IS TABLE OF TEMPLATE_QRY.XLS_QRY%TYPE;
        V_TEMPLATE_QRY_TABLE T_TEMPLATE_QRY_TABLE;
      
        TYPE T_SEARCH_PARAMS_TABLE IS TABLE OF NUMBER;
        V_SEARCH_PARAMS_TABLE T_SEARCH_PARAMS_TABLE;
      
        C1 SYS_REFCURSOR;
        C2 SYS_REFCURSOR;
      
        V_PARAMETER1_BOOLEAN NUMBER;
        V_PARAMETER2_BOOLEAN NUMBER;
        V_PARAMETER3_BOOLEAN NUMBER;
        V_PARAMETER4_BOOLEAN NUMBER;
        V_PARAMETER5_BOOLEAN NUMBER;
        V_PARAMETER6_BOOLEAN NUMBER;
      
        V_DBMS_SQL_OPEN_CUR    NUMBER;
        V_DBMS_SQL_EXECUTE_CUR NUMBER;
      
        V_PASSED_PARAMETER1 VARCHAR2(1000);
        V_PASSED_PARAMETER2 VARCHAR2(1000);
        V_PASSED_PARAMETER3 VARCHAR2(1000);
        V_PASSED_PARAMETER4 VARCHAR2(1000);
        V_PASSED_PARAMETER5 VARCHAR2(1000);
        V_PASSED_PARAMETER6 VARCHAR2(1000);
      
        vrun_id   log_tbl.run_id%TYPE;
        lv_run_id log_tbl.run_id%TYPE;    
      
      BEGIN
      
       -- Code to generate log run id to be used across package
        SELECT pkg_aml_log.fn_log_impl(SYSDATE,vrun_id,'PKG_AML_DATA_EXTRACTOR Executing for '||PTEMPLATE_NM ,'INFO')
          INTO vrun_id
          FROM dual;
    
       OPEN C1 FOR
          SELECT DYNQ.XLS_SECT_NAME,
                 CONFIG.SECTION_ORDER,
                 DYNQ.XLS_QRY,
                 CONFIG.SEARCH_PARAMS
            FROM TEMPLATE_QRY DYNQ, CMT_IVIEW_SECTION_DETAILS CONFIG
           WHERE DYNQ.XLS_TEMP_NAME = CONFIG.SOURCE_SYSTEM
             AND DYNQ.XLS_SECT_NAME = CONFIG.SECTION_NAME
             AND CONFIG.SOURCE_SYSTEM = PTEMPLATE_NM
           ORDER BY XLS_SECT_NAME, SECTION_ORDER;
        LOOP
          FETCH C1 BULK COLLECT
            INTO V_XLS_SECT_NAME_TABLE, V_SECTION_ORDER_TABLE, V_TEMPLATE_QRY_TABLE, V_SEARCH_PARAMS_COMMA_TABLE;
          FOR I IN 1 .. V_SECTION_ORDER_TABLE.COUNT LOOP
            OPEN C2 FOR
               SELECT regexp_substr(V_SEARCH_PARAMS_COMMA_TABLE(i), '[^,]+', 1, level) coll
                 FROM (SELECT V_SEARCH_PARAMS_COMMA_TABLE(i) FROM dual)
               CONNECT BY LEVEL <= length(regexp_replace(V_SEARCH_PARAMS_COMMA_TABLE(i), '[^,]+')) + 1;
            LOOP
              FETCH C2 BULK COLLECT
                INTO V_SEARCH_PARAMS_TABLE;
            
              V_PARAMETER1_BOOLEAN := 0;
              V_PARAMETER2_BOOLEAN := 0;
              V_PARAMETER3_BOOLEAN := 0;
              V_PARAMETER4_BOOLEAN := 0;
              V_PARAMETER5_BOOLEAN := 0;
              V_PARAMETER6_BOOLEAN := 0;
            
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 1 THEN
                V_PARAMETER1_BOOLEAN := 1;
                V_PASSED_PARAMETER1 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(1));
              END IF;
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 2 THEN
                V_PARAMETER2_BOOLEAN := 2;
                V_PASSED_PARAMETER2 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(2));
              END IF;          
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 3 THEN
                V_PARAMETER3_BOOLEAN := 3;
                V_PASSED_PARAMETER3 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(3));
              END IF;          
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 4 THEN
                V_PARAMETER4_BOOLEAN := 4;
                V_PASSED_PARAMETER4 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(4));
              END IF;          
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 5 THEN
                V_PARAMETER5_BOOLEAN := 5;
                V_PASSED_PARAMETER5 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(5));
              END IF;          
              IF V_SEARCH_PARAMS_TABLE.COUNT >= 6 THEN
                V_PARAMETER6_BOOLEAN := 6;
                V_PASSED_PARAMETER6 := P_INPUT_ARRAY(V_SEARCH_PARAMS_TABLE(6));
              END IF;                                                          
           
              EXIT WHEN C2%NOTFOUND;
            END LOOP; --for c2
            CLOSE C2;
          
            V_DBMS_SQL_OPEN_CUR := DBMS_SQL.OPEN_CURSOR;
          
            DBMS_SQL.PARSE(V_DBMS_SQL_OPEN_CUR,
                           V_TEMPLATE_QRY_TABLE(I),
                           DBMS_SQL.NATIVE);
          
          
            IF V_PARAMETER1_BOOLEAN = 1 THEN 
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'1',V_PASSED_PARAMETER1);
            END IF;
            IF V_PARAMETER2_BOOLEAN = 2 THEN
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'2',V_PASSED_PARAMETER2);
            END IF;
            IF V_PARAMETER3_BOOLEAN = 3 THEN
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'3',V_PASSED_PARAMETER3);
            END IF;
            IF V_PARAMETER4_BOOLEAN = 4 THEN 
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'4',V_PASSED_PARAMETER4);
            END IF;
            IF V_PARAMETER5_BOOLEAN = 5 THEN
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'5',V_PASSED_PARAMETER5);
            END IF;
            IF V_PARAMETER6_BOOLEAN = 6 THEN 
               DBMS_SQL.BIND_VARIABLE(V_DBMS_SQL_OPEN_CUR,'6',V_PASSED_PARAMETER6);
            END IF;
          
            V_DBMS_SQL_EXECUTE_CUR := DBMS_SQL.EXECUTE(V_DBMS_SQL_OPEN_CUR);
          
            IF V_SECTION_ORDER_TABLE(I) = 1 THEN
              REF_CUR1 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 2 THEN
              REF_CUR2 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 3 THEN
              REF_CUR3 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 4 THEN
              REF_CUR4 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 5 THEN
              REF_CUR5 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 6 THEN
              REF_CUR6 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 7 THEN
              REF_CUR7 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 8 THEN
              REF_CUR8 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 9 THEN
              REF_CUR9 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 10 THEN
              REF_CUR10 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 11 THEN
              REF_CUR11 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 12 THEN
              REF_CUR12 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 13 THEN
              REF_CUR13 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 14 THEN
              REF_CUR14 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 15 THEN
              REF_CUR15 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 16 THEN
              REF_CUR16 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 17 THEN
              REF_CUR17 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 18 THEN
              REF_CUR18 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 19 THEN
              REF_CUR19 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 20 THEN
              REF_CUR20 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 21 THEN
              REF_CUR21 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 22 THEN
              REF_CUR22 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 23 THEN
              REF_CUR23 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 24 THEN
              REF_CUR24 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
            IF V_SECTION_ORDER_TABLE(I) = 25 THEN
              REF_CUR25 := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_OPEN_CUR);
            END IF;
          
          --  CLOSE REF_CUR1 ;
          --  DBMS_SQL.CLOSE_CURSOR(V_DBMS_SQL_OPEN_CUR);
          --    END LOOP;
          --  END IF;
          END LOOP; --for c1
        
          EXIT WHEN C1%NOTFOUND;
        END LOOP;
        CLOSE C1;
        
      EXCEPTION
        WHEN OTHERS THEN
          lv_run_id := pkg_aml_log.fn_log_impl(SYSDATE,vrun_id,'PKG_AML_DATA_EXTRACTOR Encountered Following Errors For '||PTEMPLATE_NM || ' SQLCODE: ' ||SQLCODE || ' SQLERRM: ' ||SQLERRM,'ERROR');
      END PR_DATA_EXTRACTOR;
    END PKG_AML_DATA_EXTRACTOR;
    /
    
    SHO ERR;
    
    set define on;
    spool off;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points