4 Replies Latest reply: Mar 17, 2011 8:42 PM by Barbara Boehmer RSS

    Create object

    847013
      hi guys
      plz help me in this problem i created type as object with some columns then i created table that will use columns of this type.
      my question is how to add more columns into this table. i tried my times but couldn't even i tried to alter the table


      create type resource_type as object (
      title varchar2(35),
      publisher varchar2(35),
      location varchar2 (35),
      member function getstatus return varchar,
      PRAGMA RESTRICT_REFERENCES(getstatus, WNDS)
      );
      /


      create table book of resource_type;
      thax
        • 1. Re: Create object
          Barbara Boehmer
          Please see the demonstration below.
          SCOTT@orcl_11gR2> create type resource_type as object (
            2    title       varchar2 (15),
            3    publisher  varchar2 (15),
            4    location   varchar2 (15),
            5    member function getstatus return varchar,
            6    PRAGMA RESTRICT_REFERENCES (getstatus, WNDS)
            7  );
            8  /
          
          Type created.
          
          SCOTT@orcl_11gR2> create table book of resource_type
            2  /
          
          Table created.
          
          SCOTT@orcl_11gR2> desc book
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           TITLE                                              VARCHAR2(15)
           PUBLISHER                                          VARCHAR2(15)
           LOCATION                                           VARCHAR2(15)
          
          SCOTT@orcl_11gR2> insert into book values
            2    (resource_type ('title1', 'pub1', 'loc11'))
            3  /
          
          1 row created.
          
          SCOTT@orcl_11gR2> commit
            2  /
          
          Commit complete.
          
          SCOTT@orcl_11gR2> select * from book
            2  /
          
          TITLE           PUBLISHER       LOCATION
          --------------- --------------- ---------------
          title1          pub1            loc11
          
          1 row selected.
          
          SCOTT@orcl_11gR2> alter type resource_type
            2  add attribute
            3    (author       varchar2 (15))
            4    cascade
            5  /
          
          Type altered.
          
          SCOTT@orcl_11gR2> desc book
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           TITLE                                              VARCHAR2(15)
           PUBLISHER                                          VARCHAR2(15)
           LOCATION                                           VARCHAR2(15)
           AUTHOR                                             VARCHAR2(15)
          
          SCOTT@orcl_11gR2> update book
            2  set    book.author = 'author1'
            3  where  title = 'title1'
            4  /
          
          1 row updated.
          
          SCOTT@orcl_11gR2> insert into book values
            2    (resource_type ('title2', 'pub2', 'loc2', 'author2'))
            3  /
          
          1 row created.
          
          SCOTT@orcl_11gR2> commit
            2  /
          
          Commit complete.
          
          SCOTT@orcl_11gR2> select * from book
            2  /
          
          TITLE           PUBLISHER       LOCATION        AUTHOR
          --------------- --------------- --------------- ---------------
          title1          pub1            loc11           author1
          title2          pub2            loc2            author2
          
          2 rows selected.
          
          SCOTT@orcl_11gR2> 
          • 2. Re: Create object
            847013
            thk a lot
            really i appreciate it
            • 3. Re: Create object
              847013
              i did it and it was working but i found anther problem which is when i create anther table (multimedia table that has columns length, file format, storage type) that using resource_type columns which are Title, Publisher and location , it is appear other columns for book table and i dont want columns of book table to appear in multimedia table, just resource_type

              so what i should do??plz help me
              thx
              • 4. Re: Create object
                Barbara Boehmer
                I am not sure if the following is what you are looking for. If not, please clarify.
                SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE resource_typ AS OBJECT (
                  2    title            VARCHAR2(15),
                  3    publisher       VARCHAR2(15),
                  4    location        VARCHAR2(15))
                  5   NOT FINAL;
                  6  /
                
                Type created.
                
                SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE book_typ UNDER resource_typ (
                  2       author            VARCHAR2(15));
                  3  /
                
                Type created.
                
                SCOTT@orcl_11gR2> CREATE TABLE book_tab OF book_typ
                  2  /
                
                Table created.
                
                SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE multimedia_typ UNDER resource_typ (
                  2       length            NUMBER,
                  3       file_format    VARCHAR2(15),
                  4       storage_type   VARCHAR2(15));
                  5  /
                
                Type created.
                
                SCOTT@orcl_11gR2> CREATE TABLE multimedia_tab OF multimedia_typ
                  2  /
                
                Table created.
                
                SCOTT@orcl_11gR2> INSERT INTO book_tab VALUES
                  2  (book_typ ('title1', 'pub1', 'loc1', 'auth1'))
                  3  /
                
                1 row created.
                
                SCOTT@orcl_11gR2> INSERT INTO multimedia_tab VALUES
                  2  (multimedia_typ ('title2', 'pub2', 'loc2', 10, 'format2', 'type2'))
                  3  /
                
                1 row created.
                
                SCOTT@orcl_11gR2> SELECT * FROM book_tab
                  2  /
                
                TITLE           PUBLISHER       LOCATION        AUTHOR
                --------------- --------------- --------------- ---------------
                title1          pub1            loc1            auth1
                
                1 row selected.
                
                SCOTT@orcl_11gR2> SELECT * FROM multimedia_tab
                  2  /
                
                TITLE           PUBLISHER       LOCATION            LENGTH FILE_FORMAT
                --------------- --------------- --------------- ---------- ---------------
                STORAGE_TYPE
                ---------------
                title2          pub2            loc2                    10 format2
                type2
                
                
                1 row selected.
                
                SCOTT@orcl_11gR2>