• Title and Copyright Information
• Preface
  • Audience
  • Documentation Accessibility
  • Related Documents
  • Conventions
• Introduction to 2 Day Oracle Database Development
  • About This Document
  • About Oracle Database
    • About Schema Objects
    • About Oracle Database Access
      • About SQL*Plus
      • About SQL Developer
      • About Structured Query Language (SQL)
      • About Procedural Language/SQL (PL/SQL)
      • About Other Client Programs, Languages, and Development Tools
        • Oracle Application Express
        • Oracle Java Database Connectivity (JDBC)
        • Hypertext Preprocessor (PHP)
        • Oracle Call Interface (OCI)
        • Oracle C++ Call Interface (OCCI)
        • Open Database Connectivity (ODBC)
        • Pro*C/C++ Precompiler
        • Pro*COBOL Precompiler
        • Microsoft .NET Framework
        • Oracle Provider for OLE DB (OraOLEDB)
  • About Sample Schema HR
• Connecting to Oracle Database and Exploring It
  • Connecting to Oracle Database from SQL*Plus
  • Connecting to Oracle Database from SQL Developer
  • Connecting to Oracle Database as User HR
    • Unlocking the HR Account
    • Connecting to Oracle Database as User HR from SQL*Plus
    • Connecting to Oracle Database as User HR from SQL Developer
  • Exploring Oracle Database with SQL*Plus
    • Viewing HR Schema Objects with SQL*Plus
    • Viewing EMPLOYEES Table Properties and Data with SQL*Plus
  • Exploring Oracle Database with SQL Developer
    • Tutorial: Viewing HR Schema Objects with SQL Developer
    • Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer
  • Selecting Table Data
    • About Queries
    • Running Queries in SQL Developer
    • Tutorial: Selecting All Columns of a Table
    • Tutorial: Selecting Specific Columns of a Table
    • Displaying Selected Columns Under New Headings
    • Selecting Data that Satisfies Specified Conditions
    • Sorting Selected Data
    • Selecting Data from Multiple Tables
    • Using Operators and Functions in Queries
      • Using Arithmetic Operators in Queries
      • Using Numeric Functions in Queries
      • Using the Concatenation Operator in Queries
      • Using Character Functions in Queries
      • Using Datetime Functions in Queries
      • Using Conversion Functions in Queries
      • Using Aggregate Functions in Queries
      • Using NULL-Related Functions in Queries
      • Using CASE Expressions in Queries
      • Using the DECODE Function in Queries
• About DML Statements and Transactions
  • About Data Manipulation Language (DML) Statements
    • About the INSERT Statement
    • About the UPDATE Statement
    • About the DELETE Statement
  • About Transaction Control Statements
  • Committing Transactions
  • Rolling Back Transactions
  • Setting Savepoints in Transactions
• Creating and Managing Schema Objects
  • About Data Definition Language (DDL) Statements
  • Creating and Managing Tables
    • About SQL Data Types
    • Creating Tables
      • Tutorial: Creating a Table with the Create Table Tool
      • Creating Tables with the CREATE TABLE Statement
    • Ensuring Data Integrity in Tables
      • About Constraints
      • Tutorial: Adding Constraints to Existing Tables
    • Tutorial: Adding Rows to Tables with the Insert Row Tool
    • Tutorial: Changing Data in Tables in the Data Pane
    • Tutorial: Deleting Rows from Tables with the Delete Selected Row(s) Tool
    • Managing Indexes
      • Tutorial: Adding an Index with the Create Index Tool
      • Tutorial: Changing an Index with the Edit Index Tool
      • Tutorial: Dropping an Index
    • Dropping Tables
  • Creating and Managing Views
    • Creating Views
      • Tutorial: Creating a View with the Create View Tool
      • Creating Views with the CREATE VIEW Statement
    • Changing Queries in Views
    • Tutorial: Changing View Names with the Rename Tool
    • Dropping a View
  • Creating and Managing Sequences
    • Tutorial: Creating a Sequence
    • Dropping Sequences
  • Creating and Managing Synonyms
    • Creating Synonyms
    • Dropping Synonyms
