how to set up Array using pl/sql

[mdl2]

I need to create a flat file of case information in fixed columns from an oracle database. There will be one output record per case. I may have to output up to 5 dependents on 1 record for a case -- Simplified version

Table 1 table 2
case name case
Case 123 Johnny 123
Case 234 ben 123
mary 123
jane 123
dick 123
bill 123
ann 234

at the end of each case I want to print
case 123 johnny ben mary jane bill
case 234 ann

bill will be ignored as he is 6th person on case

and all others will be printed in specific columns

is this the way to use an index in oracle pl/sql

type desc_typ is table of char(15)
index by binary_integer;

V_CLIENT_name desc_typ;

FOR BENEFICIARY_REC IN BENEFICIARY LOOP /* BENEFICIARY LOOP */

BEGIN /* BENEFICIARY ASSIGNMENT OF VALUES */

V_BENF_COUNT := V_BENF_COUNT + 1;

V_CLIENT_name (V_BENF_COUNT) := NVL(BENEFICIARY_REC.CLIENT_name ,' ');
end;

...
V_TEXT := (V_FILE_NO,V_CLIENT_name(1),V_CLIENT_name(2),V_CLIENT_name(3),V_CLIENT_name(4),V_CLIENT_name(5));
UTL_FILE.PUT_LINE (FILE_ID,SUBSTR(v_text,1,2209));

 

[SantaMufasa]

MDL,

Do you want/need to use PL/SQL and its arrays? If not, then you can probably do your formatting in SQL only if you prefer.

...is this the way to use an index in oracle pl/sql?

The manner you asked about is certainly fine for referencing and incrementing PL/SQL array indexes, but if you were not using a PL/SQL "CURSOR 'FOR' loop" to drive your loop, then you can use this construct:

   for i in 1..<numeric expression of ending value> LOOP
        tab(i) := <expression>;
   end loop;
...

Let us know if you need more followup.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.