SELECTing Individual Columns in TCROs : Select « Object Oriented Database « Oracle PL / SQL






SELECTing Individual Columns in TCROs



SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /

Type created.

SQL>
SQL> CREATE TABLE address_table OF ADDRESS_OBJ;

Table created.

SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF address_obj scope is address_table);

Table created.

SQL>
SQL> INSERT INTO address_table VALUES (ADDRESS_OBJ('1 A St.','Mobile','AL','36608'));

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO client SELECT 'Walsh', REF(aa) FROM address_table aa;

1 row created.

SQL>
SQL>
SQL> -- SELECTing Individual Columns in TCROs
SQL>
SQL>
SQL> SELECT * FROM client;

NAME      ADDRESS
--------- --------------------------------------------------
Walsh     0000220208589A813178324DF39D575B3701C24CE19751BEFE
          0AF34AC18C9B571EDA8D0418


SQL>
SQL>
SQL>
SQL>
SQL> drop table client;

Table dropped.

SQL>
SQL> drop table address_table;

Table dropped.

SQL>
SQL>
           
       








Related examples in the same category

1.Display the New Table (SELECT * and SELECT by Column Name)
2.Query object table
3.Use type member function in select statement
4.SELECT Only One Column in the Composite
5.SELECT with a WHERE Clause
6.Query data from table based on object
7.Use the function we created: use the table alias in our SELECT as well as the qualifier
8.Use * to reference column with user-defined type
9.demonstrates SQL operations on object types.
10.Select the object type from the table, rather than the columns.
11.Use %ROWTYPE to select from the relational table.