• Developing Stored Subprograms and Packages
  • About Stored Subprograms
  • About Packages
  • About PL/SQL Identifiers
  • About PL/SQL Data Types
  • Creating and Managing Standalone Subprograms
    • About Subprogram Structure
    • Tutorial: Creating a Standalone Procedure
    • Tutorial: Creating a Standalone Function
    • Changing Standalone Subprograms
    • Tutorial: Testing a Standalone Function
    • Dropping Standalone Subprograms
  • Creating and Managing Packages
    • About Package Structure
    • Tutorial: Creating a Package Specification
    • Tutorial: Changing a Package Specification
    • Tutorial: Creating a Package Body
    • Dropping a Package
  • Declaring and Assigning Values to Variables and Constants
    • Tutorial: Declaring Variables and Constants in a Subprogram
    • Ensuring that Variables, Constants, and Parameters Have Correct Data Types
    • Tutorial: Changing Declarations to Use the %TYPE Attribute
    • Assigning Values to Variables
      • Assigning Values to Variables with the Assignment Operator
      • Assigning Values to Variables with the SELECT INTO Statement
  • Controlling Program Flow
    • About Control Statements
    • Using the IF Statement
    • Using the CASE Statement
    • Using the FOR LOOP Statement
    • Using the WHILE LOOP Statement
    • Using the Basic LOOP and EXIT WHEN Statements
  • Using Records and Cursors
    • About Records
    • Tutorial: Declaring a RECORD Type
    • Tutorial: Creating and Invoking a Subprogram with a Record Parameter
    • About Cursors
    • Using a Declared Cursor to Retrieve Result Set Rows One at a Time
    • Tutorial: Using a Declared Cursor to Retrieve Result Set Rows One at a Time
    • About Cursor Variables
    • Using a Cursor Variable to Retrieve Result Set Rows One at a Time
    • Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time
  • Using Associative Arrays
    • About Collections
    • About Associative Arrays
    • Declaring Associative Arrays
    • Populating Associative Arrays
    • Traversing Dense Associative Arrays
    • Traversing Sparse Associative Arrays
  • Handling Exceptions (Runtime Errors)
    • About Exceptions and Exception Handlers
    • When to Use Exception Handlers
    • Handling Predefined Exceptions
    • Declaring and Handling User-Defined Exceptions
• Using Triggers
  • About Triggers
  • Creating Triggers
    • About OLD and NEW Pseudorecords
    • Tutorial: Creating a Trigger that Logs Table Changes
    • Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted
    • Creating an INSTEAD OF Trigger
    • Tutorial: Creating Triggers that Log LOGON and LOGOFF Events
  • Changing Triggers
  • Disabling and Enabling Triggers
    • Disabling or Enabling a Single Trigger
    • Disabling or Enabling All Triggers on a Single Table
  • About Trigger Compilation and Dependencies
  • Dropping Triggers
• Working in a Global Environment
  • About Globalization Support Features
    • About Language Support
    • About Territory Support
    • About Date and Time Formats
    • About Calendar Formats
    • About Numeric and Monetary Formats
    • About Linguistic Sorting and String Searching
    • About Length Semantics
    • About Unicode and SQL National Character Data Types
  • About Initial NLS Parameter Values
  • Viewing NLS Parameter Values
  • Changing NLS Parameter Values
    • Changing NLS Parameter Values for All SQL Developer Connections
    • Changing NLS Parameter Values for the Current SQL Function Invocation
  • About Individual NLS Parameters
    • About Locale and the NLS_LANG Parameter
    • About the NLS_LANGUAGE Parameter
    • About the NLS_TERRITORY Parameter
    • About the NLS_DATE_FORMAT Parameter
    • About the NLS_DATE_LANGUAGE Parameter
    • About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters
    • About the NLS_CALENDAR Parameter
    • About the NLS_NUMERIC_CHARACTERS Parameter
    • About the NLS_CURRENCY Parameter
    • About the NLS_ISO_CURRENCY Parameter
    • About the NLS_DUAL_CURRENCY Parameter
    • About the NLS_SORT Parameter
    • About the NLS_COMP Parameter
    • About the NLS_LENGTH_SEMANTICS Parameter
  • Using Unicode in Globalized Applications
    • Representing Unicode String Literals in SQL and PL/SQL
    • Avoiding Data Loss During Character-Set Conversion
