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!

Using Loops with INDEX BY BINARY INTEGER

EZGmsMar 12 2011 — edited Mar 12 2011
Hi,

I have a little question with the next procedure:
                               SET SERVEROUTPUT ON

                            DECLARE
                               TYPE dept_table_type IS TABLE OF hr.departments%ROWTYPE
                                  INDEX BY BINARY_INTEGER;

                               my_dept_table   dept_table_type;
                               v_count        NUMBER (3)     := 160;
                            BEGIN
                               FOR i IN 100 .. v_count
                               LOOP
                                                                      
                                  SELECT *
                                    INTO my_dept_table (i)
                                    FROM hr.departments
                                   WHERE department_id = i;
                               
                               END LOOP;

                               FOR i IN my_dept_table.FIRST .. my_dept_table.LAST
                               LOOP
                                  DBMS_OUTPUT.put_line (my_dept_table (i).department_name);
                               END LOOP;
                            END;                               
When I run this procedure I get the following error:
ORA-01403: Not found any data 
ORA-06512: line 11
But I Know why is it, the department_id numbers are (100,110,120,130,140,150,160) and the first loop it's ok but
in the second loop the 101 department_id doesn't exist an then it fails.
what can I do to resolve this error?

thanks in advance!!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 9 2011
Added on Mar 12 2011
10 comments
1,808 views