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”!


	CREATE TYPE source AS OBJECT (
	name VARCHAR2(30),
	ra FLOAT,
	dec FLOAT
	)
	NOT FINAL;
	/


	CREATE TYPE bsource
	UNDER source
	(
	bmag NUMBER(5, 2)
	)
	NOT FINAL;
	/


	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 ) );


	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


	CREATE TABLE mysources ( petsource VARCHAR2(170), bestars REF source );


	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;


	Fixed maximum size
	Fixed order
	CREATE TYPE intarray AS VARRAY(20) OF INTEGER;
	/


	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;


	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


	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 “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”!