Web Bölümü Sayfası Başlık Çubuğu resmi
Development Tools

Development Tools
Oracle Application Express 3.0: Developing Web Applications - LVC
Eğitim Süresi:
48 Saat
Neler Öğreneceksiniz:
This course is designed to introduce the students to Oracle Application Express 3.0. The course covers various
Application Express components and wizards to build applications and database objects that are easy to deploy. The
students learn how to build forms and reports and incorporate different types of items and shared components. Creating
and utilizing various navigation components as well as session management and debugging are discussed. In addition,
students incorporate access control, and session protection to enforce application security. Once the application is
complete, students learn how to define their packaged application and export the application. Students will also use all
the Utilities and Report capabilities that Oracle Application Express provides to build a robust application.
Learn to:
Develop a database-centric Web application using the Application Builder
Manage Authentication, Authorization and Session State within your Application
Utilize and Manage Shared Components
Administer Application Express Workspaces
Manage Database Objects Using SQL Workshop
Deploy Your Application
A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any
time. Registered students must view the class from the country listed in the registration form. Unauthorized recording,
copying, or transmission of LVC content may not be made.
Katılımcı Profili:
  • Application Developers
  • Developer
  • Forms Developer
  • PL/SQL Developer
  • Reports Developer
Ön Şartlar:
Basic knowledge of SQL, PL/SQL, and HTML
Hedefler:
  • Login to Oracle Application Express
  • Develop a database-centric Web application
  • Utilize and Manage Shared Components in an Application
  • Add Page Processing components
  • Manage database objects
  • Build queries of database objects and incorporate in an application
  • Deploy the Application as a Packaged Application
  • Manage users and groups
  • Manage Access Control by authorizing and authenticating users
  • Administer Oracle Application Express Workspaces
  • Load and Unload data or application components
Eğitim İçeriği:
Introducing Oracle Application Express
Explaining the General Concepts
Identifying the Main Components
Creating a Workspace and Workspace Administrator
Logging In to Oracle Application Express
Installing and Running a Sample Application
Installing a Packaged Application
Interacting with the Database by Using SQL Workshop
Browsing, Creating and Modifying Objects
Processing Ad Hoc SQL Statements
Managing Scripts
Creating, Running and Saving SQL Queries
Building an Application
Identifying the Components of an Application
Creating an Application from Scratch
Creating an Application from a Spreadsheet
Creating Reports
Generating Reports Using Wizards
Editing Report Attributes
Adding Links in Reports
Saving or Printing Reports in Different Formats
Creating Forms
Creating a Form on a Table With a Report
Creating a Tabular Form
Creating a Master-Detail Form
Editing Form Attributes

Working with Pages and Regions
Viewing Page Definitions
Editing Page Attributes
Creating a New Region
Viewing Region Attributes
Adding Comments
Adding Items and Buttons
Creating Items
Editing Item Attributes
Creating Buttons
Editing Button Attributes
 
Understanding Session State and Debugging
Implementing Session State
Setting a Session State Value
Clearing the Session State
Reviewing the Session State
Reviewing the Messages in Debug Mode
 
Including Page Processing
Explaining the Difference Between Page Rendering and Page Processing
Creating Computations
Validating Input Data
Process Data
Creating Application Branching
 
Adding Shared Components That Aid Navigation
Providing an Overview of Shared Components
Managing Parent and Standard Tabs
Managing Navigation Bars
Managing Lists
Managing Breadcrumbs
 
Adding Other Shared Components
Managing List of Values
Managing Trees
Accessing Shared Component Reports
Setting User Interface Defaults
 
Displaying Dynamic Content
Filtering Reports Using Dynamic Queries
Creating Charts Using a Wizard
Creating a Calendar
 
Working with Themes and Templates
Switching to a Different Theme
Viewing Existing Templates
Managing Templates
Adding a Logo
Referencing a Cascading Style Sheet
Changing a Field to Use a Class in the Referencing Stylesheet
 
Administering Oracle Application Express Workspaces
Managing Users
Requesting a Schema or Storage
Purging Session State
Monitoring Developer Activity Log
Monitoring Workspace Activity
Viewing Activity Reports
 
Implementing Security
Differentiating Between Authentication and Authorization
Providing an Authentication Scheme
Creating an Authorization Scheme Using Access Control
Enabling and Configuring Session State Protection
 
Deploying Your Application
Defining Supporting Objects
Exporting Your Application
Importing Your Application
Installing Supporting Objects
 
Using Utilities
Importing and Exporting Data
Generating Data Definition Language (DDL) Statements
Viewing Object Reports
Monitoring the Database
Oracle Application Express 3.0: Developing Web Applications - LVC
Eğitim Süresi:
24 Saat
Neler Öğreneceksiniz:
This course is designed to introduce the students to Oracle Application Express 3.0. The course covers various
Application Express components and wizards to build applications and database objects that are easy to deploy. The
students learn how to build forms and reports and incorporate different types of items and shared components. Creating
and utilizing various navigation components as well as session management and debugging are discussed. In addition,
students incorporate access control, and session protection to enforce application security. Once the application is
complete, students learn how to define their packaged application and export the application. Students will also use all
the Utilities and Report capabilities that Oracle Application Express provides to build a robust application.
Katılımcı Profili:
Application Developers
Developer
Forms Developer
PL/SQL Developer
Reports Developer
Ön Şartlar:
Required Prerequisites
Basic knowledge of SQL, PL/SQL, and HTML
Hedefler:
Login to Oracle Application Express
Develop a database-centric Web application
Utilize and Manage Shared Components in an Application
Add Page Processing components
Manage database objects
Build queries of database objects and incorporate in an application
Deploy the Application as a Packaged Application
Manage users and groups
Manage Access Control by authorizing and authenticating users
Administer Oracle Application Express Workspaces
Load and Unload data or application components
Eğitim İçeriği:
Introducing Oracle Application Express
Explaining the General Concepts
Identifying the Main Components
Creating a Workspace and Workspace Administrator
Logging In to Oracle Application Express
Installing and Running a Sample Application
Installing a Packaged Application
Interacting with the Database by Using SQL Workshop
Browsing, Creating and Modifying Objects
Processing Ad Hoc SQL Statements
Managing Scripts
Creating, Running and Saving SQL Queries
Building an Application
Identifying the Components of an Application
Creating an Application from Scratch
Creating an Application from a Spreadsheet
Creating Reports
Generating Reports Using Wizards
Editing Report Attributes
Adding Links in Reports
Saving or Printing Reports in Different Formats
Creating Forms
Creating a Form on a Table With a Report
Creating a Tabular Form
Creating a Master-Detail Form
Editing Form Attributes
Working with Pages and Regions
Viewing Page Definitions
Editing Page Attributes
Creating a New Region
Viewing Region Attributes
Adding Comments
Adding Items and Buttons
Creating Items
Editing Item Attributes
Creating Buttons
Editing Button Attributes
 
Understanding Session State and Debugging
Implementing Session State
Setting a Session State Value
Clearing the Session State
Reviewing the Session State
Reviewing the Messages in Debug Mode
Including Page Processing
Explaining the Difference Between Page Rendering and Page Processing
Creating Computations
Validating Input Data
Process Data
Creating Application Branching
Adding Shared Components That Aid Navigation
Providing an Overview of Shared Components
Managing Parent and Standard Tabs
Managing Navigation Bars
Managing Lists
Managing Breadcrumbs
Adding Other Shared Components
Managing List of Values
Managing Trees
Accessing Shared Component Reports
Setting User Interface Defaults
Displaying Dynamic Content
Filtering Reports Using Dynamic Queries
Creating Charts Using a Wizard
Creating a Calendar
Working with Themes and Templates
Switching to a Different Theme
Viewing Existing Templates
Managing Templates
Adding a Logo
Referencing a Cascading Style Sheet
Changing a Field to Use a Class in the Referencing Stylesheet
Administering Oracle Application Express Workspaces
Managing Users
Requesting a Schema or Storage
Purging Session State
Monitoring Developer Activity Log
Monitoring Workspace Activity
Viewing Activity Reports
Implementing Security
Differentiating Between Authentication and Authorization
Providing an Authentication Scheme
Creating an Authorization Scheme Using Access Control
Enabling and Configuring Session State Protection
Deploying Your Application
Defining Supporting Objects
Exporting Your Application
Importing Your Application
Installing Supporting Objects
Using Utilities
Importing and Exporting Data
Generating Data Definition Language (DDL) Statements
Viewing Object Reports
Monitoring the Database
Oracle Application Express: Advanced Workshop
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
Students attending this workshop enhance their skills in Oracle Application Express. Students examine topics on using
application navigation to build site maps and dashboards. In addition, students learn about AJAX and Javascript and
how these can be used in an APEX application.
Katılımcı Profili:
Application Developers
Database Administrators
Developer
Ön Şartlar:
Required Prerequisites
Oracle Application Express 3.0: Developing Web Applications
Hedefler:
Monitor and manage your application by using custom reports
Manage application navigation by using hierarchical lists with images, database-driven navigation, site maps, and dashboards
Build custom tabular forms that use collections and validations
Manage application content dynamically by using JavaScript and AJAX
Extend your application to add binary large object (BLOB) data, send email notifications, use Web services, and integrate with SQL Manipulate application content through templates, themes, and style sheets
Create reports declaratively and use the print APIs
Secure your application by using LDAP and SSO and prevent SQL injection attacks and cross-site scripting and usage of Session
Eğitim İçeriği:
Managing Application Navigation
Build a Hierarchical List with Images
Build a Database Driven Navigation Report
Build a Site Map
Adding a Dashboard
Incorporate Security into Navigation
Manipulate Interactive Reports
Building Custom Tabular Forms
Build a Custom Tabular Form
Add Validations to your Tabular Form
Manage Changes to the Tabular Form using Collections
Using Javascript and AJAX Techniques
Incorporating some JavaScript examples into your APEX Application
Incorporating some AJAX examples into your APEX Application
Extending your Application
Adding BLOB Data to your Application
Sending Email Notifications
Incorporating Web Services into your Application
Accessing APEX Application objects in SQL Developer
Customizing Themes and Templates
Copying and Modifying a template
Creating and modifying a theme
Using a variety of styles in a template
Utilizing Application Express Printing
Printing a Standard Report with Derived Output
Creating a PDF Report with Multiple Queries
Including Dynamic Images in your Report
Creating a Report with a Custom XML and XSL
Sending a Report as an Email Attachment
Creating a Report with a Dynamic Layout Selection
Securing your Application
Authenticating Users Using LDAP
Configuring your Application for Single-Sign On
Preventing SQL Injection Attacks
Preventing Cross-Site Scripting
Using Session State Protection
Monitoring and Managing your Application
Using Prepackaged Monitoring Capabilities
Monitoring Activity of your Application
Monitoring Performance of your Application
Tuning your Application
Oracle Application Express: Developing Web Applications NEW
Eğitim Süresi:
24 Saat
Neler Öğreneceksiniz:
Oracle Application Express is a rapid web application development tool for the Oracle database. This course is designed
to introduce the students to Oracle Application Express 3.2. The course covers various Application Express components
and wizards to build applications and database objects that are easy to deploy. The students learn how to build forms
and reports and incorporate different types of items and shared components. This course introduces interactive reports
that enable end user customization. Students learn how to build and customize interactive reports. Creating and utilizing
various navigation components as well as session management and debugging are discussed as well. In addition,
students incorporate access control, and session protection to enforce application security. Once the application is
complete, students learn how to define their packaged application and export the application. Students will also use all
the Utilities and Report capabilities that Oracle Application Express provides to build a robust application.
Katılımcı Profili:
Application Developers
Business Analysts
Developer
Forms Developer
PL/SQL Developer
Reports Developer
Ön Şartlar:
Required Prerequisites
Oracle Database 11g: SQL Fundamentals I

