1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using Dynamic SQL.

Discussion in 'SQL PL/SQL' started by FDavidov, Dec 18, 2013.

  1. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hello all,

    I'm trying to make some use of Dynamic SQL with only partial success. Here is where I'm stuck (I'll give a simple example without going too deep into details):

    Suppose you have a table with columns "ATTRIBUTE_01", "ATTRIBUTE_02",..."ATTRIBUTE_99", and you want to scan each and every field to check if it is empty or it has a value, and copy whatever you find into an array (let's call it "L_My_Values").

    The most elegant way would be to write a loop like:


    :
    :

    FOR I in 1..99
    LOOP

    L_String := 'BEGIN :LL_Value := ATTRIBUTE_' || lpad(I,2,'0') || ' ; END ;' ;

    EXECUTE IMMEDIATE L_String USING OUT L_My_Values(I) ;

    END LOOP ;

    :
    :



    When trying this, it turns out that the ATTRIBUTE_xx are not visible within the scope of the block provided to the EXECUTE IMMEDIATE.

    Any suggestion?

    Thanks in advance,
    Fernando.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    using dynamic sql it is possible to solve rather complex problems...

    how you process data from the table: row by row or array ?

    here links which probably will help :

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01102

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABEDAHF

    http://www.oracle-developer.net/display.php?id=505

    One method for data transformation in Oracle, is pipelined table functions. The basic approach is as follows: https://forums.oracle.com/thread/2453123
     
  3. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Sergey,

    Thanks for your quick response.

    Please note that the key issue in what I'm trying to do, is to execute an "assignment" where the NAME of the source variable is only known during execution.

    For instance:

    AAA := BBB_<x>_CCC ;

    where <x> could be 1, 2, ... 59, making hence the assignments:

    AAA := BBB_1_CCC ;

    or

    AAA := BBB_2_CCC ;

    or

    AAA := BBB_59_CCC ;

    and only at run time it becomes known which variable needs to be used.

    Thanks,

    Fernando.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't understand why you think the execute immediate block can't see the column names; that's not true if I perform the actions as the table owner:


    Code (SQL):
    SQL> CREATE TABLE att_list(
      2  attribute_01       NUMBER,
      3  attribute_02       NUMBER,
      4  attribute_03       NUMBER,
      5  attribute_04       NUMBER,
      6  attribute_05       NUMBER,
      7  attribute_06       NUMBER,
      8  attribute_07       NUMBER,
      9  attribute_08       NUMBER,
     10  attribute_09       NUMBER,
     11  attribute_10       NUMBER,
     12  attribute_11       NUMBER,
     13  attribute_12       NUMBER,
     14  attribute_13       NUMBER,
     15  attribute_14       NUMBER,
     16  attribute_15       NUMBER,
     17  attribute_16       NUMBER,
     18  attribute_17       NUMBER,
     19  attribute_18       NUMBER,
     20  attribute_19       NUMBER,
     21  attribute_20       NUMBER,
     22  attribute_21       NUMBER,
     23  attribute_22       NUMBER,
     24  attribute_23       NUMBER,
     25  attribute_24       NUMBER,
     26  attribute_25       NUMBER,
     27  attribute_26       NUMBER,
     28  attribute_27       NUMBER,
     29  attribute_28       NUMBER,
     30  attribute_29       NUMBER,
     31  attribute_30       NUMBER,
     32  attribute_31       NUMBER,
     33  attribute_32       NUMBER,
     34  attribute_33       NUMBER,
     35  attribute_34       NUMBER,
     36  attribute_35       NUMBER,
     37  attribute_36       NUMBER,
     38  attribute_37       NUMBER,
     39  attribute_38       NUMBER,
     40  attribute_39       NUMBER,
     41  attribute_40       NUMBER,
     42  attribute_41       NUMBER,
     43  attribute_42       NUMBER,
     44  attribute_43       NUMBER,
     45  attribute_44       NUMBER,
     46  attribute_45       NUMBER,
     47  attribute_46       NUMBER,
     48  attribute_47       NUMBER,
     49  attribute_48       NUMBER,
     50  attribute_49       NUMBER,
     51  attribute_50       NUMBER,
     52  attribute_51       NUMBER,
     53  attribute_52       NUMBER,
     54  attribute_53       NUMBER,
     55  attribute_54       NUMBER,
     56  attribute_55       NUMBER,
     57  attribute_56       NUMBER,
     58  attribute_57       NUMBER,
     59  attribute_58       NUMBER,
     60  attribute_59       NUMBER,
     61  attribute_60       NUMBER,
     62  attribute_61       NUMBER,
     63  attribute_62       NUMBER,
     64  attribute_63       NUMBER,
     65  attribute_64       NUMBER,
     66  attribute_65       NUMBER,
     67  attribute_66       NUMBER,
     68  attribute_67       NUMBER,
     69  attribute_68       NUMBER,
     70  attribute_69       NUMBER,
     71  attribute_70       NUMBER,
     72  attribute_71       NUMBER,
     73  attribute_72       NUMBER,
     74  attribute_73       NUMBER,
     75  attribute_74       NUMBER,
     76  attribute_75       NUMBER,
     77  attribute_76       NUMBER,
     78  attribute_77       NUMBER,
     79  attribute_78       NUMBER,
     80  attribute_79       NUMBER,
     81  attribute_80       NUMBER,
     82  attribute_81       NUMBER,
     83  attribute_82       NUMBER,
     84  attribute_83       NUMBER,
     85  attribute_84       NUMBER,
     86  attribute_85       NUMBER,
     87  attribute_86       NUMBER,
     88  attribute_87       NUMBER,
     89  attribute_88       NUMBER,
     90  attribute_89       NUMBER,
     91  attribute_90       NUMBER,
     92  attribute_91       NUMBER,
     93  attribute_92       NUMBER,
     94  attribute_93       NUMBER,
     95  attribute_94       NUMBER,
     96  attribute_95       NUMBER,
     97  attribute_96       NUMBER,
     98  attribute_97       NUMBER,
     99  attribute_98       NUMBER,
    100  attribute_99       NUMBER);


    TABLE created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10 loop
      3                  INSERT INTO att_list
      4                  VALUES(i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,
    i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i);
      5          END loop;
      6
      7          commit;
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> DECLARE
      2          v_sqltxt varchar2(4000);
      3  BEGIN
      4          FOR i IN 1..99 loop
      5                  v_sqltxt:='update att_list set attribute_'||lpad(i,2,'0')||' = null where attribute_'||lpad(i,2,'0')||' = '||i;
      6                  EXECUTE immediate v_sqltxt;
      7          END loop;
      8
      9          commit;
     10  END;
     11  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SET linesize 4000 trimspool ON
    SQL>
    SQL> SELECT * FROM att_list ORDER BY attribute_01 NULLS FIRST
      2
    SQL> spool att_list_ex.log
    SQL> /


    ATTRIBUTE_01 ATTRIBUTE_02 ATTRIBUTE_03 ATTRIBUTE_04 ATTRIBUTE_05 ATTRIBUTE_06 ATTRIBUTE_07 ATTRIBUTE_08 ATTRIBUTE_09 ATTRIBUTE_10 ATTRIBUTE_
    11 ATTRIBUTE_12 ATTRIBUTE_13 ATTRIBUTE_14 ATTRIBUTE_15 ATTRIBUTE_16 ATTRIBUTE_17 ATTRIBUTE_18 ATTRIBUTE_19 ATTRIBUTE_20 ATTRIBUTE_21 ATTRIBU
    TE_22 ATTRIBUTE_23 ATTRIBUTE_24 ATTRIBUTE_25 ATTRIBUTE_26 ATTRIBUTE_27 ATTRIBUTE_28 ATTRIBUTE_29 ATTRIBUTE_30 ATTRIBUTE_31 ATTRIBUTE_32 ATTR
    IBUTE_33 ATTRIBUTE_34 ATTRIBUTE_35 ATTRIBUTE_36 ATTRIBUTE_37 ATTRIBUTE_38 ATTRIBUTE_39 ATTRIBUTE_40 ATTRIBUTE_41 ATTRIBUTE_42 ATTRIBUTE_43 A
    TTRIBUTE_44 ATTRIBUTE_45 ATTRIBUTE_46 ATTRIBUTE_47 ATTRIBUTE_48 ATTRIBUTE_49 ATTRIBUTE_50 ATTRIBUTE_51 ATTRIBUTE_52 ATTRIBUTE_53 ATTRIBUTE_5
    4 ATTRIBUTE_55 ATTRIBUTE_56 ATTRIBUTE_57 ATTRIBUTE_58 ATTRIBUTE_59 ATTRIBUTE_60 ATTRIBUTE_61 ATTRIBUTE_62 ATTRIBUTE_63 ATTRIBUTE_64 ATTRIBUT
    E_65 ATTRIBUTE_66 ATTRIBUTE_67 ATTRIBUTE_68 ATTRIBUTE_69 ATTRIBUTE_70 ATTRIBUTE_71 ATTRIBUTE_72 ATTRIBUTE_73 ATTRIBUTE_74 ATTRIBUTE_75 ATTRI
    BUTE_76 ATTRIBUTE_77 ATTRIBUTE_78 ATTRIBUTE_79 ATTRIBUTE_80 ATTRIBUTE_81 ATTRIBUTE_82 ATTRIBUTE_83 ATTRIBUTE_84 ATTRIBUTE_85 ATTRIBUTE_86 AT
    TRIBUTE_87 ATTRIBUTE_88 ATTRIBUTE_89 ATTRIBUTE_90 ATTRIBUTE_91 ATTRIBUTE_92 ATTRIBUTE_93 ATTRIBUTE_94 ATTRIBUTE_95 ATTRIBUTE_96 ATTRIBUTE_97
     ATTRIBUTE_98 ATTRIBUTE_99
    ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ----------
    -- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ -------
    ----- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ----
    -------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ -
    ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ -----------
    - ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ --------
    ---- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ -----
    ------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ --
    ---------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
     ------------ ------------
                            1            1            1            1            1            1            1        1                1
     1                1            1            1            1            1            1            1        1                1            1
        1            1            1            1            1            1            1            1        1                1            1
       1            1            1            1            1            1            1            1        1                1            1
      1            1            1            1            1            1            1            1        1        1            1            1
          1            1            1            1            1            1            1            1        1        1            1
     1                1            1            1            1            1            1            1        1                1            1
        1            1            1            1            1            1            1            1        1                1            1
       1            1            1            1            1            1            1            1        1                1            1
               2                         2            2            2            2            2            2        2                2
     2                2            2            2            2            2            2            2        2                2            2
        2            2            2            2            2            2            2            2        2                2            2
       2            2            2            2            2            2            2            2        2                2            2
      2            2            2            2            2            2            2            2        2        2            2            2
          2            2            2            2            2            2            2            2        2        2            2
     2                2            2            2            2            2            2            2        2                2            2
        2            2            2            2            2            2            2            2        2                2            2
       2            2            2            2            2            2            2            2        2                2            2
               3            3                         3            3            3            3            3        3                3
     3                3            3            3            3            3            3            3        3                3            3
        3            3            3            3            3            3            3            3        3                3            3
       3            3            3            3            3            3            3            3        3                3            3
      3            3            3            3            3            3            3            3        3        3            3            3
          3            3            3            3            3            3            3            3        3        3            3
     3                3            3            3            3            3            3            3        3                3            3
        3            3            3            3            3            3            3            3        3                3            3
       3            3            3            3            3            3            3            3        3                3            3
               4            4            4                         4            4            4            4        4                4
     4                4            4            4            4            4            4            4        4                4            4
        4            4            4            4            4            4            4            4        4                4            4
       4            4            4            4            4            4            4            4        4                4            4
      4            4            4            4            4            4            4            4        4        4            4            4
          4            4            4            4            4            4            4            4        4        4            4
     4                4            4            4            4            4            4            4        4                4            4
        4            4            4            4            4            4            4            4        4                4            4
       4            4            4            4            4            4            4            4        4                4            4
               5            5            5            5                         5            5            5        5                5
     5                5            5            5            5            5            5            5        5                5            5
        5            5            5            5            5            5            5            5        5                5            5
       5            5            5            5            5            5            5            5        5                5            5
      5            5            5            5            5            5            5            5        5        5            5            5
          5            5            5            5            5            5            5            5        5        5            5
     5                5            5            5            5            5            5            5        5                5            5
        5            5            5            5            5            5            5            5        5                5            5
       5            5            5            5            5            5            5            5        5                5            5
               6            6            6            6            6                         6            6        6                6
     6                6            6            6            6            6            6            6        6                6            6
        6            6            6            6            6            6            6            6        6                6            6
       6            6            6            6            6            6            6            6        6                6            6
      6            6            6            6            6            6            6            6        6        6            6            6
          6            6            6            6            6            6            6            6        6        6            6
     6                6            6            6            6            6            6            6        6                6            6
        6            6            6            6            6            6            6            6        6                6            6
       6            6            6            6            6            6            6            6        6                6            6
               7            7            7            7            7            7                         7        7                7
     7                7            7            7            7            7            7            7        7                7            7
        7            7            7            7            7            7            7            7        7                7            7
       7            7            7            7            7            7            7            7        7                7            7
      7            7            7            7            7            7            7            7        7        7            7            7
          7            7            7            7            7            7            7            7        7        7            7
     7                7            7            7            7            7            7            7        7                7            7
        7            7            7            7            7            7            7            7        7                7            7
       7            7            7            7            7            7            7            7        7                7            7
               8            8            8            8            8            8            8                     8                8
     8                8            8            8            8            8            8            8        8                8            8
        8            8            8            8            8            8            8            8        8                8            8
       8            8            8            8            8            8            8            8        8                8            8
      8            8            8            8            8            8            8            8        8        8            8            8
          8            8            8            8            8            8            8            8        8        8            8
     8                8            8            8            8            8            8            8        8                8            8
        8            8            8            8            8            8            8            8        8                8            8
       8            8            8            8            8            8            8            8        8                8            8
               9            9            9            9            9            9            9            9                 9            9
      9            9            9            9            9            9            9            9        9        9            9            9
          9            9            9            9            9            9            9            9        9        9            9
     9                9            9            9            9            9            9            9        9                9            9
        9            9            9            9            9            9            9            9        9                9            9
       9            9            9            9            9            9            9            9        9                9            9
      9            9            9            9            9            9            9            9        9        9            9            9
          9            9            9            9            9            9            9            9        9        9            9
     9                9            9            9            9            9            9            9        9
              10           10           10           10           10           10           10           10       10
    10               10           10           10           10           10           10           10       10               10           10
       10           10           10           10           10           10           10           10       10               10           10
      10           10           10           10           10           10           10           10       10               10           10
     10           10           10           10           10           10           10           10       10       10           10           10
         10           10           10           10           10           10           10           10       10       10           10
    10               10           10           10           10           10           10           10       10               10           10
       10           10           10           10           10           10           10           10       10               10           10
      10           10           10           10           10           10           10           10       10               10           10


    10 ROWS selected.


    SQL> spool off
    SQL>

    As you can see the execute immediate block in this example can 'see' the column names without issue. If we try another user, with only select privilege on the table and no synonyms to translate the table name into owner.table_name syntax we see:


    Code (SQL):
    SQL> GRANT SELECT ON att_list TO bong;
    GRANT succeeded.
    SQL>
    SQL> CONNECT bong/bing
    Connected.
    SQL>
    SQL> DECLARE
      2          v_sqltxt varchar2(4000);
      3  BEGIN
      4          FOR i IN 1..99 loop
      5                  v_sqltxt:='select attribute_'||lpad(i,2,'0')||' from att_list';
      6                  EXECUTE immediate v_sqltxt;
      7          END loop;
      8
      9          commit;
     10  END;
     11  /
    DECLARE
    *
    ERROR at line 1:
    ORA-00942: TABLE OR VIEW does NOT exist
    ORA-06512: at line 6

    SQL>

    which is why the column names aren't visible.
     
  5. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Zargon,

    Many thanks for your detailed response.

    Indeed, things work perfectly (as you described) when you perform operations vis-à-vis DB tables, views, etc. The problem is not there. I'm including here a small code sample through which my intention may be better described.

    Code (SQL):

    SET serveroutput ON

    DECLARE

      Dummy_1 NUMBER := 334 ;
      Dummy_2 NUMBER := 567 ;
      Target  NUMBER        ;
     
      Which   NUMBER        ;
     
      L_Str   VARCHAR2(512) ;

    BEGIN

        Which := 1  ;
       
        l_Str := '
                  BEGIN
                 
                      :1 := Dummy_'
    || Which || ' ;
                     
                  END ;'
    ;
       
        DBMS_OUTPUT.PUT_LINE('Code to be executed is:' || CHR(10) || l_Str) ;
       
        EXECUTE IMMEDIATE l_Str USING OUT Target ;
       
        DBMS_OUTPUT.PUT_LINE('Result is:' || Target) ;
       

    END ;
     
    If you run this simple example, you will get the following error message:

    Error report:
    ORA-06550: line 4, column 25:
    PLS-00201: identifier 'DUMMY_1' must be declared
    ORA-06550: line 4, column 19:
    PL/SQL: Statement ignored
    ORA-06512: at line 24
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
    Code to be executed is:

    BEGIN

    :1 := Dummy_1 ;

    END ;

    It is very possible that I'm doing something wrong, but I can't really see that it could be.

    Thanks again,
    Fernando.
     
  6. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Zargon,

    Many thanks for your detailed response.

    Indeed, things work perfectly (as you described) when you perform operations vis-à-vis DB tables, views, etc. The problem is not there. I'm including here a small code sample through which my intention may be better described.

    Code (SQL):

    SET serveroutput ON

    DECLARE

      Dummy_1 NUMBER := 334 ;
      Dummy_2 NUMBER := 567 ;
      Target  NUMBER        ;
     
      Which   NUMBER        ;
     
      L_Str   VARCHAR2(512) ;

    BEGIN

        Which := 1  ;
       
        l_Str := '
                  BEGIN
                 
                      :1 := Dummy_'
    || Which || ' ;
                     
                  END ;'
    ;
       
        DBMS_OUTPUT.PUT_LINE('Code to be executed is:' || CHR(10) || l_Str) ;
       
        EXECUTE IMMEDIATE l_Str USING OUT Target ;
       
        DBMS_OUTPUT.PUT_LINE('Result is:' || Target) ;
       

    END ;
     
    If you run this simple example, you will get the following error message:

    Error report:
    ORA-06550: line 4, column 25:
    PLS-00201: identifier 'DUMMY_1' must be declared
    ORA-06550: line 4, column 19:
    PL/SQL: Statement ignored
    ORA-06512: at line 24
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
    Code to be executed is:

    BEGIN

    :1 := Dummy_1 ;

    END ;

    It is very possible that I'm doing something wrong, but I can't really see that it could be.

    Thanks again,
    Fernando.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    the DUMMY variable is local variable of the unit and isn't visible in the unit of dynamic sql/plsql
     
  8. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Well Sergey, if you take a look at the initial post, that is exactly my problem.

    I need a way to perform this type of assignments when the name of the "source" variable (i.e. the one at the right of the ":=") is only known at run time (as described in the example of my previous post).
     
  9. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Well Sergey, if you take a look at the initial post, that is exactly my problem.

    I need a way to perform this type of assignments when the name of the "source" variable (i.e. the one at the right of the ":=") is only known at run time (as described in the example of my previous post).
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):

    SET serveroutput ON
    CREATE OR REPLACE package pkgvar
    IS
      dummy_1 NUMBER ;
      dummy_2 NUMBER ;
    END;
    /

    DECLARE
      Target  NUMBER;
      Which   NUMBER;
      L_Str   VARCHAR2(512) ;
    BEGIN
      pkgvar.dummy_1  := 334 ;
      pkgvar.dummy_2  := 567 ;
       
        Which := 1  ;
        l_Str := '
                  BEGIN
     
                      :1 := pkgvar.Dummy_'
    || Which || ' ;
     
                  END ;'
    ;
        DBMS_OUTPUT.PUT_LINE('Code to be executed is:' || CHR(10) || l_Str) ;
     
        EXECUTE IMMEDIATE l_Str USING OUT Target ;
     
        DBMS_OUTPUT.PUT_LINE('Result is:' || Target) ;
     
     
    END ;

     
     
    FDavidov likes this.
  11. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Sergey,

    Thank you very much for you help. Indeed, it works but...

    Apparently, when you run an "EXECUTE IMMEDIATE" it has its own context and scope (within the same schema/user though) and hence it does not see anything that is defined within the function/procedure/block it is invoked from. When you use variables within a package, everyone has visibility to what the package exposes and hence your result.

    I must say, however, that it looks quite strange to me that you would need to create a package for the sole purpose of having variable definitions nobody cares for.

    Still I very much appreciate your help and, if you have any additional direction it would be worth exploring, I'll be very much thankful.

    All the best,
    Fernando.
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    ....all the codes is given for an example...

    yes, global variables require attention in the course of use...

    other possible option... to create a packet with SERIALLY_REUSABLE in specification package

    Code (SQL):


    SET serveroutput ON
    CREATE OR REPLACE package pkgvar
    IS
      dummy_1 NUMBER := 100; -- default
      dummy_2 NUMBER := 200; -- default
      pragma serially_reusable;
    END;
    /
     
    DECLARE
      Target  NUMBER;
      Which   NUMBER;
      L_Str   VARCHAR2(512) ;
    BEGIN
      pkgvar.dummy_1  := 334 ;
      pkgvar.dummy_2  := 567 ;
     
        Which := 1  ;
        l_Str := '
                  BEGIN
     
                      :1 := pkgvar.Dummy_'
    || Which || ' ;
     
                  END ;'
    ;
        DBMS_OUTPUT.PUT_LINE('Step_1: Code to be executed is:' || CHR(10) || l_Str) ;
     
        EXECUTE IMMEDIATE l_Str USING OUT Target ;
     
        DBMS_OUTPUT.PUT_LINE('Result is:' || Target) ;
     
    END ;
    /


    DECLARE
      Target  NUMBER;
      Which   NUMBER;
      L_Str   VARCHAR2(512) ;
    BEGIN

        Which := 1  ;
        l_Str := '
                  BEGIN
     
                      :1 := pkgvar.Dummy_'
    || Which || ' ;
     
                  END ;'
    ;
        DBMS_OUTPUT.PUT_LINE('Step_2: Code to be executed is:' || CHR(10) || l_Str) ;
     
        EXECUTE IMMEDIATE l_Str USING OUT Target ;
     
        DBMS_OUTPUT.PUT_LINE('Result is:' || Target) ;
     
    END ;
    /

    SQL >

    Package created
     
    Step_1: Code TO be executed IS:

                  BEGIN

                      :1 := pkgvar.Dummy_1 ;

                  END ;
    RESULT IS:334
     
    PL/SQL PROCEDURE successfully completed
     
    Step_2: Code TO be executed IS:

                  BEGIN

                      :1 := pkgvar.Dummy_1 ;

                  END ;
    RESULT IS:100
     
    PL/SQL PROCEDURE successfully completed
     
     

    next variant...


    Code (SQL):


    SET serveroutput ON SIZE 1000000

    CREATE OR REPLACE package pkgvar

    IS
     
      list_dummy  sys.odcinumberlist ;

      PRAGMA SERIALLY_REUSABLE;
    END;
    /

    DECLARE
     
      Target  NUMBER        ;

      L_Str   VARCHAR2(512) ;
     
    BEGIN    

        -- init varray    
        pkgvar.list_dummy := sys.odcinumberlist();
        -- extend varray
        pkgvar.list_dummy.extend(99);
       
        -- init of value
        FOR z IN 1 .. 99
        loop
              pkgvar.list_dummy (z) := z;
             
        END loop;    
       
        l_Str := '
                  BEGIN
     
                      :1 := pkgvar.list_dummy (:2);
     
                  END ;'
    ;
     
        DBMS_OUTPUT.PUT_LINE('Code to be executed is:' || CHR(10) || l_Str) ;
       
        dbms_output.new_line;
       
        FOR Which IN 1 .. 99
        loop          
            EXECUTE IMMEDIATE l_Str USING OUT Target, Which;
            DBMS_OUTPUT.PUT_LINE('Result is list_dummy('||which||')='|| Target) ;
        END loop;    
       
       
    END ;
    /


    rem this CALL = > raise error
    rem the error IS raised an  therefore AS now VARIABLES are SET IN VALUES BY DEFAULT (i.e. an array empty)

    DECLARE
     
      Target  NUMBER        ;

      L_Str   VARCHAR2(512) ;

    BEGIN
        l_Str := '
                  BEGIN
     
                      :1 := pkgvar.list_dummy (:2);
     
                  END ;'
    ;
       
        dbms_output.put_line('Step 2');    
        FOR Which IN 1 .. 99
        loop          
            EXECUTE IMMEDIATE l_Str USING OUT Target, Which;
            DBMS_OUTPUT.PUT_LINE('Result is list_dummy('||which||')='|| Target) ;
        END loop;    
    END;
    /    


    SQL>

     Connected TO Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0

     SQL>
     
    Package created
     
    Code TO be executed IS:

                  BEGIN

                      :1 := pkgvar.list_dummy (:2);

                  END ;
     
    RESULT IS list_dummy(1)=1
    RESULT IS list_dummy(2)=2
    RESULT IS list_dummy(3)=3
    RESULT IS list_dummy(4)=4
    RESULT IS list_dummy(5)=5
    RESULT IS list_dummy(6)=6
    RESULT IS list_dummy(7)=7
    RESULT IS list_dummy(8)=8
    RESULT IS list_dummy(9)=9
    RESULT IS list_dummy(10)=10
    RESULT IS list_dummy(11)=11
    RESULT IS list_dummy(12)=12
    RESULT IS list_dummy(13)=13
    RESULT IS list_dummy(14)=14
    RESULT IS list_dummy(15)=15
    RESULT IS list_dummy(16)=16
    RESULT IS list_dummy(17)=17
    RESULT IS list_dummy(18)=18
    RESULT IS list_dummy(19)=19
    RESULT IS list_dummy(20)=20
    RESULT IS list_dummy(21)=21
    RESULT IS list_dummy(22)=22
    RESULT IS list_dummy(23)=23
    RESULT IS list_dummy(24)=24
    RESULT IS list_dummy(25)=25
    RESULT IS list_dummy(26)=26
    RESULT IS list_dummy(27)=27
    RESULT IS list_dummy(28)=28
    RESULT IS list_dummy(29)=29
    RESULT IS list_dummy(30)=30
    RESULT IS list_dummy(31)=31
    RESULT IS list_dummy(32)=32
    RESULT IS list_dummy(33)=33
    RESULT IS list_dummy(34)=34
    RESULT IS list_dummy(35)=35
    RESULT IS list_dummy(36)=36
    RESULT IS list_dummy(37)=37
    RESULT IS list_dummy(38)=38
    RESULT IS list_dummy(39)=39
    RESULT IS list_dummy(40)=40
    RESULT IS list_dummy(41)=41
    RESULT IS list_dummy(42)=42
    RESULT IS list_dummy(43)=43
    RESULT IS list_dummy(44)=44
    RESULT IS list_dummy(45)=45
    RESULT IS list_dummy(46)=46
    RESULT IS list_dummy(47)=47
    RESULT IS list_dummy(48)=48
    RESULT IS list_dummy(49)=49
    RESULT IS list_dummy(50)=50
    RESULT IS list_dummy(51)=51
    RESULT IS list_dummy(52)=52
    RESULT IS list_dummy(53)=53
    RESULT IS list_dummy(54)=54
    RESULT IS list_dummy(55)=55
    RESULT IS list_dummy(56)=56
    RESULT IS list_dummy(57)=57
    RESULT IS list_dummy(58)=58
    RESULT IS list_dummy(59)=59
    RESULT IS list_dummy(60)=60
    RESULT IS list_dummy(61)=61
    RESULT IS list_dummy(62)=62
    RESULT IS list_dummy(63)=63
    RESULT IS list_dummy(64)=64
    RESULT IS list_dummy(65)=65
    RESULT IS list_dummy(66)=66
    RESULT IS list_dummy(67)=67
    RESULT IS list_dummy(68)=68
    RESULT IS list_dummy(69)=69
    RESULT IS list_dummy(70)=70
    RESULT IS list_dummy(71)=71
    RESULT IS list_dummy(72)=72
    RESULT IS list_dummy(73)=73
    RESULT IS list_dummy(74)=74
    RESULT IS list_dummy(75)=75
    RESULT IS list_dummy(76)=76
    RESULT IS list_dummy(77)=77
    RESULT IS list_dummy(78)=78
    RESULT IS list_dummy(79)=79
    RESULT IS list_dummy(80)=80
    RESULT IS list_dummy(81)=81
    RESULT IS list_dummy(82)=82
    RESULT IS list_dummy(83)=83
    RESULT IS list_dummy(84)=84
    RESULT IS list_dummy(85)=85
    RESULT IS list_dummy(86)=86
    RESULT IS list_dummy(87)=87
    RESULT IS list_dummy(88)=88
    RESULT IS list_dummy(89)=89
    RESULT IS list_dummy(90)=90
    RESULT IS list_dummy(91)=91
    RESULT IS list_dummy(92)=92
    RESULT IS list_dummy(93)=93
    RESULT IS list_dummy(94)=94
    RESULT IS list_dummy(95)=95
    RESULT IS list_dummy(96)=96
    RESULT IS list_dummy(97)=97
    RESULT IS list_dummy(98)=98
    RESULT IS list_dummy(99)=99
     
    PL/SQL PROCEDURE successfully completed
     
    Step 2
     
    DECLARE

      Target  NUMBER        ;

      L_Str   VARCHAR2(512) ;

    BEGIN
        l_Str := '
                  BEGIN

                      :1 := pkgvar.list_dummy (:2);

                  END ;'
    ;

        dbms_output.put_line('Step 2');
        FOR Which IN 1 .. 99
        loop
            EXECUTE IMMEDIATE l_Str USING OUT Target, Which;
            DBMS_OUTPUT.PUT_LINE('Result is list_dummy('||which||')='|| Target) ;
        END loop;
    END;
     
    ORA-06531: Reference TO uninitialized collection
    ORA-06512: at line 4
    ORA-06512: at line 19
     
    SQL>

     
     
  13. FDavidov

    FDavidov Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Sergey,

    Got your suggestion about using the "SERIALLY_REUSABLE" pragma. This is much better indeed. Will need to do some tests about performance though.

    Many thanks,
    Fernando.