==========================================================
PL/SQL TABLE TYPE 사용 시에 ORA-1403 NO DATA FOUND ERROR
==========================================================

PURPOSE
---------

PL/SQL table에서 define되지 않은 row을 reference하려고 할 때
'no data found error'가 발생한다.

Explanation & Example
----------------------

< 예제 1>

declare
TYPE t_my_table is TABLE OF number INDEX BY BINARY_INTEGER;
my_table t_my_table;
BEGIN
FOR i IN 1..10 LOOP
my_table(i):=i*10;
END LOOP;
FOR i IN 1..11 LOOP
dbms_output.put_line('Value is '||my_table(i));
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Element in table does not exist');
END;

< 예제 2> dbms_sql에서의 table type 이용

CREATE or REPLACE PROCEDURE get_rows (where_clause varchar2) AS
cursor1 integer;
rows_processed integer;

type EmpRecord is record
(
myno emp.empno%type,
myname emp.ename%TYPE
);
type EmpTable is Table of EmpRecord
index by binary_integer;
idx binary_integer;
AreaSil EmpTable;
BEGIN
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse (cursor1, 'select empno, ename from emp where '
|| where_clause, dbms_sql.native);

FOR i IN 1..10 LOOP
/*-------------------------------------------------------------------------
아래의 두 줄을 넣지 않으면 define되지 않았으므로 아래애서 실행 시에 error.
AreaSil(i).myno:=0;
AreaSil(i).myname :=' ';

SQL> execute get_rows('SAL > 1000 AND DEPTNO = 10');
ORA-01403: no data found

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------*/

AreaSil(i).myno:=0;
AreaSil(i).myname :=' ';
dbms_sql.define_column (cursor1, 1, AreaSil(i).myno);
dbms_sql.define_column (cursor1, 2, AreaSil(i).myname, 20);
END LOOP;


rows_processed := dbms_sql.execute (cursor1);

idx := 1;
loop
if dbms_sql.fetch_rows (cursor1) > 0 then
dbms_sql.column_value (cursor1, 1, AreaSil(idx).myno);
dbms_sql.column_value (cursor1, 2, AreaSil(idx).myname);
dbms_output.put_line(to_char(AreaSil(idx).myno) || ' ' || AreaSil(idx).myname);
idx := idx + 1;
else
exit;
end if;
end loop;
dbms_sql.close_cursor (cursor1);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
if dbms_sql.is_open (cursor1) then
dbms_sql.close_cursor (cursor1);
end if;
END;
/

SQL> execute get_rows('SAL > 1000 AND DEPTNO = 10');
7782 CLARK
7839 KING
8100 MILLER

PL/SQL procedure successfully completed.

Reference Ducumment
---------------------
PL/SQL User's Guide and Reference.