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
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.
TYPE list_blank_sections_type IS TABLE OF VARCHAR2(50);
blank_secs list_blank_sections_type := list_blank_sections_type(); -- initialise
FOR i IN 1 .. 10 LOOP
blank_secs(blank_secs.last) := 'SECTION_' || i;
FOR i IN 1 .. 10 LOOP
dbms_output.put_line(i || ' - ' || blank_secs(i));
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
TYPE list_blank_sections_type IS VARRAY(20) of VARCHAR2(50);
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?
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.