Oracle Desk Reference

Guy Harrison has been an Oracle DBA and application developer for over a decade. He is author of Oracle SQL High Performance Tuning (Prentice Hall PTR), and has written for many leading database publications, including Database Programming and Design and Oracle Magazine. He is currently Project Manager for Database Monitoring Tools at Quest Software.

1. The Select Statement.


Introduction. Reference. General Form. Subqueries. Correlated Subquery. FROM Clause Table Specifications. Simple Table Specification. Subquery. Partition Specification. Collection Specification. Sampling. Joins. Inner Join. Equi-joins and Theta Joins. Outer Join. Anti-join. Self-join. Set Operations. Group Operations. Hierarchical Queries. Locking. Select Statement Reference.



2. SQL Expressions and Functions.


Introduction. References. Identifiers. Literals. Column and Object Names. Bind Variables. Operators. User-Defined Operators. Functions. User-Defined Functions. Date and Numeric Format Masks. Date Format Masks. Numeric Format Masks. Pseudo-Columns. Hints.



3. Data Manipulation and Transaction Control.


Introduction. The insert Statement. Direct Mode Insert. Parallel Insert. Inserting REF Data. Unlogged Inserts. Array Insert. The update Statement. Correlated Updates. The delete Statement. Table Specifications. Transactions. Savepoints. Locks. Autonomous Transactions. Triggers. DML Statements on Views. DML Reference.



4. Data Definition Language (DDL).


Introduction. References. Database Objects and Datatypes. Types of Database Objects. Datatypes. System Privileges. Audit Options.



5. PL/SQL Language.


Introduction. References. PL/SQL Block Structure. Operators. PL/SQL Functions. PL/SQL Variables and Datatypes. RecordTypes. %type and %rowtype. Cursors. Cursor Variables. Collections and PL/SQL Tables. Scoping. Exceptions and Error Handling. Exceptions. Raise_application_error. PL/SQL Stored Objects. Stored Procedure Parameters. Packages. Triggers. Object Types. Forward Declarations. Overloading. Execution Privileges. PL/SQL Functions in SQL statements. Transactions. Autonomous Transactions. SQL Statements. PL/SQL language reference.



6. Oracle-Supplied PL/SQL Packages.


Introduction. References. Calling Supplied Packages. Installing Supplied Packages. Package dbms_alert. Package dbms_application_info. Package dbms_ddl. Package dbms_job. Package dbms_lob. Package dbms_lock. Package dbms_logmnr. Package dbms_logmnr_d. Package dbms_output. Package dbms_pipe. Package dbms_random. Package dbms_rowid. Package dbms_session. Package dbms_shared_pool. Package dbms_snapshot. Package dbms_space. Package dbms_sql. Package dbms_stats. Package dbms_system. Package dbms_transaction. Package dbms_utility. Package utl_file. Package utl_raw.



7. Oracle Java.


Introduction. Reference. Overview of JDBC. Required Classes. Establishing a Connection. Error Handling in JDBC. Creating SQL Statements. Executing SQL Statements. Processing Queries. Bind and Substitution Variables. Dynamic Queries. Stored Procedures. Transaction Control. Oracle JDBC Extensions. Overview of SQLJ. Importing Classes. SQLJ Statements. Context Declarations. Iterator Declarations. Executable Statement Declarations. Setting Up a Connection. Simple SQL Statements. Query Processing. Error Handling. Bind Variables. Transaction Control. Translating SQLJ Programs. Creating Java Stored Procedures and Functions. Loading Java into the Database. Resolver Specifications. Stored Procedure Privileges. Publishing Java Stored Procedures. JDBC Language Reference. Datatypes. DriverManager Class. Connection Interface. OracleConnection Extensions. Statement Class. OracleStatement Extensions. PreparedStatement Class. OraclePreparedStatement Extensions. CallableStatement Interface. OracleCallableStatement. ResultSet interface. OracleResultSet Extensions. ResultSetMetaData Interface. DatabaseMetaData Interface. SQLJ Language Reference. Connection Context Methods. Execution Context Methods. Iterator Class Methods.



8. Command Line Utilities.


Introduction. Naming Conventions. Command Summaries.



9. SQL*PLUS.


Introduction. Reference. Command-Line Arguments. SQL*PLUS Features. Parameter Substitution. SQL*Plus Edit Buffer. Bind Variables. Formatting Options. SQL*Plus Commands. Options of the SET Command.



10. Initialization Parameters.


Introduction. Reference. Parameter Descriptions.



11. Terms, Acronyms, and Jargon.


Introduction. Reference. Terms.



Appendix A: Internet Resources.


The Oracle Technology Network. Oracle Support Services. Oracle Usenet Newsgroups. Deja News Power Search. The Database Domain. RevealNet Pipelines. OraPub. International Oracle User Group. Hayden Worthington, Inc. The Oracle Underground FAQ. Oracle Magazine. Oreview. The Ultimate Software Consultants (TUSC).



Index.