Dba_admin, in your code you should (IMHO) do the following:
- declare your cursor explicitely, so that it has a name
- use that named cursor in your cursor-for-loop
- use cursor_name%ROWCOUNT attribute to find out if it is the first record returned
Something like that:
CURSOR my_cur IS
SELECT column_name FROM user_tab_columns
FOR rec IN my_cur LOOP
IF my_cur%ROWCOUNT = 1 THEN
I think this is much more readable and elegant than using inline view inside the implicitly declared cursor.
To see if the currently returned record is the last record in the resultset? No, you can't tell that in advance, not from inside the cursor.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
SQL> create table my_table (col1 number, col2 number,col3 number);
2 CURSOR my_cur IS
3 SELECT column_name ,count(*) over () tot_rows FROM user_tab_columns
4 WHERE table_name=UPPER('&1');
6 FOR rec IN my_cur LOOP
7 IF my_cur%ROWCOUNT = 1 THEN
8 dbms_output.put_line('FIRST_ROW '||rec.column_name);
9 ELSIF my_cur%rowcount = rec.tot_rows then
10 dbms_output.put_line('LAST_ROW '||rec.column_name);
11 END IF;
12 END LOOP;
Enter value for 1: MY_TABLE
old 4: WHERE table_name=UPPER('&1');
new 4: WHERE table_name=UPPER('MY_TABLE');
PL/SQL procedure successfully completed.
Since the "count(*) over ()" tells you the number of rows in the result set, you can use it within the loop to find out if you have reached the last record or not.