Unmasking the Best of PLSQL: Expert Level (2024)

Welcome to your Unmasking the Best of PLSQL: Expert Level

This quiz has multiple choice questions and each has only one correct answer.

Total Questions : 30

To pass this quiz you need to score 80% or above.

Time Provided : 30 minutes

Today's Date : 9 November 2024

All the best!!

Name

What is the purpose of the AUTONOMOUS_TRANSACTION pragma in PL/SQL?

Deselect Answer

To rollback a transaction within a PL/SQL block

To start a new transaction within a PL/SQL block

To commit a transaction within a PL/SQL block

To create an independent transaction within a PL/SQL block

It sets the isolation level of the transaction to serializable

None

Which of the following statements is used to handle any exceptions in PL/SQL?

Deselect Answer

RAISE_ERROR

EXCEPTION ALL

EXCEPTION WHEN OTHERS

RAISE_APPLICATION_ERROR

EXCEPTION ANY

None

What is the purpose of the SQL%BULK_ROWCOUNT attribute in PL/SQL?

Deselect Answer

It returns the number of elements in a collection

It returns the number of rows affected by a DML statement for each iteration of a FORALL statement

It returns the number of rows processed by a SQL statement

It returns the number of open cursors in the current session

None

What is the purpose of the BULK COLLECT clause in PL/SQL?

Deselect Answer

It fetches multiple rows from a collection into a cursor at once

It fetches multiple rows from a cursor into a collection at once

It fetches multiple columns from a cursor into a collection at once

It fetches a single row from a cursor into a collection

None

Which PL/SQL construct is used to handle collections that are not known at compile time?

Deselect Answer

RECORD

ASSOCIATIVE ARRAY

VARRAY

TABLE

None

What is the purpose of the PRAGMA SERIALLY_REUSABLE directive in PL/SQL?

Deselect Answer

It indicates that the package can be invalidated and recompiled automatically

It specifies that the cursor can be reused by multiple sessions

It optimizes the memory usage of the PL/SQL cursor

It declares that the package state is not changed during its execution

None

What is the difference between a PL/SQL function and a procedure?

Deselect Answer

A procedure cannot contain SQL statements, while a function can

A function must return a value, while a procedure may or may not

A function cannot return a value, while a procedure can

A function cannot have parameters, while a procedure can

None

Which statement is used to define a user-defined exception in PL/SQL?

Deselect Answer

DECLARE

RAISE

PRAGMA

EXCEPTION

None

What is the purpose of the FORALL statement in PL/SQL?

Deselect Answer

To perform operations on a single column

To perform operations on a single row

To perform conditional operations on multiple rows

To perform bulk operations on multiple rows

None

10.

How do you handle multiple exceptions and process differently in PL/SQL?

Deselect Answer

By using the RAISE statement

By using the WHEN OTHERS exception handler

By using nested BEGIN...END blocks

By using multiple EXCEPTION sections

None

11.

Which of the following statement is used to create a PL/SQL package specification?

Deselect Answer

CREATE OR REPLACE PACKAGE package_name;

CREATE OR REPLACE PACKAGE BODY package_name;

CREATE PACKAGE BODY package_name;

CREATE PACKAGE SPECIFICATION package_name;

None

12.

Which of the following is the correct way to declare a record type in PL/SQL?

Deselect Answer

RECORD rec_name (col1 data_type, col2 data_type);

DECLARE rec_name RECORD (col1 data_type, col2 data_type);

TYPE rec_name IS RECORD (col1 data_type, col2 data_type);

CREATE RECORD rec_name (col1 data_type, col2 data_type);

None

13.

Which of the following statements is used to exit from a loop in PL/SQL?

Deselect Answer

CONTINUE

STOP

BREAK

EXIT

None

14.

Which of the following statements is used to explicitly open a cursor?

Deselect Answer

DEFINE cursor_name;

EXECUTE IMMEDIATE

OPEN cursor_name;

DECLARE cursor_name;

FETCH cursor_name;

None

15.

What is the purpose of the SAVEPOINT statement in PL/SQL?

Deselect Answer

To start a new transaction by committing the previous transaction

To rollback a transaction

