Home » SQL & PL/SQL » SQL & PL/SQL » Is It Possible To Read an data from tabel in which Column Defined as Type?
Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306481] Fri, 14 March 2008 08:27 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
create or replace type EMP_NAME_TYPE AS OBJECT (emp_name_type varchar2(180))
/
CREATE TABLE EMP
(
empid NUMBER,
empname EMP_NAME_TYPE not null
)
/

DECLARE
LEMPID NUMBER;
LEMPNAME VARCHAR2(180);
BEGIN
BEGIN
SELECT EMPID,EMPNAME INTO LEMPID,LEMPNAME
FROM EMP;
EXECEPTION WHEN TOO_MANY_ROWS THEN NULL;
END;
DBMS_OUTPUT.PUT_LINE(LEMPID||','||LEMPNAME);
END;
/

ERROR:
ORA-06550: line 6, column 15:
ORA-00932: inconsistent datatypes: expected NUMBER got PACE_MASTERDBO.EMP_NAME_TYPE
ORA-06550: line 6, column 2:
SQL Statement ignored



Pls Help Me To proceed?
Re: Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306483 is a reply to message #306481] Fri, 14 March 2008 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
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.

Regards
Michel
Re: Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306504 is a reply to message #306481] Fri, 14 March 2008 09:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
It is best not to use the same names for a type and a column, to avoid confusion. If you want to retrieve the column within an object type, you need to use an alias for the table. Please see the demonstration below.


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> 


Re: Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306509 is a reply to message #306504] Fri, 14 March 2008 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! I was stuck on this:
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.

It works only if you give an alias to the table!
(This is surely somewhere in the doc. Smile )

Regards
Michel
Re: Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306581 is a reply to message #306509] Fri, 14 March 2008 14:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Fri, 14 March 2008 07:12

It works only if you give an alias to the table!
(This is surely somewhere in the doc. Smile )



http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/nameresolve.htm#CBBHHFBF

[Updated on: Fri, 14 March 2008 14:52]

Report message to a moderator

Re: Is It Possible To Read an data from tabel in which Column Defined as Type? [message #306584 is a reply to message #306581] Fri, 14 March 2008 15:22 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks.
Michel
Previous Topic: comparing data between two databases
Next Topic: Aggregate and select in same query?
Goto Forum:
  


Current Time: Thu Dec 08 02:16:04 CST 2016

Total time taken to generate the page: 0.17428 seconds