OraFAQ Forum: SQL & PL/SQL » How can I modify length of a column in type definition.

Home » SQL & PL/SQL » SQL & PL/SQL » How can I modify length of a column in type definition. (11.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I modify length of a column in type definition. [message #653246]

Thu, 30 June 2016 01:53

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

How can I modify length of a column in type.

Eg.

CREATE OR REPLACE TYPE TYP_OBJECT AS OBJECT
( 
     A             VARCHAR2(12),
     B             NUMBER(12),
     C             NUMBER(2)
) ;

In above type I need to modify length of column C to NUMBER(10) from NUMBER(2)

CREATE OR REPLACE TYPE TYP_OBJECT AS OBJECT
( 
     A             VARCHAR2(12),
     B             NUMBER(12),
     C             NUMBER(10)
) ;

Please suggest.

Thanks & Regards
Manoj

Report message to a moderator

Re: How can I modify length of a column in type definition. [message #653247 is a reply to message #653246]

Thu, 30 June 2016 01:59

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Use the ALTER TYPE command.
http://docs.oracle.com/database/121/LNPLS/alter_type.htm#LNPLS99995

Report message to a moderator

Re: How can I modify length of a column in type definition. [message #653281 is a reply to message #653246]

Thu, 30 June 2016 16:24

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE TYP_OBJECT AS OBJECT
  2  (
  3  	  A		VARCHAR2(12),
  4  	  B		NUMBER(12),
  5  	  C		NUMBER(2)
  6  ) ;
  7  /

Type created.

SCOTT@orcl_12.1.0.2.0> DESC typ_object
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(12)
 B                                                  NUMBER(12)
 C                                                  NUMBER(2)

SCOTT@orcl_12.1.0.2.0> SELECT t.vals.a, t.vals.b, t.vals.c
  2  FROM   (SELECT typ_object ('testing', 1, 10) vals
  3  	     FROM   DUAL) t
  4  /

VALS.A      VALS.B     VALS.C
------- ---------- ----------
testing          1         10

1 row selected.

SCOTT@orcl_12.1.0.2.0> ALTER TYPE typ_object MODIFY ATTRIBUTE c NUMBER(10) CASCADE
  2  /

Type altered.

SCOTT@orcl_12.1.0.2.0> CONNECT scott/tiger
Connected.
SCOTT@orcl_12.1.0.2.0> DESC typ_object
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(12)
 B                                                  NUMBER(12)
 C                                                  NUMBER(10)

SCOTT@orcl_12.1.0.2.0> SELECT t.vals.a, t.vals.b, t.vals.c
  2  FROM   (SELECT typ_object ('testing', 1, 1000000000) vals
  3  	     FROM   DUAL) t
  4  /

VALS.A      VALS.B     VALS.C
------- ---------- ----------
testing          1 1000000000

1 row selected.

Report message to a moderator

Previous Topic:	Issue in Exxternal Table
Next Topic:	First business day of a given month

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 09:53:48 CDT 2024