Description:
In this course, participants learn how to create and maintain database objects and how to store, retrieve, and manipulate data. In addition, participants learn how to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

This course enables participants to learn how to write PL/SQL procedures, functions and packages. Working in both the Procedure Builder and the SQL*Plus environments, participants will learn how to create and manage PL/SQL program units and database triggers. Participants will also learn how to use some of the Oracle-supplied packages.

Course Objectives:

• Control transactions
• Describe relational and object relational database concepts
• Control data and user access
• Use basic SQL*Plus commands
• Describe the features and syntax of PL/SQL
• Conditionally control code flow, loops, control structures and explicit cursors
• Handle runtime errors
• Retrieve, insert, update, and delete data
• Create and maintain database objects
• Describe the PL/SQL development environments
• Create, execute, and maintain procedures, functions, packages, database triggers, and object types
• Manage PL/SQL program constructs
• Describe Oracle supplied packages
• Manipulate large objects (LOB)
• Use advanced techniques to retrieve data including SET operators, correlated subqueries, and hierarchical queries
• Write SQL scripts that use SQL to generate SQL
• Use SQL*Plus to format reports

Combine Queries with SET Operators

• Describe the set operators
• Obey the set operators rules and guidelines
• Include the order by clause

• Describe the types of problems that can be solved with a correlated subquery
• Describe a correlated subquery
• Write correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Update and delete rows using correlated subqueries

• Discuss the benefits of the hierarchical query
• Order the rows retrieved by a query in a hierarchical manner
• Format hierarchical data so that it is easy to read
• Exclude branches from the tree structure

• Describe the types of problems that are solved by writing SQL scripts that generate other SQL scripts
• Write and execute a script that generates a script of drop table commands
• Write and execute a script that generates a script of insert commands

• Format output with the set commands
• Add header and footer information
• Aggregate data in the output

• Complete a DELETE and an UPDATE statement by using a correlated subquery Identify the features of a dual table
• Format numbers by using numeric functions
• Write a function to determine the position of a substring within a string
• Explore the properties of the different types of subqueries
• Write single-row, multiple-row, multiple-column and correlated subqueries Identify the features of a hierarchical query
• Write hierarchical queries and format hierarchical reports
• Complete a statement to prune a hierarchical tree
• Recognize the rules that must be followed when using set operators in compound queries
• Use set operators to control the order of the output of the compound queries
• Sequence the steps in the execution of a single-row subquery
• Complete a SELECT statement by using single-row and multiple single-row subqueries
• Complete a query by using group functions in a single-row subquery
• Create a table based on the definition of an existing table by using a subquery
• Insert values into a table from another table by using a subquery

• Define the steps to tuning an application
• Describe the causes of performance problems
• Identify the main system areas that benefit from tuning
• Discuss where SQL statements tuning fits into the methodology

• Use Explain Plan to identify how a statement will be processed
• Invoke SQL*Trace to gather statistics on execution of statements, including access paths
• Format and examine the trace statistics with tkprof utility
• Use the Autotrace utility in SQL*Plus to get quick feedback on statement optimization

• Create access paths to change the way SQL statements execute
• Describe the purpose and use of partitioned views
• Decide whether or not to use an a regular (B-tree) or bitmapped index
• Create index or hash clusters.