Create the object and collection types
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 );
7 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created.
SQL>
SQL> -- varray in temporary table example
SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeVArray
6 );
SQL>
SQL> CREATE TABLE empTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeNestedTable
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses2 TABLESPACE users;
Table created.
SQL>
SQL>
SQL> -- equal/not equal example
SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable('A', 'B', 'C');
9 emp2 := charTable('A', 'B', 'C');
10 emp3 := charTable('B', 'C', 'D');
11
12
13 result := emp1 <> emp3;
14 IF result THEN
15 DBMS_OUTPUT.PUT_LINE('emp1 not equal to emp3');
16 END IF;
17 END equal_example;
18 /
Procedure created.
SQL>
SQL> drop type addressType force;
Type dropped.
SQL> drop type addressTypeVArray force;
Type dropped.
SQL> drop TYPE addressTypeNestedTable force;
Type dropped.
SQL> drop TABLE empTable;
Table dropped.
Related examples in the same category