To create a temporary checkpoint within a transaction

To commit a transaction

None

16.

What is the purpose of the %TYPE attribute in PL/SQL?

Deselect Answer

It returns the value of a variable or column

It returns the position of a variable or column

It returns the datatype of a variable or column

It returns the size of a variable or column

None

17.

Which of the following is NOT a valid cursor attribute?

Deselect Answer

%ROWCOUNT

%OPEN

%FOUND

%NOTFOUND

None

18.

Which of the following packages is used for file operations in PL/SQL?

Deselect Answer

DBMS_OUTPUT

DBMS_FILE

UTL_FILE

DBMS_SQL

None

19.

What is the purpose of the DBMS_OUTPUT.PUT_LINE procedure in PL/SQL?

Deselect Answer

It writes data to a file

It executes a SQL statement and returns the output

It retrieves the current date and time

It displays a message on the screen or in the output buffer

None

20.

Which of the following is the correct way to declare a cursor parameter in a PL/SQL function?

Deselect Answer

FUNCTION func_name(cur_param IN OUT SYS_REFCURSOR)

FUNCTION func_name(cur_param OUT SYS_REFCURSOR)

FUNCTION func_name(cur_param IN SYS_REFCURSOR)

FUNCTION func_name(cur_param SYS_REFCURSOR)

None

21.

What is the purpose of the DBMS_CRYPTO package in PL/SQL?

Deselect Answer

To perform encryption and decryption operations

To perform file operations

To manage database user keys

To manage user sessions

None

22.

How can you dynamically execute any SQL query in PL/SQL?

Deselect Answer

Using the FETCH statement

Using the OPEN-FOR statement

Using the EXECUTE IMMEDIATE statement

DBMS_DYNAMIC_SQL

None

23.

What is the purpose of the %ROWTYPE attribute in PL/SQL?

Deselect Answer

It returns the size of a record

It returns the value of a record

It returns the datatype of a record

It returns the structure of a record

None

24.

Which of the following statement is used to recreate a PL/SQL function?

Deselect Answer

CREATE FUNCTION function_name(parameters) RETURN data_type;

CREATE OR REPLACE FUNCTION function_name(parameters) RETURN data_type;

DECLARE FUNCTION function_name(parameters) RETURN data_type;

DEFINE FUNCTION function_name(parameters) RETURN data_type;

None

25.

What is the purpose of the CONTINUE statement in PL/SQL?

Deselect Answer

It exits the loop and continues with the next iteration

It returns control to the caller and exits the current block of code

It raises an exception and terminates the program

It skips the remaining statements in the current iteration and continues with the next iteration

None

26.

What is the purpose of the NOCOPY hint in PL/SQL parameter passing?

Deselect Answer

It forces the parameter to be passed by value instead of by reference

It optimizes the performance of parameter passing by reducing memory usage

It prevents the parameter from being copied when passed to a subprogram

It ensures that the parameter is passed by reference instead of by value

None

27.

Which of the following statement is used to declare a cursor variable in PL/SQL?

Deselect Answer

DECLARE cur_name SYS_REFCURSOR;

CURSOR cur_name IS SELECT ...;

OPEN cur_name FOR SELECT ...;

FETCH cur_name INTO ...;

None

28.

What is the purpose of the BULK COLLECT clause in PL/SQL?

Deselect Answer

To perform bulk operations on multiple columns

To perform bulk operations on a single row

To perform bulk operations on multiple rows

To perform bulk operations on a single column

None

29.

What is the purpose of the DBMS_LOCK package in PL/SQL?

Deselect Answer

To manage user locks

To manage database locks

To manage file locks

To manage transaction locks

None

30.

What is the purpose of the DBMS_ALERT package in PL/SQL?

Deselect Answer

To profile and analyze the performance of PL/SQL code

To manage database alerts and notifications

To perform encryption and decryption

To send and receive emails

None

If you have anything to suggest or correction on any quiz, feel free to comment in the box below

Discover the power of technology and learning with TechyBuddy

Welcome to your Unmasking the Best of PLSQL: Expert Level

Are you sure to submit the quiz: Unmasking the Best of PLSQL: Expert Level ?