Program with PL/SQL

Exam Number:	1Z0-147
Associated Certifications:	PL/SQL Developer OCA Advanced PL/SQL Developer OCP
Exam Price:	US$125
Exam Registration:	Register

Duration:

90 minutes

Number of Questions:

Passing Score:

77%

Passing score subject to change. View Details

Recommended Training and Preparation

Oracle9i: Program with PL/SQL

Oracle Database 10g: Program with PL/SQL

Oracle9i: Program with PL/SQL Self-Study CD Course (NETg Oracle)
Self-Study CD-ROMs are an excellent tool to help you prepare for your exam. SSCDs reinforce the course material and allow you to focus on sections that you need to review. Note: the SSCDs do not count towards your course requirements for certification and should be used as a study aid only.

Additional Information & Resources

This exam is for PL/SQL Developers on Oracle9i Database or Oracle Database 10g.
Practice Exams

Oracle authorized practice exam from Self Test Software. 1Z0-147 Program with PL/SQL
Oracle authorized practice exam from Transcender: Cert-1Z0-147 : DBCert: 9i Database: Program with PL/SQL

Oracle Application Developer Candidate Guide
Exam Retake Policy

Exam Topics

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

Oracle Certification Program Fraudulent Activity Policy

Oracle reserves the right to take action against any candidate involved in fraudulent activities, including, but not limited to, fraudulent use of vouchers, promotional codes, reselling exam discounts and vouchers, cheating on an exam, alteration of score reports, alteration of completion certificates, violation of exam retake policies or other activities deemed fraudulent by Oracle.

If Oracle determines, in its sole discretion, that fraudulent activity has taken place, it reserves the right to take action up to and including, but not limited to, decertification of a candidate's Oracle Certified Associate, Oracle Certified Professional and/or OCM credentials, temporary, indefinite or permanent ban of a candidate from Oracle certification programs, notification to a candidate's employer, and notification to law enforcement agencies. Candidates found committing fraudulent activities forfeit all fees previously paid to Oracle, or to Oracle's authorized vendors, and may be required to pay additional fees for services rendered.

View the Oracle Certification Program Candidate Agreement which requires your agreement before the start of each exam.