• Building Effective Applications
  • Building Scalable Applications
    • About Scalable Applications
    • Using Bind Variables to Improve Scalability
    • Using PL/SQL to Improve Scalability
      • How PL/SQL Minimizes Parsing
      • About the EXECUTE IMMEDIATE Statement
      • About OPEN FOR Statements
      • About the DBMS_SQL Package
      • About Bulk SQL
    • About Concurrency and Scalability
      • About Sequences and Concurrency
      • About Latches and Concurrency
      • About Nonblocking Reads and Writes and Concurrency
      • About Shared SQL and Concurrency
    • Limiting the Number of Concurrent Sessions
    • Comparing Programming Techniques with Runstats
      • About Runstats
      • Setting Up Runstats
      • Using Runstats
    • Real-World Performance and Data Processing Techniques
      • About Iterative Data Processing
      • About Set-Based Processing
  • Recommended Programming Practices
    • Use Instrumentation Packages
    • Statistics Gathering and Application Tracing
    • Use Existing Functionality
    • Cover Database Tables with Editioning Views
  • Recommended Security Practices
• Developing a Simple Oracle Database Application
  • About the Application
    • Purpose of the Application
    • Structure of the Application
      • Schema Objects of the Application
      • Schemas for the Application
    • Naming Conventions in the Application
  • Creating the Schemas for the Application
  • Granting Privileges to the Schemas
    • Granting Privileges to the app_data Schema
    • Granting Privileges to the app_code Schema
    • Granting Privileges to the app_admin Schema
    • Granting Privileges to the app_user and app_admin_user Schemas
  • Creating the Schema Objects and Loading the Data
    • Creating the Tables
    • Creating the Editioning Views
    • Creating the Triggers
      • Creating the Trigger to Enforce the First Business Rule
      • Creating the Trigger to Enforce the Second Business Rule
    • Creating the Sequences
    • Loading the Data
    • Adding the Foreign Key Constraint
    • Granting Privileges on the Schema Objects to Users
  • Creating the employees_pkg Package
    • Creating the Package Specification for employees_pkg
    • Creating the Package Body for employees_pkg
    • Tutorial: Showing How the employees_pkg Subprograms Work
    • Granting the Execute Privilege to app_user and app_admin_user
    • Tutorial: Invoking get_job_history as app_user or app_admin_user
  • Creating the admin_pkg Package
    • Creating the Package Specification for admin_pkg
    • Creating the Package Body for admin_pkg
    • Tutorial: Showing How the admin_pkg Subprograms Work
    • Granting the Execute Privilege to app_admin_user
    • Tutorial: Invoking add_department as app_admin_user
• Deploying an Oracle Database Application
  • About Development and Deployment Environments
  • About Installation Scripts
    • About DDL Statements and Schema Object Dependencies
    • About INSERT Statements and Constraints
  • Creating Installation Scripts
    • Creating Installation Scripts with the Cart
    • Creating an Installation Script with the Database Export Wizard
    • Editing Installation Scripts that Create Sequences
    • Editing Installation Scripts that Create Triggers
    • Creating Installation Scripts for the Sample Application
      • Creating Installation Script schemas.sql
      • Creating Installation Script objects.sql
      • Creating Installation Script employees.sql
      • Creating Installation Script admin.sql
      • Creating Master Installation Script create_app.sql
  • Deploying the Sample Application
  • Checking the Validity of an Installation
  • Archiving the Installation Scripts