Home / Interview Questions / Database Interview Questions / PL/Sql Interview Questions Part 3

PL/Sql Interview Questions Part 3

1. What are some predefined exceptions in PL/SQL?

A list of predefined exceptions in PL/SQL:

  • DUP_VAL_ON_INDEX
  • ZERO_DIVIDE
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • CURSOR_ALREADY_OPEN
  • INVALID_NUMBER
  • INVALID_CURSOR
  • PROGRAM_ERROR
  • TIMEOUT _ON_RESOURCE
  • STORAGE_ERROR
  • LOGON_DENIED
  • VALUE_ERROR
  • etc.

 

2. What is a trigger in PL/SQL?

 

  • A trigger is a PL/SQL program which is stored in the database.
  • It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.

 

 

 

3. What is the maximum number of triggers, you can apply on a single table?

 

12 triggers.

 

 

 

4. How many types of triggers exist in PL/SQL?

 

There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

  • BEFORE ALL ROW INSERT
  • AFTER ALL ROW INSERT
  • BEFORE INSERT
  • AFTER INSERT etc.

 

 

5. What is the difference between execution of triggers and stored procedures?

 

  • A trigger is automatically executed without any action required by the user, while a stored procedure is explicitly invoked by the user.

 

 

6. What happens when a trigger is associated to a view?

 

  • When a trigger is associated to a view, the base table triggers are normally enabled.

 

 

 

7. What is the usage of WHEN clause in trigger?

 

  • A WHEN clause specifies the condition that must be true for the trigger to be triggered.

 

 

8. How to disable a trigger name update_salary?

 

  • ALTER TRIGGER update_salary DISABLE;

 

 

 

9. Which command is used to delete a trigger?

 

  • DROP TRIGGER command.

 

 

10. what are the two virtual tables available at the time of database trigger execution?

 

Table columns are referred as THEN.column_name and NOW.column_name.

  • For INSERT related triggers, NOW.column_name values are available only.
  • For DELETE related triggers, THEN.column_name values are available only.
  • For UPDATE related triggers, both Table columns are available.

 

 

11. What is stored Procedure?

 

A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions.

  • It is similar to a procedure in other programming languages.
  • It is stored in the database and can be repeatedly executed.
  • It is stored as schema object.
  • It can be nested, invoked and parameterized.

 

 

12. What are the different schemas objects that can be created using PL/SQL?

 

  • Stored procedures and functions
  • Packages
  • Triggers
  • Cursors

 

 

13. What do you know by PL/SQL Cursors?

 

  • Oracle uses workspaces to execute the SQL commands.
  • When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area.
  • This area is identified by the cursor.
  • It allows programmers to name this area and access it’s information.

 

 

 

14. What is the difference between the implicit and explicit cursors?

 

  • Implicit cursor is implicitly declared by Oracle.
  • This is a cursor to all the DDL and DML commands that return only one row.
  • Explicit cursor is created for queries returning multiple rows.

 

 

15. What will you get by the cursor attribute SQL%ROWCOUNT?

 

  • The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.

 

 

 

About Syed Saad Ali

As a certified and experienced Oracle Database Administrator with Almost 10 Years of Experience I am able to perform all levels of database maintenance and am fully qualified to do Oracle updates as well. Capitalizing on my interest in Business & IT both, I chose to navigate my career towards Enterprise Solutions, which has enabled me to excel in the combination of both. I have developed exceptional analytical skills that allow me to diagnose and take care of issues immediately. Extremely skilled in planning and execution of special projects in time- critical environments. Extensive knowledge in directing, coordinating and exercising concern authority for planning, organization, control and successful completions. Very skilled at solving customer relations issues by seeking first to understand and then to be understood. Effectively able to communicate with customers, vendors and management.

Check Also

Apps Technical Interview Questions Part 5

Leave a Reply