Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

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

0
kencrest
Asked:
kencrest
  • 5
  • 3
  • 2
2 Solutions
 
kencrestAuthor Commented:
...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
0
 
ravindran_eeeCommented:
You have provided the code for defining the record. How is the record populated? Is there any cursor defined?
0
 
ajexpertCommented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kencrestAuthor Commented:
...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;
0
 
kencrestAuthor Commented:
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
0
 
ajexpertCommented:
Sorry for typo
 
Here is correct one

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

Open in new window

0
 
kencrestAuthor Commented:
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;    
0
 
ravindran_eeeCommented:
Try this..

FOR i in counted_assets_record.asset_filename.FIRST..counted_assets.asset_filename.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;  
0
 
kencrestAuthor Commented:
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
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now