Format: Multiple Choice
Duration: 90 Minutes
Number of Questions: 65
Passing Score: 66%
Validation: Exam has been validated for product version Oracle Database 19c
Examkingdom Oracle 1Z0-149 Exam pdf,
Best Oracle 1Z0-149 Free downloads , Oracle 1Z0-149 Dumps at Certkingdom.com
Earn associated certifications
Passing this exam is required to earn these certifications. Select each certification title below to view full requirements.
Prepare to pass exam: 1Z0-149
Oracle Advanced PL/SQL Developer Certified Professionals demonstrate expertise in database-centric Internet applications for Oracle Database 10g, 11g and 12c.
Review exam topics
Declaring PL/SQL Variables
Recognize valid and invalid identifiers
List the uses of variables, declare and initialize variables, use bind variables
List and describe various data types using the %TYPE and %ROWTYPE attributes
Writing Executable Statements
Identify lexical units in a PL/SQL block
Use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions
Describe when implicit conversions take place and when explicit conversions have to be dealt with
Write nested blocks and qualify variables with labels
Write readable code with appropriate indentation
Writing SQL in PL/SQL
Create PL/SQL executable blocks using DML and transaction control statements
Make use of the INTO clause to hold the values returned by a SQL statement
Writing Control Structures
Identify the uses and types of control structures (IF, CASE statements and expressions)
Construct and identify loop statements
Use EXIT and CONTINUE statements inside loops
Working with Composite Data Types
Create user-defined PL/SQL records
Create a record with the %ROWTYPE attribute
Create an INDEX BY table and INDEX BY table of records
Describe the differences among records, collections, and collections of records
Initialize collections and records
Using Explicit Cursors
Distinguish between implicit and explicit cursors and use SQL cursor attributes
Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data
Declare and use cursors with parameters
Lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause
Handling Exceptions
Define PL/SQL exceptions
Recognize unhandled exceptions
Handle different types of exceptions (internally defined exceptions, predefined exceptions and user-defined exceptions)
Propagate exceptions
Using PL/SQL Subprograms
Differentiate between anonymous blocks and subprograms
Create a simple procedure and invoke it from an anonymous block
Identify benefits of subprograms
Creating Procedures and Using Parameters
Create a procedure with parameterrs
Use named notation
Work with procedures (create, invoke and remove procedures)
Handle exceptions in procedures and display a procedure’s information
Creating Functions
Differentiate between a procedure and a function
Describe the uses of functions
Work with functions (create, invoke and remove functions)
Creating Packages
Identify the benefits and the components of packages
Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
Overload package subprograms and use forward declarations
Working with Packages
Use package types and variables
Use packaged constants and functions in SQL
Use ACCESSIBLE BY to restrict access to package subprograms
Using Dynamic SQL
Describe the execution flow of SQL statements
Use Native Dynamic SQL (NDS)
Bind PL/SQL types in SQL statements
Design Considerations for PL/SQL Code
Create standard constants and exceptions
Write and call local subprograms
Control the run-time privileges of a subprogram
Perform autonomous transactions
Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause
Use bulk binding and the RETURNING clause with DML
Creating Compound, DDL, and Event Database Triggers
Create triggers on DDL statements
Create triggers on system events
Describe different types of triggers and their uses
Using the PL/SQL Compiler
Describe the PL/SQL compiler and features
Use the PL/SQL compiler initialization parameters
Use the PL/SQL compile time warnings
Managing PL/SQL Code
Describe and use conditional compilation
Code-based access control: granting roles to program units
Whitelist code access with the ACCESSIBLE BY clause
Mark code as deprecated
Managing Dependencies
Track and manage procedural dependencies
QUESTION 1
Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)
A. PACKAGES
B. VARIABLES
C. ANONYMOUS BLOCK
D. TRIGGER BODY
E. DATABASE LINKS
Answer: A, E
QUESTION 2
Which three are true about functions and procedures? (Choose three.)
A. The ACCESSIBLE BY clause can be used only for procedures.
B. In a function, every execution path must lead to a RETURN statement.
C. Both can have only constants as actual parameters for IN mode parameters.
D. Both can be invoked from within SQL statements.
E. In a procedure the RETURN statement cannot specify an expression.
F. In a function every RETURN statement must specify an expression.
Answer: B, E, F
QUESTION 3
Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.)
A. PL/SQL code can be compiled and executed based on different versions of the operating system.
B. PL/SQL code can be compiled and executed based on different versions of Oracle.
C. It is newer syntax that works the same way as ‘IF , ELSEIF , ELSE, and END IF’.
D. Conditional compilation is disabled by default.
E. The PL/SQL compiler can conditionally include selected parts of a program.
Answer: B, E
QUESTION 4
Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint, and the DETERMINISTIC clause? (Choose three.)
A. The PARALLEL_ENABLE clause can be used only in the CREATE FUNCTION statement.
B. The NOCOPY hint asks the compiler to pass the actual parameters by reference.
C. A deterministic function’s results always depend on the state of session variables.
D. The NOCOPY hint asks the compiler to pass the actual parameters by value.
E. A function is deterministic if it always returns the same result for a specific combination of input values.
F. The PARALLEL_ENABLE clause can be specified for a nested function.
G. A function defined with the PARALLEL_ENABLE clause may be executed in parallel in a SELECT statement or a subquery in a DML statement.
Answer: B, E, G
QUESTION 5
Which two are true about INDEX-BY tables? (Choose two.)
A. The index can be integer or string.
B. The index can be integer only.
C. INDEX-BY table types can be created in PL/SQL blocks only.
D. INDEX-BY table types can be created both with the CREATE TYPE statement and in PL/SQL blocks.
E. INDEX-BY table types can be created with the CREATE TYPE statement.
Answer: A, C
QUESTION 6
Which three are true about anonymous blocks and subprograms? (Choose three.)
A. Named subprograms cannot be called from other packages.
B. PROCEDURE subprograms can accept parameters.
C. A FUNCTION subprogram must return one or more values.
D. Anonymous blocks cannot use packaged variables.
E. Named subprograms are stored in the database server.
F. Anonymous blocks must always start with the Declare keyword.
G. FUNCTION subprograms must be called and passed through one or more parameters.
Answer: B, C, E
