Oracle Database 12c Advanced PLSQL

Here you have the best Oracle 1z0-148 practice exam questions

You have 74 total questions to study from
Each page has 5 questions, making a total of 15 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on November 21, 2024

Question 1 of 74

The STUDENTS table exists in your schema.

Examine the DECLARE section of a PL/SQL block:

Which two blocks are valid?

BEGIN OPEN cursor3 FOR SELECT * FROM students; cursor1 :=cursor3; END;

BEGIN OPEN stcur; cursor1 :=stcur; END;

BEGIN OPEN cursor1 FOR SELECT * FROM students; stcur :=cursor1; END;

BEGIN OPEN stcur; cursor3 :=stcur; END;

BEGIN OPEN cursor1 FOR SELECT * FROM students; cursor2 :=cursor1;

Correct Answer: A, E

Cursor variables and explicit cursors behave differently in PL/SQL. SYS_REFCURSOR and user-defined REF CURSOR types can be assigned to each other, but explicit cursors cannot be assigned to cursor variables. Therefore, blocks that attempt to mix these types without following this rule will fail. Option A is valid because SYS_REFCURSOR can be opened for a query and assigned to a user-defined REF CURSOR type. Similarly, option E is valid because a REF CURSOR can be opened and assigned to another REF CURSOR type. Options involving the explicit cursor 'stcur' (like D) are invalid because you cannot assign an explicit cursor to other cursor variables.

Question 2 of 74

Examine the code:

Which two subprograms will be created successfully?

CREATE FUNCTION p4 (y pkg.tab_typ) RETURN pkg.tab_typ IS BEGIN EXECUTE IMMEDIATE SELECT pdt_id, pdt_name FROM TABLE (:b) BULT COLLECT INTO pkg.x USING y; RETURN pkg.x; END p4;

CREATE PROCEDURE p1 (y IN OUT pkg.tab_typ) IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO y USING pkg.x; END p1;

CREATE PROCEDURE p2 (v IN OUT VARCHAR2) IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO v USING pkg.x; END p2;

CREATE FUNCTION p3 RETURN pkg. tab_typ IS BEGIN EXECUTE IMMEDIATE SELECT f (:b) FROM DUAL INTO pkg.x; END p3;

CREATE PROCEDURE p5 (y pkg. rec_typ) IS BEGIN EXECUTE IMMEDIATE SELECT pdt_name FROM TABLE (:b) BULK COLLECT INTO y USING pkg.x;

Correct Answer: A, C

The two subprograms that will be created successfully are A and C. In option A, the function utilizes an anonymous PL/SQL block with BULK COLLECT to fetch data and assigns it to pkg.x using the correct types consistent with package definition. In option C, the procedure employs EXECUTE IMMEDIATE to call function f and store the result into variable v, also maintaining data type consistency. Options B, D, and E do not meet the requirements due to either data type mismatches, missing clauses, or incorrect use of IN variables.

Question 3 of 74

Examine the section of code taken from a PL/SQL program:

PLSQL_OPTIMIZE_LEVEL PARAMETER is set to 3.

Which two statements are true?

Calls to TESTPROC will always be inlined as it is compiled with PLSQL_OPTIMIZE_LEVEL=3.

Calls to TESTPROC are never inlined in both lines commented as Call1 and Call 2.

Calls to TESTPROC are not inlined in the line commented as Call 1.

Calls to TESTPROC are inlined in both lines commented as Call 1 and Call 2.

Calls to TESTPROC might be inlined in the line commented as Call 2.

Correct Answer: B, C

In the provided code, the PRAGMA INLINE(TESTPROC, 'NO') directive explicitly instructs the compiler not to inline calls to the TESTPROC function, regardless of the PLSQL_OPTIMIZE_LEVEL setting being set to 3. This means that none of the calls to TESTPROC will be inlined, making option B correct as calls to TESTPROC are never inlined in both Call 1 and Call 2. Option C is also correct because, due to the PRAGMA INLINE directive, calls to TESTPROC are not inlined in the line commented as Call 1.

Question 4 of 74

Which statement is true about the DBMS_PARALLEL_EXECUTE package?

DBMS_PARALLEL_EXECUTE is a SYS-owned package and can be accessed only by a user with DBA privileges.

To execute chunks in parallel, users must have CREATE JOB system privilege.

No specific system privileges are required to create or run parallel execution tasks.

Only DBAs can create or run parallel execution tasks.

Users with CREATE TASK privilege can create or run parallel execution tasks.

Correct Answer: B

To execute chunks in parallel using the DBMS_PARALLEL_EXECUTE package, users must have the CREATE JOB system privilege. This ensures the user has the necessary permissions to create and manage jobs, which are required for executing tasks in parallel within the DBMS_PARALLEL_EXECUTE framework.

Question 5 of 74

Which two statements are true regarding edition-based redefinition (EBR)?

There is no default edition defined in the database.

EBR does not let you upgrade the database components of an application while in use.

You never use EBR to copy the database objects and redefine the copied objects in isolation.

Editions are non-schema objects.

When you change an editioned object, all of its dependents remain valid.

Tables are not editionable objects.

Correct Answer: D, F

Edition-based redefinition (EBR) allows for the upgrade of the database components of an application while it is in use, ensuring continuous availability. Editions are non-schema objects, meaning they do not have owners and exist in a single namespace where multiple editions can coexist. Tables, however, are not eligible for editioning and thus are not editionable objects. This enables modifications to be made to editioned objects without affecting the base tables directly. As a result, the true statements are that editions are non-schema objects and that tables are not editionable objects.