Transcript Object Tables

What is an Oracle Object? (cont.)
●
Essential Background (cont)
–
Oracle Object Syntax
●
TREAT(x AS y)
–
●
REF(alias)
–
●
SELECT REF(a) FROM Animal_Obj_Table a;
VALUE(alias)
–
●
v_animal := TREAT(v_cat AS Animal_Type);
SELECT VALUE(a) FROM Animal_Obj_Table a;
DEREF(ref)
–
SELECT DEREF(pet_owner.pet_ref) FROM Pet_Owner_Tab;
Object Tables - Issues
●
Type/Data Binding – The sucker hole
–
What makes a type match?
●
●
●
●
●
Each type created is assigned a unique id within DB
Different schemas mean different type id
Dropping/Creating gets new id
Saved data is marked with type id
Type id of saved data cannot be changed
Object Tables - Issues
●
Type Evolution
–
Alter Type has lots of options
●
●
●
–
Add/Modify/Drop Attribute
Replace
Cascade (Not) Including Table Data
Sometimes (but not always) they work
●
●
Evolved Type Limitations
Internal Errors
Object Tables – Object Columns
●
Object Columns
–
–
–
CREATE TABLE table_name
(col_1
NUMBER,
col_2
object_type
);
A column of an object type in a relational table
Cannot be accessed via REF
Object Views - Syntax (simplified)
CREATE OR REPLACE VIEW view_name
OF obj_type_name
WITH OBJECT IDENTIFIER(key_constructor)
AS select_stmt
;
● view_name – Name of view being created
● obj_type_name – Type of object being returned
● key_constructor – Set of values identifying the data
● select_stmt – Returns the attributes needed to construct
the object
Object Views - Syntax (simplified)
CREATE OR REPLACE VIEW vic_mode_v OF
vic_mode
WITH OBJECT IDENTIFIER(mode_key) AS
SELECT m.mode_key,
m.mode_name,
m.model_key,
m.max_speed,
m.endurance
FROM vic_mode_tab m;
Object Views – Updatable
●
Updatable object views
–
–
–
Can be difficult or impossible to construct and may
involve some highly complex DDL
May become not updatable depending on underlying
data structures
Preserve traditional Insert/Update/Delete coding
methodology
Object Views - Instead Of Triggers
●
Instead Of Triggers
–
–
–
CREATE OR REPLACE TRIGGER trig_name
INSTEAD OF action ON view_name
BEGIN
...
END;
Intercept DML operations on non-updatable views
Can perform complex DML
Object Views - Persistence Methods
●
Object Methods
–
–
–
–
Can perform complex DML
Isolates DML from application logic as objects just
“know” how to “save” themselves
Generalized “Save()” method can inspect object
attributes representing PK to determine if INSERT or
UPDATE is appropriate
Could use “local indicator” attributes to determine if
ANY action is needed
Object Tables - REFs
●
REFs – Pointers by any other name
–
–
–
Implicit dereferencing in SQL
Explicit dereferencing (via UTL_REF package) in
PL/SQL
System Generated
●
Up to 42 bytes long
–
–
–
●
●
16 byte OID of object
16 byte OID of table or view
10 byte ROWID hint
Point to an instance in an object table
May be saved in a REF column of a table or attribute of an
object table
Object Tables - REFs
●
REFs – Pointers by any other name (cont.)
–
User REFs
●
Consist of PK values needed to construct object
Time Permitting – Implementation
Options
●
If you decide to try it
–
–
–
–
–
Everything in 1 schema (worst)
Separate type schema for each environment (better)
Shared type schema (better)
Shared type schema with invoker rights (best)
Beware of REFs pointing to source data
Time Permitting - Obj Tables – All in
One
●
Database A
Database B
Schema 1 Types
Schema 1 Types
●
Schema 1
Object Tables
Schema 1
Object Tables
●
●
Restore to new DB to
copy environment.
If you have the
resources
Not reasonable for
large applications
Dedicated DB
Time Permitting - OBJ Tables - Separate
Type Schema
Database A
●
Schema 1 Types
Syn
Schema 3 Types
Syn
●
●
Schema 2
Object Tables
Schema 4
Object Tables
Table data may be exported
from Schema 2 or 4 and
imported into same schema
Object tables must reference
Schema 1 or Schema 2 in
DDL
Synonyms required for
Schema 1 or 3 methods to
reference object table
Time Permitting - Obj Tables - Shared
Type Schema
Database A
●
Schema 1 Types
●
Public
Synonyms
●
Schema 2
Object Tables
Schema 3
Object Tables
Table data may be exported
from Schema 2 or 3 and
imported into same schema
Object tables do not qualify
type schema in DDL
Type evolution affects
schema2 and 3.
Time Permitting - Obj Tables - Shared
Type Schema with Invoker Rights
Database A
Schema 2
Context
Schema 3
Context
Schema 1 Types
●
●
●
Schema 2
Object Tables
Schema 3
Object Tables
Table data may be exported
from Schema 2 or 3 and
imported into same schema
Object tables may qualify
type schema or use public
synonyms in DDL
Type evolution affects
schema 2 and 3.
Time Permitting - Object Tables – UH
OH!
●
If you already did and got it wrong (All in One
Schema)
–
–
Custom conversion code
To separate tables from type schema:
●
●
●
●
●
●
Backup DB
Create schema to hold object tables
Pre-create tables qualifying object type in DDL
Copy data (INSERT INTO SELECT * ....)
If copied data contains REFS, create cross reference table
of old REF, new REF and PK of object
Swap the old REFs for the new REFs in the new data
Time Permitting - Object Tables – UH
OH!
●
If you already did and got it wrong (All in One
Schema) (cont.)
–
To separate tables from type schema (cont.):
●
●
●
●
●
If possible, REPLACE types using invoker rights otherwise
create private synonyms in type schema to new tables
DROP original tables
Verify Export/Import on new table schema
Move/Modify packages, procedures, etc. as necessary to
obtain working application
TEST TEST TEST
Time Permitting - What is an Oracle
Object? (cont.)
●
Advanced Topics (just a sample)
–
–
–
–
–
ANYDATA
Manufacturing REFs in object views
Complex modeling using object views
Object table internals
Object table tuning