Oracle 10g: PL/SQL - Part 2

Overview

This course is highly interactive and consists of theory coupled with plenty of hands on practical sessions. It provides an in-depth look at PL/SQL in the SQL*Plus environment in order to create and use user defined packages, as well as be able to use Oracle supplied packages. The course also covers creation of Database Triggers and use of the Native Dynamic SQL (NDS).

This training may be available onsite; please contact us if you are interested.

Audience

This course is for programmers and support staff who wish to gain a comprehensive understanding of how the Oracle Procedural Language / Structured Query Language (PL/SQL) can be used in order to utilise Packages, Triggers and Native Dynamic SQL (NDS).

Skills Gained

After completing the course, delegates will be able to:

• Write user defined Packages and Triggers.
• Produce PL/SQL code that uses Oracle supplied Packages.
• Create re-usable code with NDS.

Prerequisites

Delegates should be computer literate with a good knowledge of Oracle SQL and SQL*Plus and the standard editor available on their target hardware platform. Understanding of Oracle PL/SQL programming concepts and techniques is also required. Previous attendance of our course Oracle PL/SQL Programming Part 1 is highly recommended.

Course Outline

Packages

• Introduction
• Package structure
• Package creation
• Quiz and practical session on packages

Built-In Packages

• STANDARD and DBMS_STANDARD
• DBMS_ALERT
• REGISTER
• REMOVE
• REMOVEALL
• SET_DEFAULTS
• SIGNAL
• WAITANY
• WAITONE
• DBMS_DDL
• ALTER_COMPILE
• ANALYZE_OBJECT
• DBMS_JOB
• BROKEN
• CHANGE
• INTERVAL
• ISUBMIT
• NEXT_DATE
• RUN
• SUBMIT
• USER_EXPORT
• WHAT
• DBMS_LOCK
• ALLOCATE_UNIQUE
• CONVERT
• RELEASE
• REQUEST
• SLEEP
• DBMS_PIPE
• CREATE_PIPE
• NEXT_ITEM_TYPE
• PACK_MESSAGE
• PURGE
• RECEIVE_MESSAGE
• REMOVE_PIPE
• RESET_BUFFER
• SEND_MESSAGE
• UNIQUE_SESSION_NAME
• UNPACK_MESSAGE
• DBMS_SESSION
• IS_ROLE_ENABLED
• SET_ROLE
• SET_SQL_TRACE
• DBMS_SQL
• BIND_VARIABLE
• CLOSE_CURSOR
• COLUMN_VALUE
• DEFINE_COLUMN
• EXECUTE
• EXECUTE_AND_FETCH
• FETCH_ROWS
• IS_OPEN
• LAST_ERROR_POSITION
• LAST_ROW_COUNT
• LAST_ROW_ID
• LAST_SQL_FUNCTION_CODE
• OPEN_CURSOR
• PARSE
• VARIABLE_VALUE
• DBMS_TRANSACTION
• READ_ONLY
• READ_WRITE
• USE_ROLLBACK_SEGMENT
• BEGIN_DISCRETE_TRANSACTION

DBMS_UTILITY

• ANALYZE_SCHEMA
• COMMA_TO_TABLE
• COMPILE_SCHEMA
• GET_TIME
• COMPILE_SCHEMA
• NAME_RESOLVE
• TABLE_TO_COMMA
• UTL_FILE
• FCLOSE
• FCLOSE_ALL
• FFLUSH
• FOPEN
• GET_LINE
• IS_OPEN
• NEW_LINE
• PUT
• PUTF
• PUT_LINE
• UTL_FILE Exceptions
• ORACLE9i Packages
• QUIZ and practical session on built-in packages

Native Dynamic SQL (NDS)

• DBMS_SQL Vs. NDS
• NDS Commands
• EXECUTE IMMEDIATE
• OPEN FOR (CURSOR VARIABLES)
• Using BIND variables with NDS
• QUIZ and practical session ON Native Dynamic SQL (NDS)

DDL and Database Level Triggers

• DDL Triggers
• Database level triggers
• A LOGON trigger
• Quiz and practical session on triggers

Collections

• Declaring nested tables and VARRAYs
• Initialising collections
• Manipulating elements
• Collection methods
• Bulk BINDS
• QUIZ and practical session on collections

External Procedures and Java Integration

• External procedures
• Java integration
• Publishing java to PL/SQL
• Collection methods
• Quiz