Accessing records in PL/Sql table

[prabal]

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

[dmitriy]

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,