Oracle 12c PLSQL - Coming Soon

Course ID

31110

Course Description

This course provides a complete, hands-on comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g.

Prerequisites

Oracle Database 12c: SQL

Audience

This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators.

Course Content

    PL/SQL program structure
  • PL/SQL vs. SQL
  • PL/SQL engines available
  • Anonymous PL/SQL block structure
  • Object naming rules
  • Variable declarations
  • Available datatypes
    • Scalar datatypes
    • Using extended datatypes
    • Object types
  • Executable statements
  • Expressions
  • Block labeling
  • Variable scoping rules
  • Comments in programs and scripts
  • Basic coding standards

    PL/SQL flow control
  • Conditional control
  • Comparison operators
  • Logical operators
    • Truth tables
  • Repetition control
    • The simple loop
    • While loop
    • For loop
    • Continue statements in loops
    • Step loops
  • The goto statement
  • Case expressions / statements
  • Bind variables
  • Substitution variables

    SQL developer and PL/SQL
  • SQL developer and PL/SQL
  • Creating and executing scripts

    Select into
  • Selecting single rows of data
  • Anchoring variables to datatypes
  • Dml in PL/SQL
  • Returning... into
  • Sequences in PL/SQL
  • Transaction control in PL/SQL
  • Autonomous transactions

    The PL/SQL cursor
  • Declaring explicit cursors
  • Opening and closing explicit cursors
  • Using explicit cursors to retrieve values
  • Explicit cursor attributes
  • Using a loop with an explicit cursor
  • Using %rowtype with cursors
  • The cursor for loop
  • Dbms_output

    Optimization
  • Timing PL/SQL
  • For update / where current of

    PL/SQL exception handling
  • The exception section
  • Oracle named exceptions
  • Pragma exception_init
  • User defined exceptions
    • The scope of user-defined exceptions
  • Raising named exceptions
  • Exception propagation
  • Raising an exception again
  • Life after an exception
  • When others
  • Taking your ball and going home
  • Dbms_errlog

    Stored procedures
  • Procedures
  • Benefits of stored procedures
    • Database security
    • Performance
    • Productivity
    • Portability
  • Parameters and stored procedures
    • Parameter notation
  • Stored object creation
    • Syntax for creating a procedure
  • Compilation errors
  • Viewing compiled code
  • Dropping a procedure
  • The alter command and stored procedures

    Creating functions in PL/SQL
  • Functions
  • Purity levels
  • Using white lists
  • Optimizations
  • Parallel_enable
  • Deterministic functions
  • PL/SQL result cache
  • Nocopy
  • Dbms_output in functions
  • Using the with clause for functions
  • Pragma udf
  • Pragma inline
  • Using SQL developer with stored procedures
  • Debugging

    Packages
  • Creating packages
  • Package benefits
    • Security
    • Persistent state
    • I/o efficiency
  • A simple package
  • Overloading
  • Bodiless packages
  • Source code encryption
  • Creating packages from procedures and functions

    Creating dml triggers
  • Dml triggers
  • Dml trigger structure
  • Conditional triggering predicates
  • Triggers for business rules enforcement
  • Mutating and constraining tables
  • Compound triggers
  • Controlling firing order
  • Ddl for triggers
  • Viewing trigger source
  • Instead of triggers

    Advanced concepts
  • Embedded procedures
  • The optimizing compiler
  • PL/SQL compiler warnings
  • Compiling for debugging
  • Conditional compilation / inquiry directives
    • Error directives
    • Inquiry directives
    • Using static constants
  • Dbms_db_version
  • Native compilation
    • Recompiling all database objects

    File operations
  • Moving files between databases
  • Directory access
  • File manipulation
    • Fclose procedure
    • Fclose_all procedure
    • Fcopy procedure
    • Fflush procedure
    • Fgetattr procedure
    • Fgetpos function
    • Fopen function
    • Fremove procedure
    • Frename procedure
    • Fseek procedure
    • Get_line procedure
    • Get_raw procedure
    • Is_open function
    • New_line procedure
    • Put procedure
    • Put_line procedure
    • Putf procedure
    • Put_raw procedure

    Communications
  • Dbms_alert
    • Register procedure
    • Remove procedure
    • Removeall procedure
    • Set_defaults procedure
    • Signal procedure
    • Waitany procedure
    • Waitone procedure
    • Security
    • Dbms_alert example
    • Dbms_pipe
    • Create_pipe function
    • Next_item_type function
    • Pack_message procedure
    • Purge procedure
    • Receive_message function
    • Reset_buffer procedure
    • Remove_pipe function
    • Send_message function
    • Unique_session_name function
    • Unpack_message procedure
    • Dbms_pipe example
  • Utl_smtp
  • Utl_http
  • Utl_tcp
  • Utl_mail
  • Dbms_network_acl_admin

    Security
  • Dbms_rls
    • Implementation tasks
    • Our example scenario
    • The policy package
    • The dynamic predicate package
    • Creating the context and the trigger
    • Using dbms_rls
    • Exempting policies
  • Dbms_fga
  • Controlling column access with virtual columns

    Scheduling
  • Oracle scheduling
  • Dbms_job
    • Procedure broken
    • Procedure change
    • Procedure instance
    • Procedure interval
    • Procedure isubmit
    • Function is_jobq
    • Procedure next_date
    • Procedure remove
    • Procedure run
    • Procedure submit
    • Procedure user_export
    • Procedure user_export
    • Procedure what
    • The next_date parameter
    • The interval parameter
    • The what parameter
  • Dbms_scheduler
    • Evaluating calendaring expressions
    • Dbms_scheduler capabilities

    Miscellaneous packages
  • Dbms_output
  • Dbms_random
    • String function
    • Value function
  • Dbms_stats
  • Gather_schema_stats
  • Gather_table_stats
  • Dbms_wm
  • Dbms_metadata
  • Dbms_redefinition
  • Other notable packages / procedures

    Database triggers
  • Permissions needed
  • Ddl triggers
  • Servererror triggers
  • Logon / logoff triggers
  • Startup / shutdown triggers
  • Suspend triggers

    Collections
  • Defining records
  • Collections
    • Associative arrays
    • Nested tables
    • Varrays / varying arrays
    • Assignments
    • Comparing collections
  • Collection methods
    • Exists
    • First
    • Last
    • Count
    • Limit
    • Prior
    • Next
    • Delete
    • Trim
  • Set theory and nested tables

    Bulk operations
  • Bulk binding
  • Forall
  • SQL%bulk_rowcount
  • Save exceptions / SQL%bulk_exceptions
  • Bulk collect
    • The limit clause
    • Forall and the indices of clause
    • Forall and values of
  • Pipelined table functions
  • Multidimensional collections

    Cursor variables
  • What is a cursor variable?
  • Cursor variable control
  • Open for
  • Fetch
  • Close
  • Cursor variable examples
  • Rowtype_mismatch

    Dynamic SQL
  • Standard dynamic SQL
  • New dbms_SQL functionality
  • Native dynamic SQL (nds)
  • Execute immediate
    • Open for, fetch and close
    • Additional rules for native dynamic SQL
  • Bulk execute immediate
  • SQL injection attacks
    • SQL statement manipulation
    • Additional statement insertion
    • The using clause vs. Concatenation
    • Dbms_assert
  • Switching between nds and dbms_SQL

    Large objects
  • External vs. Internal large objects
  • Initializing internal lob locators
  • The dbms_lob package
  • Inserting internal large objects
  • Loading errors
  • The securefile option

    Object-oriented programming
  • Oracle objects
  • Ordering object types
  • Object tables
  • SQL for object tables
  • Advanced object concepts

    Java and other languages
  • Alternative languages
  • Loading code
  • Publishing code
  • Executing the code
  • Interfacing with c
  • Enhanced language interface features

For More Information

For training inquiries, call 850-308-1376

or email us at eramos@gbsi.com

Course Details

Duration - 5 days
Price - $2995.00 USD


(Discounts may apply. Call for more information.)

Course Actions

Schedule

Acceletrain Collaborative Learning Environment (formerly know as VILT) places industry certified and expert instructors, peers, learners and multi-media components into a "borderless classroom", and interactive learning environment that can span multiple physical locations. VILT combines the benefits of the traditional brick-and-mortar classroom with innovative learning techniques and the cost savings of internet-based training.