Oracle and ObjectOriented SQL

1
Oracle and Object-Oriented SQL

• Oracle specific SQL commands

2
User-defined types

• CREATE TYPE sourceAS OBJECT(
• name VARCHAR2(30),
• ra FLOAT,
• dec FLOAT
• )
• NOT FINAL
• /

3
Inheritance

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

4
Instantiating a type

• An object table is a table with a single column
  of a user-defined typeCREATE TABLE sources OF
  source
• INSERT INTO sourcesVALUES( rsource( 'R2D2',
  4.3, -22.9, 21.2 ))

5
Accessing types

• SELECT VALUE() FROM sourcesretrieves
  constructors from sources
• CREATE VIEW sourcesvOF sourcean object view
  with objects of type source
• CREATE VIEW bsourcesv OF bsourceUNDER
  sourcesvan object view with objects of type
  bsource which, at the same time, makes those
  objects available from the sourcesvview

6
References

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

7
Methods

• Include in the TYPE definitionMEMBER FUNCTION
  lon() RETURN NUMBER
• CREATE TYPE BODY sourceAS MEMBER FUNCTION lon
  ()RETURN NUMBERIS BEGIN / left as an
  exercise / RETURN lon ENDEND

8
Varying Arrays

• Fixed maximum size
• Fixed order
• CREATE TYPE intarrayAS VARRAY(20) OF INTEGER
• /

9
Nested tables

• Unlimited maximum size
• Random order
• CREATE TYPE intarrayAS TABLE OF INTEGER
• CREATE TABLE demo( name VARCHAR2(37), v intarray
  )NESTED TABLE v STORE AS vtable

10
Command-line usage

• sqlplus username_at_databaseservice
• _at_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

11
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

12
Python

• Python class Oracle type
• Python DB API for direct access
• DBObject for ASTRO-WISE access
• from astro.database.DBMain import DBObject,
  persistentclass 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!