Query table column with user defined type : Select « Object Oriented « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Object Oriented
  3. Select
SQL>
SQL> create or replace type Address_Type
  2  as object
  3  (  street_addr1   varchar2(25),
  4     street_addr2   varchar2(25),
  5     city           varchar2(30),
  6     state          varchar2(2),
  7     zip_code       number
  8  )
  9  /

Type created.

SQL>
SQL> create table people
  2  ( name           varchar2(10),
  3    home_address   address_type,
  4    work_address   address_type
  5  )
  6  /

Table created.

SQL>
SQL> declare
  2      l_home_address address_type;
  3      l_work_address address_type;
  4  begin
  5      l_home_address := Address_Type( '1 Street', null,'R', 'VA', 45678 );
  6      l_work_address := Address_Type( '1 Way', null,'R', 'CA', 23456 );
  7
  8      insert into people( name, home_address, work_address )values ( 'Tom Kyte', l_home_address, l_work_address );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> column "HOME_ADDRESS.STATE" format a20
SQL> column "WORK_ADDRESS.STATE" format a20
SQL> select name, P.home_address.state, P.work_address.state from people P
  2  /


NAME       HOME_ADDRESS.STATE   WORK_ADDRESS.STATE
---------- -------------------- --------------------
Tom Kyte   VA                   CA

SQL> drop table people;

Table dropped.

SQL> drop type Address_Type;

Type dropped.








32.11.Select
32.11.1.Selecting Rows from the Table with object type column
32.11.2.Select an individual column object from a table
32.11.3.Querying Rows from the object Table
32.11.4.SELECTing Only One Column in the Composite
32.11.5.COLUMN Formatting in SELECT for Object
32.11.6.ORA-00932: inconsistent datatypes: expected REF
32.11.7.Query table column with user defined type
32.11.8.Use table function to convert type to a 'table'
32.11.9.Create type and use it in inner query
32.11.10.Use user-defined type to combine query logic