Question bank
Oracle PL/SQL Developer Certified Professional
44 Oracle PL/SQL Developer Certified Professional questions with the correct answer and a written explanation, organised across 7 exam domains.
- 44 questions
- 7 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Code Management
4 questions in this domain
- Question 1Single Choice
Which SQL construct is used in a PL/SQL block to put the result of a single-row query into local variables?
- ASELECT INTO statementCorrect answer
- BOPEN-FOR statement
- CEXECUTE IMMEDIATE statement
- DNULLIF function
Why this is the answer
A single-row query is embedded in PL/SQL with SELECT INTO. The INTO clause names the variables that receive the selected column values.
- Question 2Multiple Select
Which statements are true about static SQL embedded directly in a PL/SQL block?
- AIt is parsed at compile time.Correct answer
- BIt can execute DML statements.Correct answer
- CIts SQL text is determined at run time.
- DIt must always use EXECUTE IMMEDIATE.
Why this is the answer
Static SQL is parsed at compile time and supports DML statements directly. The SQL text is fixed at compile time and does not require dynamic SQL.
- Question 3Interactive
Review the supplied PL/SQL block. Which missing keyword should replace the blank so the selected values are assigned to the declared variables?
- AINTOCorrect answer
- BWHERE
- CSET
- DFROM
Why this is the answer
The INTO clause is required in a SELECT embedded in PL/SQL when values must be assigned to local variables.
- Question 4Single Choice
Which implicit cursor attribute indicates the number of rows changed by a SQL statement in PL/SQL?
- ASQL%ROWCOUNTCorrect answer
- BSQL%FOUND
- CSQL%ISOPEN
- DSQL%NOTFOUND
Why this is the answer
SQL%ROWCOUNT returns the number of rows processed by the most recent SQL statement. SQL%FOUND and SQL%NOTFOUND return Boolean values instead.
Dynamic SQL and Advanced Topics
7 questions in this domain
- Question 5Single Choice
Which PL/SQL feature permits SQL statements to be constructed and executed at the time the program is running?
- AStatic SQL
- BDynamic SQLCorrect answer
- CEmbedded SQL
- DCursor expression
Why this is the answer
Dynamic SQL lets a PL/SQL program build and process SQL statements at run time. Static SQL is compiled when the PL/SQL unit is compiled, so it cannot change at run time.
- Question 6Multiple Select
Which two statements are true about the EXECUTE IMMEDIATE statement in PL/SQL native dynamic SQL? (Choose two.)
- AIt is the main statement used for native dynamic SQL.Correct answer
- BIt can be used to execute DDL statements such as CREATE TABLE.Correct answer
- CIt is required for every static SELECT in PL/SQL.
- DIt displays the row results of a SELECT in the console.
Why this is the answer
EXECUTE IMMEDIATE is the native dynamic SQL statement in PL/SQL. It can execute DDL statements and supports bind variables through its USING clause.
- Question 7Interactive
Examine the code block. What type of element is used as the placeholder for the value supplied by the USING clause?
- ABind variable placeholderCorrect answer
- BCursor attribute
- CIndex value
- DSchema object name
Why this is the answer
A bind variable is written with a leading colon and acts as a placeholder. The USING clause supplies a value at runtime, and this technique also helps prevent SQL injection.
- Question 8Single Choice
Which Oracle-supplied PL/SQL package provides advanced dynamic SQL functionality beyond the basic EXECUTE IMMEDIATE statement?
- ADBMS_SQLCorrect answer
- BDBMS_SCHEDULER
- CDBMS_JOB
- DDBMS_PIPE
Why this is the answer
DBMS_SQL is the Oracle-supplied package for advanced dynamic SQL. It is used when dynamic SQL support beyond EXECUTE IMMEDIATE is needed, such as an unknown number of selected columns.
- Question 9Multiple Select
Which two kinds of SQL statements usually require dynamic SQL in PL/SQL because they cannot be written as static SQL? (Choose two.)
- ADDL statements such as CREATE INDEXCorrect answer
- BSession-control statements such as ALTER SESSIONCorrect answer
- CFixed SELECT statements whose table and column names are already known
- DSimple UPDATE statements with a known WHERE clause
Why this is the answer
DDL and session-control statements cannot be included in static SQL in PL/SQL. DML and well-formed SELECT statements can usually be static when all object names are known.
- Question 10Interactive
Look at the code block. What category of SQL statement is included inside the string passed to EXECUTE IMMEDIATE?
- ADDL (CREATE TABLE)Correct answer
- BDML (DELETE)
- CDCL (GRANT)
- DTCL (COMMIT)
Why this is the answer
The string passed to EXECUTE IMMEDIATE contains CREATE TABLE, which is a data definition language (DDL) statement. Native dynamic SQL can execute such statements from PL/SQL.
- Question 11Single Choice
What clause must be included when EXECUTE IMMEDIATE runs a dynamic SELECT that returns only one row?
- AAn INTO clauseCorrect answer
- BA RETURNING INTO clause
- CA BULK COLLECT INTO clause
- DAn OUTPUT clause
Why this is the answer
For a dynamic SELECT returning one row, EXECUTE IMMEDIATE requires an INTO clause. The USING clause supplies bind values; it does not receive the rows.
Exception Handling
4 questions in this domain
- Question 12Single Choice
Which predefined exception is raised when a SELECT INTO statement does not return any rows?
- ANO_DATA_FOUNDCorrect answer
- BTOO_MANY_ROWS
- CVALUE_ERROR
- DZERO_DIVIDE
Why this is the answer
A SELECT INTO that returns no rows raises NO_DATA_FOUND. TOO_MANY_ROWS occurs when the statement returns more than one row.
- Question 13Multiple Select
Which two of the following listed options are predefined PL/SQL exception names in Oracle Database?
- ANO_DATA_FOUNDCorrect answer
- BTOO_MANY_ROWSCorrect answer
- CCUSTOM_EXCEPTION
- DTABLE_NOT_FOUND
Why this is the answer
NO_DATA_FOUND and TOO_MANY_ROWS are predefined built-in exceptions. CUSTOM_EXCEPTION and TABLE_NOT_FOUND are not predefined in Oracle PL/SQL.
- Question 14Interactive
Assuming server output is enabled, what message is displayed when the code in the code block is run?
- ANo rowsCorrect answer
- BFound one
- CORA-01403: no data found
- DThe block produces no output
Why this is the answer
When SELECT INTO finds no matching row, it raises NO_DATA_FOUND, and the exception handler prints 'No rows'. The 'Found one' message is not executed.
- Question 15Single Choice
Which keyword starts the exception-handling section inside a PL/SQL block in Oracle?
- AEXCEPTIONCorrect answer
- BDECLARE
- CBEGIN
- DCATCH
Why this is the answer
The EXCEPTION keyword introduces the exception-handling part of a PL/SQL block. DECLARE and BEGIN open earlier sections, while CATCH is not an Oracle PL/SQL keyword.
PL/SQL Fundamentals
7 questions in this domain
- Question 16Single Choice
Which statement is correct about using a SQL SELECT query inside a PL/SQL block?
- AIt needs an INTO clause to assign values.Correct answer
- BIt returns rows directly to the screen.
- CIt is not allowed in a PL/SQL block.
- DIt must be executed with an open cursor.
Why this is the answer
A SELECT used directly in PL/SQL must include an INTO clause when its result is assigned to variables; static SQL does not return rows to the host environment.
- Question 17Multiple Select
Which two SQL statements can be embedded directly in a PL/SQL block as static SQL?
- AINSERTCorrect answer
- BCREATE TABLE
- CDELETECorrect answer
- DTRUNCATE TABLE
Why this is the answer
INSERT and DELETE are DML statements and can be written directly in PL/SQL as static SQL. CREATE TABLE and TRUNCATE TABLE are DDL and require native dynamic SQL.
- Question 18Interactive
In the displayed PL/SQL block, what is the final output?
- ANo such employeeCorrect answer
- BFound
- CA syntax error is reported
- DNothing is printed
Why this is the answer
The SELECT INTO returns no row because employee_id 0 is not found, so PL/SQL raises NO_DATA_FOUND and the handler prints the specified message.
- Question 19Single Choice
Which PL/SQL variable declaration is best for storing the result of a row count query?
- Av_count NUMBER;Correct answer
- Bv_count DATE;
- Cv_count BOOLEAN;
- Dv_count employees%ROWTYPE;
Why this is the answer
COUNT and other aggregate functions return numeric values. NUMBER is the best choice among the listed types for storing such a result in PL/SQL.
- Question 20Multiple Select
Which two attributes are valid for an implicit SQL cursor in PL/SQL?
- ASQL%FOUNDCorrect answer
- BSQL%LASTINSERTID
- CSQL%ERRORSTATE
- DSQL%ROWCOUNTCorrect answer
Why this is the answer
SQL%FOUND and SQL%ROWCOUNT are valid implicit cursor attributes in PL/SQL. SQL%LASTINSERTID and SQL%ERRORSTATE are not recognized Oracle PL/SQL attributes.
- Question 21Interactive
In the displayed PL/SQL block, what value is written when the searched condition matches no rows?
- A0Correct answer
- B1
- CNULL
- DNO_DATA_FOUND is raised
Why this is the answer
SQL%ROWCOUNT returns the number of rows affected by the most recent implicit SQL statement. When no row is affected by the DELETE, the value printed is 0.
- Question 22Single Choice
What is the effect of issuing COMMIT inside a PL/SQL block?
- AMakes the current session's pending changes permanent.Correct answer
- BRestores the database to the last savepoint.
- CCloses all open explicit cursors.
- DEnds the current PL/SQL block with an error.
Why this is the answer
COMMIT signals a successful transaction and permanently saves pending changes made by the current session. It also releases locks held on the modified rows.
SQL in PL/SQL
5 questions in this domain
- Question 23Single Choice
In PL/SQL, which class of SQL statements can be embedded directly in a procedural block without dynamic SQL?
- AData Definition Language (DDL) statements
- BData Control Language (DCL) statements
- CData Manipulation Language (DML) statementsCorrect answer
- DEvery SQL statement type
Why this is the answer
PL/SQL supplies native support for DML statements and SELECT queries. DDL and DCL are not executed directly; they must be run through dynamic SQL.
- Question 24Multiple Select
Which two of the following are SQL DML operations that can be used directly inside a PL/SQL block?
- ACREATE INDEX
- BDELETECorrect answer
- CINSERTCorrect answer
- DGRANT
Why this is the answer
DELETE and INSERT are DML operations that PL/SQL supports natively. CREATE INDEX is DDL, and GRANT is DCL; both require dynamic SQL if used in PL/SQL.
- Question 25Interactive
What value is assigned to the variable when this PL/SQL block is run?
- A20
- B24
- C16Correct answer
- D4
Why this is the answer
The SELECT INTO statement reads from DUAL and evaluates 20 - 4 once. The expression result, 16, is therefore placed into the declared variable.
- Question 26Single Choice
What clause must be included when a SELECT statement is used in PL/SQL to store returned values in variables?
- AWHERE
- BORDER BY
- CGROUP BY
- DINTOCorrect answer
Why this is the answer
A SELECT in PL/SQL that returns values into variables must include the INTO clause between the select list and the FROM clause.
- Question 27Multiple Select
Which two transaction-control SQL commands can be written directly in the executable section of a PL/SQL block?
- ACOMMITCorrect answer
- BTRUNCATE TABLE
- CROLLBACKCorrect answer
- DCREATE USER
Why this is the answer
COMMIT and ROLLBACK are transaction-control commands that PL/SQL can execute directly. TRUNCATE TABLE creates an immediate DDL action, and CREATE USER is an administrative DDL/DCL command; neither is static SQL in PL/SQL.
Subprograms and Packages
10 questions in this domain
- Question 28Single Choice
Which PL/SQL construct retrieves exactly one row from a database table into local variables?
- ASELECT INTOCorrect answer
- BFETCH cursor
- COPEN FOR cursor
- DEXECUTE IMMEDIATE
Why this is the answer
SELECT INTO is the native static SQL statement in PL/SQL for retrieving a single row into scalar variables or a record.
- Question 29Multiple Select
Which SQL statements can be written directly as static SQL inside a PL/SQL subprogram? (Choose all that apply.)
- AINSERTCorrect answer
- BUPDATECorrect answer
- CSELECT INTOCorrect answer
- DCREATE TABLE
Why this is the answer
Static SQL in PL/SQL supports DML and single-row SELECT INTO. DDL such as CREATE TABLE requires dynamic SQL.
- Question 30Interactive
Examine the code. If no employee has employee_id 9000, what is displayed?
- ANo rowCorrect answer
- BNULL
- CORA-01403
- DORA-01422
Why this is the answer
A SELECT INTO that returns no rows raises NO_DATA_FOUND; the exception handler catches it and prints 'No row'.
- Question 31Single Choice
After a successful single-row SELECT INTO in PL/SQL, which implicit cursor attribute evaluates to TRUE?
- ASQL%FOUNDCorrect answer
- BSQL%NOTFOUND
- CSQL%ROWCOUNT
- DSQL%ISOPEN
Why this is the answer
SQL%FOUND returns TRUE when the most recent implicit SQL statement found at least one row.
- Question 32Multiple Select
Which statements about SQL%ROWCOUNT are true? (Choose all that apply.)
- AReturns rows processed by most recent implicit SQLCorrect answer
- BValid after DML statements such as UPDATECorrect answer
- CRaises NO_DATA_FOUND when zero rows are processed
- DRolls back transaction when value is zero
Why this is the answer
SQL%ROWCOUNT is a numeric attribute updated after an implicit SQL statement. It does not raise exceptions or control transactions.
- Question 33Interactive
Examine this procedure. When update_salary(100, 100) is executed and employee 100 exists, what is printed?
- AUpdated rows: 1Correct answer
- BUpdated rows: 0
- CNot found
- DNo output
Why this is the answer
When one row qualifies, SQL%NOTFOUND is FALSE and SQL%ROWCOUNT is 1, so the ELSE branch prints 'Updated rows: 1'.
- Question 34Single Choice
Which exception is raised when a SELECT INTO statement returns multiple rows?
- ATOO_MANY_ROWSCorrect answer
- BNO_DATA_FOUND
- CDUP_VAL_ON_INDEX
- DVALUE_ERROR
Why this is the answer
TOO_MANY_ROWS is raised whenever a SELECT INTO returns more than one row.
- Question 35Multiple Select
Which statements about using SQL in PL/SQL are true? (Choose all that apply.)
- ADML statements can be written directlyCorrect answer
- BCOMMIT and ROLLBACK are valid in PL/SQLCorrect answer
- CA SELECT that returns values needs an INTO clauseCorrect answer
- DDDL is valid as static SQL inside PL/SQL
Why this is the answer
PL/SQL static SQL supports DML and transaction control. A query that returns values must include INTO, while DDL requires dynamic SQL.
- Question 36Interactive
Examine the block. If department 80 has no employees, what output is displayed?
- AEmployees: 0Correct answer
- BEmployees: NULL
- CNo data found
- DORA-01422
Why this is the answer
COUNT(*) always returns one row, even when no matching rows exist, so the block prints Employees: 0.
- Question 37Single Choice
Which PL/SQL construct is appropriate for retrieving and processing a multi-row query result one row at a time?
- AExplicit cursorCorrect answer
- BSELECT INTO
- CRecord type
- DException handler
Why this is the answer
Explicit cursors are used to fetch and process multi-row query results one row at a time.
Working with Data
7 questions in this domain
- Question 38Single Choice
Which SQL statement must a PL/SQL program use to retrieve a single row from a database table into local variables?
- ASELECT INTOCorrect answer
- BFETCH
- COPEN
- DEXECUTE IMMEDIATE
Why this is the answer
PL/SQL uses SELECT INTO to transfer a single row's column values into variables. OPEN and FETCH belong to explicit cursor processing, and EXECUTE IMMEDIATE is used for dynamic SQL.
- Question 39Multiple Select
Which attributes can be applied to the SQL implicit cursor to evaluate the outcome of a DML statement in PL/SQL?
- A%FOUNDCorrect answer
- B%NOTFOUNDCorrect answer
- C%ROWCOUNTCorrect answer
- D%TYPE
Why this is the answer
%FOUND, %NOTFOUND, and %ROWCOUNT are valid SQL cursor attributes used after DML in PL/SQL. %TYPE declares a variable with the same data type as a column.
- Question 40Interactive
What happens when the embedded query in the block returns more than one row?
- ATOO_MANY_ROWS is raisedCorrect answer
- BNO_DATA_FOUND is raised
- COnly the first generated row is assigned
- DAll matching rows are assigned to the variables
Why this is the answer
The SELECT INTO statement requires the query to return exactly one row. If multiple rows are returned, PL/SQL raises the predefined TOO_MANY_ROWS exception.
- Question 41Single Choice
What does SQL%ROWCOUNT return after an UPDATE statement has executed in a PL/SQL block?
- ANumber of rows updated by the statementCorrect answer
- BNumber of rows present in the updated table
- CTotal number of rows updated by all sessions
- DNumber of columns modified by the statement
Why this is the answer
SQL%ROWCOUNT contains the number of rows affected by the most recently executed DML statement, such as the number of rows updated by an UPDATE.
- Question 42Multiple Select
Which of the following data manipulation statements can be executed directly as static SQL in a PL/SQL block?
- AINSERTCorrect answer
- BUPDATECorrect answer
- CDELETECorrect answer
- DCREATE TABLE
Why this is the answer
INSERT, UPDATE, and DELETE are static DML statements supported directly in PL/SQL. CREATE TABLE is DDL and normally requires dynamic SQL.
- Question 43Interactive
The code processes many employee rows in a loop. What type of cursor is declared for that loop?
- AAn explicit cursor used in a FOR loopCorrect answer
- BAn implicit SELECT INTO cursor
- CA cursor variable declared without a query
- DA dynamic cursor created by EXECUTE IMMEDIATE
Why this is the answer
The block declares an explicit cursor with CURSOR emp_cursor IS. The cursor FOR loop automatically opens, fetches, and closes that explicit cursor.
- Question 44Single Choice
If a SELECT INTO statement finds no rows, which exception is raised in PL/SQL?
- ANO_DATA_FOUNDCorrect answer
- BTOO_MANY_ROWS
- CVALUE_ERROR
- DCOLLECTION_IS_NULL
Why this is the answer
When a SELECT INTO statement returns no rows, PL/SQL raises the predefined NO_DATA_FOUND exception.
Frequently asked questions
Everything about the Oracle PL/SQL Developer Certified Professional question bank and how to use it.