Triggers – Views Interview Questions

What is a trigger ? How many types of triggers are supported in Oracle ?

A trigger is a defined procedures that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Different types of triggers are:

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

What is a Statement Trigger ?

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected.

What is a Row Trigger ?

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

What are the different components of a PL/SQL trigger ?

Trigger Action, Trigger Restriction and Trigger Action are the different components of a PL/SQL Trigger.

Name some usages of database trigger ?

• Restrict DML operations against a table to those issued during regular business hours.
• Automatically generate derived column values.
• Prevent invalid transactions.
• Enforce complex security authorizations.
• Enforce referential integrity across nodes in a distributed database.
• Enforce complex business rules.
• Provide transparent event logging.
• Provide auditing.
• Maintain synchronous table replicates.
• Gather statistics on table access.
• Modify table data when DML statements are issued against views.
• Publish information about database events, user events, and SQL statements to subscribing applications.

How can you enable or disable a trigger ?

To Enable a Trigger : Alter Trigger Trigger_Name Enable;

To Disable a Trigger : Alter Trigger Trigger_Name Disable;

How to disable multiple triggers of a table at at a time ?

Use query : ALTER TABLE table_name DISABLE ALL TRIGGERS;

What are two virtual tables available during database trigger execution ?

The two virtual tables available during trigger execution are OLD and NEW. These have the same column names as that of base table on which trigger is written.

Triggers for INSERT, since we are inserting a new row, only NEW.column names have value (OLD values are null)

Triggers for UPDATE, since we are updating old values to new values, both OLD.column and NEW.column have values

Triggers for DELETE, since we are deleting the old record, only OLD.column names have value (NEW values are null)

Hence we assign the :old and :new columns as appropriate in a trigger.

Can we have a commit / rollback in trigger ?

Commits, rollbacks and save points are not allowed in the trigger body. But if you want it then you have to declare it as AUTONOMOUS_TRANSACTION.

What is mutating table error and how can you solve it ?

Mutating table error occurs when a trigger/function tried to modify or query a table that is currently being modified by the statement that fired the trigger/function. Options to avoid this error are:

• Don't use triggers
• Use an "after" or "instead of" trigger
• Use compound trigger
• Use autonomous transactions
• Using the temporary tables or by the views

What is a view ?

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view.

What is Advantages and Disadvantages of views ?
Advantages

Security
A user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, therefore  restricting the user's access to base table.

Query simplicity
A view can turn multi-table queries into single-table queries.

Structural simplicity
Views can give a user a personalized view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

Consistency
A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity
If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.


Disadvantages

Performance
If the view is defined by a complex & multi-table query then simple queries on the views may take considerable time since database translate queries against the view into queries against the underlying base tables.

Update restrictions
When a user tries to update rows of a view, the database must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

What is materialized view (snapshots) ?

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views whose contents are periodically refreshed based on a query, either against a local or remote tables.

Syntax for creating materialized views :

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

The BUILD clause options are shown below.

• IMMEDIATE : Specify IMMEDIATE to indicate that the materialized view is to be populated immediately. This is the default.
• DEFERRED : Specify DEFERRED to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite.

The following refresh types are available.

• FAST : Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables.
• COMPLETE : Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
• FORCE : Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh if fast refresh is not possible. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

A refresh can be triggered in one of two ways.

• ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
• ON DEMAND : Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.

The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. The table must have the same name and be in the same schema as the resulting materialized view.

Difference between a view and materialized view ?

• In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

• When we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.

• In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

• Performance of View is less than Materialized view.

• In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table.

• In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

Procedures - Functions - Packages Interview Questions

What are advantages of Stored Procedures ?

To help you build powerful database applications, stored procedures provide several advantages :

• Better performance
• Higher productivity
• Ease of use
• Increased scalability
• Simplifies maintainability
• Interoperability
• Security
• Replication

What are the modes of parameters that can be passed to a procedure ?

In, Out and In-Out are different modes of a PL/SQL Procedure.

How we can create a table through procedure ?

Using dynamic sql.

Explain about Package in short.

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. Packages support the development and maintenance of reliable, reusable code with modularity, easier application design, information hiding, and better performance.

A package has two parts : specification which declares the public items and body to code for public subprograms.

Name the tables where characteristics of Package, procedure and functions are stored ?

user_objects

What is the difference between procedure and function ?

• A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all.Although, OUT parameters can still be used in functions, but restricts a function from being used in a SQL Statement.
• Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can't.
• Functions are normally used for computations where as procedures are normally used for executing business logic.
• Stored procedure is precompiled execution plan where as functions are not.
• Oracle provides the provision of creating "Function Based Indexes" to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.

What is the difference between package and procedure ?

Package is a collection of procedures and functions while procedure is a standalone pl/sql unit.

Which is best to use package or procedure ?

Always use a package. Never use a standalone procedure except for demos, tests and standalone utilities because packages

• break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)
• support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
• increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding
• support overloading
• support session variables when you need them
• promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together...
• have better performance

Packages have better performance, Why ?

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.

What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

The scope of a cursor declared in a procedure is limited to that procedure only. While the scope of cursor declared in a package specification is global.

Can we have a procedure declared in package body but not declared in package specifications ?

