Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    Unanswered: STRUCTURED TYPES & TABLES

    can anyone share with me how to insert values into a structured-typed table, as such in the example below:

    CREATE TYPE address_udt AS OBJECT (
    NUMB INTEGER(2)
    STREET VARCHAR2(12)); /

    CREATE TYPE A AS OBJECT (
    ID INT,
    NAME VARCHAR2(10),
    ADDRESS address_udt)
    NOT FINAL; /

    CREATE TABLE AA OF A;

    INSERT INTO AA VALUES (1,'Andy', (6, 'North ST')); - this doesnt work!

    can anyone offer advice or exp on how to get around this prob?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How to do it:

    Code:
    SQL> INSERT INTO AA VALUES (A(1, 'Andy', address_udt(6, 'North ST')));
    
    1 row created.
    Advice/experience: don't do it! It's all pain and no gain.

  3. #3
    Join Date
    Jun 2004
    Posts
    127
    hi tony thanks for response! i knew how to achieve it in postreSQL through using the row command, but was unsure in oracle. so thanks!
    Last edited by FAC51; 12-15-04 at 12:50.

  4. #4
    Join Date
    Jun 2004
    Posts
    127
    if i were to apply type inheritance to another table i.e

    CREATE TYPE B UNDER A (
    gender VARCHAR2(1));

    CREATE TABLE BB OF B

    could you show me the SQL of how to insert the values (inc inherited from A) into table BB, as again im struggling.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    insert into bb values (b(1, 'Andy', address_udt(6, 'North ST'), 'M'));

    You really are a glutton for punishment, aren't you?

  6. #6
    Join Date
    Jun 2004
    Posts
    127
    thanks again. for some reason i was inserting the gender values before all the other values, a lesson learned.

  7. #7
    Join Date
    Jun 2004
    Posts
    127
    one final question, is it possible to put some kind of unique constraint on the attribute 'ID' from my type, so that every time I insert values into a table no two ID values (from the type inheritance model) would be the same, even under diff tables. so for example if i insert ID = 1 into table AA, when i insert ID = 1 this time into table BB same value (through inheriting values from A) a constraint chk will display some form of error.

    chk example below to see a [hopefully] clearer explination:

    CREATE TYPE A AS OBJECT (
    ID INT);

    CREATE TABLE AA OF A (
    CONSTRAINT AA_PK PRIMARY KEY (ID));

    CREATE TYPE B UNDER A...

    CREATE TABLE BB OF B...

    i.e INSERT VALUES INTO AA (1); 1 row created

    INSERT VALUES INTO BB (1); violation! value already used in AA etc,.

    this is what im trying to acheve.

    also, is it possible to apply a check constraint on a type? i.e.

    CREATE TYPE Z AS OBJECT (
    abc VARCHAR2(5),
    CONSTRAINT z_chk CHECK VALUE IN (
    'left',
    'right'));

    for example?
    Last edited by FAC51; 12-16-04 at 08:04.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not like that, no. Tables AA and BB are entirely unrelated.

    There are various ways to create such a multi-table constraint (regardless of whether the tables happen to be based on object types or not). One is with materialized views:
    Code:
    SQL> create materialized view log on aa
      2  with rowid including new values;
    
    Snapshot log created.
    
    SQL> create materialized view log on bb
      2  with rowid including new values;
    
    Snapshot log created.
    
    SQL> create materialized view mv
      2  refresh fast on commit
      3  as
      4  select aa.id, aa.rowid aa_rowid, bb.rowid bb_rowid
      5  from aa, bb
      6  where aa.id = bb.id;
    
    Snapshot created.
    
    SQL> alter table mv add constraint mv_chk check (1=0);
    
    Table altered.
    
    SQL> INSERT INTO AA VALUES (A(1, 'Andy', address_udt(6, 'North ST')));
    
    1 row created.
    
    SQL> insert into bb values (b(1, 'Andy', address_udt(6, 'North ST'), 'M'));
    
    1 row created.
    
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error in materialized view refresh path
    ORA-02290: check constraint (TANDREWS.MV_CHK) violated
    How it works:
    - The materialized view mv selects the join of aa and bb on id: this must always be empty for your constraint
    - The check constraint mv_chk ensures that whenever a row is inserted into the materialized view the condition 1=0 is TRUE (of course it never is!)
    - The materialized view is maintained whenever you COMMIT changes.

    So if you ever insert an id into aa that already exists in bb (or vice versa), Oracle will attempt to create a row in mv, and is sure to fail on mv_chk.

    Note: you still need primary keys on the tables! The mv will not prevent inserting duplicates into aa alone, or bb alone.

  9. #9
    Join Date
    Jun 2004
    Posts
    127
    would another option be to use table inheritance? i.e.

    CREATE TABLE BB UNDER AA - although is has this feature been implemented in oracle? (I know its not supported in ver 9.01)

    AS For 'the check value in' constraint, i take it this cannot be done with domains?

  10. #10
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Im having Oracle8i.
    Is this feature not supported in 8i?

    SQL> CREATE TYPE B UNDER A (
    2 gender VARCHAR2(1));
    3 /

    Warning: Type created with compilation errors.

    SQL> sho err
    Errors for TYPE B:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    1/8 PLS-00103: Encountered the symbol "UNDER" when expecting one of
    the following:
    ; is authid as compress compiled wrapped

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by FAC51
    would another option be to use table inheritance? i.e.

    CREATE TABLE BB UNDER AA - although is has this feature been implemented in oracle? (I know its not supported in ver 9.01)

    AS For 'the check value in' constraint, i take it this cannot be done with domains?
    No: Oracle does not support table inheritance (AFAIK), and does not support domains.

    You would do well to read all the documentation Oracle provides on this topic, e.g. Application Developer's Guide - Object-Relational Features.

    By the way, are you doing this as a pure learning exercise, or do you actually intend to build a database like this? I would strongly advise against the latter.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by neema
    Im having Oracle8i.
    Is this feature not supported in 8i?
    Apparently not. It works in 9.2 for sure.

Posting Permissions

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