Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    5

    Unanswered: Alter object type

    I have a table with below definitions.

    HTML Code:
    SQL> desc service_connection;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SERVICE_CONNECTION_ID                            NOT NULL   NUMBER
     FILTER                                                 FILTER_TYPE
     BILLING_OPTION                                    NUMBER
     SERVICE_EMAIL                                      VARCHAR2(1000)
    
    SQL> desc FILTER_TYPE;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TYPE_OF_FILTER                                     VARCHAR2(20)
     BGP                                                T_BGP
     STATIC_CUSTOMER                                    T_STATIC_CUSTOMER
    
    SQL> desc T_BGP
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     BGP_TYPE                                           VARCHAR2(100)
     PEER_ADDRESS                                       VARCHAR2(100)
     LINK_TYPE                                          VARCHAR2(100)
     ACCLIST                                            VARCHAR2(100)
     PRINCIPAL_LINK                                     VARCHAR2(100)
     CLEAR_SESSION                                      VARCHAR2(100)
     ASNUM                                              VARCHAR2(20)
     ASPATH                                             VARCHAR2(100)
    
    SQL> desc T_STATIC_CUSTOMER;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     STATIC_TYPE                                        VARCHAR2(20)
     IP_ADDRESS                                         VARCHAR2(100)
     ROUTER_2ND                                         VARCHAR2(100)
     IP_ADDRESS_2ND                                     VARCHAR2(100)

    I need to modify the object type t_bgp and T_STATIC_CUSTOMER and the new object type definitions should be -

    HTML Code:
    SQL> desc t_bgp;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     BGP_TYPE                                           VARCHAR2(100)
     PEER_ADDRESS                                       VARCHAR2(100)
     PEER_ADDRESS_IPV6                                  VARCHAR2(100)
     LINK_TYPE                                          VARCHAR2(100)
     ACCLIST                                            VARCHAR2(100)
     PRINCIPAL_LINK                                     VARCHAR2(100)
     CLEAR_SESSION                                      VARCHAR2(100)
     ASNUM                                              VARCHAR2(20)
     ASPATH                                             VARCHAR2(100)
    
    SQL> desc t_static_customer;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     STATIC_TYPE                                        VARCHAR2(20)
     IP_ADDRESS                                         VARCHAR2(100)
     IPV6_ADDRESS                                       VARCHAR2(100
     ROUTER_2ND                                         VARCHAR2(100)
     IP_ADDRESS_2ND                                     VARCHAR2(100)

    The only way i can find to change this was by droping the table (droping all constraints first) , droping the object type and then recreate this table with new definition.

    Now the problem is i am not able to find a quick way to move the data ..copying to a temprary table is not helping.

    The only way i could found to do this is to get all data to a file and reload in the new table, but it consumes lot of time (around 150000 rows of data).

    Can anyone suggest a simpler way?
    I am using oracle 9i and can only use sqlplus.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by swapnesh View Post
    copying to a temprary table is not helping.
    What exactly do you mean with "not helping"?
    Do you get an error?

    Edit: Sorry, of course it will not help as the backup table would also reference the object types.
    Last edited by shammat; 08-12-10 at 06:37.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    I did some digging in the manual, and as far as I can tell (tested with a small table) the following works for me in 10g.
    Code:
    ALTER TYPE t_static_customer ADD ATTRIBUTE ipv6_address VARCHAR2(100) CASCADE;
    ALTER TYPE t_bgp ADD ATTRIBUTE peer_address_ipv6 VARCHAR2(100) CASCADE;
    I don't know if your outdated (and non-supported) Oracle version supports this as well.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have been programming for 30 years and I have never felt the need for using object_types in a table. It is not transportable to a different database and is difficult to maintain and view.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    I have been programming for 30 years and I have never felt the need for using object_types in a table
    Same here
    I typically use it in stored procedures or for return types of functions

    It is not transportable to a different database
    Except when porting to DB2 or PostgreSQL

    and is difficult to maintain and view.
    I don't understand the "view" part of this statement.
    What's difficult about viewing the data? Just do a SELECT...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •