• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1670
  • Last Modified:

PL/SQL LOOP and SUBSTR Question

Hello...
I have 2 questions for the PL/SQL experts here. I'm trying to loop thru a record of filenames that I add to along the way in my pl/sql procedure. I'm use to dealing with cursors that are based on table types and not just a plain record of information. So...1) how do I properly loop through a bunch of plain records? I'm use to "Fetch Into" for a loop...but this record type is not a cursor (see my code below) Do I have it correct?

 ....and my second question is 2) how do i compare two substring values to each other?  (see my code below). I'm getting an error back from the Oracle compiler saying that it was expecting a "=" when that is what I actually have. I'm a bit stumped.

Thx!
Ken
code snippet...
 
  CURSOR bookpagekey_cursor IS
       SELECT e.record_id,
              e.book_page_key,
              e.content_category,
              d.file_name
       FROM vsdm.editorial e, vsdm.doc_renditions d
       WHERE e.record_id=d.record_id;
    
    bookpagekey_record       bookpagekey_cursor%ROWTYPE;
    
    
    TYPE counted_assets IS RECORD
       (asset_filename    varchar2(64));
    
    counted_assets_record    counted_assets;
 
...
 
proceed:='Y';
                   While proceed:='Y' 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;   
...
-- I insert filenames into the RECORD further below (bottom) in the procedure (snippet below)
  IF ((bookpagekey_record.content_category LIKE '%Primary Image%') AND
                           (bookcode = SUBSTR(bookpagekey_record.book_page_key, 2, 2)))  
                           AND                
                          ((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;

Open in new window

0
kencrest
Asked:
kencrest
1 Solution
 
gatorvipCommented:
>>While proceed:='Y' LOOP

This is where you're getting the "=" error , not in the substr. Change that to "=" instead of ":="
0
 
kencrestAuthor Commented:
Nice Catch! Thx for spotting that. I have another PL/SQL question I'm posting. If you could answer it for me asap I'd appreciate it. Thx again!

Ken
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now