urgent help required regarding PL/SQL table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: urgent help required regarding PL/SQL table

  1. #1
    Join Date
    Apr 2003
    Posts
    29

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Apr 2003
    Posts
    29
    how do first populate my PL/SQL table with some data before i reference it in for-loop.

    please help

  4. #4
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    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

  5. #5
    Join Date
    Apr 2003
    Posts
    29
    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

  6. #6
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    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

  7. #7
    Join Date
    Apr 2003
    Posts
    29
    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?

  8. #8
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    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 05:35 AM.
    Andrej

  9. #9
    Join Date
    Apr 2003
    Posts
    29
    ok thanx a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width