PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Nested Table Constraints
Version 11.1
 
Primary Key Constraint

Object Identifier		 CREATE OR REPLACE TYPE address_type AS OBJECT (
street  VARCHAR2(30),
city    VARCHAR2(30),
state   VARCHAR2(2),
zipcode VARCHAR2(5));
/

desc address_type

SELECT text
FROM user_source
WHERE name = 'ADDRESS_TYPE';

CREATE OR REPLACE TYPE person_type AS OBJECT (
name         VARCHAR2(30),
dob          DATE,
home_address address_type,
work_address address_type);
/

desc person_type

set describe depth all linenum on indent on

desc person_type

SELECT text
FROM user_source
WHERE name = 'PERSON_TYPE';

SELECT type_name, type_oid, typecode, incomplete, final,
instantiable
FROM user_types;

CREATE TABLE people OF person_type (
CONSTRAINT pk_people
PRIMARY KEY (name))
OBJECT IDENTIFIER IS PRIMARY KEY;

desc people

-- not here
SELECT table_name, nested
FROM user_tables;

-- not here
SELECT table_name
FROM user_nested_tables;

-- here
SELECT table_name, nested
FROM user_all_tables;

col object_name format a30

SELECT object_name, object_type
FROM user_objects;

-- Note: use type name from above query
SELECT text
FROM user_source
WHERE name = 'SYS_YOID0000117802$';

SELECT constraint_name, constraint_type, table_name
FROM user_constraints;
 
DANGLING
When a REF value points to a nonexistent object, the REF is said to be "dangling". A dangling REF is different from a null REF. To determine whether a REF is dangling or not, use the condition IS [NOT] DANGLING. DANGLING
conn oe/oe

SELECT o.customer_ref.cust_email
FROM oc_orders o
WHERE o.customer_ref IS NOT DANGLING;

SELECT o.customer_ref.cust_email
FROM oc_orders o
WHERE o.customer_ref IS DANGLING;
 
DEREF
returns the object reference of argument expr, where expr must return a REF to an object DEREF(<expression>)
See Demo below
 
MAKE_REF
Creates a REF to a row of an object view or a row in an object table whose object identifier is primary key based. MAKE_REF(<table_or_view_name>, <key>)
SELECT MAKE_REF (oc_inventories, 3003) FROM dual;
 
REF
Takes a correlation variable (table alias) associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row. REF(<correlation_variable>)
See Demo below
 
Referential Constraint Demo

Foreign Key Without OID Reference
CREATE OR REPLACE TYPE cust_address_t AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE TABLE address_table OF cust_address_t;

desc address_table

INSERT INTO address_table
VALUES ('123 Main St.','98040','Mercer Island','WA','US');

INSERT INTO address_table
VALUES ('1 Broadway','10202','New York','NY','US');

INSERT INTO address_table
VALUES ('2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN');

SELECT * FROM address_table;

SELECT REF(tabref) FROM address_table tabref;

CREATE TABLE customer_addresses (
add_id  NUMBER(10),
address REF cust_address_t SCOPE IS address_table);

set describe depth all linenum on indent on

desc customer_addresses

INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a
WHERE country_id = 'CN';

SELECT *
FROM customer_addresses;

ROLLBACK;

INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a;

SELECT *
FROM customer_addresses;

col deref_add format a80

SELECT add_id, DEREF(address) DEREF_ADD
FROM customer_addresses;
 
Check Constraint
Conditions of check constraints cannot contain the following constructs:
  • Dereferencing of REF columns (for example, using the DEREF function)
  • Nested table columns or attributes
  • Calls to user-defined functions
 
Related Topics
Constraints
Conversion Functions (REFTOHEX)
Nested Tables
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [105 users online]    © 2010 psoug.org