-
urgent help required regarding PL/SQL table
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
-
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:
Code:
...
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
how do first populate my PL/SQL table with some data before i reference it in for-loop.
please help
-
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';
....
Andrej
-
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
-
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;
Andrej
-
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?
-
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
Last edited by andrejm; 02-03-2004 at 06:35 AM.
Andrej
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|