Notes
Outline
Oracle and Object-Oriented SQL
Oracle specific SQL commands
User-defined types
CREATE TYPE source
AS OBJECT
(
name VARCHAR2(30),
ra   FLOAT,
dec  FLOAT
)
NOT FINAL;
/
Inheritance
CREATE TYPE bsource
UNDER source
(
bmag NUMBER(5, 2)
)
NOT FINAL;
/
Instantiating a type
An object table is a table with a single column of a user-defined type

CREATE TABLE sources OF source;
INSERT INTO sources
VALUES
(
rsource( 'R2D2', 4.3, -22.9, 21.2 )
);
Accessing types
SELECT VALUE(*) FROM sources;
retrieves constructors from “sources”
CREATE VIEW sources$v
OF source;
an object view with objects of type “source”
CREATE VIEW bsources$v OF bsource
UNDER sources$v;
an object view with objects of type “bsource” which, at the same time, makes those objects available from the “sources$v”
view
References
CREATE TABLE mysources
(
petsource VARCHAR2(170),
bestars REF source
);
Methods
Include in the TYPE definition:
MEMBER FUNCTION lon() RETURN NUMBER
CREATE TYPE BODY source
AS MEMBER FUNCTION lon ()
RETURN NUMBER
IS BEGIN
/* left as an exercise */
RETURN lon;
END;
END;
Varying Arrays
Fixed maximum size
Fixed order
CREATE TYPE intarray
AS VARRAY(20) OF INTEGER;
/
Nested tables
Unlimited maximum size
Random order
CREATE TYPE intarray
AS TABLE OF INTEGER;
CREATE TABLE demo
( name VARCHAR2(37), v intarray )
NESTED TABLE v STORE AS vtable;
Command-line usage
sqlplus username@databaseservice
@script - start script named script.sql
edit – edit the buffer
describe – gives definition of datastructures
spool – write results to file
!command – run unix command  in a shell
! – spawn a unix shell
GUI access
oemapp worksheet
oemapp console (requires SELECT ANY DICTIONARY privilige)
Follow the built-in quick tours for an overview of database concepts and functions.
Use the ‘Show SQL’ button to see what the equivalent SQL query is for a GUI operation
Python
Python “class” ~ Oracle “type”
Python DB API for direct access
DBObject for ASTRO-WISE access
from astro.database.DBMain import DBObject, persistent

class Source(DBObject):
name = persistent(‘The name’, str, ‘Nameless’)
ra = persistent(‘R.A.’, float, 0.0)
dec = persistent(‘Declination’, float, 0.0)
The class is mapped to a “TYPE”, a “TABLE” and a “VIEW”!