DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ALTER TYPE COMMAND!IS IT POSSIBLE!

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi all!

    Query:

    I had tried the command create or replace type typename
    but it doesn't work.

    There is one Alter typecommand but the syntax I don't know.Could you please help.

    We have many tables referencing a specific typename
    ADDRESS_TY in the middle of development stage.

    What happens and what effect will it have on the tables datas if we drop and recreate the same type.

    I tried this command:

    SQL>ALTER TYPE PERSON_TY
    MODIFY NAME VARCHAR2(60)

    where name is a column name of person_ty and wanted to increase the data type for name column from varchar2(40)
    to varchar2(60) as above.

    But oracle gave this errror:

    ORA:00922: missing or invalid option.

    Couldyou pleasehelp on this.

    regards,

    Amitst.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  3. #3
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    here is the Oracle documentaion.. there r some restrictions.

    Restriction
    You cannot change the existing properties (attributes, member subprograms, map or order functions) of an object type, but you can add new member subprogram specifications.


    Example I
    In the following example, member function QTR is added to the type definition of DATA_T:

    CREATE TYPE data_t AS OBJECT
    ( year NUMBER,
    MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
    );

    CREATE TYPE BODY data_t IS
    MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
    BEGIN
    RETURN (year + invent);
    END;
    END;

    ALTER TYPE data_t REPLACE AS OBJECT
    ( year NUMBER,
    MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER,
    MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR
    );

    CREATE OR REPLACE TYPE BODY data_t IS
    MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
    BEGIN
    RETURN (year + invent);
    END;
    MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS
    BEGIN
    RETURN 'FIRST';
    END;
    END;
    Example II
    The following example recompiles type LOAN_T:

    CREATE TYPE loan_t AS OBJECT
    ( loan_num INTEGER,
    interest_rate FLOAT,
    amount FLOAT,
    start_date DATE,
    end_date DATE );

    ALTER TYPE loan_t COMPILE;
    Example III
    The following example compiles the type body of LINK2:

    CREATE TYPE link1 AS OBJECT
    (a NUMBER);

    CREATE TYPE link2 AS OBJECT
    (a NUMBER,
    b link1,
    MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER);

    CREATE TYPE BODY link2 AS
    MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t13 link1;
    BEGIN t13 := link1(13);
    dbms_output.put_line(t13.a);
    RETURN 5;
    END;
    END;

    CREATE TYPE link3 AS OBJECT (a link2);
    CREATE TYPE link4 AS OBJECT (a link3);
    CREATE TYPE link5 AS OBJECT (a link4);
    ALTER TYPE link2 COMPILE BODY;
    Example IV
    The following example compiles the type specification of LINK2:

    CREATE TYPE link1 AS OBJECT
    (a NUMBER);

    CREATE TYPE link2 AS OBJECT
    (a NUMBER,
    b link1,
    MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER);

    CREATE TYPE BODY link2 AS
    MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t14 link1;
    BEGIN t14 := link1(14);
    dbms_output.put_line(t14.a);
    RETURN 5;
    END;
    END;

    CREATE TYPE link3 AS OBJECT (a link2);
    CREATE TYPE link4 AS OBJECT (a link3);
    CREATE TYPE link5 AS OBJECT (a link4);
    ALTER TYPE link2 COMPILE SPECIFICATION;
    sonofsita
    http://www.ordba.net

Posting Permissions

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


Click Here to Expand Forum to Full Width