Oracle9i PL/SQL Database Programming Overview/Description Target Audience Expected Duration Lesson Objectives Course Number Overview/Description
This course discusses the features of stored subprograms and packages. Participants will learn how to create and invoke stored procedures and functions as well as how to create stored packages. This course also discusses built-in server-side packages and their use in debugging stored subprograms. Participants will learn how to manage stored subprograms and packages. This course also introduces participants to database triggers: how to create them and their application to complement the capabilities of the Oracle base product. This course consists of four units including a unit on course startup.
Target Audience
The intended audience for this course includes Application Developers, Database Administrator, Tech Support Professionals.
Expected Duration (hours)
8.0
Lesson Objectives Oracle9i PL/SQL Database Programming
The learner will be able to sequence the steps used to create stored subprograms by using iSQL*Plus.
The learner will be able to identify the effects of storing code separately on the server and on the client.
The learner will be able to perform the steps needed to create a stored procedure using iSQL*Plus.
The learner will be able to perform the steps needed to create a stored function using iSQL*Plus.
The learner will be able to match run-time exceptions with their descriptions and the directions for their handling.
The learner will be able to perform the steps needed to create a stored package using iSQL*Plus.
The learner will be able to perform the steps to invoke stored procedures in iSQL*Plus using IN and OUT parameters.
The learner will be able to perform the steps needed to invoke a stored function using iSQL*Plus.
The learner will be able to perform the steps needed to invoke a stored subprogram with multiple arguments in iSQL*Plus.
The learner will be able to identify the rules for calling user-defined functions in SQL statements.
The learner will be able to match the Oracle server supplied packages with their uses.
The learner will be able to identify the correct combinations of DBMS_PIPE subprograms and their uses.
The learner will be able to identify the uses of the DBMS_DDL package.
The learner will be able to match the subprograms of the DBMS_OUTPUT package with their uses.
The learner will be able to create an appropriate statement to display messages on the screen using DBMS_OUTPUT.
identify the features of the packages used to interact with operating system files.
The learner will be able to identify the uses of the DBMS_JOB package.
match the subprograms of the DBMS_JOB package with their uses.
identify the features of the UTL_FILE package.
match the subprograms of the UTL_FILE package with their uses.
match the exceptions of the UTL_FILE package with their uses.
sequence the steps to use the UTL_FILE package for file processing.
identify the features of the UTL_HTTP package.
identify the features of the UTL_TCP package.
The learner will be able to identify the uses of dynamic SQL.
The learner will be able to match the subprograms of the DBMS_SQL Oracle server supplied package with their uses.
The learner will be able to match the parameters of the EXECUTE IMMEDIATE statement with their uses.
The learner will be able to identify the distinctive properties of a LOB datatype.
The learner will be able to sequence the steps involved in managing an internal LOB datatype.
The learner will be able to sequence the steps involved in managing an external LOB datatype.
identify the guidelines for creating DIRECTORY objects.
sequence the steps to use a BFILE within an Oracle table.
identify the features of the BFILENAME function.
identify the features of the FILEEXISTS function.
identify the features of migrating data from the LONG datatype to the LOB datatype.
The learner will be able to match the subprograms of the DBMS_LOB package with their uses.
The learner will be able to identify the features of the DBMS_LOB package when selecting, reading, writing, and updating LOBs.
The learner will be able to identify the features of using SQL to create, insert, update, select, and remove LOBs.
identify the guidelines for using privileges that are related to a stored subprogram.
The learner will be able to grant privileges on a stored subprogram by using SQL commands.
The learner will be able to match the facilities available to manage stored subprograms with their application.
The learner will be able to display details about stored subprograms from the Oracle data dictionary.
The learner will be able to perform the steps to display compile time errors stored in the Oracle data dictionary.
identify the methods used to debug stored subprograms.
The learner will be able to match the different types of dependencies with their descriptions.
The learner will be able to perform the steps to display dependencies stored in the Oracle data dictionary.
The learner will be able to identify the situations in which a dependent subprogram can be successfully recompiled.
The learner will be able to identify the features of the Oracle server remote dependency mechanism.
The learner will be able to match the different tasks involved in managing packages with the methods of implementing them.
The learner will be able to identify the package dependency rules implemented by the Oracle server.
The learner will be able to match the components of a database trigger with their descriptions.
identify the guidelines for designing triggers.
The learner will be able to create a statement- level database trigger by using iSQL*Plus.
The learner will be able to create a row-level database trigger by using iSQL*Plus.
The learner will be able to identify the features of an INSTEAD OF trigger.
The learner will be able to identify the features of DBA triggers.
identify the features of Form Builder triggers.
The learner will be able to perform the steps needed to manage database triggers using iSQL*Plus.
drop a trigger by using the DROP TRIGGER statement.
sequence the steps followed by the Oracle server while firing triggers.
match the system privileges that you need to manage triggers with their functions.
The learner will be able to match the tasks performed in an Oracle database with the role that triggers have in achieving them.
call a stored procedure from a trigger by using the CALL statement.
match the data dictionary views that store the trigger information with their contents.
Course Number: en_US_61135_ng