OraFAQ Forum: SQL & PL/SQL » How to fetch a single record in an array/nested table

Home » SQL & PL/SQL » SQL & PL/SQL » How to fetch a single record in an array/nested table (O/S:Windows 7 PRO; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to fetch a single record in an array/nested table [message #629941]

Thu, 18 December 2014 01:15

buggleboy007
Messages: 282
Registered: November 2010
Location: Canada

Senior Member

I would like to know as to how to fetch a single record in an array/nested table. The situation is as follows:
I am using the default EMP table on SCOTT schema
So EMP table is :

DESC EMP
Name     Null     Type         
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)

Values in EMP table:

select * from emp
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 

 14 rows selected

Now I want to access an employee with empno = 7499 into a emp record. My code is as follows:

SET SERVEROUTPUT ON
SET ECHO ON

DECLARE
  TYPE emp_type IS TABLE OF emp%ROWTYPE;
  emp_dtls   emp_type;
  
BEGIN

  emp_dtls:=emp_type();
  emp_dtls.EXTEND;
  
  SELECT * 
  INTO emp_dtls
  FROM emp
  WHERE empno=7499;
  
  
 --DBMS_OUTPUT.put_line('Employee details are'||' '||emp_dtls);

END;
/

When I run it, I am getting bogged down with the following error:

Error report -
ORA-06550: line 11, column 8:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 12, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I did look up a few sites/books before coming here but have not been successful. The only solution that seems to be working is:

SELECT * 
  INTO emp_dtls(1)
  FROM emp
  WHERE empno=7499;

This I found in SF's text book, but then I am unable to know the significance of it and hence don't want to use it blindly. Could any one help me out?

Thanks,
Sandeep