Oracle 1Z0-007 Certification Notes

Oracle 1Z0-147 Certification Notes

• Overview of PL/SQL Programs
  • Describe a PL/SQL program construct
  • List the components of a PL/SQL block
  • List the benefits of subprograms
  • Describe how a stored procedure/function is invoked
• Creating Procedures
  • Define what a stored procedure is
  • List the development steps for creating a procedure
  • Create a procedure
  • Describe the difference between formal and actual parameters
  • List the types of parameter modes
  • List the methods for calling a procedure with parameters
  • Describe the DEFAULT option for parameters
  • Create a procedure with parameters
  • Invoke a procedure that has parameters
  • Define a subprogram in the declarative section of a procedure
  • Describe how exceptions are propagated
  • Remove a procedure
• Creating Functions
  • Define what a stored function is
  • Create a function
  • List how a function can be invoked
  • List the advantages of user-defined functions in SQL statements
  • List where user-defined functions can be called from within an SQL statement
  • Describe the restrictions on calling functions from SQL statements
  • Remove a function
  • Describe the differences between procedures and functions
• Managing Subprograms
  • Contrast system privileges with object privileges
  • Grant privileges
  • Contrast invokers rights with definers rights
  • Identify views in the data dictionary to manage stored objects
• Creating Packages
  • Use DESCRIBE command to describe packages and list their possible components
  • Identify a package specification and body
  • Create packages: Create related variables , cursors, constants, exceptions, procedures, and functions
  • Designate a package construct as either public or private
  • Invoke a package construct
  • Use a bodiless package
  • Drop Packages
  • Identify benefits of Packages
• More Package Concepts
  • Write packages that use the overloading feature
  • Use Forward Referencing
  • Describe errors with mutually referential subprograms
  • Initialize variables with a one-time-only procedure
  • Identify persistent states in package variables and cursors
  • Identify restrictions on using Packaged functions in SQL statements
  • Invoke packaged functions from SQL
  • Use PL/SQL tables and records in Packages
• Oracle Supplied Packages
  • Describe the benefits of Execute Immediate over DBMS_SQL for Native Dynamic SQL
  • Identify the flow of execution
  • Use EXECUTE IMMEDIATE
  • Describe the use and application of some Oracle server-supplied packages: DBMS_SQL, DBMS_OUTPUT, UTL_FILE
• Manipulating Large Objects
  • Compare and contrast LONG and large object (LOB) data types
  • Describe LOB datatypes and how they are used
  • Differentiate between internal and external LOBs
  • Identify and Manage Bfiles
  • Migrate from LONG To LOB
  • Use the DBMS_LOB PL/SQL package
  • Create LOB columns and populate them
  • Perform SQL operations on LOBS: Update LOBs with SQL, Select from LOBS, Delete LOBS
  • Describe the use of temporary LOBs
• Creating Database Triggers
  • Describe the different types of triggers
  • Describe database triggers and their uses
  • List guidelines for designing triggers
  • Create a DML trigger
  • List the DML trigger components
  • Describe the trigger firing sequence options
  • Use conditional predicates in a DML trigger
  • Create a row level trigger
  • Create a statement level trigger
  • Use the OLD and NEW qualifiers in a database trigger
  • Create an INSTEAD OF trigger
  • Describe the difference between stored procedures and triggers
  • Describe the trigger execution model
  • Alter a trigger status
  • Remove a trigger
• More Trigger Concepts
  • Define what a database trigger is
  • Describe events that cause database triggers to fire
  • Create a trigger for a DDL statement
  • Create a trigger for a system event
  • Describe the functionality of the CALL statement
  • Describe the cause of a mutating table
  • List what triggers can be implemented for
  • List the privileges associated with triggers
  • View trigger information in the dictionary views
• Managing Dependencies
  • Track procedural dependencies
  • Describe dependent objects and referenced objects
  • View dependency information in the dictionary views
  • Describe how the UTLDTREE script is used
  • Describe how the IDEPTREE and DEPTREE procedures are used
  • Describe a remote dependency
  • List how remote dependencies are governed
  • Describe when a remote dependency is unsuccessfully recompiled
  • Describe when a remote dependency is successfully recompiled
  • List how to minimize dependency failures