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). 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. DURATION:   2 days. Hands on. OBJECTIVES:   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). 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. COURSE CONTENT:   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 PI07/01