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

    Unanswered: Inserting values into domains or types

    can anyone show me the syntax of how to insert values into domains or types.

    for example i have create these types:

    TYPE Test1_UDT AS (test_1 INT)

    TYPE Test2_UDT AS (test_2 INT)

    how would i insert values of test_1 or test_2 (integers) type into either Test1_UDT or Test2_UDT

  2. #2
    Join Date
    Nov 2003
    Posts
    33
    for i in test_1.first ... test_1.last
    loop
    test_udt(i) := test_1(i);
    end loop;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can't insert into a TYPE - that would be like "INSERT INTO VARCHAR2..."!

    What you can do is base variables or columns on these types and then set the values of those variables or columns:
    Code:
    SQL> create TYPE Test1_UDT AS object (test_1 INT)
    SQL> /
    
    Type created.
    
    SQL> create table t (col1 test1_udt);
    
    Table created.
    
    SQL> insert into t (col1) values (test1_udt(123));
    
    1 row created.
    Code:
    SQL> declare
      2    v test1_udt;
      3  begin
      4    v := test1_udt(456);
      5    insert into t (col1) values (v);
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from t;
    
    COL1(TEST_1)
    --------------------------------------------------------------------------------
    TEST1_UDT(123)
    TEST1_UDT(456)

  4. #4
    Join Date
    Jun 2004
    Posts
    127
    is it possible to place constraints on domains such as unique, or ref integrity constraints? & if so, how is it done?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, such constraints can only be created on the table not on the type/domain.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    One use of the members in SQL types could be to enforce constraint within the type's attributes.

    i.e.
    Code:
    SQL@8i> create or replace type mytype as object (
      2    id number,
      3    member function isvalid return boolean
      4  );
      5  /
    
    Type created.
    
    SQL@8i> create or replace type body mytype
      2  as
      3    member function isvalid return boolean
      4      is
      5    begin
      6      if id between 1 and 2
      7      then
      8        return true;
      9      else
     10        return false;
     11      end if;
     12    end;
     13  end;
     14  /
    
    Type body created.
    
    SQL@8i> declare
      2    n mytype := mytype( 1 );
      3  begin
      4    if n.isvalid()
      5    then
      6      dbms_output.put_line( 'Constraint not violated!' );
      7    else
      8      dbms_output.put_line( 'Constraint violated!' );
      9    end if;
     10  end;
     11  /
    Constraint not violated!
    
    PL/SQL procedure successfully completed.
    
    SQL@8i>
    Too bad you can't overload the constructor. Because of this limitation, you must first assign the attributes, and then later check the constraint by invoking its methods.

Posting Permissions

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