Databases
StackOverflow2013
Postsdbo
POAre there alternative methods for saying 'next' in a pl/sql for loop?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
    primarykey
    data
    text
    <p>So I've got a for loop that processes a list of IDs and has some fairly complex things to do. Without going into all the ugly details, basically this:</p> <pre> DECLARE l_selected APEX_APPLICATION_GLOBAL.VC_ARR2; ...snip... BEGIN -- get the list ids l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST); -- process each in a nice loop FOR i IN 1..l_selected.count LOOP -- do some data checking stuff... -- here we will look for duplicate entries, so we can noop if duplicate is found BEGIN SELECT county_id INTO v_dup_check FROM org_county_accountable WHERE organization_id = :P4_ID AND county_id = v_county_id; -- NEXT;! NOOP;! but there is no next! EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('no dups found, proceeding'); END; -- here we have code we only want to execute if there are no dupes already IF v_dup_check IS NULL THEN -- if not a duplicate record, proceed... ELSE -- reset duplicate check variable v_dup_check := NULL; END; END LOOP; END; </pre> <p>How I normally handle this is by selecting into a value, and then wrap the following code in an IF statement checking to make sure that duplicate check variable is NULL. But it's annoying. I just want to be able to say NEXT; or NOOP; or something. Especially since I already have to catch the NO_DATA_FOUND exception. I suppose I could write a letter to Oracle, but I'm curious how others handle this.</p> <p>I could also wrap this in a function, too, but I was looking for something a little cleaner/simpler.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBilly Gray
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONice to see someone else using APEX!
      singulars
      1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
      1. USTony Andrews
    2. COAPEX rocks, man ;-) The forum on forums.oracle.com is really helpful.
      singulars
      1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
      1. USBilly Gray
    3. COIn your question and both tuinstoel and Pourquoi's solutions, you're performing an additional SQL query (and context switch to/from PL/SQL) in every iteration of your FOR loop...that's not necessarily cheap. If performance is a concern, it'd be better to remove all dups up front in a single query.
      singulars
      1. POAre there alternative methods for saying 'next' in a pl/sql for loop?
      1. USjimmyorr
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload