urgent help required regarding PL/SQL table

[varshanswamy]

I am trying to do manipulations in a PL/SQL table. And I am getting the following error. Please solve the error.


SQL> DECLARE
2 TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 dept_tab DeptTabTyp;
5 begin
6 FOR i IN dept_tab.FIRST .. dept_tab.LAST LOOP
7 INSERT INTO dept (deptno, dname)
8 VALUES (dept_tab(i).deptno, dept_tab(i).dname);
9 END LOOP;
10 end;
11 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7

[jmodic]

When you start your for-loop, your pl/sql table is empty. So both dep_tab.FIRST and dept_tab.LAST return NULL. So in fact your for-loop statement is like this:

  ...
  FOR i IN NULL .. NULL LOOP
    ...
  END LOOP;
  ...

This of course results in "ORA-06502: PL/SQL: numeric or value error".

You should first populate your PL/SQL table with som data before you reference it in for-loop.

[varshanswamy]

how do first populate my PL/SQL table with some data before i reference it in for-loop.

please help

[andrejm]

if you want, you can use bulk collect

SQL> DECLARE
2 TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 dept_tab DeptTabTyp;
5 begin
select * bulk collect into dept_tab
from dept;
6 FOR i IN dept_tab.FIRST .. dept_tab.LAST LOOP
7 INSERT INTO dept (deptno, dname)
8 VALUES (dept_tab(i).deptno, dept_tab(i).dname);
9 END LOOP;
10 end;
11 /


or you can fill it like this

dept_tab(1).deptno:= 10;
dept_tab(1).dname:='name1';


dept_tab(2).deptno:= 20;
dept_tab(2).dname:='name2';

....

[varshanswamy]

1 DECLARE
2 TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 dept_tab DeptTabTyp;
5 begin
6 select * bulk collect into dept_tab
7 from dept;
8 FOR i IN dept_tab.FIRST .. dept_tab.LAST LOOP
9 INSERT INTO dept (deptno, dname)
10 VALUES (dept_tab(i).deptno, dept_tab(i).dname);
11 END LOOP;
12* end;
13 /
select * bulk collect into dept_tab
*
ERROR at line 6:
ORA-06550: line 6, column 10:
PLS-00103: Encountered the symbol "BULK" when expecting one of the following:
from into


Please solve this error

[andrejm]

Maybe problem is this( i didn't look very close)

if you have version 9... you can use
DECLARE
TYPE emp_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
t_emptab emp_type;
BEGIN
SELECT *
BULK COLLECT INTO t_emptab
FROM emp;
FOR i IN 1..t_emptab.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_emptab(i).ename);
-- here i have lot of code....
DBMS_OUTPUT.PUT_LINE(t_emptab(i).empno);
END LOOP;
END;

if you have version <9 you must declare every field

DECLARE
TYPE empno_type IS TABLE OF emp.empno%TYPE
INDEX BY BINARY_INTEGER;
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
t_empno empno_type;
t_ename ename_type;
BEGIN
SELECT empno, ename
BULK COLLECT INTO t_empno, t_ename
FROM emp;
FOR i IN 1..t_empno.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_empno(i));
DBMS_OUTPUT.PUT_LINE(t_ename(i));
END LOOP;
END;

[varshanswamy]

hi andrej

DECLARE
TYPE empno_type IS TABLE OF emp.empno%TYPE
INDEX BY BINARY_INTEGER;
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
t_empno empno_type;
t_ename ename_type;
BEGIN
SELECT empno, ename
BULK COLLECT INTO t_empno, t_ename
FROM emp;
FOR i IN 1..t_empno.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(t_empno(i));
DBMS_OUTPUT.PUT_LINE(t_ename(i));
END LOOP;
END;
/
COLLECT INTO t_empno, t_ename
*
ERROR at line 10:
ORA-06550: line 10, column 14:
PLS-00385: type mismatch found at 'T_EMPNO' in SELECT...INTO statement
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored

my oracle version is 8.0.5

is it really because of version?

[andrejm]

i heard thart bulk collect work in 8.1... and on

so this(bulk collect) wont work in 8.0.5

ther is link to documentation 8.0.5.

http://otn.oracle.com/documentation/..._arch_805.html

[varshanswamy]

ok thanx a lot