Suggested Prerequisites
Basic knowledge of SQL, PL/SQL, and HTML
Hedefler:
Login to Oracle Application Express
Identify the Components of an Application
Develop a database-centric Web application
Manage database objects
Build queries of database objects and incorporate in an application
Build and Customize Interactive Reports
Create Forms
Utilize and Manage Shared Components in an Application
Add Page Processing Components
Deploy the Application as a Packaged Application
Manage users and groups
Manage Access Control by authorizing and authenticating users
Administer Oracle Application Express Workspaces
Load and Unload data or application components
Eğitim İçeriği:
Introducing Oracle Application Express
Explaining Oracle Application Express Concepts
Identifying the Components of Oracle Application Express
Explaining the Oracle Application Express architecture
Creating a Workspace and a Workspace administrator user
Installing and Running a Sample Application provided with Oracle Application Express
Installing a Packaged Application
Logging in to an Oracle Application Express Workspace
Interacting with the Database by Using SQL Workshop
Browsing, Creating, and Modifying Objects
Processing Ad Hoc SQL Statements
Managing SQL Scripts
Creating, Running, and Saving SQL Queries
Building an Application
Identifying the Components of an Application
Creating an Application from Scratch
Creating an Application from a Spreadsheet
Creating Reports
Identifying Report Types
Creating Interactive Reports
Generating Reports Using Wizards
Manipulating your Interactive Report
Customizing your Interactive Report
Creating Forms
Identifying the types of Forms
Creating a Form on a Table with Report
Creating a Tabular Form
Creating a Master Detail Form
Editing Form Attributes
Working with Pages and Regions
Viewing Page Definitions
Editing Page Attributes
Creating a New Region
Viewing Region Attributes
Adding Comments
Adding Items and Buttons
Creating Items
Editing Item Attributes
Creating Buttons
Editing Button Attributes
Understanding Session State and Debugging
Implementing Session State
Setting a Session State Value
Clearing the Session State
Reviewing the Session State
Reviewing the Messages in Session State
Including Page Processing
Explaining the Difference Between Page Rendering and Page Processing
Creating Computations for your Application
Validating Input Data
Process Data
Creating Application Branching
Adding Shared Components That Aid Navigation
Providing an Overview of Shared Components
Managing Parent and Standard Tabs
Managing Navigation Bars
Managing Lists
Managing Breadcrumbs
Adding Other Shared Components
Including List of Values in your Application
Creating Trees
Accessing Shared Component Reports
Setting User Interface Defaults
Displaying Dynamic Content
Filtering Reports Using Dynamic Queries
Creating Charts Using a Wizard
Creating a Calendar
Working with Themes and Templates
Explaining Themes and Templates
Switching to a Different Theme
Creating a Custom Theme
Viewing Existing Templates
Creating and Editing a Template
Referencing a Cascading Style Sheet
Changing a Filed to Use a Class in the Referencing style sheet
Administering Oracle Application Express Workspaces
Managing Users
Requesting a Schema or Storage
Purging Session State
Monitoring Developer Activity Log
Monitoring Workspace Activity
Viewing Activity Reports
Implementing Security
Differentiating Between Authentication and Authorization
Providing an Authentication Scheme
Creating an Authorization Scheme Using Access Control
Enabling and Configuring Session State Protection
Deploying Your Application
Defining Supporting Objects
Exporting Your Application
Importing Your Application
Installing Supporting Objects
Using Utilities
Importing and Exporting Data
Generating Data Definition Language (DDL) Statements
Viewing Object Reports
Viewing the Data Dictionary
Monitoring the Database
Oracle Database 11g: Advanced PL/SQL
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
In this course, students learn to use the advanced features of PL/SQL in order to design and tune PL/SQL to interface
with the database and other applications in the most efficient manner. Using advanced features of program design,
packages, cursors, extended interface methods, large objects, and collections, students learn to write powerful PL/SQL
programs. Students also learn programming efficiency, use of external C and Java routines, fine-grained access and
safeguarding code against SQL Injection attacks.
Katılımcı Profili:
Application Developers
Developer
PL/SQL Developer
Ön Şartlar:
Required Prerequisites
Oracle Database 11g: SQL Fundamentals II
Oracle Database 11g: SQL Fundamentals I
 
Suggested Prerequisites
Experience with SQL and PL/SQL required
Familiarity with the Oracle Database
Oracle Database 11g: Introduction to SQL
Oracle Database 11g: Program with PL/SQL
Hedefler:
Tune PL/SQL code
Categorize and explain various types of SQL injection attacks
Apply coding standards to eliminate SQL injection vulnerabilities
Create subtypes based on existing types for an application
Create and use collections
Execute external C programs from PL/SQL
Execute Java programs from PL/SQL
Describe the process of fine-grained access control
Create and maintain LOB data types
Use the DBMS_LOB PL/SQL package to control LOBs
Describe SecureFile LOB features
Identify guidelines for cursor design
Enable SecureFile LOB deduplication, compression, and encryption
Improve memory usage by caching SQL result sets
Set up PL/SQL functions to use PL/SQL result caching
Profile PL/SQL applications
Eğitim İçeriği:
Overview of the Development Environments
SQL Developer
SQL*Plus
Design Considerations
Describe the predefined data types
Create subtypes based on existing types for an application
List the different guidelines for cursor design
Use cursor variables
Pass cursor variables as program parameters
Compare cursor variables to static cursors

Using Collections
Overview of collections
Use Associative arrays
Use Nested tables
Use Varrays
Write PL/SQL programs that use collections
Use Collections effectively

Using Advanced Interface Methods
Calling C from PL/SQL
Calling Java from PL/SQL

Implementing VPD with Fine-Grained Access Control
Understand how fine-grained access control works overall
Describe the features of fine-grained access control
Describe an application context
Create an application context
Set an application context
List the DBMS_RLS procedures
Implement a policy
Query the dictionary views holding information on fine-grained access
 
Manipulating Large Objects
Describe a LOB object
Manage internal LOBs
Describe BFILEs
Create and use the DIRECTORY object to access and use BFILEs
Describe the DBMS_LOB package
Remove LOBs
Create a temporary LOB programmatically with the DBMS_LOB package
Administering SecureFile LOBs
Introduction to SecureFile LOBs
Enable the environment for SecureFile LOBs
Use SecureFile LOBs to store documents
Convert BasicFile LOBs to SecureFile LOB format
Examine the performance of SecureFile LOBs
Enable deduplication and compression
Enable encryption
Tuning and Performance
Understand and influence the compiler
Tune PL/SQL code
Enable intra unit inlining
Identify and tune memory issues
Improving Performance with SQL and PL/SQL Caching
Describe result caching
Use SQL query result cache
PL/SQL function cache

Analyzing PL/SQL Code
Use the supplied packages and dictionary views to find coding information
Determine identifier types and usages with PL/Scope
Use the DBMS_METADATA package to obtain metadata from the data dictionary as XML or creation DDL that can be used
to re-
Profiling and Tracing PL/SQL Code
Trace PL/SQL program execution
Profile PL/SQL applications