Yes. But that procedure will be local and can not be called by other procedures in the package. When we declare a proc in package spec, it becomes global for the package.

Can we have a package spec without a body ?

Yes

Can we have a procedure spec without body ?

No. It should atleast have NULL; inside begin and end.

Name some Oracle's built-in packages ?

• DBMS_OUTPUT - used to display a message, especially while debugging
• DBMS_PIPE - This allows 2 different sessions of the database to communicate over the named pipe.
• DBMS_DDL - Contains procedures to wrap single PL/SQL units like package specs, package body, function, procedure, type specs, type body, etc. Eg : DBMS_DDL.CREATE_WRAPPED();
• DBMS_ALERT - lets you use database triggers to alert an application when some database values change
• UTL_FILE - it allows the PL/SQL program to read from and write into text files.
• UTL_SMTP - allows the PL/SQL program to send emails over Simple Mail Transfer Protocol
• UTL_HTP, HTF - allows PL/SQL to generate HTML tags.
• UTL_HTTP - allows PL/SQL programs to make HTTP calls. It gets the data from Internet. The inputs ar the URL and the contacts of the website. The return data is usually in HTML format.
• DBMS_SQL - Helps you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. For ex: it allows us to create a procedure based on a table, whose name is given at run-time.

Interview Questions -- Cursors

What are cursors? Distinguish between implicit and explict cursors ?

Oracle execute every query in a seperate memory area called context area and pointer (with a name) to this area is known as cursor. Implicit cursor are internally used by oracle engine to run a query. Explicit cursors are user defined which are declared & open for a select query in pl/sql code block. They are used when SELECT INTO query return more than one row and we need to process each row one by one.

What are the different parts of an Explicit Cursor ?

The different parts in the process of making an Explicit Cursor are as follows:

1. Declaring the Cursor

2. Opening the Cursor

3. Fetching the Cursor

4. Closing the Cursor

What are the cursor attributes used in PL/SQL ?

%Rowcount: checks the number of rows that are updated, deleted or fetched.

%Isopen: checks whether the Cursor you want to access is currently open or closed.

%Found: checks if the Cursor fetched a row. It returns a TRUE if any row is fetched.

%NotFound: checks if the Cursor fetched any row or not. It returns a TRUE value if any row is not fetched.

What is the difference between NO DATA FOUND and %NOTFOUND ?

NO DATA FOUND is a oracle exception which is thrown if the SELECT INTO statement doesn't return at least one row. While %NOTFOUND is cursor attribute to check whether cursor has any row or not.

State the advantage and disadvantage of Cursor ?

Advantages:

• we can perform row by row processing so we can perform row wise validation or operations on each row.
• Cursors can be faster than a for loop but at the cost of more overhead

Disadvantages:

• occupies memory from your system that may be available for other processes
• Need to be closed properly to avoid ORA-01000: maximum open cursors exceeded

What is cursor for loop ?

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause is used along with FOR UPDATE clause. When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

The WHERE CURRENT OF clause is used with UPDATE or DELETE statement to update or delete the most recent row fetched from the cursor.

Syntax: WHERE [CURRENT OF cursor_name | search_condition]

What is ref cursor ?

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database.

What are types of Ref cursor ?

There are 2 types of REF cursors : Strong and weak.

TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --Strong REF cursor

TYPE emp_curtype IS REF CURSOR; --weak REF cursor

Which one is better - Strong or weak ?

It depends on your requirement. In strong cursor, we attach the return ROWTYPE at the declaration time itself. So, the compiler knows what the cursor variable should return, making it less error prone. On the other hand, weak cursors are more flexible because they can be used with any query, any return ROWTYPE, even different ROWTYPES at different calls to it.

What is the difference between cursor & ref cursor ?

A "normal" plsql cursor is static in defintion while Ref cursors may be dynamically opened or opened based on logic.

A ref cursor can be returned to a client -- a cursor cannot be.

A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

A cursor can be global -- a ref cursor cannot.

What are the alternative to cursors for better performance ?

Cursors are not bad for performance. However cursor for loops are bad as they process row by row. You can use bulk collect with SELECT INTO by making sure there is a list of collections in the INTO clause. Even in Oracle 10g and higher Oracle will implicitly perform bulk collects.

What are the Restrictions on Cursor Variables ?

• You cannot declare a cursor variable in a package specification. That is, a package cannot have a public cursor variable (a cursor variable that can be referenced from outside the package).
• You cannot store the value of a cursor variable in a collection or database column.
• You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
• Using a cursor variable in a server-to-server remote procedure call (RPC) causes an error. However, you can use a cursor variable in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway.
• You cannot use LOB parameters in a server-to-server RPC.

Which is the Default Cursor in Oracle PL/SQL ?

Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.

What is an Active Set ?

The set of rows that a Cursor holds at a single point of time is called as an Active Set.

Why is closing the Cursor required during explicit cursor development ?

It is important because it will consume system memory while it is in active state and if it is not closed or terminated then it won’t let the other things in the memory as memory will be occupied and later on it will be full. Hence, deletion is necessary. At some point time you may face error ORA-01000: maximum open cursors exceeded.

What cursor type do you use to retrieve multiple recordsets ?

Explicit cursors and Ref cursor if recordset need to be returned to calling program.