| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Update an Object Type Column
On Dec 21, 9:33 am, "jimmy.brock" <jimmybr..._at_gmail.com> wrote:
> I know how to update all values in an object type column, but how do
> you update only certain values?
> I can't create a nested table or any other object because this
> database belongs to a vendor.
>
> Here is what I have:
>
> CREATE OR REPLACE TYPE ACKHEADER_TY
> AS OBJECT(
> MESSAGETYPE VARCHAR2(16)
> , MESSAGENUMB VARCHAR2(100)
> , MESSAGESENDERIDENTIFIER VARCHAR2(60)
> , MESSAGERECEIVERIDENTIFIER VARCHAR2(60)
> , MESSAGEDATE DATE
> , TRANSMISSIONACKCODE VARCHAR2(2)
> )
> /
>
> CREATE TABLE t
> (
> , t_id NUMBER
> , agency_id NUMBER(22)
> , ackheader ackheader_ty
> , CONSTRAINT t_pk PRIMARY KEY (t_id)
> )
> /
>
> INSERT INTO t
> VALUES ( t_seq.nextval, 17,
> ACKHEADER_TY('2','1001','FDA','PG',null,null))
> /
>
> I need to update ONLY the 'messagedate' and 'transmissionackcode'
> without changing the other values.
>
> Thanks!
Hello,
You can update the object type just as you would update any other field. See the attached session log below:
SQL> connect amathur_at_dbdev
Connected.
SQL> drop table t cascade constraints;
Table dropped.
SQL> CREATE OR REPLACE TYPE ACKHEADER_TY
2 AS OBJECT( 3 MESSAGETYPE VARCHAR2(16) 4 , MESSAGENUMB VARCHAR2(100) 5 , MESSAGESENDERIDENTIFIER VARCHAR2(60) 6 , MESSAGERECEIVERIDENTIFIER VARCHAR2(60) 7 , MESSAGEDATE DATE 8 , TRANSMISSIONACKCODE VARCHAR2(2) 9 )
Type created.
SQL> CREATE TABLE t
2 (
3 -- removing leading comma
4 -- , t_id NUMBER
5 t_id number
6 -- , agency_id NUMBER(22)
7 , agency_id number(22) 8 , ackheader ackheader_ty 9 , CONSTRAINT t_pk PRIMARY KEY (t_id)10 )
Table created.
SQL> variable new_t_id number;
SQL> INSERT INTO t
2 VALUES ( t_seq.nextval, 17,
3 ACKHEADER_TY('2','1001','FDA','PG',null,null)) returning t_id
4 into :new_t_id;
1 row created.
SQL> print :new_t_id;
NEW_T_ID
7
SQL> select * from t;
T_ID
AGENCY_ID
7
17
ACKHEADER_TY('2', '1001', 'FDA', 'PG', NULL,
NULL)
SQL> update t set ackheader =
ackheader_ty('2','1001','FDA','PG',sysdate,'OK')
2 where t_id = :new_t_id;
1 row updated.
SQL> select * from t;
T_ID
AGENCY_ID
7
17
ACKHEADER_TY('2', '1001', 'FDA', 'PG', '21-DEC-07',
'OK')
SQL> show user
USER is "AMATHUR"
SQL> select * from v$version;
BANNER
 |
 |