asked on

How to properly Loop thru a User Defined Record in PL/SQL

Hello,
I'm trying to loop thru a PL/SQL "User Defined Record" that has just one value to it which holds a bunch of filenames that I add along the way in my pl/sql procedure. So...1) how do I properly loop through a bunch of plain records? Do I need to do a Fetch like a cursor? I don't know how many records to loop thru so what can I do? I'm use to "Fetch Into" for a loop...but this record type is not a cursor (see my code below).
Thx!
Ken

(snippet)...
 
    TYPE counted_assets IS RECORD
       (asset_filename    varchar2(64));
    
    counted_assets_record    counted_assets;
 
proceed:='Y';
                   FOR counted_assets_record IN counted_assets LOOP
                      IF (SUBSTR(bookpagekey_record.file_name, 1, 7) = SUBSTR(counted_assets_record.asset_filename, 1, 7))
                      AND (bookpagekey_record.content_category LIKE '%Primary Image%')
                      THEN proceed:='N';
                      END IF;
                   END LOOP;

Open in new window

kencrest

ASKER

...forgot to add that I get an Oracle error with this syntax.

Error(70,49): PLS-00330: invalid use of type name or subtype name on Line 9

Ravindran Gopinathan

You have provided the code for defining the record. How is the record populated? Is there any cursor defined?

ajexpert

For looping thru collection, the synax is

 FOR i in counted_assets_record.first IN counted_assets.last LOOP
 
Just replace this and try
 
Hope this helps

Open in new window

kencrest

ASKER

...the population of the record occurs further down in the procedure....(snippet below)

IF ((bookpagekey_record.content_category LIKE '%Primary Image%') AND
(bookcode = SUBSTR(bookpagekey_record.book_page_key, 2, 2)))
AND
-- EXCLUDE RC'S (recolor pickups) / EDIT'S (edit pickups), T4G'S, SELECT'S (alternate images), AND SPLIT/COMBO IMAGES
((INSTR(bookpagekey_record.file_name, UPPER('_RC'), 1)=0) AND
(INSTR(bookpagekey_record.file_name, UPPER('_EDIT'), 1)=0) AND
(SUBSTR(bookpagekey_record.file_name, -4, 1) <> '_') AND
(SUBSTR(bookpagekey_record.file_name, -2, 1) <> '_') AND
(INSTR(bookpagekey_record.file_name, UPPER('SPLIT'), 1)=0) AND
(INSTR(bookpagekey_record.file_name, UPPER('COMBO'), 1)=0))

THEN PINewScanCount:=PINewScanCount+1;
counted_assets_record.asset_filename:=bookpagekey_record.file_name;
END IF;

kencrest

ASKER

OK ajexpert...i gave it a try and got this Oracle error...

FOR i in counted_assets_record.first IN counted_assets.last LOOP
IF (SUBSTR(bookpagekey_record.file_name, 1, 7) = SUBSTR(counted_assets_record.asset_filename, 1, 7))
AND (bookpagekey_record.content_category LIKE '%Primary Image%')
THEN proceed:='N';
END IF;
END LOOP;

Error(70,57): PLS-00103: Encountered the symbol "IN" when expecting one of the following: . ( * @ % & - + / at loop mod remainder range rem .. <an exponent (**)> || multiset

ajexpert

Sorry for typo

Here is correct one

 FOR i in counted_assets_record.first..counted_assets.last LOOP

Open in new window

kencrest

ASKER

ok...i know get...
Error(70,51): PLS-00302: component 'FIRST' must be declared

FOR i in counted_assets_record.first..counted_assets.last LOOP
IF (SUBSTR(bookpagekey_record.file_name, 1, 7) = SUBSTR(counted_assets_record.asset_filename, 1, 7))
AND (bookpagekey_record.content_category LIKE '%Primary Image%')
THEN proceed:='N';
END IF;
END LOOP;

ASKER CERTIFIED SOLUTION

ajexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ravindran Gopinathan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kencrest

ASKER

Hey guys, I read up on using FIRST..LAST syntax in a LOOP and tried implementing it. It didn't work at all. It seems to me (imho) that FIRST..LAST LOOP scenarios do not work for User Defined Records...perhaps only for Cursor type Records. I wound up using a counter variable to establish the loop. Thanks for trying.

Ken