1Z0-149
Free 1Z0-149 practice questions
10 questions from our 1Z0-149 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Declaring Variables and Writing Executable Statements
Which statement correctly assigns the next value of the sequence ORD_SEQ to a variable in PL/SQL in Oracle Database 19c?
- Av_id := SEQUENCE.NEXTVAL('ord_seq');
- Bv_id := NEXTVAL(ord_seq);
- Cv_id := ord_seq.CURRVAL + 1;
- Dv_id := ord_seq.NEXTVAL; Correct answer
Answer and explanation: Since Oracle Database 11g, the NEXTVAL and CURRVAL pseudocolumns can be used directly in PL/SQL expressions, so the assignment is valid without SELECT ... FROM dual. NEXTVAL is not a function taking the sequence as an argument, CURRVAL + 1 does not advance the sequence and fails if NEXTVAL has not yet been called in the session, and there is no SEQUENCE package with a NEXTVAL function.
Question 2 of 10, Writing SQL in PL/SQL and Control Structures
A block inserts an order header and then 20 lines. If any line insert fails, the header must remain but all lines must be undone, and the block should continue. Which approach meets the requirement?
- AIssue COMMIT after each line insert so that failed lines do not affect earlier ones
- BWrap the header insert in an autonomous transaction and roll back the main transaction on error
- CRely on statement-level rollback, which undoes all line inserts automatically when one fails
- DSAVEPOINT before the lines, and ROLLBACK TO it in a handler around the line inserts Correct answer
Answer and explanation: A savepoint marks the point after the header; rolling back to it in an exception handler removes only the line inserts while the header stays pending and processing continues. Committing each line keeps partial lines, an autonomous header would be committed independently and complicate the transaction, and statement-level rollback undoes only the single failing statement.
Question 3 of 10, Working with Composite Data Types
A developer must load 2 million rows from a cursor and process them in chunks without running out of PGA memory. Which technique is appropriate?
- ADeclare the collection as a VARRAY(2000000) so its memory is preallocated
- BUse a cursor FOR loop, which always fetches the entire result set at once
- CFETCH the cursor with BULK COLLECT INTO a collection LIMIT 1000 in a loop Correct answer
- DSELECT ... BULK COLLECT INTO a collection once without any LIMIT clause
Answer and explanation: BULK COLLECT with LIMIT fetches a bounded number of rows per round trip, keeping memory usage predictable while still minimizing context switches. Collecting everything at once can exhaust PGA, a large VARRAY does not reduce memory use, and a cursor FOR loop does not fetch the entire result set at once (it array-fetches 100 rows at a time when optimized).
Question 4 of 10, Using Explicit Cursors
An explicit cursor is opened, and after OPEN another session commits an update that changes rows the cursor's query will return. What does the first session see as it fetches?
- AData as of the moment the cursor was opened, thanks to read consistency Correct answer
- BA mix of old and new values depending on which data blocks are still in the buffer cache
- CAn ORA-01555 error is raised as soon as the other session commits its change
- DThe newly committed values for rows that have not yet been fetched by the cursor
Answer and explanation: The result set of a cursor is determined at OPEN time; Oracle uses undo to give a read-consistent view as of that point, so later commits by other sessions are not seen. ORA-01555 can occur only if the required undo is overwritten, not simply because of a commit, and the buffer cache does not affect the consistent view.
Question 5 of 10, Handling Exceptions. Choose 2.
Which two statements about the WHEN OTHERS handler are correct? (Choose two.)
- AInside it, SQLCODE and SQLERRM can be used to identify the error that was raised Correct answer
- BIt also catches exceptions raised in the same block's declaration section
- CIt can be combined with a named exception using WHEN OTHERS OR NO_DATA_FOUND
- DIt must be the last handler in the exception section of a block Correct answer
- EIt automatically re-raises the error to the caller after its statements complete
Answer and explanation: WHEN OTHERS must appear last, and inside it SQLCODE and SQLERRM identify the actual error. Errors raised during declaration propagate to the enclosing block and are never seen by the same block's handlers. OTHERS cannot be combined with other names using OR, and once a handler finishes the exception is considered handled unless the code issues RAISE explicitly.
Question 6 of 10, Creating Procedures and Functions. Choose 2.
Which two requirements must a stored function meet to be called from a SQL statement such as SELECT f(salary) FROM employees? (Choose two.)
- AIt must be created with AUTHID CURRENT_USER
- BIt must be declared DETERMINISTIC
- CIts parameters must use IN mode only Correct answer
- DIt must be defined inside a package rather than as a standalone function
- EIts parameter and return types must be SQL-compatible, so a BOOLEAN parameter is not allowed in Oracle Database 19c Correct answer
Answer and explanation: Functions callable from SQL may only have IN parameters, and in Oracle Database 19c their parameter and return types must be SQL data types, so PL/SQL-only types such as BOOLEAN or records are rejected. DETERMINISTIC is an optimization hint, not a requirement. Standalone and packaged functions can both be called from SQL, and the rights model is irrelevant to callability.
Question 7 of 10, Creating and Working with Packages
In a package body, procedure calc_total calls the private procedure apply_discount, which is defined further down in the same body. Compilation fails with PLS-00313 (not declared in this scope). What is the correct fix without moving code?
- AAdd PRAGMA INLINE(apply_discount, 'YES') immediately before the call in calc_total
- BPrefix the call with the package name, writing pkg_name.apply_discount, inside calc_total
- CDeclare apply_discount in the package specification so that it becomes public
- DAdd a forward declaration of apply_discount before calc_total in the body Correct answer
Answer and explanation: PL/SQL requires identifiers to be declared before use; a forward declaration (the subprogram specification ending with a semicolon) placed earlier in the body solves this while keeping the procedure private. Moving it to the spec would also work but needlessly exposes it. Qualifying the name does not change declaration order, and PRAGMA INLINE controls optimization only.
Question 8 of 10, Using Dynamic SQL
A reporting tool must run arbitrary ad hoc queries whose number and types of select-list columns are unknown until run time and describe each column. Which approach is required?
- AEXECUTE IMMEDIATE with USING OUT bind arguments for every select-list column
- BOPEN a strong REF CURSOR FOR the query string and fetch into a %ROWTYPE record
- CThe DBMS_SQL package, using DESCRIBE_COLUMNS and DEFINE_COLUMN Correct answer
- DEXECUTE IMMEDIATE with BULK COLLECT INTO a record of VARCHAR2 fields
Answer and explanation: Native dynamic SQL needs the select list's shape at compile time; when the number or types of columns are unknown, DBMS_SQL (method 4) is needed because it can describe and define columns dynamically. A fixed record cannot adapt to unknown columns, a strong REF CURSOR cannot be opened for a dynamic string, and OUT binds do not return query columns.
Question 9 of 10, Design Considerations for PL/SQL Code
A function is called from a WHERE clause of a large query and always returns the same output for the same input values, with no side effects. Which clause tells the optimizer it may reuse earlier results within the query?
- ADETERMINISTIC Correct answer
- BPRAGMA AUTONOMOUS_TRANSACTION
- CRESULT_CACHE RELIES_ON (employees)
- DAUTHID DEFINER
Answer and explanation: DETERMINISTIC declares that the function's result depends only on its arguments, allowing the database to avoid redundant calls and to use it in function-based indexes. RESULT_CACHE is a cross-session cache (RELIES_ON is deprecated and ignored since 11.2), AUTHID controls privilege checking, and an autonomous transaction is unrelated to result reuse.
Question 10 of 10, Creating DML, Compound, DDL and Event Triggers
A DBA needs to capture every error raised in the database, such as ORA-01555, along with the user who hit it. Which trigger event is appropriate?
- AAFTER DDL ON DATABASE
- BBEFORE SHUTDOWN ON DATABASE
- CAFTER SERVERERROR ON DATABASE Correct answer
- DAFTER SUSPEND ON DATABASE
Answer and explanation: An AFTER SERVERERROR trigger fires when a server error message is logged, and attribute functions such as ora_server_error give the error numbers. AFTER SUSPEND fires for resumable space allocation suspensions, BEFORE SHUTDOWN fires when the instance is shut down, and AFTER DDL fires after successful DDL commands.