Accessing records in PL/Sql table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Accessing records in PL/Sql table

  1. #1
    Join Date
    Mar 2002
    Posts
    2
    Hello ,

    I am trying to run this code. I want to populate plsql table and access the records from the table. Can anyone please help me out.
    Thanks in advance,
    Prabal


    Code is :
    -------------------------------------
    begin
    DECLARE
    TYPE E_type is record (
    empno emp.empno%type,
    ename emp.ename%type);

    TYPE e_tab is TABLE OF e_type INDEX BY BINARY_INTEGER;
    --emp_rec e_type;
    emp_tab e_tab;
    Counter NUMBER;

    begin

    select Empno, Ename BULK COLLECT into emp_tab from emp where rownum=1;

    counter := 1;
    FOR rec IN Emp_tab.FIRST..Emp_tab.LAST LOOP
    dbms_output.put_line(emp_tab(rec).ename);
    counter := counter + 1;
    end loop;

    end;
    end;
    /


    Error I am getting is :
    --------------------
    select Empno, Ename BULK COLLECT into emp_tab from emp where rownum=1;
    *
    ERROR at line 14:
    ORA-06550: line 14, column 42:
    PLS-00597: expression 'EMP_TAB' in the INTO list is of wrong type
    ORA-06550: line 14, column 4:
    PL/SQL: SQL Statement ignored

  2. #2
    Join Date
    Apr 2001
    Posts
    47
    Hi,

    I think (but I am not sure) this is because of the restrictions on bulk collect - you can't use it with a table of records.

    This should work:

    begin
    DECLARE
    TYPE E_type is record (
    empno emp.empno%type,
    ename emp.ename%type);

    TYPE e_tab is TABLE OF e_type INDEX BY BINARY_INTEGER;
    --emp_rec e_type;
    emp_tab e_tab;
    Counter NUMBER;

    begin

    -- select Empno, Ename BULK COLLECT into emp_tab from emp where rownum=1;
    select Empno, Ename into emp_tab(1) from emp where rownum=1;

    counter := 1;
    FOR rec IN Emp_tab.FIRST..Emp_tab.LAST LOOP
    dbms_output.put_line(emp_tab(rec).ename);
    counter := counter + 1;
    end loop;

    end;
    end;
    /

    Regards,

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