PLSQL Quiz – 07 – NiC IT Academy

PLSQL Quiz – 07

By Mr.Chandra

Full Name

Mobile Number

1) You have a PL/SQL block that needs to handle different exceptions, including specific exceptions like NO_DATA_FOUND and TOO_MANY_ROWS. What is the correct order to handle these in an EXCEPTION block?

OTHERS, NO_DATA_FOUND, TOO_MANY_ROWS

NO_DATA_FOUND, TOO_MANY_ROWS, OTHERS

OTHERS should always be last, with specific exceptions before it

2) Which of the following is NOT a valid usage of the FORALL statement in PL/SQL?

Executing multiple UPDATE statements based on a collection of data

Using FORALL with dynamic SQL in the EXECUTE IMMEDIATE statement

Using FORALL to fetch data into a collection

3) What is the main advantage of using a package in PL/SQL?

It reduces execution time by compiling procedures separately

It increases code complexity for better performance

It allows the grouping of related subprograms for modularization

4) Consider the following cursor: sql Copy code CURSOR c_emp IS SELECT employee_id, department_id FROM employees WHERE department_id = 50 FOR UPDATE NOWAIT; What will happen if another session tries to update the same row locked by this cursor?

The other session will immediately update the row

The other session will wait until the row is unlocked

The other session will raise an error due to the NOWAIT clause

5) What is the output of the following PL/SQL block? sql Copy code DECLARE v_sal NUMBER := 5000; BEGIN IF v_sal BETWEEN 3000 AND 6000 THEN v_sal := v_sal + 1000; ELSE v_sal := v_sal - 1000; END IF; DBMS_OUTPUT.PUT_LINE(v_sal); END;

4000

6000

7000

6) You want to declare a PL/SQL collection to store multiple records from a table. Which of the following is the best choice?

%TYPE for a variable collection

%ROWTYPE for a table record collection

VARCHAR2 for a character collection

7) Given the following PL/SQL code: sql Copy code DECLARE v_num NUMBER := 10; BEGIN LOOP EXIT WHEN v_num = 0; v_num := v_num - 1; END LOOP; DBMS_OUTPUT.PUT_LINE('Loop ended with v_num = ' || v_num); END; What is the output of the above block?

Loop ended with v_num = 0

Loop ended with v_num = 1

It goes into an infinite loop

8) Which of the following statements about PL/SQL triggers is correct?

Triggers can be invoked manually

Triggers cannot include COMMIT or ROLLBACK statements

Triggers can only be defined at the table level

9) What is the purpose of the RAISE_APPLICATION_ERROR procedure in PL/SQL?

To display output messages

To handle errors automatically

To generate a user-defined error message with a specific error number

10) Given the table products with columns product_id, product_name, and quantity, you want to update the quantity to 0 if it is negative using a PL/SQL block. Which statement is best suited for this scenario?

Using IF condition inside an UPDATE statement

Using CASE statement directly in SQL

Using UPDATE with a WHERE clause filtering negative values

11) You are tasked with creating a stored procedure to archive data from the orders table to the orders_archive table every month. The archive process involves moving completed orders. Which PL/SQL block is best suited for this task?

Using a FOR LOOP to iterate through each row and perform INSERT

Using a single INSERT INTO SELECT statement with a DELETE after it

Using a MERGE statement to combine data into the archive table

12) In a data migration project, you need to validate if all customer records in a source table have corresponding records in the destination table after migration. Which PL/SQL query would best check this condition?

Using a CURSOR to iterate and compare each record

Using EXISTS in a subquery to validate each customer

Using MINUS to find missing records in the destination table

13) Your project requires creating a trigger that automatically updates the last_updated_date field in the employees table whenever a record is updated. What kind of trigger is best suited for this?

AFTER UPDATE trigger

INSTEAD OF UPDATE trigger

BEFORE UPDATE trigger

14) You are building a financial report that needs to aggregate large volumes of transactional data. To improve performance, you decide to implement a PL/SQL block using bulk operations. Which technique would you use?

Using a standard FOR LOOP to process each record

Using BULK COLLECT with LIMIT and FORALL for batch processing

Using CURSOR to fetch and process one row at a time

15) During an ETL process, you need to ensure that only unique customer records are inserted into the customers table. If a duplicate is found, the process should log the record in an error_log table. What is the best approach to achieve this?

Using a TRIGGER to check for duplicates and insert into error_log

Using a MERGE statement to handle duplicates

Using EXCEPTION handling in a PL/SQL block with a unique constraint violation check

16) A retail application needs to apply a discount based on customer membership level using a PL/SQL function. Which is the best way to implement this?

Using a PROCEDURE to update the discount directly in the table

Using a TRIGGER to apply discount upon insertion of a new order

Using a FUNCTION that calculates and returns the discount percentage based on input membership level

17) In a data warehousing project, you need to perform incremental data loading from a source to a staging table based on a timestamp field. What is the best practice to implement this in PL/SQL?

Loading all data from the source table every time

Using a MERGE statement to insert new and update existing records

Using a TRUNCATE statement followed by an INSERT for full reload

18) You are working on a billing system where you need to calculate the total invoice amount including tax for each order using a PL/SQL procedure. The tax rate is stored in a configuration table. How should you implement this?

Hard-code the tax rate in the PL/SQL procedure

Use a CURSOR to fetch the tax rate from the configuration table for each calculation

Use a SELECT statement to fetch the tax rate once and store it in a variable

19) Your team needs to implement an audit mechanism in the database to log changes to the employees table. What approach should be used?

Create a scheduled job to periodically check changes in the table

Use an AFTER INSERT OR UPDATE trigger to log changes into an audit table

Use a MERGE statement to track changes

20) A banking application requires a monthly report on account transactions with aggregated data for each account. Which PL/SQL technique is most efficient for this reporting task?

PLSQL Quiz – 07

By Mr.Chandra

For enrolment support contact us at:

+91 70100 80468

+91 99529 74013

Contact Us

NiC IT Academy

An Online Software Training Institute

G1, Venkatesan st, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email

support@nicitacademy.com

Useful Links

©2025 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

Login with your email & password

Sign up with your email & password