|Oracle® Database 2 Day Developer's Guide
11g Release 2 (11.2)
Part Number E10766-04
This chapter contains the following topics:
An Oracle Database developer is responsible for creating or maintaining the database components of an application that uses the Oracle technology stack. Oracle Database developers either develop applications or convert existing applications to run in the Oracle Database environment.
See Also:Oracle Database Concepts for more information about the duties of Oracle Database developers
This document is the entry into the Oracle Database documentation set for application developers. It does the following:
Explains the basic concepts behind development with Oracle Database
Shows how to use basic features of SQL and PL/SQL
Provides references to detailed information about subjects that it introduces
Shows, with tutorials and examples, how to develop and deploy a sample application
Chapter 1, "Introduction," describes the reader for whom this document is intended, outlines the organization of this document, introduces important Oracle Database concepts, and describes the sample schema used in the tutorials and examples in this document.
Chapter 2, "Connecting to Oracle Database," explains how to connect to Oracle Database.
Chapter 3, "Exploring Oracle Database with SQL Developer," shows how to view schema objects and the properties and data of Oracle Database tables.
Chapter 4, "Selecting Table Data," shows how to use queries to retrieve data from an Oracle Database table.
Chapter 5, "About DML Statements and Transactions," introduces data manipulation language (DML) statements and transactions. DML statements add, change, and delete Oracle Database table data. A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are.
Chapter 6, "Creating and Managing Schema Objects," introduces data definition language (DDL) statements, which create, change, and drop schema objects. The tutorials and examples show how to create the objects for the sample application.
Chapter 7, "Developing Stored Subprograms and Packages," introduces stored subprograms and packages, which can be used as building blocks for many different database applications. The tutorials and examples show how to create the package of subprograms for the sample application.
Chapter 8, "Using Triggers," introduces triggers, which are stored PL/SQL units that automatically execute ("fire") in response to specified events. The tutorials and examples show how to create the triggers for the sample application.
Chapter 9, "Working in a Global Environment," introduces globalization support—National Language Support (NLS) parameters and Unicode-related features of SQL and PL/SQL.
Chapter 10, "Deploying an Oracle Database Application," explains how to deploy a database application—that is, how to install it in one or more environments where other users can run it—using the sample application as an example.
Oracle Database groups related information into logical structures called schemas. The logical structures are called schema objects. When you connect to the database by providing your user name and password, you specify the schema and indicate that you are its owner. In Oracle Database, the user name and the name of the schema to which the user connects are the same.
Every object in an Oracle Database belongs to only one schema, and has a unique name with that schema.
Some of the objects that schemas can contain are:
Tables are the basic units of data storage in Oracle Database. Tables hold all user-accessible data. Each table contains rows that represent individual data records. Rows are composed of columns that represent the fields of the records. For more information, see "Creating and Managing Tables".
Indexes are optional objects that can improve the performance of data retrieval from tables. Indexes are created on one or more columns of a table, and are automatically maintained in the database. For more information, see "Managing Indexes".
You can create a view that combines information from several different tables into a single presentation. A view can rely on information from both tables and other views. For more information, see "Creating and Managing Views".
When all records of a table must be distinct, you can use a sequence to generate a serial list of unique integers for numeric columns, each of which represents the ID of one record. For more information, see "Creating and Managing Sequences".
Synonyms are aliases for schema objects. You can use synonyms for security and convenience; for example, to hide the ownership of an object or to simplify SQL statements. For more information, see "Creating and Managing Synonyms".
Stored subprograms (also called schema-level subprograms) are procedures and functions that are stored in the database. They can be invoked from client applications that access the database. For more information, see "Developing Stored Subprograms and Packages".
Triggers are stored subprograms that are automatically run by the database when specified events occur in a particular table or view. Triggers can restrict access to specific data and perform logging. For more information, see "Using Triggers".
A package is a group of related subprograms, along with the explicit cursors and variables they use, stored in the database as a unit, for continued use. Like stored subprograms, package subprograms can be invoked from client applications that access the database. For more information, see "Developing Stored Subprograms and Packages".
Typically, the objects that an application uses belong to the same schema.
See Also:Oracle Database Concepts for a comprehensive introduction to schema objects
You can access Oracle Database only through a client program, such as SQL*Plus or SQL Developer. The client program's interface to Oracle Database is Structured Query Language (SQL). Oracle provides an extension to SQL called Procedural Language/SQL (PL/SQL).
SQL*Plus (pronounced sequel plus) is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface that acts as the client when connecting to the database.
SQL*Plus has its own commands and environment. In the SQL*Plus environment, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands to perform tasks such as:
Formatting, performing calculations on, storing, and printing query results
Examining tables and object definitions
Developing and running batch scripts
Performing database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility.
You can use SQL*Plus in SQL Developer. For details, see Oracle Database SQL Developer User's Guide.
The SQL Developer user interface includes a navigation frame, tools (with menus), and a SQL Worksheet. From the SQL Worksheet, you can enter and run SQL statements, PL/SQL statements, and SQL*Plus commands. You can do some tasks—for example, creating a table—either in the SQL Worksheet or with the navigation frame and tools.
Oracle Database SQL Developer User's Guide for complete information about SQL Developer
SQL is a declarative, or nonprocedural, language; that is, it describes what to do, but not how. You specify the desired result set (for example, the names of current employees), but not how to get it.
Procedural Language/SQL (PL/SQL) (pronounced P L sequel) is a native Oracle Database extension to SQL. It bridges the gap between declarative and imperative program control by adding procedural elements, such as conditional control and loops.
In PL/SQL, you can declare constants and variables, procedures and functions, types and variables of those types, and triggers. You can handle exceptions (run-time errors). You can create PL/SQL units—procedures, functions, packages, types, and triggers—that are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
The basic unit of a PL/SQL source program is the block, which groups related declarations and statements. A block has an optional declarative part, a required executable part, and an optional exception-handling part.
Oracle Database Concepts for more information about tools for Oracle Database developers
Oracle Database Advanced Application Developer's Guide for information about choosing a programming environment
Oracle Application Express (APEX) is an application development and deployment tool that enables you to quickly create secure and scalable Web applications even if you have limited previous programming experience. The embedded Application Builder tool assembles an HTML interface or a complete application that uses schema objects, such as tables or stored procedures, into a collection of pages that are linked through tabs, buttons, or hypertext links.
See Also:Oracle Database 2 Day + Application Express Developer's Guide for more information about APEX
Oracle Java Database Connectivity (JDBC) is an API that enables Java to send SQL statements to an object-relational database, such as Oracle Database. Oracle Database JDBC provides complete support for the JDBC 3.0 and JDBC RowSet (JSR-114) standards, advanced connection caching for both XA and non-XA connections, exposure of SQL and PL/SQL data types to Java, and fast SQL data access.
See Also:For more information about JDBC:
The Hypertext Preprocessor (PHP) is a powerful interpreted server-side scripting language for quick Web application development. PHP is an open source language that is distributed under a BSD-style license. PHP is designed for embedding database access requests directly into HTML pages.
See Also:Oracle Database 2 Day + PHP Developer's Guide for more information about PHP
The OCI Software Development Kit is also installed as part of the Oracle Instant Client, which enables you to run applications without installing the standard Oracle client or having an
ORACLE_HOME. Your applications work without change, using significantly less disk space.
Oracle Database Advanced Application Developer's Guide for more information about OCI
Oracle Call Interface Programmer's Guide for complete information about OCI
Oracle C++ Call Interface (OCCI) is the native C++ language API for accessing Oracle Database directly from C++ applications. Very similar to the OCI, OCCI supports both relational and object-oriented programming paradigms.
The OCCI Software Development Kit is also installed as part of the Oracle Instant Client, which enables you to run applications without installing the standard Oracle client or having an
ORACLE_HOME. Your applications work without change, using significantly less disk space.
Oracle Database Advanced Application Developer's Guide for more information about OCCI
Oracle C++ Call Interface Programmer's Guide for complete information about OCCI
Open Database Connectivity (ODBC) is a set of database access APIs that connect to the database, prepare, and then run SQL statements on the database. An application that uses an ODBC driver can access non-uniform data sources, such as spreadsheets and comma-delimited files.
The Oracle ODBC driver conforms to ODBC 3.51 specifications. It supports all core APIs and a subset of Level 1 and Level 2 functions. Microsoft supplies the Driver manager component for the Windows platform.
Like OCI, OCCI, and JDBC, ODBC is part of the Oracle Instant Client installation.
Oracle Services for Microsoft Transaction Server Developer's Guide for information about using the Oracle ODBC driver with Windows
Oracle Database Administrator's Reference for Linux and UNIX-Based Operating Systems for information about using Oracle ODBC driver on Linux
The Pro*C/C++ precompiler enables you to embed SQL statements in a C or C++ source file. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle run-time library calls, and generates a modified source program that you can compile, link, and run.
Oracle Database Concepts for more information about Oracle precompilers
Oracle Database Advanced Application Developer's Guide for more information about the Pro*C/C++ precompiler
Pro*C/C++ Programmer's Guide for complete information about the Pro*C/C++ precompiler
The Pro*COBOL precompiler enables you to embed SQL statements in a COBOL source file. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle run-time library calls, and generates a modified source program that you can compile, link, and run.
Oracle Database Concepts for more information about Oracle precompilers
Oracle Database Advanced Application Developer's Guide for more information about the Pro*COBOL precompiler
Pro*COBOL Programmer's Guide for complete information about the Pro*COBOL precompiler
The Microsoft .NET Framework is a multilanguage environment for building, deploying, and running applications and XML Web services. Its main components are:
The Common Language Runtime (CLR) is a language-neutral development and run-time environment that provides services that help manage running applications.
Oracle Data Provider for .NET (ODP.NET) provides fast and efficient ADO.NET data access from .NET client applications to Oracle Database and access to other Oracle Database features.
ODP.NET allows developers to take advantage of advanced Oracle Database functionality, including Real Application Clusters, XML DB, and advanced security.
Oracle Developer Tools for Visual Studio (ODT) is a set of application tools that integrate with the Visual Studio environment. These tools provide graphic user interface access to Oracle functionality, enable the user to perform a wide range of application development tasks, and improve development productivity and ease of use. Oracle Developer Tools supports the programming and implementation of .NET stored procedures using Visual Basic, C#, and other .NET languages.
.NET Stored Procedures
Oracle Database Extensions for .NET is a database option for Oracle Database on Windows. It makes it possible to build and run .NET stored procedures or functions with Oracle Database for Microsoft Windows using Visual Basic .NET or Visual C#.
After building .NET procedures and functions into a .NET assembly, you can deploy them in Oracle Database using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio.
Oracle Providers for ASP.NET offer ASP.NET developers an easy way to store state common to Web applications within Oracle Database. These providers are modeled on existing Microsoft ASP.NET providers, sharing similar schema and programming interfaces to provide .NET developers a familiar interface. Oracle supports the Membership, Profile, Role, and other providers.
Oracle Provider for OLE DB (OraOLEDB) is an open standard data access methodology that uses a set of Component Object Model (COM) interfaces for accessing and manipulating different types of data. These interfaces are available from various database providers.
See Also:Oracle Provider for OLE DB Developer's Guide for more information about OraOLEDB
Oracle Database Advanced Application Developer's Guide for more information about OO4O
Oracle Objects for OLE Help, and Oracle Objects for OLE C++ Class Library Help, for detailed information about Oracle Objects for OLE
Oracle Database SecureFiles and Large Objects Developer's Guide for examples of how to use Oracle Objects for OLE
Oracle Streams Advanced Queuing User's Guide for examples of how to use Oracle Objects for OLE
HR schema is a sample schema that can be installed as part of Oracle Database. This schema contains information about employees—their departments, locations, work histories, and related information. Like all schemas, the
HR schema has tables, views, indexes, procedures, functions, and other attributes of a database schema.
The examples and tutorials in this document use the