 |
|
Putting Methods to Work in Oracle
Oracle Tips by Mike Ault |
Creation of a
REF Request and REF Passing Method Set
CREATE TABLE job_sites
OF site_t
(employee_id_r WITH ROWID
SCOPE IS employees)
OIDINDEX oid_job_sites (TABLESPACE raw_index)
PCTFREE 10 PCTUSED 80
INITRANS 5 MAXTRANS 255
TABLESPACE raw_data
STORAGE ( INITIAL 20m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 10
PCTINCREASE 0
)
/
ALTER TABLE job_sites ADD
CONSTRAINT PK_job_sites
PRIMARY KEY (site_id)
USING INDEX
PCTFREE 20
INITRANS 5 MAXTRANS 255
TABLESPACE raw_index
STORAGE (INITIAL 10m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 0 FREELISTS 5
)
/
CREATE TYPE BODY site_t AS
MEMBER FUNCTION get_ref (emp_id IN integer)
RETURN INTEGER IS
status INTEGER:=0;
BEGIN
SELF.employee_id_r := emp_t.give_ref(emp_id);
RETURN status;
EXCEPTION
WHEN others THEN
status := 1;
RETURN status;
END;
END;
/
As you can see the actual methods, get_ref
and give_ref are quite small and non-complex. To use the
methods they are called like normal functions during insert or
update operations involving the REFed row in the employees table
object or the actual stored REF value in the job_sites table object.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
If you
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with
over 900 pages of BC's favorite tuning tips &
scripts.
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning
scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
