0 Replies Latest reply: Aug 20, 2013 2:41 AM by user9130445 RSS

    Bug (?) with nested pl/sql table

    user9130445

      Greetings,

       

      In the below script I am using a pl/sql table where each element is a record having 2 fields: a varchar2 and another pl/sql table (nested),

      I have a procedure for loading data into that pl/sql table and a routine that displays it's contents (in real world it processes the data).

      The problem appears to be in the Show_Data routine where during iteration of "main" pl/sql table I assign the current record in a respective variable for simplicity of use.

      The thing is that this assignment is not (allways) working!

      For example I placed a RAISE_APPLICATION_ERROR inside an IF which normally should never be called because it checks if records exists in the nested table.

      Records do exist because of earlier call to Load_Data.

      The thing is that it is called and the error is raised dispite the fact that data do exist.

      In direct reference to the "main" pl/sql table record the error is not raised.

      So I assume that there is something wrong with the assignment of the records into the respective variable.

       

      The strange thing is that if I uncomment the DBMS_OUTPUT.PUT_LINE command in line 53, then the RAISE_APPLICATION_ERROR is not executed because now for some reason the data are found! (lots of hours to find that out...)

       

      Another way to make it run is to move the declarations of the records and pl/sql tables from the body of the anonymous pl/sql block inside the Main_Test procedure (move to line 24).

      Also I could make it to work if in the given version I uncomment the "A DATE;" declaration and a few lines below the "A := SYSDATE;" statement.

       

      Is this a bug and what is the official workaround for this?

       

      (Note: the line numbers I mentioned may vary due to re-formatting of my text during copy-paste)

       

      Script:

       

      DECLARE

         --Move declarations inside Main_Test

         TYPE trec_NESTED_DATA

         IS RECORD

           (FIELD_1 VARCHAR2(100),

            FIELD_2 VARCHAR2(100));

          

         TYPE ttbl_NESTED_DATA

         IS TABLE OF trec_NESTED_DATA

         INDEX BY PLS_INTEGER;

       

         TYPE trec_DATA

         IS RECORD

           (INFO VARCHAR2(100),

            NESTED_DATA ttbl_NESTED_DATA);

       

         TYPE ttbl_DATA

         IS TABLE OF trec_DATA

         INDEX BY PLS_INTEGER;

       

       

         PROCEDURE Main_Test

         IS



            PROCEDURE Load_Data

              (rtbl_DATA OUT ttbl_DATA)

            IS

            BEGIN

               rtbl_DATA(1).INFO := 'TEST DATA';

               rtbl_DATA(1).NESTED_DATA(1).FIELD_1 := '1.FIELD1';

               rtbl_DATA(1).NESTED_DATA(1).FIELD_2 := '1.FIELD2';

               rtbl_DATA(1).NESTED_DATA(2).FIELD_1 := '2.FIELD1';

               rtbl_DATA(1).NESTED_DATA(2).FIELD_2 := '2.FIELD2';

            END Load_Data;

       

            PROCEDURE Show_Data

              (ptbl_DATA IN ttbl_DATA)

            IS

               num_Index        PLS_INTEGER;

               rec_DATA         trec_DATA;

               vch_Message      VARCHAR2(4000);

               --A                DATE; --UNCOMMENT TO WORK! v2

            BEGIN

       

               num_Index := ptbl_DATA.FIRST;

               <<DATES_LOOP>>

               WHILE num_Index IS NOT NULL LOOP

       

                  rec_DATA := NULL;

       

                  --DBMS_OUTPUT.PUT_LINE(''); --UNCOMMENT TO WORK!

                  --A := SYSDATE; --UNCOMMENT TO WORK! v2

       

                  rec_DATA := ptbl_DATA(num_Index);

       

                  vch_Message :=

                    '   ['||TO_CHAR(num_Index)||'], NESTED_DATA ['||TO_CHAR(rec_DATA.NESTED_DATA.COUNT)||']';

                     

             

                  IF rec_DATA.NESTED_DATA.COUNT = 0 THEN

                     RAISE_APPLICATION_ERROR(-20000,'Zero records!');

                  END IF;

       

                  DBMS_OUTPUT.PUT_LINE(vch_Message);

       

                  num_Index := ptbl_DATA.NEXT(num_Index);

               END LOOP DATES_LOOP;

       

            END Show_Data;

       

       

            PROCEDURE Test

            IS

               tbl_DATA ttbl_DATA;

            BEGIN

       

               Load_Data

                 (rtbl_DATA => tbl_DATA);

                

               Show_Data

                 (ptbl_DATA => tbl_DATA);

       

            END Test;

       

         BEGIN

            Test;

         END Main_Test;

       

      BEGIN

       

         Main_Test;

       

      END;

       

       

       

      Thansk In advance,
      Dimitris

       

      PS. The Oracle XE versions I used:

      1. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit for Linux

      2. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 32bit for Windows