Home » SQL & PL/SQL » SQL & PL/SQL » Update attributes, ORA-01733 (Oracle 10.2 , win xp)
Update attributes, ORA-01733 [message #381708] Mon, 19 January 2009 06:38 Go to next message
WhiteFox
Messages: 3
Registered: January 2009
Junior Member
Hi,

My problem is update the object table attributes ( I want to change a subtype attribute (employee attribute) )

I Have object type:

- adress
- contact
- person ( supertype )
- employee ( employee is subtype person - Inheritance )

and

- object table name persons

SQL> CREATE OR REPLACE TYPE adress AS OBJECT(
  2  street VARCHAR2(30),
  3  code  VARCHAR2(10),
  4  city VARCHAR2(30)
  5  );
  6  /

SQL> CREATE OR REPLACE TYPE contact AS OBJECT(
  2  phone  VARCHAR2(15),   
  3  e_mail   VARCHAR2(50)      
  4  );
  5  /


SQL> CREATE OR REPLACE TYPE person  AS OBJECT(
  2  name VARCHAR2(20),
  3  surname  VARCHAR2(50),
  4  MEMBER FUNCTION identify RETURN VARCHAR2
  5  )NOT FINAL;
  6  /

SQL> CREATE OR REPLACE TYPE employee UNDER person (
  2  position  VARCHAR2(30),
  3  adress_person adress,
  4  contact_person contact,
  5  id VARCHAR2(11),
  6  wage   VARCHAR2(20),
  7  date_z DATE,
  8  OVERRIDING MEMBER FUNCTION identify  RETURN VARCHAR2
  9  );
 10  /

SQL> CREATE TYPE BODY employee AS
  2    OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN ' employee ';
  5      END;
  6  END;
  7  /


SQL> CREATE TABLE persons OF person;


SQL> INSERT INTO persons VALUES (
  2  employee 
  3  (
  4  'Peter',
  5  'Smith',
  6  'Director',
  7  adress('street','code','city'),
  8  contact('987654321','email@persons.com
  9  '12345678910',
 10  '1200',
 11  to_date('2008-10-08','YYYY-MM-DD')
 12  ));


SQL> select treat(value(p) as employee) from persons p;

TREAT(VALUE(P)ASEMPLOYEE)(NAME, SURNAME, POSITION, ADRESS_PERSON(STREET, CODE, C
--------------------------------------------------------------------------------
EMPLOYEE('Peter', 'Smith', 'Director', ADRESS('street', 'code', 'city'), CONTACT
('2841370', 'email@persons.com'), '12345678910', '1200', '08/10/08')


when I update employee attribute (position,adress_person,contact_person,id,wage,date_z) is ok

For exemple:

SQL> UPDATE 
  2  (SELECT TREAT(VALUE(p) AS employee).contact_person.phone AS vPhone 
  3  FROM persons p 
  4  WHERE TREAT(VALUE(p) AS employee).id = '12345678910') 
  5  SET vPhone = '2841370';
1 row updated.


but when i tired update attributes ( name or surrname ) :

SQL> UPDATE 
  2  (SELECT TREAT(VALUE(p) AS employee).name AS vName
  3  From persons p
  4  WHERE TREAT(VALUE(p) AS employee).id ='2222222222')
  5  SET vName = 'Tom';
SET vName = 'Tom'




ORA-01733: virtual column not allowed here


tank you for the suggestion.

P.S Sorry for my english

Regards

[Updated on: Mon, 19 January 2009 06:41]

Report message to a moderator

Re: Update attributes, ORA-01733 [message #381764 is a reply to message #381708] Mon, 19 January 2009 12:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE adress AS OBJECT(
  2  street VARCHAR2(30),
  3  code  VARCHAR2(10),
  4  city VARCHAR2(30)
  5  );
  6  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE contact AS OBJECT(
  2  phone  VARCHAR2(15),
  3  e_mail   VARCHAR2(50)
  4  );
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE person  AS OBJECT(
  2  name VARCHAR2(20),
  3  surname  VARCHAR2(50),
  4  MEMBER FUNCTION identify RETURN VARCHAR2
  5  )NOT FINAL;
  6  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE employee UNDER person (
  2  position  VARCHAR2(30),
  3  adress_person adress,
  4  contact_person contact,
  5  id VARCHAR2(11),
  6  wage   VARCHAR2(20),
  7  date_z DATE,
  8  OVERRIDING MEMBER FUNCTION identify  RETURN VARCHAR2
  9  );
 10  /

Type created.

SCOTT@orcl_11g> CREATE TYPE BODY employee AS
  2    OVERRIDING MEMBER FUNCTION identify RETURN VARCHAR2 IS
  3  	BEGIN
  4  	   RETURN ' employee ';
  5  	 END;
  6  END;
  7  /

Type body created.

SCOTT@orcl_11g> CREATE TABLE persons OF person;

Table created.

SCOTT@orcl_11g> INSERT INTO persons VALUES
  2    (employee
  3  	 ('Peter', 'Smith', 'Director',
  4  	  adress ('street','code','city'),
  5  	  contact ('987654321',
  6  		   'email@persons.com'
  7  		  ),
  8  	  '12345678910', '1200', to_date ('2008-10-08', 'YYYY-MM-DD')
  9  	 )
 10    )
 11  /

1 row created.

SCOTT@orcl_11g> INSERT INTO persons VALUES
  2    (employee
  3  	 ('Thomas', 'Kyte', 'VP',
  4  	  adress ('street','code','city'),
  5  	  contact ('1234567890',
  6  		   'tkyte@oracle.com'
  7  		  ),
  8  	  '1111111111', '9999', sysdate
  9  	 )
 10    )
 11  /

1 row created.

SCOTT@orcl_11g> select treat(value(p) as employee) from persons p;

TREAT(VALUE(P)ASEMPLOYEE)(NAME, SURNAME, POSITION, ADRESS_PERSON(STREET, CODE, C
--------------------------------------------------------------------------------
EMPLOYEE('Peter', 'Smith', 'Director', ADRESS('street', 'code', 'city'), CONTACT
('987654321', 'email@persons.com'), '12345678910', '1200', '08-OCT-08')

EMPLOYEE('Thomas', 'Kyte', 'VP', ADRESS('street', 'code', 'city'), CONTACT('1234
567890', 'tkyte@oracle.com'), '1111111111', '9999', '19-JAN-09')


SCOTT@orcl_11g> UPDATE
  2    (SELECT TREAT(VALUE(p) AS employee).contact_person.phone AS vPhone
  3  	FROM   persons p
  4  	WHERE  TREAT(VALUE(p) AS employee).id = '12345678910')
  5  SET vPhone = '2841370'
  6  /

1 row updated.

SCOTT@orcl_11g> UPDATE persons p
  2  SET    p.name = 'Tom'
  3  WHERE  TREAT(VALUE(p) AS employee).id = '12345678910'
  4  /

1 row updated.

SCOTT@orcl_11g> select treat(value(p) as employee) from persons p
  2  /

TREAT(VALUE(P)ASEMPLOYEE)(NAME, SURNAME, POSITION, ADRESS_PERSON(STREET, CODE, C
--------------------------------------------------------------------------------
EMPLOYEE('Tom', 'Smith', 'Director', ADRESS('street', 'code', 'city'), CONTACT('
2841370', 'email@persons.com'), '12345678910', '1200', '08-OCT-08')

EMPLOYEE('Thomas', 'Kyte', 'VP', ADRESS('street', 'code', 'city'), CONTACT('1234
567890', 'tkyte@oracle.com'), '1111111111', '9999', '19-JAN-09')


SCOTT@orcl_11g>

Re: Update attributes, ORA-01733 [message #381783 is a reply to message #381764] Mon, 19 January 2009 17:18 Go to previous message
WhiteFox
Messages: 3
Registered: January 2009
Junior Member
Thank you Barbara Smile
Previous Topic: Extract CREATE TABLE statements from USER_TAB_COLUMNS
Next Topic: Dynamic SQL with dynamic variable names
Goto Forum:
  


Current Time: Sat Dec 10 01:16:48 CST 2016

Total time taken to generate the page: 0.05090 seconds