Oracle Database 10g: Develop PL/SQL Program Units Duration: 3 days Description: This class is applicable to Oracle8i, Oracle9i and Oracle Database 10g users. This course is designed for developers with basic PL/SQL and SQL language skills who want to develop stored procedures, functions, packages and database triggers. Students will also learn to manage PL/SQL program units manage dependencies, manipulate large objects, and use some of the Oracle-supplied packages. Students use iSQL*Plus to develop these program units. JDeveloper is introduced as an optional tool. Demonstrations and handson practice reinforce the fundamental concepts. Objectives: - Create overloaded package subprograms for more flexibility - Categorize the Oracle supplied PL/SQL packages - Use the compiler warnings infrastructure - Create stored procedures and functions - Create triggers to solve business challenges - Manage dependencies between PL/SQL subprograms - Manipulate large objects - Schedule PL/SQL jobs to run independently - Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions - Design PL/SQL packages to group and contain related constructs - Use the Oracle supplied PL/SQL packages to generate screen output, file output, web output, and mail output - Write dynamic SQL for more coding flexibility Audiences: - Technical Consultant - Database Administrators - PL/SQL Developer - Support Engineer Topics: Introduction 1 - Modularize program development with PL/SQL blocks 2 - Describe the Human Resources (HR) data set 3 - Show the PL/SQL environment (PLSQL block / PLSQL engine / Oracle server and statement executor) 4 - Code PL/SQL in the iSQL*Plus environment 5 - Code PL/SQL in the SQL*Plus environment 6 - Code PL/SQL in the JDeveloper environment 7 - Create and invoke simple procedures and functions
Creating Stored Procedures 1 - Describe the block structure for PL/SQL stored procedures 2 - Invoke a stored procedure/function from different tools 3 - Call a stored procedure with host variables from iSQL*Plus, Forms, Java, C, etc 4 - Invoke a stored procedure from an anonymous block or another stored procedure 5 - List the CREATE OR REPLACE PROCEDURE syntax 6 - Identify the development steps for creating a stored procedure 7 - Use the SHOW ERRORS command 8 - View source code in the USER_SOURCE dictionary view Creating Stored Functions 1 - Describe stored functions 2 - List the CREATE OR REPLACE FUNCTION syntax 3 - Identify the steps to create a stored function 4 - Create a stored function in iSQL*Plus 5 - Execute a stored function 6 - Identify the advantages of using stored functions in SQL statements 7 - Identify the restrictions of calling functions from SQL statements 8 - Remove a function Creating Packages 1 - List the advantages of packages 2 - Describe packages 3 - Show the components of a package 4 - Diagram the visibility of constructs within a package 5 - Develop a package 6 - Create the package specification 7 - Declare public constructs 8 - Create the package body Using More Package Concepts 1 - List the benefits of overloading 2 - Show overloading example 3 - Use forward declarations in packages 4 - Create a one-time only procedure (package code initialization) 5 - List the restrictions on package functions used in SQL 6 - Encapsulate code in a package demonstration 7 - Invoke a user-defined package function from a SQL statement 8 - Utilize the persistent state of package variables Utilizing Oracle Supplied Packages in Application Development 1 - List the various uses for the Oracle supplied packages 2 - Reuse pre-packaged code to complete various tasks from developer to DBA purposes 3 - Use the DESCRIBE command to view the package specifications and overloading 4 - Explain how DBMS_OUTPUT works (in conjunction with SET SERVEROUPUT ON)
5 - Interact with operating system files with UTL_MAIL 6 - Describe file processing with UTL_FILE 7 - Review UTL_FILE routines and exceptions 8 - Use UTL_FILE to generate a report to a file Dynamic SQL and Metadata 1 - Describe using native dynamic SQL 2 - List the execution flow of SQL 3 - Show the syntax for the EXECUTE IMMEDIATE statement for native dynamic SQL 4 - Create a procedure to generate native dynamic SQL using EXECUTE IMMEDIATE to delete rows from a table 5 - Create a procedure to generate native dynamic SQL using EXECUTE IMMEDIATE that compiles PL/SQL code 6 - Describe the DBMS_SQL package 7 - Provide an example of DBMS_SQL 8 - List the advantages of using Native Dynamic SQL Over the DBMS_SQL package Design Considerations for PL/SQL Code 1 - Standardize constants with a constant package 2 - Standardize exceptions with an exception handling package 3 - Introduce local subprograms 4 - Use local subprograms 5 - Track run time errors with an exception package 6 - Describe the NOCOPY compiler hint 7 - Use the NOCOPY compiler hint 8 - Explain the effects of NOCOPY Managing Dependencies 1 - Define dependent and referenced objects 2 - Diagram dependencies with code, views, procedures, and tables 3 - Manage local dependencies between a procedure, view, and a table 4 - Analyze a scenario of local dependencies 5 - Display direct dependencies using the USER_DEPENDENCIES view 6 - Run the UTL_DTREE.SQL script to create objects that enable you to view direct and indirect dependencies 7 - Display dependencies from the DEPTREE view 8 - Predict the effects of changes on dependent objects Manipulating Large Objects 1 - Describe a LOB object 2 - Diagram the anatomy of a LOB 3 - Manage and list the features on internal LOBs 4 - Describe, manage, and secure BFILEs 5 - Create and use the DIRECTORY object to access and use BFILEs 6 - Prepare BFILEs for usage 7 - Use the BFILENAME function to load BFILEs
8 - Describe the DBMS_LOB package Creating Triggers 1 - Describe the different types of triggers and how they execute 2 - List the benefits and guidelines of using database triggers 3 - Show how triggers are executed with a basic database trigger example 4 - Show syntax and create DML triggers, and list the DML trigger components 5 - Explain the firing sequence of triggers 6 - Create a DML statement and row level triggers 7 - Use the OLD and NEW qualifiers to reference column values 8 - Use conditional predicates with triggers Applications for Triggers 1 - Create triggers for DDL events of CREATE, ALTER, and DROP 2 - Create triggers for system events of SERVERERROR, STARTUP, SHUTDOWN, LOGON and LOGOFF 3 - Show a LOGON and LOGOFF trigger example 4 - Use the CALL statement to call a stored procedure from a trigger 5 - Define a mutating table 6 - Show a mutating table code example 7 - Describe business application scenarios for implementing with triggers 8 - Describe the privileges required to manage triggers Understanding and Influencing the PL/SQL Compiler 1 - Describe the features of the PL/SQL compiler in Oracle Database 10g 2 - List the features of native compilation 3 - Identify the 3 parameters used to influence compilation (PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL) 4 - Show how to set the parameters 5 - Describe the dictionary view used to see how code is compiled (USER_PLSQL_OBJECTS) 6 - Change the parameter settings, recompile code, and view the results 7 - Describe the compiler warning infrastructure in Oracle Database 10g 8 - List the steps used in setting compiler warning levels