7 Replies Latest reply: Oct 9, 2013 5:38 AM by Billy~Verreynne RSS

    PL/SQL Insert values into a nested table or array with a loop

    NewApexCoder

      Greetings

       

      I am a little confused on how to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario:

       

      I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. Any help or clarity on this matter would be much appreciated. I've posted somewhat of a pseudo coded example below

       

       

      If  (you_belong_in_table)  then
            variable := store_me_in_varray      /* OR */
            variable := array_type(sector.sector_name)
            i := i + 1;
      end if;
      
      /* Now we output our varray or table */
      
      start loop
      output(sector names one by one)
      end loop
      
      

       

      I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.

       

      THanks in advance for any help

        • 1. Re: PL/SQL Insert values into a nested table or array with a loop
          padders

          Did you declare a nested table or varray type somewhere? If so please post declaration.

          • 2. Re: PL/SQL Insert values into a nested table or array with a loop
            NewApexCoder

            TYPE list_blank_sections_type  IS VARRAY(20) of VARCHAR2(50);

            blank_secs list_blank_sections_type

             

            Is that correct>?

            • 3. Re: PL/SQL Insert values into a nested table or array with a loop
              padders

              I would probably use nested table, varray adds nothing except a limit and takes away a number of options.

               

              Code for varry and nested table is pretty much identical here though except for the declaration.

               

              e.g.

               

              DECLARE

                 TYPE list_blank_sections_type IS TABLE OF VARCHAR2(50);

               

                 blank_secs list_blank_sections_type := list_blank_sections_type(); -- initialise

              BEGIN

                 -- populate

                 FOR i IN 1 .. 10 LOOP

                    blank_secs.extend;

                    blank_secs(blank_secs.last) := 'SECTION_' || i;

                 END LOOP;

               

                 -- print

                 FOR i IN 1 .. 10 LOOP

                    dbms_output.put_line(i || ' - ' || blank_secs(i));

                 END LOOP;

              END;

              /

              • 4. Re: PL/SQL Insert values into a nested table or array with a loop
                NewApexCoder

                Well I've incorporated some of your code but I keep getting the error

                ORA-06533: Subscript beyond count

                 

                Trying to figure out why now.

                • 5. Re: PL/SQL Insert values into a nested table or array with a loop
                  NewApexCoder

                  I appreciate your help but Im not sure that the example above best suits what I already have. It's kind of throwing me off. I will pseudocode what I have as it is too much to copy n paste into here.

                   

                  BEGIN
                  output (Title of document)
                  output (Purpose of document)
                  
                  loop
                  fetch sectors into csectors
                  sector_id := csector.inspection_sector_id;
                  
                  SELECT count(*) into has_incid
                        FROM inspection_feat_report_view
                        where HIGH_WATER_EVENT_ID = high_water_eventid
                        and INSPECTION_SECTOR_ID = sector_id;
                  
                  if(has_incid != 0) then
                  DO STUFF
                  end if;
                  
                  if(has_incid = 0) then
                  HERE IS WHERE I NEED TO ADD INFORMATION TO MY TABLE OR VARRAY
                  NOTICE I AM STILL IN MY INITIAL LOOP
                  SO WOULD I NEED TO INCREMENT A COUNTER HERE?
                  HOW WOULD THE SYNTAX LOOK TO ADD VALUES TO A TABLE / VARRAY HERE?
                  end if;
                  
                  end loop
                  
                  Now i can print out everything from within my table using another loop
                  
                  
                  • 6. Re: PL/SQL Insert values into a nested table or array with a loop
                    padders

                    > HOW WOULD THE SYNTAX LOOK TO ADD VALUES TO A TABLE / VARRAY HERE?

                     

                    Pretty much as I posted.

                     

                    > ORA-06533: Subscript beyond count

                     

                    What line did this error occur on?

                    • 7. Re: PL/SQL Insert values into a nested table or array with a loop
                      Billy~Verreynne

                      NewApexCoder wrote:

                       

                      TYPE list_blank_sections_type  IS VARRAY(20) of VARCHAR2(50);

                      blank_secs list_blank_sections_type

                       

                      Is that correct>?

                      I personally do not like VARRAYS, or see any compelling reason to use these. Why constrain the number of elements in an array - especially as code and data seldom are static?

                       

                      Some basics.

                       

                      Defining an (unconstrained and standard) array:

                      declare
                         type TStringArray is table of varchar2(100);
                        myArray TStringArray;
                      
                      

                       

                      Constructing an empty array:

                      begin
                        myArray := new TStringArrray();
                      
                      

                       

                      Constructing an array with default values:

                      begin
                        myArray := new TStringArrray( 'Tom', 'D1ck', 'Harry', 'Sally' );
                      
                      

                       

                      Constructing an array using SQL:

                      begin
                        select distinct object_type bulk collect into myArray from user_objects order by 1;
                      
                      

                       

                      Extending an array:

                      begin
                         ..array was constructed (and optionally populated)
                        myArray.Extend( 5 ); -- add 5 new array cells (as nulls)
                      
                      

                       

                      Looping through an array (do not use .First and .Last methods unless you fully realise the consequences of that):

                      begin
                         ...
                        for i in 1..myArray.Count loop
                          ..do something with myArray(i)
                        end loop;
                      
                      

                       

                      None of this should be foreign to a programmer that has dealt with structured programming languages, with the exception of using SQL bulk collection to perform (implicit in this case) bulk fetching. And of course the fact that unlike other languages, array offsets start at 1 in PL/SQL and not 0.