Oracle Solutions We believe in delivering tangible results for our customers in a cost-effective manner
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.
