SQL> DECLARE
  2  LEMPID NUMBER;
  3  LEMPNAME EMP_NAME_TYPE;
  4  BEGIN
  5  BEGIN
  6  SELECT EMPID,EMPNAME INTO LEMPID,LEMPNAME 
  7  FROM EMP2;
  8  EXCEPTION WHEN TOO_MANY_ROWS THEN NULL;
  9            WHEN NO_DATA_FOUND THEN NULL;
 10  END;
 11  DBMS_OUTPUT.PUT_LINE(LEMPID||','||LEMPNAME.emp_name_type);
 12  END;
 13  /
,

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> create or replace type EMP_NAME_TYPE AS OBJECT (emp_name varchar2(180))
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE EMP2
  2  (
  3  empid NUMBER,
  4  empname EMP_NAME_TYPE not null
  5  )
  6  /

Table created.

SCOTT@orcl_11g> INSERT INTO emp2 VALUES (1, emp_name_type ('ename1'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM emp2
  2  /

     EMPID
----------
EMPNAME(EMP_NAME)
--------------------------------------------------------------------------------
         1
EMP_NAME_TYPE('ename1')


SCOTT@orcl_11g> SELECT e.empid, e.empname.emp_name FROM emp2 e
  2  /

     EMPID
----------
EMPNAME.EMP_NAME
--------------------------------------------------------------------------------
         1
ename1


SCOTT@orcl_11g> DECLARE
  2    LEMPID NUMBER;
  3    LEMPNAME VARCHAR2(180);
  4  BEGIN
  5    BEGIN
  6  	 SELECT e.EMPID, e.EMPNAME.emp_name INTO LEMPID,LEMPNAME
  7  	 FROM EMP2 e;
  8    EXCEPTION WHEN TOO_MANY_ROWS THEN NULL;
  9    END;
 10    DBMS_OUTPUT.PUT_LINE(LEMPID||','||LEMPNAME);
 11  END;
 12  /
1,ename1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

SQL> DECLARE
  2  LEMPID NUMBER;
  3  LEMPNAME VARCHAR2(180);
  4  BEGIN
  5  BEGIN
  6  SELECT EMPID, emp2.empname.emp_name INTO LEMPID,LEMPNAME 
  7  FROM EMP2;
  8  EXCEPTION WHEN TOO_MANY_ROWS THEN NULL;
  9            WHEN NO_DATA_FOUND THEN NULL;
 10  END;
 11  DBMS_OUTPUT.PUT_LINE(LEMPID||','||LEMPNAME);
 12  END;
 13  /
SELECT EMPID, emp2.empname.emp_name INTO LEMPID,LEMPNAME
              *
ERROR at line 6:
ORA-06550: line 6, column 15:
PL/SQL: ORA-00904: "EMP2"."EMPNAME"."EMP_NAME": invalid
identifier
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored


SQL> DECLARE
  2  LEMPID NUMBER;
  3  LEMPNAME VARCHAR2(180);
  4  BEGIN
  5  BEGIN
  6  SELECT EMPID, e.empname.emp_name INTO LEMPID,LEMPNAME 
  7  FROM EMP2 e;
  8  EXCEPTION WHEN TOO_MANY_ROWS THEN NULL;
  9            WHEN NO_DATA_FOUND THEN NULL;
 10  END;
 11  DBMS_OUTPUT.PUT_LINE(LEMPID||','||LEMPNAME);
 12  END;
 13  /
1,Michel

PL/SQL procedure successfully completed.