CODEWITHSUNDEEP
oracle-databaseloopsplsqlwhile-loop
Gabrial MacLeod
Gabrial MacLeod

Reputation: 37

New to Oracle, Unsure if I'm "missing" something

The following query via PL/SQL returns expected results:

select member_id, birth_date from ( select * from member order by member_id asc ) where birth_date > 0 and update_end_date = 29991231 and rownum <= 20;

It returns 20 rows of data, ordered like I want it to.

However when I incorporate a variant of that query within a while loop inside of a SQL block, it errors on the second iteration of the loop:

set serveroutput on;

declare
    dob         number (8);
    currentDate number (8);
    mID         number (8);
    members     integer;
    i           integer;

begin
  select to_char(sysdate, 'YYYYMMDD') into currentDate from dual;
  dbms_output.put_line('Current Date: ' || currentDate);

  select count(*) into members from member where birth_date > 0 and update_end_date = 29991231;
  dbms_output.put_line('Member Records: ' || members);

  i := 1;
  while i <= 10
  loop 

    select member_id, birth_date into mID, dob from ( select * from member order by member_id asc ) where birth_date > 0 and update_end_date = 29991231 and rownum = i;

    dbms_output.put_line('Row number: ' || i || ' > Member ID: ' || mID || ' | Member DOB: ' || dob);

    i := i + 1;
  end loop;
end;
/

The idea is to start at the first row of the ordered results, then iterate through each row, outputting that row's results.

Eventually once I have this working, I intend to compare this data with an expected value and perform more conditional logic after that.

So unless I'm missing a fundamental "Oracle Thing" then I don't see any good reason as to why this logic fails.

I do recognize that I'm using more of a general-purpose scripting language approach to perform my logic as opposed to nested Oracle statements, but I don't see a reason why that should make a difference.

Again, could be some ignorance at play here.

Thank you in advance.

Upvotes: 1

Views: 63

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

The problem is with how rownum works. You can only use rownum < or rownum <=, or rownum = 1; you can't compare directly to anything other than 1, or use the > or >= operators.

From Tom Kyte's article:

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row: 

select * 
  from t 
 where ROWNUM > 1;

So your main query with rownum <= 20 is fine, and in the first iteration of your loop where rownum = 1 is also OK. But on the second iteration rownum = 2 can never actually be true, so you get a no-data-found error.

Using your own variable for a loop iterator when you have a result set is doing more work than you need to. You could make it work by including the rownum in a subquery, and then filtering that in an outer query, but it won't be very pretty or efficient. You can get what you need, I think, from:

declare
    currentDate number (8);
begin
  select to_char(sysdate, 'YYYYMMDD') into currentDate from dual;
  dbms_output.put_line('Current Date: ' || currentDate);

  for r in (
    select member_id as mID, birth_date as dob, rownum as rn
    from (
      select * from member order by member_id asc
    )
    where birth_date > 0
    and update_end_date = 29991231
    and rownum <= 20
  )
  loop
    dbms_output.put_line('Row number: ' || r.rn
      || ' > Member ID: ' || r.mID
      || ' | Member DOB: ' || r.dob);
  end loop;
end;
/

This only executes the query once, in the cursor, rather than partially repeating it for each value of i. Read more about cursors.

I've included the rownum (with an alias so you can refer to it later) which has the same value as your i would have. You can do anything you want with the selected values inside the loop, referring to them with the r. prefix (use a more meaningful name!) and the column name/alias from the cursor query.

Storing or manipulating dates as numbers (or strings) is not a good idea though, and a bad habit if you're just starting out. Always use the correct data type.

Upvotes: 3

DaaaahWhoosh
DaaaahWhoosh

Reputation: 379

It's quite possible there are not enough records in the result set to complete the loop. For instance, if you only have two records, asking for a record where rownum = 5 would give you a NO_DATA_FOUND exception. You could catch this in an exception block:

BEGIN
i := 1;
  while i <= 10
  loop 

    select member_id, birth_date into mID, dob from ( select * from member order by member_id asc ) where birth_date > 0 and update_end_date = 29991231 and rownum = i;

    dbms_output.put_line('Row number: ' || i || ' > Member ID: ' || mID || ' | Member DOB: ' || dob);

    i := i + 1;
  end loop;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;

Change the 'NULL' to handle the exception, but this should just exit out of the loop for you.

Upvotes: 0

Related Questions