Safeguarding Your Code Against SQL Injection Attacks
Describe SQL injections
Reduce attack surfaces
Use DBMS_ASSERT
Design immune code
Test code for SQL injection flaws
Oracle Database 11g: Develop PL/SQL Program Units - LVC
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
This course is designed for developers with basic PL/SQL and SQL language skills. Students learn to develop, execute,
and manage PL\SQL stored program units such as procedures, functions, packages, and database triggers. Students
also learn to manage, PL/SQL subprograms and triggers. Students are introduced to the utilization of some of the
Oracle-supplied packages.
Katılımcı Profili:
Application Developers
Database Administrators
Forms Developer
PL/SQL Developer
Support Engineer
System Analysts
Ön Şartlar:
Required Prerequisites
Oracle Database 11g: Introduction to SQL (combination of Oracle Database 11g: SQL Fundamentals I and Oracle Database 11g: Previous programming experience
Basic Knowledge of PL/SQL
Hedefler:
Manage dependencies between PL/SQL subprograms
Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
Create, and use stored procedures and functions
Design and use PL/SQL packages to group and contain related constructs
Create overloaded package subprograms for more flexibility
Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
Create triggers to solve business challenges
Write dynamic SQL for more coding flexibility
Use the compiler warnings infrastructure
Design PL/SQL code for pre-defined data types, local subprograms, additional programs and standardized constants and exceptions
Eğitim İçeriği:
Introduction
Course Objectives and Agenda
Describing the Human Resources (HR) Schema
Identifying the Appendices Used in this Course
Listing the PL/SQL Development Environments Available in this Course
Using SQL Developer
Reviewing the Online Oracle 11g SQL and PL/SQL Documentation and the Additional Available Resources
Creating Stored Procedures
Creating a Modularize and Layered Subprogram Design
Modularizing Development With PL/SQL Blocks
Understanding the PL/SQL Execution Environment
The Benefits of Using PL/SQL Subprograms
The Differences Between Anonymous Blocks and Subprograms
Creating, Calling, and Removing Stored Procedures Using the CREATE Command and SQL Developer
Using Procedures Parameters and Parameters Modes
Viewing Procedures Information Using the Data Dictionary Views and SQL Developer
Creating Stored Functions
Creating, Calling, and Removing a Stored Function Using the CREATE Command and SQL Developer
Identifying the Advantages of Using Stored Functions in SQL Statements
Identify the steps to create a stored function
Using User-Defined Functions in SQL Statements
Restrictions When Calling Functions from SQL statements
Controlling Side Effects When Calling Functions from SQL Expressions
Viewing Functions Information
Creating Packages
Listing the Advantages of Packages
Describing Packages
The Components of a Package
Developing a Package
The Visibility of a Package’s Components
Creating the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
Invoking the Package Constructs
Viewing the PL/SQL Source Code Using the Data Dictionary
 
Working With Packages
Overloading Subprograms in PL/SQL
Using the STANDARD Package
Using Forward Declarations to Solve Illegal Procedure Reference
Using Package Functions in SQL and Restrictions
Persistent State of Packages
Persistent State of a Package Cursor
Controlling Side Effects of PL/SQL Subprograms
Using PL/SQL Tables of Records in Packages
Using Oracle-Supplied Packages in Application Development
Using Oracle-Supplied Packages
Examples of Some of the Oracle-Supplied Packages
How Does the DBMS_OUTPUT Package Work?
Using the UTL_FILE Package to Interact With Operating System Files
Using the UTL_MAIL Package
Using the UTL_MAIL Subprograms
Using Dynamic SQL
The Execution Flow of SQL
What is Dynamic SQL?
Declaring Cursor Variables
Dynamically Executing a PL/SQL Block
Using Native Dynamic SQL to Compile PL/SQL Code
Using DBMS_SQL Package
Using DBMS_SQL with a Parameterized DML Statement
Dynamic SQL Functional Completeness
Design Considerations for PL/SQL Code
Standardizing Constants and Exceptions
Using Local Subprograms
Using Autonomous Transactions
Using the NOCOPY Compiler Hint
Using the PARALLEL_ENABLE Hint
Using the Cross-Session PL/SQL Function Result Cache
Using the DETERMINISTIC Clause with Functions
Using Bulk Binding to Improve Performance
Creating Triggers
Working With Triggers
Identifying the Trigger Event Types and Body
Business Application Scenarios for Implementing Triggers
Creating DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
Identifying the Trigger Event Types, Body, and Firing (Timing)
Statement Level Triggers Versus Row Level Triggers
Creating Instead of and Disabled Triggers
Managing, Testing, and Removing Triggers
Creating Compound, DDL, and Event Database Triggers
Working With Compound Triggers
Identifying the Timing-Point Sections of a Table Compound Trigger
Compound Trigger Structure for Tables and Views
Using a Compound Trigger to Resolve the Mutating Table Error
Comparing Database Triggers to Stored Procedures
Creating Triggers on DDL Statements
Creating Database-Event and System-Event Triggers
System Privileges Required to Manage Triggers
Using the PL/SQL Compiler
Using the PL/SQL Compiler
Using the Initialization Parameters for PL/SQL Compilation
Using the New PL/SQL Compile Time Warnings
Overview of PL/SQL Compile Time Warnings for Subprograms
The Benefits of Compiler Warnings
The PL/SQL Compile Time Warning Messages Categories
Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Viewing the Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
Managing PL/SQL Code
What Is Conditional Compilation and How Does it Work?
Using Selection Directives
Using Predefined and User-Defined Inquiry Directives
The PLSQL_CCFLAGS Parameter and the Inquiry Directive
Using Conditional Compilation Error Directives to Raise User-Defined Errors
Using the DBMS_DB_VERSION Package
Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text°
Obfuscating and Wrapping PL/SQL Code
Managing Dependencies
Overview of Schema Object Dependencies
Querying Direct Object Dependencies Using the USER_DEPENDENCIES View
Querying an Object’s Status
Invalidation of Dependent Objects
Displaying Direct and Indirect Dependencies
Fine-Grained Dependency Management in Oracle Database 11g
Understanding Remote Dependencies
Recompiling a PL/SQL Program Unit
Oracle Database 11g: Introduction to SQL
Eğitim Süresi:
30 Saat
Neler Öğreneceksiniz:
In this course students learn the concepts of relational databases. This course provides the essential SQL skills that
allow developers to write queries against single and multiple tables, manipulate data in tables, and create database
objects. Students learn to control privileges at the object and system level.
Katılımcı Profili:
Application Developers
Business Analysts
Data Warehouse Administrator
Developer
Forms Developer
PL/SQL Developer
System Analysts
Ön Şartlar:
Suggested Prerequisites
Familiarity with data processing concepts and techniques
Data processin
Hedefler:
Employ SQL functions to generate and retrieve customized data
Display data from multiple tables using the ANSI SQL 99 JOIN syntax
Identify the major structural components of the Oracle Database 11g
Create reports of aggregated data
Write SELECT statements that include queries
Retrieve row and column data from tables with the SELECT statement
Run data manipulation statements (DML) to update data in the Oracle Database 11g
Create tables to store data
Utilize views to display and retrieve data
Control database access to specific objects
Manage schema objects
Manage objects with data dictionary views
Write multiple-column sub-queries
Use scalar and correlated sub-queries
Use the regular expression support in SQL
Create reports of sorted and restricted data
Eğitim İçeriği:
Introducing Oracle Database 11g
List the features of Oracle Database 11g
Discuss the basic design, theoretical and physical aspects of a relational database
Categorize the different types of SQL statements
Describe the data set used by the course
Log onto the database using the SQL Developer environment
Save queries to files and use script files in SQL Developer
Retrieving Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements
Generate a report of data from the output of a basic SELECT statement
Select All Columns
Select Specific Columns
Use Column Heading Defaults
Use Arithmetic Operators
Understand Operator Precedence
Learn the DESCRIBE command to display the table structure
Restricting and Sorting Data
Write queries that contain a WHERE clause to limit the output retrieved
List the comparison operators and logical operators that are used in a WHERE clause
Describe the rules of precedence for comparison and logical operators
Use character string literals in the WHERE clause
Write queries that contain an ORDER BY clause sort the output of a SELECT statement
Sort output in descending and ascending order
Using Single-Row Functions to Customize Output
Describe the differences between single row and multiple row functions
Manipulate strings with character function in the SELECT and WHERE clauses
Manipulate numbers with the ROUND, TRUNC and MOD functions
Perform arithmetic with date data
Manipulate dates with the date functions
Using Conversion Functions and Conditional Expressions
Describe implicit and explicit data type conversion
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nest multiple functions
Apply the NVL, NULLIF, and COALESCE functions to data
Use conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group Functions
Use the aggregation functions in SELECT statements to produce meaningful reports
Create queries that divide the data in groups by using the GROUP BY clause
Create queries that exclude groups of date by using the HAVING clause
Displaying Data From Multiple Tables
Write SELECT statements to access data from more than one table
View data that generally does not meet a join condition by using outer joins
Join a table by using a self join
Using Sub-queries to Solve Queries
Describe the types of problem that sub-queries can solve
Define sub-queries
List the types of sub-queries
Write single-row and multiple-row sub-queries
Using the SET Operators
Describe the SET operators
Use a SET operator to combine multiple queries into a single query
Control the order of rows returned when using the SET operators
Manipulating Data
Describe each DML statement
Insert rows into a table with the INSERT statement
Use the UPDATE statement to change rows in a table
Delete rows from a table with the DELETE statement
Save and discard changes with the COMMIT and ROLLBACK statements
Explain read consistency
Using DDL Statements to Create and Manage Tables
Categorize the main database objects
Review the table structure
List the data types available for columns
Create a simple table
Decipher how constraints can be created at table creation
Describe how schema objects work
Creating Other Schema Objects
Create a simple and complex view
Retrieve data from views
Create, maintain, and use sequences
Create and maintain indexes
Create private and public synonyms
Controlling User Access
Differentiate system privileges from object privileges
Grant privileges on tables
View privileges in the data dictionary
Grant roles
Distinguish between privileges and roles
Managing Schema Objects
Add constraints
Create indexes
Create indexes using the CREATE TABLE statement
Create function-based indexes
Drop columns and set column UNUSED
Perform FLASHBACK operations
Create and use external tables
Managing Objects with Data Dictionary Views
Explain the data dictionary
Find table information
Report on column information
View constraint information
Find view information
Verify sequence information
Understand synonyms
Add comments
Manipulating Large Data Sets
Manipulate data using sub-queries
Describe the features of multi-table inserts
Use the different types of multi-table inserts
Merge rows in a table
Track the changes to data over a period of time
Managing Data in Different Time Zones
Use data types similar to DATE that store fractional seconds and track time zones
Use data types that store the difference between two date-time values
Practice using the multiple data-time functions for globalize applications
Retrieving Data Using Sub-queries
Write a multiple-column sub-query
Use scalar sub-queries in SQL
Solve problems with correlated sub-queries
Update and delete rows using correlated sub-queries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause
Regular Expression Support
List the benefits of using regular expressions
Use regular expressions to search for, match, and replace strings
Oracle Database 11g: PL/SQL Fundamentals - LVC
Eğitim Süresi:
12 Saat
Neler Öğreneceksiniz:
This course introduces students to PL/SQL and explains the benefits of this powerful programming language. Students
learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management
applications. Students also learn to create anonymous PL/SQL blocks and are introduced to stored procedures and
functions. Students learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice
reinforce the fundamental concepts.
Katılımcı Profili:
Forms Developer
PL/SQL Developer
Portal Developer
System Analysts
Technical Consultant
Ön Şartlar:
Required Prerequisites
Oracle Database 11g: SQL Fundamentals II
Oracle Database 11g: SQL Fundamentals I
Suggested Prerequisites
Oracle Database 11g: Introduction to SQL (combination of Oracle Database 11g: SQL Fundamentals I and Oracle Database 11g:
Hedefler:
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Handle runtime errors
Describe stored procedures and functions
Use cursors to process rows
Design PL/SQL anonymous block that execute efficiently.
Describe the features and syntax of PL/SQL
Eğitim İçeriği:
Introduction
Course Objectives
Course Agenda
Describing the Human Resources (HR) Schema
PL/SQL development environments Available in this course
Introduction to SQL Developer

Introduction to PL/SQL
PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block

Declaring PL/SQL Identifiers
Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions

Writing Executable Statements
Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL

Interacting with the Oracle Server
Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions
Writing Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records
Using Explicit Cursors
Understanding Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching data from the Cursor
Closing the Cursor
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Creating Stored Procedures and Functions
Understanding Stored Procedures and Functions
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function
Oracle Database 11g: Program with PL/SQL
Eğitim Süresi:
30 Saat
Neler Öğreneceksiniz:
This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming
language. Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and
data management applications. Students learn to create anonymous PL/SQL blocks as well as stored procedures and
functions. Students learn to develop, execute, and manage PL\SQL stored program units such as procedures, functions,
packages, and database triggers. Students also learn to manage PL/SQL subprograms, triggers, declaring identifiers
and trapping exceptions. Students are introduced to the utilization of some of the Oracle-supplied packages.
This course is a combination of Oracle Database 11g: PL/SQL Fundamentals and Oracle Database 11g: Develop
PL/SQL Program Units courses.
Katılımcı Profili:
Application Developers
Database Administrators
Developer
Forms Developer
PL/SQL Developer
Portal Developer
System Analysts
Technical Consultant
Ön Şartlar:
Suggested Prerequisites
Oracle Database 11g: Introduction to SQL (combination of Oracle Database 11g: SQL Fundamentals I and Oracle Database 11g: Previous programming experience
Hedefler:
Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
Create and use stored procedures and functions
Design and use PL/SQL packages to group and contain related constructs
Create overloaded package subprograms for more flexibility
Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
Write dynamic SQL for more coding flexibility
Describe the features and syntax of PL/SQL
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Manage dependencies between PL/SQL subprograms
Handle runtime errors
Describe stored procedures and functions
Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
Create triggers to solve business challenges
Design PL/SQL anonymous block that execute efficiently
Eğitim İçeriği:
Introduction
Course Objectives
Course Agenda
Describing the Human Resources (HR) Schema
PL/SQL development environments Available in this course
Introduction to SQL Developer

Introduction to PL/SQL
PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block

Declaring PL/SQL Identifiers
Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions
Writing Executable Statements
Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL
Interacting with the Oracle Server
Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions
Writing Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records
Using Explicit Cursors
Understanding Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching data from the Cursor
Closing the Cursor
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Creating Stored Procedures
Creating a Modularize and Layered Subprogram Design
Modularizing Development With PL/SQL Blocks
Understanding the PL/SQL Execution Environment
The Benefits of Using PL/SQL Subprograms
The Differences Between Anonymous Blocks and Subprograms
Creating, Calling, and Removing Stored Procedures Using the CREATE Command and SQL Developer
Using Procedures Parameters and Parameters Modes
Viewing Procedures Information Using the Data Dictionary Views and SQL Developer
Creating Stored Functions
Creating, Calling, and Removing a Stored Function Using the CREATE Command and SQL Developer
Identifying the Advantages of Using Stored Functions in SQL Statements
Identify the steps to create a stored function
Using User-Defined Functions in SQL Statements
Restrictions When Calling Functions from SQL statements
Controlling Side Effects When Calling Functions from SQL Expressions
Viewing Functions Information
Creating Packages
Listing the Advantages of Packages
Describing Packages
The Components of a Package
Developing a Package
The Visibility of a Package’s Components
Creating the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
Invoking the Package Constructs
Viewing the PL/SQL Source Code Using the Data Dictionary
Working With Packages
Overloading Subprograms in PL/SQL
Using the STANDARD Package
Using Forward Declarations to Solve Illegal Procedure Reference
Using Package Functions in SQL and Restrictions
Persistent State of Packages
Persistent State of a Package Cursor
Controlling Side Effects of PL/SQL Subprograms
Using PL/SQL Tables of Records in Packages
Using Oracle-Supplied Packages in Application Development
Using Oracle-Supplied Packages
Examples of Some of the Oracle-Supplied Packages
How Does the DBMS_OUTPUT Package Work?
Using the UTL_FILE Package to Interact With Operating System Files
Using the UTL_MAIL Package
Using the UTL_MAIL Subprograms
Using Dynamic SQL
The Execution Flow of SQL
What is Dynamic SQL?
Declaring Cursor Variables
Dynamically Executing a PL/SQL Block
Using Native Dynamic SQL to Compile PL/SQL Code
Using DBMS_SQL Package
Using DBMS_SQL with a Parameterized DML Statement
Dynamic SQL Functional Completeness
Design Considerations for PL/SQL Code
Standardizing Constants and Exceptions
Using Local Subprograms
Using Autonomous Transactions
Using the NOCOPY Compiler Hint
Using the PARALLEL_ENABLE Hint
Using the Cross-Session PL/SQL Function Result Cache
Using the DETERMINISTIC Clause with Functions
Using Bulk Binding to Improve Performance
Creating Triggers
Working With Triggers
Identifying the Trigger Event Types and Body
Business Application Scenarios for Implementing Triggers
Creating DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
Identifying the Trigger Event Types, Body, and Firing (Timing)
Statement Level Triggers Versus Row Level Triggers
Creating Instead of and Disabled Triggers
Managing, Testing, and Removing Triggers
Creating Compound, DDL, and Event Database Triggers
Working With Compound Triggers
Identifying the Timing-Point Sections of a Table Compound Trigger
Compound Trigger Structure for Tables and Views
Using a Compound Trigger to Resolve the Mutating Table Error
Comparing Database Triggers to Stored Procedures
Creating Triggers on DDL Statements
Creating Database-Event and System-Events Triggers
System Privileges Required to Manage Triggers
Using the PL/SQL Compiler
Using the PL/SQL Compiler
Using the Initialization Parameters for PL/SQL Compilation
Using the New PL/SQL Compile Time Warnings
Overview of PL/SQL Compile Time Warnings for Subprograms
The Benefits of Compiler Warnings
The PL/SQL Compile Time Warning Messages Categories
Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Viewing the Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
Managing PL/SQL Code
What Is Conditional Compilation and How Does it Work?
Using Selection Directives
Using Predefined and User-Defined Inquiry Directives
The PLSQL_CCFLAGS Parameter and the Inquiry Directive
Using Conditional Compilation Error Directives to Raise User-Defined Errors
Using the DBMS_DB_VERSION Package
Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text°
Obfuscating and Wrapping PL/SQL Code
Managing Dependencies
Overview of Schema Object Dependencies
Querying Direct Object Dependencies Using the USER_DEPENDENCIES View
Querying an Object’s Status
Invalidation of Dependent Objects
Displaying Direct and Indirect Dependencies
Fine-Grained Dependency Management in Oracle Database 11g
Understanding Remote Dependencies
Recompiling a PL/SQL Program Unit
Oracle Database 11g: SQL and PL/SQL New Features - LVC
Eğitim Süresi:
12 Saat
Neler Öğreneceksiniz:
In this course students learn new features of SQL and PL/SQL in Oracle Database 11g. This course introduces you to
the SQL Developer tool, using which you learn various enhancements in language functionality and triggers.
Katılımcı Profili:
Application Developers
Data Warehouse Administrator
Developer
PL/SQL Developer
System Analysts
Ön Şartlar:
Required Prerequisites
Experience and knowledge of SQL and PL/SQL from prior Oracle releases
Hands on experience using PL/SQL
Suggested Prerequisites
Oracle Database 10g: Advanced PL/SQL
Oracle Database 10g: Introduction to SQL
Oracle Database 10g: Program with PL/SQL
Hedefler:
Explore the PIVOT and UNPIVOT Data warehousing operators
Use the SQL Developer interface
Write SQL statements that include the new functions added to enhance regular expression support functionality
Use the enhancements added to native dynamic SQL and to DBMS_SQL which enable more interoperability between the two methodologies
Use element level dependency tracking
Write compound triggers and use the enhancements made to triggers
Practice the performance improvements
Use Secure File LOBS
Write SQL and PL/SQL calls to sequences that are simpler
Use the new CONTINUE statement to control loops
Eğitim İçeriği:
Implementing the Language Functionality Enhancements
Use the new regular expression support functions to find sub patterns and count the occurrences of patterns
Track dependencies at the element level
Find and fix exception handlers that do not pass the exception upward to the calling program or environment
Dispatch an over ride-able object type method using the ANSI SQL 2003 standard for super-types
Use the WAIT option for DDL statements
Use the LOCK TABLE new syntax that enables you to specify the maximum number of seconds the statement should wait to obtain
 
Executing Dynamic SQL in PL/SQL with the 11g Enhancements
Write PL/SQL code that uses dynamic SQL and allows for SQL statements larger than 32kb
Use the DBMS_SQL.PARSE() function that is overloaded for CLOBs
Convert a REF CURSOR to a DBMS_SQL cursor and vice versa to support interoperability
Program using the enhancements to DBMS_SQL that include supporting the full range of data types (including collections and object Create user-defined collection types and bulk bind them using DBMS_SQL
Utilizing the Performance Improvement Enhancements
List the compiler changes and how the changes impact native compilation
Use the new SIMPLE_INTEGER data type
Describe the process of in-lining
Use flashback to store and track all transactional changes to a record
Practicing the Language Usability Enhancements
Implement the sequence calls to NEXTVAL and CURRVAL without using a SQL statement to retrieve the values
Use the new CONTINUE statement to control the next loop iteration or to leave a loop
Use both named and mixed notation calls to functions from a SQL statement
Use the ALTER TABLE statement to change tables to read-only status
Coding the Trigger Enhancements
Create compound triggers
Create disabled triggers
Use the ENABLE clause with a trigger
Control trigger order with the FOLLOWS and PRECEDES clauses
Administering Secure-File LOBs
Describe Secure-File LOB features
Migrate Basic-File LOBs to the Secure-File LOB format
Analyze the performance of LOBs
Enable Secure-File LOB de-duplication, compression, and encryption
Using the Data Warehousing Usability Enhancements
Identify the benefits of pivoting and un-pivoting operations
Write cross-tab queries to pivot (rotate) column values into new columns and to un-pivot (rotate) columns into column values
Pivot and un-pivot with multiple columns and multiple aggregates
Use wildcards and aliases with pivoting operations
 
Using the SQL Developer Enhancements
Navigate through the object navigator and view the enhancements
Compile and debug PL/SQL
Browse through the available search engines
Change preferences
Create reports
Oracle Database 11g: SQL Fundamentals I - LVC
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
This course introduces students to the fundamentals of SQL using Oracle Database 11g database technology. In this
course students learn the concepts of relational databases and the powerful SQL programming language. This course
provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate
data in tables, and create database objects.
Katılımcı Profili:
Application Developers
End Users
Forms Developer
Functional Implementer
PL/SQL Developer
Portal Developer
Reports Developer
Technical Consultant
Ön Şartlar:
Required Prerequisites
Familiarity with data processing concepts and techniques
Hedefler:
Retrieve row and column data from tables with the SELECT statement
Create reports of sorted and restricted data
Employ SQL functions to generate and retrieve customized data
Display data from multiple tables using the ANSI SQL 99 JOIN syntax
Create reports of aggregated data
Use the SET operators to create subsets of data
Run data manipulation statements (DML) to update data in the Oracle Database 11g
Run data definition language (DDL) statements to create and manage schema objects
Identify the major structural components of the Oracle Database 11g
Eğitim İçeriği:
Introduction
Listing the features of Oracle Database 11g
Discussing the basic design, theoretical and physical aspects of a relational database
Describing the development environments for SQL
Describing Oracle SQL Developer
Describing the data set used by the course
Retrieving Data Using the SQL SELECT Statement
Listing the capabilities of SQL SELECT statements.
Generating a report of data from the output of a basic SELECT statement
Using arithmetic expressions and NULL values in the SELECT statement
Using Column aliases
Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Displaying the table structure using the DESCRIBE command
Restricting and Sorting Data
Writing queries with a WHERE clause to limit the output retrieved
Using the comparison operators and logical operators
Describing the rules of precedence for comparison and logical operators
Using character string literals in the WHERE clause
Writing queries with an ORDER BY clause to sort the output
Sorting output in descending and ascending order
Using the Substitution Variables
Using Single-Row Functions to Customize Output
Differentiating between single row and multiple row functions
Manipulating strings using character functions
Manipulating numbers with the ROUND, TRUNC and MOD functions
Performing arithmetic with date data
Manipulating dates with the date functions
Using Conversion Functions and Conditional Expressions
Describing implicit and explicit data type conversion
Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nesting multiple functions
Applying the NVL, NULLIF, and COALESCE functions to data
Using conditional IF THEN ELSE logic in a SELECT statement
 
Reporting Aggregated Data Using the Group Functions
Using the aggregation functions in SELECT statements to produce meaningful reports
Using AVG, SUM, MIN, and MAX function
Handling Null Values in a group function
Creating queries that divide the data in groups by using the GROUP BY clause
Creating queries that exclude groups of date by using the HAVING clause
Displaying Data From Multiple Tables
Writing SELECT statements to access data from more than one table
Joining Tables Using SQL:1999 Syntax
Viewing data that does not meet a join condition by using outer joins
Joining a table by using a self join
Creating Cross Joins
Using Sub-queries to Solve Queries
Using a Subquery to Solve a Problem
Executing Single-Row Sub-queries
Using Group Functions in a Sub-query
Using Multiple-Row Subqueries
Using the ANY and ALL Operator in Multiple-Row Sub-queries
Using the SET Operators
Describing the SET operators
Using a SET operator to combine multiple queries into a single query
Using UNION, UNION ALL, INTERSECT, and MINUS Operator
Using the ORDER BY Clause in Set Operations
Manipulating Data
Adding New Rows to a Table Using the INSERT statement
Changing Data in a Table Using the UPDATE Statement
Using DELETE and TRUNCATE Statements
Saving and discarding changes with the COMMIT and ROLLBACK statements
Implementing Read Consistency
Using the FOR UPDATE Clause
 
Using DDL Statements to Create and Manage Tables
Categorizing Database Objects
Creating Tables using the CREATE TABLE Statement
Describing the data types
Describing Constraints
Creating a table using a subquery
Altering and Dropping a table
Creating Other Schema Objects
Creating, modifying, and retrieving data from a view
Performing Data manipulation language (DML) operations on a view
Dropping a view
Creating, using, and modifying a sequence
Creating and dropping indexes
Creating and dropping synonyms
Oracle Database 11g: SQL Fundamentals II - LVC
Eğitim Süresi:
12 Saat
Neler Öğreneceksiniz:
This course is designed for application developers with basic SQL language skills. In this course, students learn to
control privileges at the object and system level. This course covers creating indexes and constraints, and altering
existing schema objects. Students also learn to create and query external tables and use the advanced features of SQL
to query and manipulate data within the database.
Katılımcı Profili:
Application Developers
Business Intelligence Developer
Database Administrators
Developer
Forms Developer
PL/SQL Developer
Support Engineer
System Analysts
Ön Şartlar:
Required Prerequisites
Basic Knowledge of SQL
Familiarity with Oracle SQL Developer and SQL*Plus
Hedefler:
Control database access to specific objects
Manage schema objects
Manage objects with data dictionary views
Manipulate large data sets in the Oracle database by using subqueries
Manage data in different time zones
Write multiple-column subqueries
Use scalar and correlated subqueries
Use the regular expression support in SQL
Add new users with different levels of access privileges
Eğitim İçeriği:
Introduction
The Human Resources (HR) Schema
Review of Using Oracle SQL Developer
Review of some basic concepts of SQL
Controlling User Access
System versus Object Privileges
Creating Users
Granting System Privileges
Creating and Granting Privileges to a Role
Changing Your Password
Granting Object Privileges
Passing On Your Privileges
Revoking Object Privileges
Managing Schema Objects
Adding, Modifying and Dropping a Column
Adding, Dropping and Deferring a Constraint
Enabling and Disabling a Constraint
Creating and Removing Indexes
Creating a Function-Based Index
Performing Flashback Operations
Creating an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
Querying External Tables
Managing Objects with Data Dictionary Views
The Data Dictionary
How to Use the Dictionary Views
USER_OBJECTS and ALL_OBJECTS Views
Table and Column Information
Querying the dictionary views for constraint information
Querying the dictionary views for view, sequence, index and synonym information
Adding a comment to a table
Querying the dictionary views for comment information
 
Manipulating Large Data Sets
Using Subqueries to Manipulate Data
Retrieving Data Using a Subquery as Source
Inserting Using a Subquery as a Target
Using the WITH CHECK OPTION Keyword on DML Statements
Types of Multitable INSERT Statements
Using Multitable INSERT Statements
Merging rows in a table
Tracking Changes in Data over a period of time
Managing Data in Different Time Zones
Time Zones
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
Comparing Date and Time in a Session’s Time Zone
DBTIMEZONE and SESSIONTIMEZONE
Difference between DATE and TIMESTAMP
INTERVAL Data Types
Using EXTRACT, TZ_OFFSET and FROM_TZ
Using TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieving Data Using Subqueries
Multiple-Column Subqueries
Pairwise and Nonpairwise Comparison
Using Scalar Subquery Expressions
Solving problems with Correlated Subqueries
Updating and Deleting Rows Using Correlated Subqueries
Using the EXISTS and NOT EXISTS operators
Using the WITH clause
Regular Expression Support
Using the Regular Expressions Functions and Conditions in SQL
Using Meta Characters with Regular Expressions
Performing a Basic Search using the REGEXP_LIKE function
Finding patterns using the REGEXP_INSTR function
Extracting Substrings using the REGEXP_SUBSTR function
Replacing Patterns Using the REGEXP_REPLACE function
Using Sub-Expressions with Regular Expression Support
Using the REGEXP_COUNT function
Oracle Database 11g: SQL Tuning Workshop
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
The SQL Tuning Workshop class covers investigative methods that reveal varying levels of detail about how the Oracle
database executes a SQL statement. Students learn the different ways in which data can be accessed, which ones are
most efficient under specific circumstances, and how to ensure that the best method is used. Partitioning topics are
covered, in addition to taking advantage of hints, bind variables, and different types of indexes.
Katılımcı Profili:
Application Developers
Data Warehouse Developer
Developer
Support Engineer
Technical Consultant
Ön Şartlar:
Required Prerequisites
Oracle Database 11g: Introduction to SQL
Oracle Database 11g: SQL Fundamentals I
Hedefler:
Identify problem SQL statements
Modify a SQL statement to perform at its best
Trace an application
Understand how the Query Optimizer makes decisions about how to access data
Interpret execution plans
Use optimizer hints effectively
Generate a load test
Eğitim İçeriği:
Exploring the Oracle Database Architecture
Oracle Database Server Architecture: Overview
Oracle Database Memory Structures: Overview
Background Process Roles
Automatic Shared Memory Management
Automated SQL Execution Memory Management
Automatic Memory Management
Database Storage Architecture
Logical and Physical Database Structures
Introduction to SQL Tuning
Reasons for Inefficient SQL Performance
Performance Monitoring Solutions
Monitoring and Tuning Tools: Overview
EM Performance Pages for Reactive Tuning
CPU and Wait Time Tuning Dimensions
Scalability with Application Design, Implementation, and Configuration
Common Mistakes on Customer Systems
Proactive Tuning Methodology
Introduction to the Optimizer
Structured Query Language
SQL Statement Representation, Implementation & Processing: Overview
SQL Statement Parsing: Overview
Why Do You Need an Optimizer?
Optimization During Hard Parse Operation
Cost-Based Optimizer
Controlling the Behavior of the Optimizer
Optimizer Features and Oracle Database Releases
Optimizer Operators
Row Source Operations
Main Structures and Access Paths
Full Table Scan
Indexes: Overview
Using Indexes: Considering Nullable Columns
Bitmap Indexes, Composite Indexes &Invisible Index
Guidelines for Managing Indexes
Clusters
Interpreting Execution Plans
Execution Plan
Links Between Important Dynamic Performance Views
Automatic Workload Repository (AWR)
Generating SQL Reports from AWR Data
SQL Monitoring: Overview
Reading More Complex Execution Plans
Reviewing the Execution Plan
Looking Beyond Execution Plans
Case Study: Star Transformation
The Star & Snowflake Schema Model
Execution Plan Without Star Transformation
Retrieving Fact Rows from One Dimension All Dimensions
Joining the Intermediate Result Set with Dimensions
Using Bitmap Join Indexes
Star Transformation
Bitmap Join Indexes
Optimizer Statistics
Optimizer Statistics & Types of Optimizer Statistics
Multicolumn Statistics: Overview
Expression Statistics: Overview
Gathering System Statistics
Statistic Preferences: Overview
Optimizer Dynamic Sampling: Overview
Locking Statistics
Using Bind Variables
Cursor Sharing and Different Literal Values
Cursor Sharing and Bind Variables
Bind Variables in SQL*Plus & Enterprise Manager
Cursor Sharing Enhancements
Adaptive Cursor Sharing: Overview
Interacting with Adaptive Cursor Sharing
Using Optimizer Hints
Optimizer Hints: Overview
Types of Hints
Specifying Hints
Rules for Hints
Hint Recommendations
Hint Categories
Optimization Goals and Approaches
Additional Hints
Application Tracing
End-to-End Application Tracing Challenge
Location for Diagnostic Traces
What Is a Service?
Use Services with Client Applications
Trace Your Own Session
SQL Trace File Contents
Formatting SQL Trace Files: Overview
Invoking the tkprof Utility
Automating SQL Tuning
Tuning SQL Statements Automatically
Application Tuning Challenges
SQL Tuning Advisor: Overview
Stale or Missing Object Statistics
SQL Statement Profiling
Plan Tuning Flow and SQL Profile Creation
Database Control and SQL Tuning Advisor
 
Related Courses
Oracle Database 11g: SQL Tuning Workshop - Self-Study CD Course
Oracle Database 11g: SQL Tuning Workshop - LVC
Eğitim Süresi:
18 Saat
Neler Öğreneceksiniz:
The SQL Tuning Workshop class covers investigative methods that reveal varying levels of detail about how the Oracle
database executes a SQL statement. Students learn the different ways in which data can be accessed, which ones are
most efficient under specific circumstances, and how to ensure that the best method is used. Partitioning topics are
covered, in addition to taking advantage of hints, bind variables, and different types of indexes.
Katılımcı Profili:
Application Developers
Data Warehouse Developer
Developer
Support Engineer
Technical Consultant
Ön Şartlar:
Suggested Prerequisites
Oracle Database 11g: SQL Fundamentals I
Oracle Database 11g: Introduction to SQL
Hedefler:
Identify problem SQL statements
Modify a SQL statement to perform at its best
Trace an application
Understand how the Query Optimizer makes decisions about how to access data
Interpret execution plans
Use optimizer hints effectively
Generate a load test
Eğitim İçeriği:
Exploring the Oracle Database Architecture
Oracle Database Server Architecture: Overview
Oracle Database Memory Structures: Overview
Background Process Roles
Automatic Shared Memory Management
Automated SQL Execution Memory Management
Automatic Memory Management
Database Storage Architecture
Logical and Physical Database Structures
Introduction to SQL Tuning
Reasons for Inefficient SQL Performance
Performance Monitoring Solutions
Monitoring and Tuning Tools: Overview
EM Performance Pages for Reactive Tuning
CPU and Wait Time Tuning Dimensions
Scalability with Application Design, Implementation, and Configuration
Common Mistakes on Customer Systems
Proactive Tuning Methodology
Introduction to the Optimizer
Structured Query Language
SQL Statement Representation, Implementation & Processing: Overview
SQL Statement Parsing: Overview
Why Do You Need an Optimizer?
Optimization During Hard Parse Operation
Cost-Based Optimizer
Controlling the Behavior of the Optimizer
Optimizer Features and Oracle Database Releases
Optimizer Operators
Row Source Operations
Main Structures and Access Paths
Full Table Scan
Indexes: Overview
Using Indexes: Considering Nullable Columns
Bitmap Indexes, Composite Indexes &Invisible Index
Guidelines for Managing Indexes
Clusters
Interpreting Execution Plans
Execution Plan
Links Between Important Dynamic Performance Views
Automatic Workload Repository (AWR)
Generating SQL Reports from AWR Data
SQL Monitoring: Overview
Reading More Complex Execution Plans
Reviewing the Execution Plan
Looking Beyond Execution Plans
Case Study: Star Transformation
The Star & Snowflake Schema Model
Execution Plan Without Star Transformation
Retrieving Fact Rows from One Dimension All Dimensions
Joining the Intermediate Result Set with Dimensions
Using Bitmap Join Indexes
Star Transformation
Bitmap Join Indexes
Optimizer Statistics
Optimizer Statistics & Types of Optimizer Statistics
Multicolumn Statistics: Overview
Expression Statistics: Overview
Gathering System Statistics
Statistic Preferences: Overview
Optimizer Dynamic Sampling: Overview
Locking Statistics
Using Bind Variables
Cursor Sharing and Different Literal Values
Cursor Sharing and Bind Variables
Bind Variables in SQL*Plus & Enterprise Manager
Cursor Sharing Enhancements
Adaptive Cursor Sharing: Overview
Interacting with Adaptive Cursor Sharing
Reviewing the Execution Plan
Looking Beyond Execution Plans
Case Study: Star Transformation
The Star & Snowflake Schema Model
Execution Plan Without Star Transformation
Retrieving Fact Rows from One Dimension All Dimensions
Joining the Intermediate Result Set with Dimensions
Using Bitmap Join Indexes
Star Transformation
Bitmap Join Indexes
Optimizer Statistics
Optimizer Statistics & Types of Optimizer Statistics
Multicolumn Statistics: Overview
Expression Statistics: Overview
Gathering System Statistics
Statistic Preferences: Overview
Optimizer Dynamic Sampling: Overview
Locking Statistics
Using Bind Variables
Cursor Sharing and Different Literal Values
Cursor Sharing and Bind Variables
Bind Variables in SQL*Plus & Enterprise Manager
Cursor Sharing Enhancements
Adaptive Cursor Sharing: Overview
Interacting with Adaptive Cursor Sharing
Using Optimizer Hints
Optimizer Hints: Overview
Types of Hints
Specifying Hints
Rules for Hints
Hint Recommendations
Hint Categories
Optimization Goals and Approaches
Additional Hints
Application Tracing
End-to-End Application Tracing Challenge
Location for Diagnostic Traces
What Is a Service?
Use Services with Client Applications
Trace Your Own Session
SQL Trace File Contents
Formatting SQL Trace Files: Overview
Invoking the tkprof Utility
Automating SQL Tuning
Tuning SQL Statements Automatically
Application Tuning ChallengesOptimizer Hints: Overview
SQL Tuning Advisor: Overview
Stale or Missing Object Statistics
SQL Statement Profiling
Plan Tuning Flow and SQL Profile Creation
Database Control and SQL Tuning Advisor
Implementing Recommendations
Related Courses
Oracle Database 11g: SQL Tuning Workshop - Self-Study CD Course
Oracle Database: Introduction to SQL - LVC NEW
Eğitim Süresi:
30 Saat
Neler Öğreneceksiniz:
Understanding the basic concepts of relational databases ensure refined code by developers. This course helps the
participants to write subqueries, combine multiple queries into a single query using SET operators and report
aggregated data using group functions. Controlling privileges at the object and system level are also dealt with in detail.
Katılımcı Profili:
Application Developers
Business Analysts
Data Warehouse Administrator
Developer
Forms Developer
PL/SQL Developer
System Analysts
Ön Şartlar:
Required Prerequisites
Data processing
Familiarity with data processing concepts and techniques
Hedefler:
Display data from multiple tables using the ANSI SQL 99 JOIN syntax.
Identify the major structural components of the Oracle Database 11g.
Create reports of aggregated data.
Write SELECT statements that include queries.
Retrieve row and column data from tables.
Run data manipulation statements (DML) in Oracle Database 11g.
Create tables to store data.
Utilize views to display data.
Control database access to specific objects.
Manage schema objects.
Manage objects with data dictionary views.
Write multiple-column sub-queries.
Employ SQL functions to retrieve customized data.
Use scalar and correlated sub-queries.
Use the regular expression support in SQL
Create reports of sorted and restricted data
Eğitim İçeriği:
Introduction to Oracle Database
List the features of Oracle Database 11g
Discuss the basic design, theoretical and physical aspects of a relational database
Categorize the different types of SQL statements
Describe the data set used by the course
Log onto the database using the SQL Developer environment
Save queries to files and use script files in SQL Developer
Retrieve Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements
Generate a report of data from the output of a basic SELECT statement
Select All Columns
Select Specific Columns
Use Column Heading Defaults
Use Arithmetic Operators
Understand Operator Precedence
Learn the DESCRIBE command to display the table structure
Learn to Restrict and Sort Data
Write queries that contain a WHERE clause to limit the output retrieved
List the comparison operators and logical operators that are used in a WHERE clause
Describe the rules of precedence for comparison and logical operators
Use character string literals in the WHERE clause
Write queries that contain an ORDER BY clause sort the output of a SELECT statement
Sort output in descending and ascending order
 
Usage of Single-Row Functions to Customize Output
Describe the differences between single row and multiple row functions
Manipulate strings with character function in the SELECT and WHERE clauses
Manipulate numbers with the ROUND, TRUNC and MOD functions
Perform arithmetic with date data
Manipulate dates with the date functions
Invoke Conversion Functions and Conditional Expressions
Describe implicit and explicit data type conversion
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nest multiple functions
Apply the NVL, NULLIF, and COALESCE functions to data
Use conditional IF THEN ELSE logic in a SELECT statement
Aggregate Data Using the Group Functions
Use the aggregation functions in SELECT statements to produce meaningful reports
Divide the data in groups by using the GROUP BY clause
Exclude groups of date by using the HAVING clause
Display Data From Multiple Tables Using Joins
Write SELECT statements to access data from more than one table
View data that generally does not meet a join condition by using outer joins
Join a table by using a self join
Use Sub-queries to Solve Queries
Describe the types of problem that sub-queries can solve
Define sub-queries
List the types of sub-queries
Write single-row and multiple-row sub-queries
The SET Operators
Describe the SET operators
Use a SET operator to combine multiple queries into a single query
Control the order of rows returned
Data Manipulation Statements
Describe each DML statement
Insert rows into a table
Change rows in a table by the UPDATE statement to
Delete rows from a table with the DELETE statement
Save and discard changes with the COMMIT and ROLLBACK statements
Explain read consistency
Use of DDL Statements to Create and Manage Tables
Categorize the main database objects
Review the table structure
List the data types available for columns
Create a simple table
Decipher how constraints can be created at table creation
Describe how schema objects work
 
Other Schema Objects
Create a simple and complex view
Retrieve data from views
Create, maintain, and use sequences
Create and maintain indexes
Create private and public synonyms
Control User Access
Differentiate system privileges from object privileges
Create Users
Grant System Privileges
Create and Grant Privileges to a Role
Change Your Password
Grant Object Privileges
How to pass on privileges?
Revoke Object Privileges
Management of Schema Objects
Add, Modify and Drop a Column
Add, Drop and Defer a Constraint
How to enable and disable a Constraint?
Create and Remove Indexes
Create a Function-Based Index
Perform Flashback Operations
Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
Query External Tables
Manage Objects with Data Dictionary Views
Explain the data dictionary
Use the Dictionary Views
USER_OBJECTS and ALL_OBJECTS Views
Table and Column Information
Query the dictionary views for constraint information
Query the dictionary views for view, sequence, index and synonym information
Add a comment to a table
Query the dictionary views for comment information
Manipulate Large Data Sets
Use Subqueries to Manipulate Data
Retrieve Data Using a Subquery as Source
Insert Using a Subquery as a Target
Usage of the WITH CHECK OPTION Keyword on DML Statements
List the types of Multitable INSERT Statements
Use Multitable INSERT Statements
Merge rows in a table
Track Changes in Data over a period of time
Data Management in Different Time Zones
Time Zones
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
Compare Date and Time in a Session’s Time Zone
DBTIMEZONE and SESSIONTIMEZONE
Difference between DATE and TIMESTAMP
INTERVAL Data Types
Use EXTRACT, TZ_OFFSET and FROM_TZ
Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
 
Retrieve Data Using Sub-queries
Multiple-Column Subqueries
Pairwise and Nonpairwise Comparison
Scalar Subquery Expressions
Solve problems with Correlated Subqueries
Update and Delete Rows Using Correlated Subqueries
The EXISTS and NOT EXISTS operators
Invoke the WITH clause
The Recursive WITH clause

Regular Expression Support
Use the Regular Expressions Functions and Conditions in SQL
Use Meta Characters with Regular Expressions
Perform a Basic Search using the REGEXP_LIKE function
Find patterns using the REGEXP_INSTR function
Extract Substrings using the REGEXP_SUBSTR function
Replace Patterns Using the REGEXP_REPLACE function
Usage of Sub-Expressions with Regular Expression Support
Implement the REGEXP_COUNT function
Copyright
Oracle Database: Program with PL/SQL - LVC NEW
Eğitim Süresi:
30 Saat
Neler Öğreneceksiniz:
This course starts with an introduction to PL/SQL and proceeds to list the benefits of this powerful programming
language. Participants are made aware of how to create PL/SQL blocks of application code that can be shared by
multiple forms, reports, and data management applications. In addition, creation of anonymous PL/SQL blocks as well
as stored procedures and functions are covered in this course.
Katılımcı Profili:
Application Developers
Database Administrators
Developer
Forms Developer
PL/SQL Developer
Portal Developer
System Analysts
Technical Consultant
Ön Şartlar:
Required Prerequisites
Oracle Database: Introduction to SQL (combination of Oracle Database: SQL Fundamentals I and Oracle Database: SQL Fundamental
 
Previous programming experience
Hedefler:
Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
Describe the features and syntax of PL/SQL
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Manage dependencies between PL/SQL subprograms
Handle runtime errors
Create triggers to solve business challenges
Design PL/SQL code for predefined data types, local subprograms, additional programs and standardized constants and exceptions
Design PL/SQL anonymous block that execute efficiently
Create and debug stored procedures and functions
Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
Design PL/SQL packages to group related constructs
Create overloaded package subprograms for more flexibility
Write dynamic SQL for more coding flexibility
Eğitim İçeriği:
Introduction
Course Objectives
Course Agenda
Describe the Human Resources (HR) Schema
PL/SQL development environments Available in this course
Introduction to SQL Developer
Introduction to PL/SQL
Overview of PL/SQL
Identify the benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Create a Simple Anonymous Block
How to generate Output from a PL/SQL Block?
Declare PL/SQL Identifiers
List the different Types of Identifiers in a PL/SQL subprogram
Usage of the Declarative Section to Define Identifiers
Use variables to store data
Identify Scalar Data Types
The %TYPE Attribute
What are Bind Variables?
Sequences in PL/SQL Expressions
Write Executable Statements
Describe Basic PL/SQL Block Syntax Guidelines
Learn to Comment the Code
Deployment of SQL Functions in PL/SQL
How to convert Data Types?
Describe Nested Blocks
Identify the Operators in PL/SQL
 
Interaction with the Oracle Server
Invoke SELECT Statements in PL/SQL
Retrieve Data in PL/SQL
SQL Cursor concept
Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
Data Manipulation in the Server Using PL/SQL
Understand the SQL Cursor concept
Use SQL Cursor Attributes to Obtain Feedback on DML
Save and Discard Transactions
Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Describe Simple Loop Statement
Describe While Loop Statement
Describe For Loop Statement
Use the Continue Statement
Composite Data Types
Use PL/SQL Records
The %ROWTYPE Attribute
Insert and Update with PL/SQL Records
INDEX BY Tables
Examine INDEX BY Table Methods
Use INDEX BY Table of Records
Explicit Cursors
What are Explicit Cursors?
Declare the Cursor
Open the Cursor
Fetch data from the Cursor
Close the Cursor
Cursor FOR loop
The %NOTFOUND and %ROWCOUNT Attributes
Describe FOR UPDATE Clause and WHERE CURRENT Clause
Exception Handling
Understand Exceptions
Handle Exceptions with PL/SQL
Trap Predefined Oracle Server Errors
Trap Non-Predefined Oracle Server Errors
Trap User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Stored Procedures
Create a Modularize and Layered Subprogram Design
Modularize Development With PL/SQL Blocks
Understand the PL/SQL Execution Environment
List the benefits of using PL/SQL Subprograms
List the differences Between Anonymous Blocks and Subprograms
Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
Implement Procedures Parameters and Parameters Modes
View Procedures Information
 
Stored Functions and Debugging Subprograms
Create, Call, and Remove a Stored Function
Identify the advantages of using Stored Functions in SQL Statements
Identify the steps to create a stored function
Invoke User-Defined Functions in SQL Statements
Restrictions When Calling Functions from SQL statements
Control Side Effects When Calling Functions from SQL Expressions
View Functions Information
How to debug Functions and Procedures?
Packages
Listing the advantages of Packages
Describe Packages
What are the components of a Package?
Develop a Package
How to enable visibility of a Package’s Components?
Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
Invoke the Package Constructs
View the PL/SQL Source Code Using the Data Dictionary
Deploying Packages
Overloading Subprograms in PL/SQL
Use the STANDARD Package
Use Forward Declarations to Solve Illegal Procedure Reference
Implement Package Functions in SQL and Restrictions
Persistent State of Packages
Persistent State of a Package Cursor
Control Side Effects of PL/SQL Subprograms
Invoke PL/SQL Tables of Records in Packages
Implement Oracle-Supplied Packages in Application Development
What are Oracle-Supplied Packages?
Examples of Some of the Oracle-Supplied Packages
How Does the DBMS_OUTPUT Package Work?
Use the UTL_FILE Package to Interact With Operating System Files
Invoke the UTL_MAIL Package
Write UTL_MAIL Subprograms
Dynamic SQL
The Execution Flow of SQL
What is Dynamic SQL?
Declare Cursor Variables
Dynamically Executing a PL/SQL Block
Configure Native Dynamic SQL to Compile PL/SQL Code
How to invoke DBMS_SQL Package?
Implement DBMS_SQL with a Parameterized DML Statement
Dynamic SQL Functional Completeness
 
Design Considerations for PL/SQL Code
Copyright
Standardize Constants and Exceptions
Understand Local Subprograms
Write Autonomous Transactions
Implement the NOCOPY Compiler Hint
Invoke the PARALLEL_ENABLE Hint
The Cross-Session PL/SQL Function Result Cache
The DETERMINISTIC Clause with Functions
Usage of Bulk Binding to Improve Performance
 
Triggers
Describe Triggers
Identify the Trigger Event Types and Body
Business Application Scenarios for Implementing Triggers
Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
Identify the Trigger Event Types, Body, and Firing (Timing)
Differences between Statement Level Triggers and Row Level Triggers
Create Instead of and Disabled Triggers
How to Manage, Test, and Remove Triggers?
Creating Compound, DDL, and Event Database Triggers
What are Compound Triggers?
Identify the Timing-Point Sections of a Table Compound Trigger
Understand the Compound Trigger Structure for Tables and Views
Implement a Compound Trigger to Resolve the Mutating Table Error
Comparison of Database Triggers to Stored Procedures
Create Triggers on DDL Statements
Create Database-Event and System-Events Triggers
System Privileges Required to Manage Triggers
PL/SQL Compiler
What is the PL/SQL Compiler?
Describe the Initialization Parameters for PL/SQL Compilation
List the new PL/SQL Compile Time Warnings
Overview of PL/SQL Compile Time Warnings for Subprograms
List the benefits of Compiler Warnings
List the PL/SQL Compile Time Warning Messages Categories
Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING View the Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
Manage PL/SQL Code
What Is Conditional Compilation?
Implement Selection Directives
Invoke Predefined and User-Defined Inquiry Directives
The PLSQL_CCFLAGS Parameter and the Inquiry Directive
Conditional Compilation Error Directives to Raise User-Defined Errors
The DBMS_DB_VERSION Package
Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
Obfuscation and Wrapping PL/SQL Code
Managing Dependencies
Overview of Schema Object Dependencies
Query Direct Object Dependencies Using the USER_DEPENDENCIES View
Query an Object’s Status
Invalidation of Dependent Objects
Display Direct and Indirect Dependencies
Fine-Grained Dependency Management in Oracle Database 11g
Understand Remote Dependencies
Recompile a PL/SQL Program Unit
Copyright
Oracle Database: SQL and PL/SQL Fundamentals - LVC NEW
Eğitim Süresi:
30 Saat
Neler Öğreneceksiniz:
This course is designed to deliver the fundamentals of SQL and PL/SQL along with the benefits of the programming
languages using Oracle Database technology. In this course participants learn the concepts of relational databases.
Additionally the participants are provided with the essential SQL skills that allow them to write queries against single and
multiple tables, manipulate data in tables, and create database objects. The usage of single row functions to customize
output, how to invoke conversion functions and conditional expressions are covered. The use of group functions to
report aggregated data is also included.
Katılımcı Profili:
Application Developers
Forms Developer
Functional Implementer
PL/SQL Developer
Portal Developer
Reports Developer
Technical Consultant
Ön Şartlar:
Required Prerequisites
Familiarity with data processing concepts and techniques
Familiarity with programming concepts
Hedefler:
Identify the major structural components of the Oracle Database 11g.
Retrieve row and column data from tables with the SELECT statement.
Create reports of sorted and restricted data.
Employ SQL functions to generate and retrieve customized data.
Display data from multiple tables using the ANSI SQL 99 JOIN syntax.
Create reports of aggregated data.
Run data definition language (DDL) statements to create and manage schema objects.
Run data manipulation statements (DML) to update data in the Oracle Database 11g.
Design PL/SQL anonymous block that execute efficiently.
Describe the features and syntax of PL/SQL.
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors).
Handle runtime errors.
Describe stored procedures and functions.
Use cursors to process rows.
Eğitim İçeriği:
Introduction
Overview of Oracle Database 11g and related products
Overview of relational database management concepts and terminologies
Introduction to SQL and its development environments
The HR schema and the tables used in this course
Oracle Database documentation and additional resources
Retrieve Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements.
Generate a report of data from the output of a basic SELECT statement
Use arithmetic expressions and NULL values in the SELECT statement
Invoke Column aliases
Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Display the table structure using the DESCRIBE command
Restricted and Sorted Data
Write queries with a WHERE clause to limit the output retrieved
Describe the comparison operators and logical operators
Describe the rules of precedence for comparison and logical operators
Usage of character string literals in the WHERE clause
Write queries with an ORDER BY clause
Sort output in descending and ascending order
Substitution Variables
Usage of Single-Row Functions to Customize Output
List the differences between single row and multiple row functions
Manipulate strings using character functions
Manipulate numbers with the ROUND, TRUNC and MOD functions
Perform arithmetic with date data
Manipulate dates with the date functions
Conversion Functions and Conditional Expressions
Describe implicit and explicit data type conversion
Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nesting multiple functions
Apply the NVL, NULLIF, and COALESCE functions to data
Usage of conditional IF THEN ELSE logic in a SELECT statement
 
Aggregated Data Using the Group Functions
Usage of the aggregation functions in SELECT statements to produce meaningful reports
Describe the AVG, SUM, MIN, and MAX function
How to handle Null Values in a group function?
Divide the data in groups by using the GROUP BY clause
Exclude groups of date by using the HAVING clause
Display Data From Multiple Tables
Write SELECT statements to access data from more than one table
Join Tables Using SQL:1999 Syntax
View data that does not meet a join condition by using outer joins
Join a table by using a self join.
Create Cross Joins
Usage of Subqueries to Solve Queries
Use a Subquery to Solve a Problem
Single-Row Subqueries
Group Functions in a Subquery
Multiple-Row Subqueries
Use the ANY and ALL Operator in Multiple-Row Subqueries
Use the EXISTS Operator
SET Operators
Describe the SET operators
Use a SET operator to combine multiple queries into a single query
Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
Use the ORDER BY Clause in Set Operations
Data Manipulation
Add New Rows to a Table
Change Data in a Table
Use DELETE and TRUNCATE Statements
How to save and discard changes with the COMMIT and ROLLBACK statements?
Implement Read Consistency
Describe the FOR UPDATE Clause
DDL Statements to Create and Manage Tables
Categorize Database Objects
Create Tables
Describe the data types
Understand Constraints
Create a table using a subquery
How to alter a table?
How to drop a table?
Other Schema Objects
Create, modify, and retrieve data from a view
Perform Data manipulation language (DML) operations on a view
How to drop a view?
Create, use, and modify a sequence
Create and drop indexes
Create and drop synonyms
 
Introduction to PL/SQL
PL/SQL Overview
List the benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Create a Simple Anonymous Block
Generate the Output from a PL/SQL Block
PL/SQL Identifiers
List the different Types of Identifiers in a PL/SQL subprogram
Usage of the Declarative Section to Define Identifiers
Use of variables to store data
Scalar Data Types
%TYPE Attribute
Bind Variables
Sequences in PL/SQL Expressions
Write Executable Statements
Basic PL/SQL Block Syntax Guidelines
How to comment code?
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL
Interaction with the Oracle Server
SELECT Statements in PL/SQL to Retrieve data
Data Manipulation in the Server Using PL/SQL
The SQL Cursor concept
Learn to use SQL Cursor Attributes to Obtain Feedback on DML
How to save and discard transactions?
Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
Usage of Composite Data Types
PL/SQL Records
The %ROWTYPE Attribute
Insert and Update with PL/SQL Records
Associative Arrays (INDEX BY Tables)
INDEX BY Table Methods
INDEX BY Table of Records
Explicit Cursors
Understand Explicit Cursors
Declare the Cursor
How to open the Cursor?
Fetching data from the Cursor
How to close the Cursor?
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
 
Exception Handling
What are exceptions?
Handle Exceptions with PL/SQL
Trap Predefined Oracle Server Errors
Trap Non-Predefined Oracle Server Errors
Trap User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions
What are Stored Procedures and Functions?
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function
Microsoft Gold Certied Partner Microsoft Gold Certied Partner Oracle Approved Education Center Prometric Ets Authorized Toefl'Ibt Center
Anasayfa Site Haritası İnsan Kaynakları İletişim English
Copyright © 2010 - 2011 İ-Akademi Tüm hakları saklıdır. İ-Akademi Proje Birimi