Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to put entries in a for loop without hard coding the values

564814Apr 1 2011 — edited Apr 1 2011
I have a below pl/sql block that needs to flag a claim based on claim number values given that I have in an excel sheet. Is there a way to capture those 500 claims efficiently in the select statement in the pl/sql without hardcoding these from value 1 ...to value 500 (as listed below)???
DECLARE
   ln_ctr   NUMBER := 0;
BEGIN
   FOR lrecclaimnumber
   IN ( SELECT   entityid claimnumber
         FROM   mpi_fnx.wfTokenEntity wte
        WHERE   wte.entityid IN
                      ('Value1','Value2',....'Value 500')
)
LOOP
   mpi_fnx.pkgSaveClaimData.spMarkClaimInvalid (pivClaimNumber          => lrecclaimnumber.claimnumber,
                                                pininvalidreasonid      => 304,
                                                pinuserid               => 4999
                                               );
   ln_ctr := ln_ctr + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('Total Entries Processed: ' || ln_ctr);
END;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 29 2011
Added on Apr 1 2011
8 comments
261 views