Go to main content
1/16
Contents
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database 2 Day Developer's Guide
Changes in Oracle Database 12
c
Release 2 (12.2)
Changes in Oracle Database 12
c
Release 1 (12.1)
1
Introduction to 2 Day Oracle Database Development
1.1
About This Document
1.2
About Oracle Database
1.2.1
About Schema Objects
1.2.2
About Oracle Database Access
1.2.2.1
About SQL*Plus
1.2.2.2
About SQL Developer
1.2.2.3
About Structured Query Language (SQL)
1.2.2.4
About Procedural Language/SQL (PL/SQL)
1.2.2.5
About Other Client Programs, Languages, and Development Tools
1.2.2.5.1
Oracle Application Express
1.2.2.5.2
Oracle Java Database Connectivity (JDBC)
1.2.2.5.3
Hypertext Preprocessor (PHP)
1.2.2.5.4
Oracle Call Interface (OCI)
1.2.2.5.5
Oracle C++ Call Interface (OCCI)
1.2.2.5.6
Open Database Connectivity (ODBC)
1.2.2.5.7
Pro*C/C++ Precompiler
1.2.2.5.8
Pro*COBOL Precompiler
1.2.2.5.9
Microsoft .NET Framework
1.2.2.5.10
Oracle Provider for OLE DB (OraOLEDB)
1.3
About Sample Schema HR
2
Connecting to Oracle Database and Exploring It
2.1
Connecting to Oracle Database from SQL*Plus
2.2
Connecting to Oracle Database from SQL Developer
2.3
Connecting to Oracle Database as User HR
2.3.1
Unlocking the HR Account
2.3.2
Connecting to Oracle Database as User HR from SQL*Plus
2.3.3
Connecting to Oracle Database as User HR from SQL Developer
2.4
Exploring Oracle Database with SQL*Plus
2.4.1
Viewing HR Schema Objects with SQL*Plus
2.4.2
Viewing EMPLOYEES Table Properties and Data with SQL*Plus
2.5
Exploring Oracle Database with SQL Developer
2.5.1
Tutorial: Viewing HR Schema Objects with SQL Developer
2.5.2
Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer
2.6
Selecting Table Data
2.6.1
About Queries
2.6.2
Running Queries in SQL Developer
2.6.3
Tutorial: Selecting All Columns of a Table
2.6.4
Tutorial: Selecting Specific Columns of a Table
2.6.5
Displaying Selected Columns Under New Headings
2.6.6
Selecting Data that Satisfies Specified Conditions
2.6.7
Sorting Selected Data
2.6.8
Selecting Data from Multiple Tables
2.6.9
Using Operators and Functions in Queries
2.6.9.1
Using Arithmetic Operators in Queries
2.6.9.2
Using Numeric Functions in Queries
2.6.9.3
Using the Concatenation Operator in Queries
2.6.9.4
Using Character Functions in Queries
2.6.9.5
Using Datetime Functions in Queries
2.6.9.6
Using Conversion Functions in Queries
2.6.9.7
Using Aggregate Functions in Queries
2.6.9.8
Using NULL-Related Functions in Queries
2.6.9.9
Using CASE Expressions in Queries
2.6.9.10
Using the DECODE Function in Queries
3
About DML Statements and Transactions
3.1
About Data Manipulation Language (DML) Statements
3.1.1
About the INSERT Statement
3.1.2
About the UPDATE Statement
3.1.3
About the DELETE Statement
3.2
About Transaction Control Statements
3.3
Committing Transactions
3.4
Rolling Back Transactions
3.5
Setting Savepoints in Transactions
4
Creating and Managing Schema Objects
4.1
About Data Definition Language (DDL) Statements
4.2
Creating and Managing Tables
4.2.1
About SQL Data Types
4.2.2
Creating Tables
4.2.2.1
Tutorial: Creating a Table with the Create Table Tool
4.2.2.2
Creating Tables with the CREATE TABLE Statement
4.2.3
Ensuring Data Integrity in Tables
4.2.3.1
About Constraints
4.2.3.2
Tutorial: Adding Constraints to Existing Tables
4.2.4
Tutorial: Adding Rows to Tables with the Insert Row Tool
4.2.5
Tutorial: Changing Data in Tables in the Data Pane
4.2.6
Tutorial: Deleting Rows from Tables with the Delete Selected Row(s) Tool
4.2.7
Managing Indexes
4.2.7.1
Tutorial: Adding an Index with the Create Index Tool
4.2.7.2
Tutorial: Changing an Index with the Edit Index Tool
4.2.7.3
Tutorial: Dropping an Index
4.2.8
Dropping Tables
4.3
Creating and Managing Views
4.3.1
Creating Views
4.3.1.1
Tutorial: Creating a View with the Create View Tool
4.3.1.2
Creating Views with the CREATE VIEW Statement
4.3.2
Changing Queries in Views
4.3.3
Tutorial: Changing View Names with the Rename Tool
4.3.4
Dropping a View
4.4
Creating and Managing Sequences
4.4.1
Tutorial: Creating a Sequence
4.4.2
Dropping Sequences
4.5
Creating and Managing Synonyms
4.5.1
Creating Synonyms
4.5.2
Dropping Synonyms
5
Developing Stored Subprograms and Packages
5.1
About Stored Subprograms
5.2
About Packages
5.3
About PL/SQL Identifiers
5.4
About PL/SQL Data Types
5.5
Creating and Managing Standalone Subprograms
5.5.1
About Subprogram Structure
5.5.2
Tutorial: Creating a Standalone Procedure
5.5.3
Tutorial: Creating a Standalone Function
5.5.4
Changing Standalone Subprograms
5.5.5
Tutorial: Testing a Standalone Function
5.5.6
Dropping Standalone Subprograms
5.6
Creating and Managing Packages
5.6.1
About Package Structure
5.6.2
Tutorial: Creating a Package Specification
5.6.3
Tutorial: Changing a Package Specification
5.6.4
Tutorial: Creating a Package Body
5.6.5
Dropping a Package
5.7
Declaring and Assigning Values to Variables and Constants
5.7.1
Tutorial: Declaring Variables and Constants in a Subprogram
5.7.2
Ensuring that Variables, Constants, and Parameters Have Correct Data Types
5.7.3
Tutorial: Changing Declarations to Use the %TYPE Attribute
5.7.4
Assigning Values to Variables
5.7.4.1
Assigning Values to Variables with the Assignment Operator
5.7.4.2
Assigning Values to Variables with the SELECT INTO Statement
5.8
Controlling Program Flow
5.8.1
About Control Statements
5.8.2
Using the IF Statement
5.8.3
Using the CASE Statement
5.8.4
Using the FOR LOOP Statement
5.8.5
Using the WHILE LOOP Statement
5.8.6
Using the Basic LOOP and EXIT WHEN Statements
5.9
Using Records and Cursors
5.9.1
About Records
5.9.2
Tutorial: Declaring a RECORD Type
5.9.3
Tutorial: Creating and Invoking a Subprogram with a Record Parameter
5.9.4
About Cursors
5.9.5
Using a Declared Cursor to Retrieve Result Set Rows One at a Time
5.9.6
Tutorial: Using a Declared Cursor to Retrieve Result Set Rows One at a Time
5.9.7
About Cursor Variables
5.9.8
Using a Cursor Variable to Retrieve Result Set Rows One at a Time
5.9.9
Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time
5.10
Using Associative Arrays
5.10.1
About Collections
5.10.2
About Associative Arrays
5.10.3
Declaring Associative Arrays
5.10.4
Populating Associative Arrays
5.10.5
Traversing Dense Associative Arrays
5.10.6
Traversing Sparse Associative Arrays
5.11
Handling Exceptions (Runtime Errors)
5.11.1
About Exceptions and Exception Handlers
5.11.2
When to Use Exception Handlers
5.11.3
Handling Predefined Exceptions
5.11.4
Declaring and Handling User-Defined Exceptions
6
Using Triggers
6.1
About Triggers
6.2
Creating Triggers
6.2.1
About OLD and NEW Pseudorecords
6.2.2
Tutorial: Creating a Trigger that Logs Table Changes
6.2.3
Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted
6.2.4
Creating an INSTEAD OF Trigger
6.2.5
Tutorial: Creating Triggers that Log LOGON and LOGOFF Events
6.3
Changing Triggers
6.4
Disabling and Enabling Triggers
6.4.1
Disabling or Enabling a Single Trigger
6.4.2
Disabling or Enabling All Triggers on a Single Table
6.5
About Trigger Compilation and Dependencies
6.6
Dropping Triggers
7
Working in a Global Environment
7.1
About Globalization Support Features
7.1.1
About Language Support
7.1.2
About Territory Support
7.1.3
About Date and Time Formats
7.1.4
About Calendar Formats
7.1.5
About Numeric and Monetary Formats
7.1.6
About Linguistic Sorting and String Searching
7.1.7
About Length Semantics
7.1.8
About Unicode and SQL National Character Data Types
7.2
About Initial NLS Parameter Values
7.3
Viewing NLS Parameter Values
7.4
Changing NLS Parameter Values
7.4.1
Changing NLS Parameter Values for All SQL Developer Connections
7.4.2
Changing NLS Parameter Values for the Current SQL Function Invocation
7.5
About Individual NLS Parameters
7.5.1
About Locale and the NLS_LANG Parameter
7.5.2
About the NLS_LANGUAGE Parameter
7.5.3
About the NLS_TERRITORY Parameter
7.5.4
About the NLS_DATE_FORMAT Parameter
7.5.5
About the NLS_DATE_LANGUAGE Parameter
7.5.6
About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters
7.5.7
About the NLS_CALENDAR Parameter
7.5.8
About the NLS_NUMERIC_CHARACTERS Parameter
7.5.9
About the NLS_CURRENCY Parameter
7.5.10
About the NLS_ISO_CURRENCY Parameter
7.5.11
About the NLS_DUAL_CURRENCY Parameter
7.5.12
About the NLS_SORT Parameter
7.5.13
About the NLS_COMP Parameter
7.5.14
About the NLS_LENGTH_SEMANTICS Parameter
7.6
Using Unicode in Globalized Applications
7.6.1
Representing Unicode String Literals in SQL and PL/SQL
7.6.2
Avoiding Data Loss During Character-Set Conversion
8
Building Effective Applications
8.1
Building Scalable Applications
8.1.1
About Scalable Applications
8.1.2
Using Bind Variables to Improve Scalability
8.1.3
Using PL/SQL to Improve Scalability
8.1.3.1
How PL/SQL Minimizes Parsing
8.1.3.2
About the EXECUTE IMMEDIATE Statement
8.1.3.3
About OPEN FOR Statements
8.1.3.4
About the DBMS_SQL Package
8.1.3.5
About Bulk SQL
8.1.4
About Concurrency and Scalability
8.1.4.1
About Sequences and Concurrency
8.1.4.2
About Latches and Concurrency
8.1.4.3
About Nonblocking Reads and Writes and Concurrency
8.1.4.4
About Shared SQL and Concurrency
8.1.5
Limiting the Number of Concurrent Sessions
8.1.6
Comparing Programming Techniques with Runstats
8.1.6.1
About Runstats
8.1.6.2
Setting Up Runstats
8.1.6.3
Using Runstats
8.1.7
Real-World Performance and Data Processing Techniques
8.1.7.1
About Iterative Data Processing
8.1.7.2
About Set-Based Processing
8.2
Recommended Programming Practices
8.2.1
Use Instrumentation Packages
8.2.2
Statistics Gathering and Application Tracing
8.2.3
Use Existing Functionality
8.2.4
Cover Database Tables with Editioning Views
8.3
Recommended Security Practices
9
Developing a Simple Oracle Database Application
9.1
About the Application
9.1.1
Purpose of the Application
9.1.2
Structure of the Application
9.1.2.1
Schema Objects of the Application
9.1.2.2
Schemas for the Application
9.1.3
Naming Conventions in the Application
9.2
Creating the Schemas for the Application
9.3
Granting Privileges to the Schemas
9.3.1
Granting Privileges to the app_data Schema
9.3.2
Granting Privileges to the app_code Schema
9.3.3
Granting Privileges to the app_admin Schema
9.3.4
Granting Privileges to the app_user and app_admin_user Schemas
9.4
Creating the Schema Objects and Loading the Data
9.4.1
Creating the Tables
9.4.2
Creating the Editioning Views
9.4.3
Creating the Triggers
9.4.3.1
Creating the Trigger to Enforce the First Business Rule
9.4.3.2
Creating the Trigger to Enforce the Second Business Rule
9.4.4
Creating the Sequences
9.4.5
Loading the Data
9.4.6
Adding the Foreign Key Constraint
9.4.7
Granting Privileges on the Schema Objects to Users
9.5
Creating the employees_pkg Package
9.5.1
Creating the Package Specification for employees_pkg
9.5.2
Creating the Package Body for employees_pkg
9.5.3
Tutorial: Showing How the employees_pkg Subprograms Work
9.5.4
Granting the Execute Privilege to app_user and app_admin_user
9.5.5
Tutorial: Invoking get_job_history as app_user or app_admin_user
9.6
Creating the admin_pkg Package
9.6.1
Creating the Package Specification for admin_pkg
9.6.2
Creating the Package Body for admin_pkg
9.6.3
Tutorial: Showing How the admin_pkg Subprograms Work
9.6.4
Granting the Execute Privilege to app_admin_user
9.6.5
Tutorial: Invoking add_department as app_admin_user
10
Deploying an Oracle Database Application
10.1
About Development and Deployment Environments
10.2
About Installation Scripts
10.2.1
About DDL Statements and Schema Object Dependencies
10.2.2
About INSERT Statements and Constraints
10.3
Creating Installation Scripts
10.3.1
Creating Installation Scripts with the Cart
10.3.2
Creating an Installation Script with the Database Export Wizard
10.3.3
Editing Installation Scripts that Create Sequences
10.3.4
Editing Installation Scripts that Create Triggers
10.3.5
Creating Installation Scripts for the Sample Application
10.3.5.1
Creating Installation Script schemas.sql
10.3.5.2
Creating Installation Script objects.sql
10.3.5.3
Creating Installation Script employees.sql
10.3.5.4
Creating Installation Script admin.sql
10.3.5.5
Creating Master Installation Script create_app.sql
10.4
Deploying the Sample Application
10.5
Checking the Validity of an Installation
10.6
Archiving the Installation Scripts
Index
Scripting on this page enhances content navigation, but does not change the content in any way.