Course Reference Material
Oracle9i® PL/SQL : Database Programming
NETg course 61135
Copyright © 2002 National Education Training Group, Inc.
All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by any information storage and retrieva 18218w221s l system, without permission in writing from National Education Training Group, Inc.
Skill Builder is a registered trademark of National Education Training Group, Inc. All other trademarks referenced are the trademark, service mark, or registered trademark of their respective holders. National Education Training Group, Inc. is not affiliated with any company or any other product or vendor mentioned in this course and its accompanying materials.
The software and technology used to implement this course contain trade secrets that NETg considers to be confidential and proprietary information, and your right to use this material is subject to the restrictions in the license agreement under which you obtained it.
Contents
Objectives
Developing Program Units
Oracle Server Supplied Packages
Managing Subprograms and Packages
Developing Database Triggers
Glossary
Alphabetical List of Terms
Total number of objectives = 64
Note: (ULT) is the unit, lesson, topic ID.
Creating Stored Program Units
Stored Subprograms (ULT 112): Sequence the steps used to create stored subprograms by using iSQL*Plus.
Partitioning Applications (ULT 113): Identify the effects of storing code separately on the server and on the client.
Stored Procedures: Creating (ULT 114): Perform the steps needed to create a stored procedure using iSQL*Plus.
Stored Functions: Creating (ULT 115): Perform the steps needed to create a stored function using iSQL*Plus.
Run-Time Exceptions: Handling (ULT 116): Match run-time exceptions with their descriptions and the directions for their handling.
Stored Packages: Creating (ULT 117): Perform the steps needed to create a stored package using iSQL*Plus.
Invoking Stored Subprograms
Stored Procedures: Invoking (ULT 121): Perform the steps to invoke stored procedures in iSQL*Plus using IN and OUT parameters.
Stored Functions: Invoking (ULT 122): Perform the steps needed to invoke a stored function using iSQL*Plus.
Subprograms: Passing Multiple Arguments (ULT 123): Perform the steps needed to invoke a stored subprogram with multiple arguments in iSQL*Plus.
Functions in SQL Statements (ULT 124): Identify the rules for calling user-defined functions in SQL statements.
Supplied Packages
Supplied Packages: Overview (ULT 212): Match the Oracle server supplied packages with their uses.
DBMS_PIPE Package (ULT 213): Identify the correct combinations of DBMS_PIPE subprograms and their uses.
DBMS_DDL Package (ULT 214): Identify the uses of the DBMS_DDL package.
DBMS_OUTPUT Package (ULT 215): Match the subprograms of the DBMS_OUTPUT package with their uses.
DBMS_OUTPUT: Debugging Stored Procedures (ULT 216): Create an appropriate statement to display messages on the screen using DBMS_OUTPUT.
OS Files Interaction Packages: Features (ULT 217): Identify the features of the packages used to interact with operating system files.
DBMS_JOB Package: Uses (ULT 218): Identify the uses of the DBMS_JOB package.
DBMS_JOB Package: Subprograms (ULT 219): Match the subprograms of the DBMS_JOB package with their uses.
UTL Packages
UTL_FILE: Overview: Overview (ULT 221): Identify the features of the UTL_FILE package.
UTL_FILE: Subprograms (ULT 222): Match the subprograms of the UTL_FILE package with their uses.
UTL_FILE: Exceptions (ULT 223): Match the exceptions of the UTL_FILE package with their uses.
UTL_FILE: File Processing Steps (ULT 224): Sequence the steps to use the UTL_FILE package for file processing.
UTL_HTTP: Features (ULT 225): Identify the features of the UTL_HTTP package.
UTL_TCP: Features (ULT 226): Identify the features of the UTL_TCP package.
Dynamic SQL
Dynamic SQL: Uses (ULT 231): Identify the uses of dynamic SQL.
Dynamic SQL: Using DBMS_SQL (ULT 232): Match the subprograms of the DBMS_SQL Oracle server supplied package with their uses.
Dynamic SQL: Using EXECUTE IMMEDIATE (ULT 233): Match the parameters of the EXECUTE IMMEDIATE statement with their uses.
Managing Large Objects
LOB Datatypes (ULT 241): Identify the distinctive properties of a LOB datatype.
LOBs: Internal (ULT 242): Sequence the steps involved in managing an internal LOB datatype.
LOBs: External (ULT 243): Sequence the steps involved in managing an external LOB datatype.
DIRECTORY Objects: Creation Guidelines (ULT 244): Identify the guidelines for creating DIRECTORY objects.
BFILEs: Implementation Steps (ULT 245): Sequence the steps to use a BFILE within an Oracle table.
BFILENAME Function: Features (ULT 246): Identify the features of the BFILENAME function.
FILEEXISTS Function: Features (ULT 247): Identify the features of the FILEEXISTS function.
LONG to LOB Migration: Features (ULT 248): Identify the features of migrating data from the LONG datatype to the LOB datatype.
DBMS_LOB Package
DBMS_LOB Package: Subprograms (ULT 251): Match the subprograms of the DBMS_LOB package with their uses.
LOBs: Manipulating Using DBMS_LOB (ULT 252): Identify the features of the DBMS_LOB package when selecting, reading, writing, and updating LOBs.
LOBs: Manipulating Using SQL (ULT 253): Identify the features of using SQL to create, insert, update, select, and remove LOBs.
Managing Stored Subprograms
Stored Subprograms: Privileges (ULT 312): Identify the guidelines for using privileges that are related to a stored subprogram.
Stored Subprograms: Granting Access (ULT 313): Grant privileges on a stored subprogram by using SQL commands.
Stored Subprograms: Management (ULT 314): Match the facilities available to manage stored subprograms with their application.
Stored Subprograms: Documentation (ULT 315): Display details about stored subprograms from the Oracle data dictionary.
Stored Subprograms: Compile Time Errors (ULT 316): Perform the steps to display compile time errors stored in the Oracle data dictionary.
Stored Subprograms: Debug Methods (ULT 317): Identify the methods used to debug stored subprograms.
Procedural Dependencies
Dependencies: Types (ULT 321): Match the different types of dependencies with their descriptions.
Local Dependencies: Tracking (ULT 322): Perform the steps to display dependencies stored in the Oracle data dictionary.
Local Dependencies: Managing (ULT 323): Identify the situations in which a dependent subprogram can be successfully recompiled.
Remote Dependencies: Rules (ULT 324): Identify the features of the Oracle server remote dependency mechanism.
Packages
Packages: Managing (ULT 331): Match the different tasks involved in managing packages with the methods of implementing them.
Packages: Dependency Rules (ULT 332): Identify the package dependency rules implemented by the Oracle server.
Creating Database Triggers
Triggers: Database Triggers (ULT 412): Match the components of a database trigger with their descriptions.
Triggers: Design Guidelines (ULT 413): Identify the guidelines for designing triggers.
Statement Triggers: Creating (ULT 414): Create a statement-level database trigger by using iSQL*Plus.
Row Triggers: Creating (ULT 415): Create a row-level database trigger by using iSQL*Plus.
Triggers: INSTEAD OF Triggers (ULT 416): Identify the features of an INSTEAD OF trigger.
DBA Triggers: Features (ULT 417): Identify the features of DBA triggers.
Form Builder Trigger: Features (ULT 418): Identify the features of Form Builder triggers.
Triggers: Managing
Database Triggers: Management (ULT 421): Perform the steps needed to manage database triggers using iSQL*Plus.
Triggers: Dropping (ULT 422): Drop a trigger by using the DROP TRIGGER statement.
Triggers: Execution Steps (ULT 423): Sequence the steps followed by the Oracle server while firing triggers.
Triggers: Managing Privileges (ULT 424): Match the system privileges that you need to manage triggers with their functions.
Triggers: Advanced Concepts
Database Triggers: Applications (ULT 431): Match the tasks performed in an Oracle database with the role that triggers have in achieving them.
Triggers: Using Call Statements (ULT 432): Call a stored procedure from a trigger by using the CALL statement.
Triggers: Data Dictionary Views (ULT 433): Match the data dictionary views that store the trigger information with their contents.
An anonymous block is an unnamed block. An anonymous block is used at the point where it is executed. An anonymous block has a DECLARE keyword instead of the header section of a named block.
A set of public programmatic interfaces that consist of a language and message format to communicate with an operating system or other programmatic environment, such as databases, Web servers, JVMs, and so forth. These messages typically call functions and methods available for application development.
An application is one or more program modules used to achieve a specific result. You can nest an application within another application. For example, an application to control a company's inventory can consist of various forms applications for input of data and various graphics applications to display data visually.
Application partitioning is the process of separating code and placing individual program units on the server or on the clients.
An application trigger is a trigger that executes implicitly whenever a particular event occurs within an application.
An argument is an expression within the parentheses of a subprogram invocation and is operated upon by the subprogram.
The keyword BEGIN separates the declaration section from the executable section of a subprogram. It is mandatory to include the keyword BEGIN in a named subprogram.
BFILEs are external LOBs that are stored outside the database. BFILEs can only be accessed in read-only mode from an Oracle server.
A bind variable is a global variable created outside a package in another environment such as SQL*Plus, Procedure Builder, or Developer Forms or Reports.
A BLOB is a binary large object (Binary LOB). BLOBs are typically used to hold data representing photographs or graphics.
A block is a basic program unit in PL/SQL defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. A block can either be a named program unit or it can exist as an unnamed, standalone anonymous block.
The body contains the same information as the program unit specification and also includes the actual implementation of the subprogram.
A buffer is a temporary storage area for data during the transfer of that data between the computer and a peripheral or between the components of a computer. A buffer also prevents loss of information due to differences in the speed or timing of the transfer and this can speed up certain operations.
Built-in subprograms are subprograms supplied with Procedure Builder.
You use the CALL statement within a trigger. You specify the stored procedure that must be called along with the CALL statement.
The CHAR datatype is an Oracle datatype provided for ANSI/ISO compatibility. This datatype has a fixed-length column. It can contain printable characters, such as A, 3, &, or blank and can have from 1 to 255 characters or null.
A clause is a part of a SQL statement that does not constitute the full statement. An example is a WHERE clause.
A client can be a user, a software application, or a computer that requests the services, data, or processing of another application or computer. In a two-task environment, the client is the user process. In a network environment, the client or client node is the local user process and the server may be local or remote.
The client-side development area is the developer's own development area which is usually located on the developer's local computer. You can edit, compile, and delete subprograms developed in this area. However, if the Procedure Builder tool is closed, any subprograms on the client-side are lost. You can debug client-side subprograms using Procedure Builder. For end users, in a client-server environment, the client-side refers to the desktop computers of each end user. Program units can be stored and executed on client computers running Developer applications. Alternatively, you can store and execute program units on the server the clients are connected to.
A CLOB is a character large object (Character LOB). CLOBs are typically used to hold data representing long pieces of text.
A column is vertical space in a database table that represents a domain of data. For example, in a table of employee information, all the employee names constitute one column.
Commit is the process in which changes to data through inserts, updates, and deletes in the database are made permanent.
Compile is the process of translating a source program into a binary, executable format. For example, PL/SQL program units must be compiled before they can be executed.
A subprogram may fail to compile and compile time errors may be generated. Compile time errors may result from attempting to compile a subprogram containing syntax errors.
A condition or boolean expression is an expression whose value is either true or false. An example of a condition is X > 100.
Connect is the process of logging on to a database. You must connect if you want to create, modify, or access data stored in a database.
A cursor is a temporary memory area used in the PL/SQL environment.
Data control language (DCL) is the category of SQL statements that is used to control access to data and to the database. Some examples of DCL are the GRANT and REVOKE statements.
Data definition language (DDL) is a category of SQL statements that is used to define or delete database objects, such as tables and views. Some examples of DDL are the CREATE, ALTER, and DROP statements.
The data dictionary is a set of tables and views owned by the database administrator. It functions as a central source of information for the Oracle server and other relational databases.
Data manipulation language (DML) is the category of SQL statements that is used to update the database data. The three DML statements are the INSERT, UPDATE, and DELETE statements.
A database is a set of data dictionary tables and user tables that is treated as a unit.
A database trigger executes implicitly when a specified DML statement is issued against the associated table. It is irrelevant which user is connected or which application is used. Database triggers also execute implicitly when some user or database system actions occur. An example of this is when a user logs on, or when the DBA shuts down the database.
A datatype is a standard form of data. The datatypes supported by Oracle8i are CHAR, DATE, NUMBER, LONG, RAW, LONG RAW, and LOB.
DATE is an Oracle server datatype that can be used to contain a date and time between January 1, 4712 B.C. and December 31, 9999 A.D.
The debug trigger is a conditional debugging action that can be placed at any source line in a PL/SQL subprogram.
Debugging is the process of detecting, diagnosing, and eliminating errors in a program.
The declaration or declarative section occurs in a PL/SQL subprogram between the keywords IS and BEGIN. The declaration section is where local identifiers are stated. This section is optional.
The default value is a value supplied by the system when a user does not specify a required command parameter or attribute.
A DIRECTORY is a nonschema database object that provides for administration of access and usage of BFILEs in an Oracle server.
Dynamic SQL statements are SQL statements stored in character strings that are input to, or built by, your program. For example, you use dynamic SQL to create a procedure that operates on a table whose name is not known until run time, or to write and execute a DDL statement in PL/SQL.
An editor is a work area in which you perform a specific set of tasks, such as creating a program unit or designing an application.
An exception is a warning or error condition generated from a PL/SQL subprogram.
The keyword EXCEPTION occurs in a PL/SQL subprogram at the end of the executable section before the end of the subprogram. Exceptions are generated in the executable section of a subprogram and handled in the exception section. The EXCEPTION section is optional. Exceptions generated by a subprogram and not handled by the same subprogram are passed up to the calling subprogram or the calling environment to be handled.
The executable section of a PL/SQL subprogram occurs between the keywords BEGIN and EXCEPTION, or BEGIN and END. The executable section is the body of the subprogram and comprises of SQL and PL/SQL statements. This section is mandatory.
The EXECUTE command is used in SQL*Plus to invoke a procedure. For example, EXECUTE raise_salary (7369), invokes the raise_salary procedure, and passes it the employee number 7369.
You need the EXECUTE object privilege to invoke a PL/SQL subprogram if you are not the owner of the PL/SQL subprogram and do not have the EXECUTE ANY system privilege. You do not need any privileges to invoke a trigger. A trigger is invoked by DML statements that you issue.
An expression is a PL/SQL construct combining variables, constants, and literals, and operations on their values.
A field is the data stored at the intersection of a row and a column.
A foreign key is a value or column in one table that refers to a primary key in another table.
A formal parameter is a variable declared in a subprogram specification and referenced in the subprogram body.
Form Builder is an Oracle tool. Form Builder applications are interactive programs composed of items that enable users to query, update, enter, and delete information from the database. The term is also used to refer to an individual Form Builder application component called a form module. A form module is a collection of objects and code routines. Objects you can define in a form module include windows, text items (also called fields), checkboxes, buttons, alerts, lists of values, and blocks of PL/SQL code. The other Form Builder application components are menu modules and library modules.
A function is a PL/SQL subprogram that executes an operation and returns a value at the completion of the operation. A function can be either built-in or user-defined.
A global variable is a logical container that exists across an application. When an application uses a global variable, the application maintains the variable until the application is exited or until another object explicitly removes it.
Grant is the process to give a user access to a database object. Only the creator of a database object can grant access to it for other users.
A group function is a SQL function that computes a single summary value from the individual values in specified groups of rows. Some examples of group functions are the AVG, MAX, and SUM functions.
The header section occurs before the keyword IS in a PL/SQL subprogram. The header section is where the subprogram name, type (that is, procedure or function), and parameters are stated. This section is mandatory for named blocks and specifies how other PL/SQL blocks call the subprogram. In functions, this section contains the RETURN datatype.
The IF statement is a control structure that allows you to execute a sequence of statements conditionally.
The IN OUT parameter mode enables a value from the calling environment to be passed into a procedure and a possibly different value from the procedure passed back to the calling environment using the same parameter.
The IN parameter mode enables a constant value to be passed from the calling environment into the procedure. The IN parameter mode is the default parameter mode.
INSTEAD OF triggers execute the trigger body instead of the triggering statement. They are used for views that are not otherwise modifiable. You can write DML statements against the view and the INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
The keyword IS separates the header section from the declaration section of the subprogram. It is mandatory to include the keyword IS.
A LOB is a large object datatype. You use LOBs to store unstructured data, such as binary images, documents, or geographical information. LOBs were introduced in Oracle8 to supersede the LONG and LONG RAW datatypes.
Local dependencies occur when the objects are on the same node in the same database. The Oracle server automatically manages all local dependencies using the internal "depends-on" database table.
The LONG datatype is a standard Oracle datatype that can contain any printable character, such as A, 3, &, blank, or null and can be of any length from 1 to 2-GB characters.
A loop is a PL/SQL conditional statement that is repeated until the condition is no longer fulfilled.
A name is the unique identifier given to an object.
An NCLOB is a fixed-width multibyte character large object (CLOB) based on the national character set. NCLOBs are used to hold long pieces of text written in a language, such as Japanese, that requires multibyte characters.
The Number datatype is a standard Oracle datatype that can contain a number with or without a decimal point and a sign. It can have from 1 to 105 decimal digits with a maximum of 38 significant digits.
Oracle Developer is a rapid application development environment for building interactive applications and transaction-based or online transaction processing (OLTP) based systems. The Procedure Builder tool is included in the suite of Oracle Developer tools.
The OUT parameter mode enables a value to be passed from the procedure to the calling environment.
A package is a method of encapsulating and storing the related procedures, functions, variables, and other package constructs as a unit in the database.
The package body includes the actual implementation of the package, which may include private subprograms and datatypes. The body is optional if the package consists only of declarations.
The package specification declares the public interface to the package, that is, the datatypes and subprograms that can be referenced by other program units.
In the header section of a subprogram, parameters are stated in a parameter list. Parameters allow information to be passed between the subprogram and the calling program. The parameter list is a named programmatic construct that lists parameter names called keys, their datatypes, and their values.
Source code is compiled into p-code or pseudo code for execution.
PL/SQL is the procedural extension of SQL and provides programming constructs, such as conditional statements, loops, and procedures.
The primary key is the column or columns used to enforce the uniqueness of rows in a database table.
A privilege is the right to successfully execute a particular type of SQL statement. For example, the right to connect to a database is a privilege.
A procedure is a PL/SQL subprogram that performs a specified sequence of actions.
Procedure Builder is a GUI environment used to develop subprograms and has two areas of development. These are client-side and server-side. You can create subprograms in Procedure Builder using the Program Units node (client-side) or using the Stored Program Units node (server-side). The Object Navigator window shows your current location. The Program Unit Editor allows you to edit, compile, and delete subprograms. The Stored Program Unit Editor allows you to edit, compile, and drop subprograms.
A program unit is a code structure created using PL/SQL. Some examples of program units are anonymous blocks and subprograms.
A query is a SQL SELECT statement that specifies the data you wish to retrieve from one or more tables or views of a database.
A record is one row fetched by a SELECT statement.
A relational database management system (RDBMS) is the software used to create and maintain a database as well as the actual data stored in the database.
The relational operator is a symbol used in search criteria to indicate a comparison between two values. An example of a relational operator is the equal sign.
A row is one set of field values in a table. An example is the set of fields representing data for one employee in the EMPLOYEES table.
A row-level trigger fires for every row affected by an INSERT, UPDATE, or DELETE statement.
The select statement is a SQL statement that specifies which rows and columns to fetch from one or more tables or views.
The server-side area is on the Oracle Database. The developer can store subprograms on the server using SQL*Plus or Procedure Builder. By storing subprograms on the server, you can grant other users access to the subprograms.
A session is the period between invoking and quitting an executable.
A statement-level trigger fires once for each INSERT, UPDATE, or DELETE statement.
The source is a set of PL/SQL statements provided in a subprogram or package. The source code for a program unit is compiled into executable form.
The SQL buffer in SQL*Plus is the default buffer used to contain the SQL command or the PL/SQL block most recently entered.
A SQL script is a file containing SQL statements that you can run to perform database administration tasks quickly and easily.
A SQL statement is a SQL instruction to Oracle. An example of a statement is the SELECT statement.
A statement is a PL/SQL construct used for conditional, iterative and sequential control, and for error handling. A semicolon (;) must terminate every PL/SQL statement.
A stored program unit is a procedure, function, or package that resides and executes in the Oracle8i Server.
A stored subprogram is a procedure or function that resides and executes in the Oracle8i Server.
Subprograms are named PL/SQL blocks. There are two types, procedures and functions. Subprograms are also known as named blocks. Subprograms can take parameters and invoke them on the client or the server. You can store subprograms on the server.
A subquery is a query that is nested in a clause of a SQL command.
A substitution parameter allows an anonymous block to prompt the SQL*Plus environment for a value. The ampersand symbol precedes the variable name in the PL/SQL block.
Supplied package is the term used to refer to the PL/SQL packages shipped with the Oracle8i database.
Syntax is the ordering system by which commands, qualifiers, and parameters are combined to form valid command strings.
The system text editor is the default text editor for the computer the developer is using. Opening a SQL script file using the EDIT command will open the source code of the script file in the system text editor. On Windows NT systems, the system text editor is often the Notepad application.
A table is a named collection of related information stored in a relational database or server in a two-dimensional grid that is made up of rows and columns.
A transaction is a sequence of SQL statements treated as a single unit.
A trigger is a PL/SQL procedure that executes (fires) implicitly whenever a particular event takes place.
Update is the process of changing the values of table data specifically by altering data values using the SQL command UPDATE but also by deleting values using the SQL command DELETE or by inserting values using the command INSERT.
A user-defined package is a package created by a developer as opposed to a supplied package that is shipped with the Oracle database.
The VARCHAR2 datatype is a datatype similar to the CHAR datatype and is a standard Oracle datatype used to store character (alphanumeric) data. In Oracle RDBMS V6, CHAR, and VARCHAR are equivalent. In Oracle7 and later, CHAR data is fixed-length and VARCHAR is variable-length.
A variable is a named object that you can assign a value, and its assigned value may change over time.
A view is a virtual table whose rows do not actually exist in the view but are based on one or more tables that are physically stored in the database.
A window is a rectangular area of the desktop that contains an application. Each window has an area in which you can interact with the application. You can open, resize, move, reduce to an icon, or enlarge a window to fill the entire desktop.
|
