Home » SQL & PL/SQL » SQL & PL/SQL » update on inherited column (Enterprise 10.2.0.1 on WinXP sp2)
update on inherited column [message #316630] Sat, 26 April 2008 11:53 Go to next message
peeterek
Messages: 9
Registered: March 2008
Junior Member
Hi,

I have problem with update statement.

Let is consider that code:

CREATE OR REPLACE TYPE aaa AS OBJECT(
id NUMBER(1)
) NOT FINAL;
/
CREATE OR REPLACE TYPE bbb UNDER aaa(
some_txt VARCHAR2(50)
);
/
CREATE TABLE tmp_tbl OF aaa;


INSERT INTO tmp_tbl VALUES (BBB(1,'tmp_text'));
INSERT INTO tmp_tbl VALUES (BBB(2,'more_txt'));


SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t;

   _______________________________________
   | ID |  TREAT(VALUE(T)ASBBB).SOME_TXT |
   ---------------------------------------	
   | 1  |  tmp_text                      |
   | 2  |  more_txt                      | 
   ---------------------------------------


Now if I wont to change value of 'SOME_TXT' I got error ORA-00927 - missing equal sign;

      
   UPDATE tmp_tbl t
   SET TREAT(VALUE(t) AS BBB).SOME_TXT = 'updated_txt'
   WHERE t.id = 1;
   
ORA-00923:missing equal sign;



   
DROP TABLE tmp_tbl;
DROP TYPE bbb;
DROP TYPE aaa;


So my question is how to update inherited column???
thank you in advance for every ideas.
Re: update on inherited column [message #316643 is a reply to message #316630] Sat, 26 April 2008 16:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE TYPE aaa AS OBJECT(
  2  id NUMBER(1)
  3  ) NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE bbb UNDER aaa(
  2  some_txt VARCHAR2(50)
  3  );
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE tmp_tbl OF aaa
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(1,'tmp_text'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(2,'more_txt'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 tmp_text
         2 more_txt

SCOTT@orcl_11g> UPDATE tmp_tbl t
  2  SET    VALUE(t) = BBB(1,'updated_txt')
  3  WHERE  t.id = 1
  4  /

1 row updated.

SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 updated_txt
         2 more_txt

SCOTT@orcl_11g> DROP TABLE tmp_tbl
  2  /

Table dropped.

SCOTT@orcl_11g> DROP TYPE bbb
  2  /

Type dropped.

SCOTT@orcl_11g> DROP TYPE aaa
  2  /

Type dropped.

Re: update on inherited column [message #316644 is a reply to message #316630] Sat, 26 April 2008 17:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Another method:

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE aaa AS OBJECT(
  2  id NUMBER(1)
  3  ) NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE bbb UNDER aaa(
  2  some_txt VARCHAR2(50)
  3  );
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE tmp_tbl OF aaa
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(1, 'tmp_text'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(2, 'more_txt'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 tmp_text
         2 more_txt

SCOTT@orcl_11g> DECLARE
  2    v_bbb bbb;
  3  BEGIN
  4    SELECT TREAT(VALUE(t) AS BBB)
  5    INTO   v_bbb
  6    FROM   tmp_tbl t
  7    WHERE  t.id = 1;
  8    v_bbb.some_txt := 'updated_txt';
  9    UPDATE tmp_tbl t
 10    SET    VALUE(t) = v_bbb
 11    WHERE  t.id = 1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 updated_txt
         2 more_txt

SCOTT@orcl_11g> DROP TABLE tmp_tbl
  2  /

Table dropped.

SCOTT@orcl_11g> DROP TYPE bbb
  2  /

Type dropped.

SCOTT@orcl_11g> DROP TYPE aaa
  2  /

Type dropped.

SCOTT@orcl_11g> 


Re: update on inherited column [message #316645 is a reply to message #316630] Sat, 26 April 2008 17:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
And another method:

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE aaa AS OBJECT(
  2  id NUMBER(1)
  3  ) NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE bbb UNDER aaa(
  2  some_txt VARCHAR2(50)
  3  );
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE tmp_tbl OF aaa
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(1, 'tmp_text'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(2, 'more_txt'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 tmp_text
         2 more_txt

SCOTT@orcl_11g> UPDATE (SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt AS the_text
  2  	     FROM   tmp_tbl t
  3  	     WHERE  t.id = 1)
  4  SET    the_text = 'updated_txt'
  5  /

1 row updated.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT t.id, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID TREAT(VALUE(T)ASBBB).SOME_TXT
---------- --------------------------------------------------
         1 updated_txt
         2 more_txt

SCOTT@orcl_11g> DROP TABLE tmp_tbl
  2  /

Table dropped.

SCOTT@orcl_11g> DROP TYPE bbb
  2  /

Type dropped.

SCOTT@orcl_11g> DROP TYPE aaa
  2  /

Type dropped.

SCOTT@orcl_11g> 


Re: update on inherited column [message #316646 is a reply to message #316630] Sat, 26 April 2008 17:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The last method is probably the one you want, since it is SQL only and you do not have to provide value for or otherwise reference any other columns they may exist as shown below.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE aaa AS OBJECT(
  2  id NUMBER(1),
  3  other_col number(1)
  4  ) NOT FINAL;
  5  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE bbb UNDER aaa(
  2  some_txt VARCHAR2(50)
  3  );
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE tmp_tbl OF aaa
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(1, 3, 'tmp_text'))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO tmp_tbl VALUES (BBB(2, 4, 'more_txt'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT t.id, other_col, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID  OTHER_COL TREAT(VALUE(T)ASBBB).SOME_TXT
---------- ---------- --------------------------------------------------
         1          3 tmp_text
         2          4 more_txt

SCOTT@orcl_11g> UPDATE (SELECT TREAT(VALUE(t) AS BBB).some_txt AS some_txt
  2  	     FROM   tmp_tbl t
  3  	     WHERE  t.id = 1)
  4  SET    some_txt = 'updated_txt'
  5  /

1 row updated.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> SELECT t.id, other_col, TREAT(VALUE(t) AS BBB).some_txt from tmp_tbl t
  2  /

        ID  OTHER_COL TREAT(VALUE(T)ASBBB).SOME_TXT
---------- ---------- --------------------------------------------------
         1          3 updated_txt
         2          4 more_txt

SCOTT@orcl_11g> DROP TABLE tmp_tbl
  2  /

Table dropped.

SCOTT@orcl_11g> DROP TYPE bbb
  2  /

Type dropped.

SCOTT@orcl_11g> DROP TYPE aaa
  2  /

Type dropped.

SCOTT@orcl_11g> 

[Updated on: Sat, 26 April 2008 17:50]

Report message to a moderator

Re: update on inherited column [message #316653 is a reply to message #316646] Sun, 27 April 2008 03:22 Go to previous message
peeterek
Messages: 9
Registered: March 2008
Junior Member
Thank you very much for all answers which are really helpful Smile

Best regards,
peeterek
Previous Topic: How can i login as DBA (merged)
Next Topic: BIN File
Goto Forum:
  


Current Time: Thu Dec 08 20:07:23 CST 2016

Total time taken to generate the page: 0.10072 seconds