Part 9iSQLforOracle 9iOracle 9iRelease 9.0.1.0.0 – Production and above.Available in various editions: personal, lite, workgroup, enterprise.JServer available as an add-on.2002 release of the Oracle database managementsystem.Not to be confused with “Oracle 11”, which is a set of application packages.More tightly tied to Java and the internet.Performance enhancements.Data mining enhancements.Support for ANSI Standard SQL, SQL/92.The Oracle 9i SQL Reference Manual is 1652 pages long.Copyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 2Oracle 9i SQL CommandsSQL command forms:alter associate statistics auditcall comment commitcreate deletedisassociate statisticsdrop explain plan grantinsert lock table mergeno audit rename revokerollback savepoint selectset truncate updateCopyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 3Oracle 9i Alter/Create/Drop Variantsalter cluster create cluster drop clustercreate context drop contextcreate controlfilealter database create databasecreate database link drop database linkalter dimension create dimension drop dimensioncreate directory drop directoryalter function create function drop functionalter index create index drop indexalter indextype create indextype drop indextypealter java create java drop javacreate library drop libraryalter materialized view create materialized view drop materialized viewalter materialized view logcreate materialized view logdrop materialized view logalter operator create operator drop operatoralter outline create outline drop outlinealter package create package drop packagecreate package bodycreate pfilealter procedure create procedure drop procedurealter profile create profile drop profilealter resource costalter role create role drop rolealter rollback segment create rollback segment drop rollback segmentcreate schemaalter sequence create sequence drop sequencealter sessioncreate spfilecreate synonym drop synonymalter systemalter table create table drop tablealter tablespace create tablespace drop tablespacecreate temporary tablespacealter trigger create trigger drop triggeralter type create type drop typecreate type body drop type bodyalter user create user drop useralter view create view drop viewCopyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 4Oracle 9i Table CreationOracle 9i’s create command not only brings a base table into existence, but it defines the field names, the field formats, storage characterisitics, event handling, object characteristics, and many other things.The Oracle 9i SQL manual devotes 87 pages to the description of this one command.To create an “ordinary” relational table, use the syntax:CREATEoptional GLOBAL TEMPORARYTABLEoptional schema.table_nameoptional ( relational_properties )optional ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWSoptional physical_propertiesoptional table_properties;Copyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 5Oracle 9i Object SupportOracle 9i provides support for objects within the context of its relational database engine, using extended SQL syntax.To create an Oracle table of objects, use the syntax:CREATEoptional GLOBAL TEMPORARYTABLEoptional schema.table_name OFoptional schema.object_typeoptional object_table_substitutionoptional ( object_properties )optional ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWSoptional OID_clauseoptional OID_index_clauseoptional physical_propertiesoptional table_properties;Copyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 6Oracle 9i Built-in Datatypes AvailableOracle 9i has six subdivisions of built-in datatypes. The first two are character data and numeric data.Character: (maximum width determined by the number ofbytes per character)CHAR (width) fixed length string up to 2000 bytesCHAR (width BYTE) fixed length string up to 2000 bytes,byte semanticsCHAR (width CHAR) fixed length string up to 2000 bytes,character semanticsVARCHAR2 (width) variable length string up to 4000 bytesVARCHAR2 (width BYTE) variable length string up to 4000 bytes, byte semanticsVARCHAR2 (width CHAR) variable length string up to 4000 bytes, character semanticsNCHAR (width) fixed length national character stringup to 2000 bytesNVARCHAR2 (width) variable length national character string up to 4000 bytesNumber:NUMBER floating point number of maximum precisionNUMBER (precision) 1 to 38 digit precisionNUMBER (precision.scale) -84 to 127 scaleCopyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 7More Oracle 9i Built-in DatatypesOracle supports long character and bit streams and dates.Long and Raw:LONG character data up to 2 GBLONG RAW variable binary data up to 2 GBRAW (size) binary data up to 2000 bytesDate and TimeDATE Valid date from January 1, 4712 BC to December 31, 9999 AD.TIMESTAMP Year through second to 6 decimalsTIMESTAMP (precision) Year through second 0 to 9 decimalsTIMESTAMP WITH TIME ZONETIMESTAMP (precision) WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONETIMESTAMP (precision) WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTH Years and months to 2 year decimalsINTERVAL YEAR (precision) TO MONTHYears and months to 0 to 9 year decimalsINTERVAL DAY TO SECOND Days to 2 decimals through seconds to 6 decimalsINTERVAL DAY (precision) TO SECONDDays to 0 to 9 decimals through seconds to 6 decimalsINTERVAL DAY TO SECOND (precision)Days to 2 decimals through seconds to 0 to 9 decimalsINTERVAL DAY (precision) to SECOND (precision)Copyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 8Remaining Oracle 9i Built-in DatatypesOracle supports long objects and uses row ID addresses internally.Large ObjectsBLOB binary large object to 4 GBCLOB character large object to 4 GBNCLOB Unicode large object to 4 GBBFILE locator to binary file object to 4 GBRow IDROWID base 64 string physical addressUROWID base 64 string logical index addressUROWID(size)Copyright © 1971-2002 Thomas P. Sturm SQL for Oracle 9i Part 9i, Page 9Oracle 9i ANSI, DB2, and SQL/DSDatatypes AvailableOracle 9i provides support for the following ANSI types:ANSI DATA TYPE ORACLE IMPLEMENTATIONCHARACTER (size) CHAR (size)CHARACTER VARYING (size) VARCHAR2 (size)CHAR VARYING (size) VARCHAR2 (size)NCHAR VARYING (size) NVARCHAR2 (size)VARCHAR (size) VARCHAR2 (size)NATIONAL CHAR (size) NCHAR (size)NATIONAL CHAR VARYING (size) NVARCHAR2 (size)NATIONAL CHARACTER (size) NCHAR (size)NATIONAL CHARACTER VARYING (size)NVARCHAR2 (size)NUMERIC NUMBERDECIMAL NUMBERDEC