insert/update/select value in user defined type of oracle

2
votes

Create user defined type in oracle 

CREATE OR REPLACE TYPE  "CUSTOMER_NAME" AS OBJECT(FIRST_NAME VARCHAR2(20),LAST_NAME VARCHAR2(20))
    /

used following code for data insertion in the table.

 INSERT INTO STAFF_INFO(STAFF_NAME,STAFF_PWD) VALUES (CUSTOMER_NAME('" & fname.Text & "','" & lname.Text & "'),'" & password.Text & "')"

but, when i'm trying 

select STAFF_ID,STAFF_PWD,CUSTOMER_NAME(FIRST_NAME,LAST_NAME) from staff_info

getting error ORA-00904: "LAST_NAME": invalid identifier

and when i'm trying this

select * from staff_info

then, i'm getting this error ORA-00932: inconsistent datatypes: expected NUMBER got ADS.CUSTOMER_NAME

need help !!

1
oracleoracle10guser-defined-types

1 Answers

3
votes

I don't have an Oracle instance in front of me, but I've dealt with this before. I recall I had to do something like:

select a.STAFF_ID,
       a.STAFF_PWD,
       a.STAFF_NAME.FIRST_NAME,
       a.STAFF_NAME.LAST_NAME
  from staff_info a;

Although the field customer_name probably shouldn't match the name of the type customer_name.