Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 24 June 2012

Interview Preparation SQL/PLSQL/D2K Part1

Difference between Key-next/Post-Text?
Post-Text–Item fires during the Leave the Item process for a text item.  Specifically, this trigger fires when the input focus moves from a text item to any other item where as Key-Next-Item fired as a result of the key action. Key next will not fire unless there is a key event.
What is Format Triggers?
A format trigger is a PL/SQL function executed before an object is formatted.  A trigger can be used to dynamically change the formatting attributes of the object.
What is Instead of Triggers?
Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable. Instead Of Trigger execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.
Instead of Trigger can’t be written at Statement Level.
Cascading Triggers
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.
P/L SQL Tables / Arrays
PL/SQL tables are declared in the declaration portion of the block. A table is a composite data type in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.
Rules for PL/SQL Tables:
1.        A loop must be used to insert values into a PL/SQL Table
2.        You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.
Locking in Forms. Is it Possible to defer locking of records until commit?
Yes, there is a block property called 'Locking Mode', which can be either 'Immediate' or 'Delayed'.  If you set it to 'Delayed', then a record in a block is not locked until commit-time, specifically until changes are 'posted'.
At that time, the current forms value is compared to the DB value before issuing the update statement.  The user will get an error if the record has been updated since it was fetched.
'Locking Mode' can be changed at runtime in a trigger with the Set_Block_Property Built-in.
Describe Locking in Oracle?
Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource - either user objects (such as tables and rows) or system objects not visible to users (such as shared data structures in memory and data dictionary rows). In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.
Oracle uses two modes of locking in a multi-user database:
Exclusive lock mode: Prevents the associate’s resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.  
Share lock mode: Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.    
What are different Types of Locks?
Oracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource.
DML locks (data locks):  DML locks protect data. For example, table locks lock entire tables, row locks lock-selected rows.    
DDL locks (dictionary locks): DDL locks protect the structure of schema objects - for example, the definitions of tables and views.  
Internal locks and latches: Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.    
Distributed locks: Distributed locks ensure that the data and other resources distributed among the various instances of an Oracle Parallel Server remain consistent. Distributed locks are held by instances rather than transactions. They communicate the current status of a resource among the instances of an Oracle Parallel Server.    
Parallel cache management (PCM) locks: Parallel cache management locks are distributed locks that cover one or more data blocks (table or index blocks) in the buffer cache. PCM locks do not lock any rows on behalf of transactions. 
What is View and its importance and what are difference Tables/Views?
Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.
Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.
This is the whole list of reasons to use views:
1) To provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table
2) To hide data complexity, For example: a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.
3) To simplify commands for the user. For example, views allow users to select information from multiple tables without actually knowing how to perform a join.
4) To present the data in a different perspective from that of the base table. For example, the columns of a view can be renamed without affecting the tables on which the view is based.
5) To isolate applications from changes in definitions of base tables. For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected.
6) To express a query that cannot be expressed without using a view. For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table. For information about GROUP BY or UNION, see the Oracle8 Server SQL Reference.
6) To save complex queries. For example, a query could perform extensive calculations with table information. By saving this query as a view, the calculations can be performed each time the view is queried.
7) To achieve improvements in availability and performance. For example, a database administrator can divide a large table into smaller tables (partitions) for many reasons, including partition level load, purge, backup, restore, reorganization, and index building. Once partition views are defined, users can query partitions, rather than very large tables. This ability to prune unneeded partitions from queries increases performance and availability.
Define the use of Inline Views & Top N Analysis
The Inline view: It is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.
Top N Analysis: The task of retrieving the top or bottom N rows from a database table. You can do so either by using the ROWNUM pseudo column available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK () and DENSE_RANK ().
Model/Modeless Windows (Difference)?
A window can be either modeless or modal.  A modal window (often a dialog) requires the end user to respond before continuing to work in the current application.  A modeless window requires no such response.
You can display multiple modeless windows at the same time, and end users can navigate freely among them. Modeless windows remain displayed until they are dismissed by the end user or hidden programmatically.  You can set the Hide on Exit property for a modeless window to specify whether it should remain displayed when the end user navigates to another window. Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms, modal windows are "always-on-top" windows that cannot be layered behind modeless windows. The Hide on Exit property does not apply to modal windows. Modal dialog windows cannot have scroll bars
DDL Statements/Dynamic SQL from P/L SQL (Execute Immediate)?
EXECUTE IMMEDIATE is the replacement for DBMS_SQL package from Oracle 8i onwards. It parses and immediately executes a dynamic SQL statement or a PL/SQL block created on the fly. Dynamically created and executed SQL statements are performance overhead; EXECUTE IMMEDIATE aims at reducing the overhead and give better performance. It is also easier to code as compared to earlier means. The error messages generated when using this feature are more user friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

Usage tips

1. EXECUTE IMMEDIATE will not commit a DML transaction carried out and an explicit commit should be done.
If the DML command is processed via EXECUTE IMMEDIATE, one needs to explicitly commit any changes that may have been done before or as part of the EXECUTE IMMEDIATE itself. If the DDL command is processed via EXECUTE IMMEDIATE, it will commit all previously changed data.
2. Multi-row queries are not supported for returning values, the alternative is to use a temporary table to store the records (see example below) or make use of REF cursors.
3. Do not use a semi-colon when executing SQL statements, and use semi-colon at the end when executing a PL/SQL block.
4. This feature is not covered at large in the Oracle Manuals. Below are examples of all possible ways of using Execute immediate. Hope it is handy.
For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL
 l_depnam varchar2(20) := 'testing';
 l_loc    varchar2(10) := 'Dubai';
 execute immediate 'insert into dept values (:1, :2, :3)'
   using 50, l_depnam, l_loc;
EXECUTE IMMEDIATE is a much easier and more efficient method of processing dynamic statements than could have been possible before. As the intention is to execute dynamic statements, proper handling of exceptions becomes all the more important. Care should be taken to trap all possible exceptions.

What is different Join statement in SQL?
Join is the process of combining data from two or more tables using matching columns. This relational computing feature consolidates multiple data tables for use in a single report.
The SQL JOIN statement is used to combine the data contained in two relational database tables based upon a common attribute.
Equi-Join: A join statement that uses an equivalency operation (i.e: colA = colB). The converse of an equijoin is a nonequijoin operation. In the Equi-Join two (or more) tables are linked via a common domain attribute. This is the most common form of joining used in relational data manipulation.
Outer Join: The Outer-Join is the opposite of an Equi-Join. It searches for records that exist outside of the specified Join condition. The (+) symbol is the Outer Join operator which causes the join condition to be inverted.
Self Join: A join in which a table is joined with itself. Sometimes we need to join a table to itself in order to search for the correct data.
Nonequijoin: A join statement that does not use an equality operation (i.e: colA <> colB). The converse of a nonequijoin is a equijoin.
What is Cartesian product?
A Cartesian product is the result of an unrestricted joins of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
Differentiate % Type & % Row Type?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
   v_EmpRecord  emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:

   v_EmpNo  emp.empno%TYPE;
How to prevent output coming to screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
How to eliminate duplicate rows from table?
Delete from EMP a
Where a.rowid <> (select min (b.rowid) from emp b where a.empno = b.empno);
Delete from EMP a
Where a.rowid <> (select max (b.rowid) from emp b where a.empno = b.empno);
How to prevent from giving information messages during SQL Execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
In Which Sequence Trigger is fired while committing?
Key Commit                                                                  
Pre Commit
Pre/On/Post Delete
Pre/On/Post Update
Pre/On/Post Insert
On commit
Post Database Commit
What is Autonomous Transactions in oracle?
Autonomous Transaction allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.
Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent … something the application developer should watch out for.
As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).
Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):
Here is an example of defining a stored procedure as autonomous:
CREATE PROCEDURE process_ord_line_shipment
       (p_order_no number, p_line_no number) AS
   l_char_1     varchar2(100);
Difference and Advantage Bitmap/B-Tree Index?
A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.
Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).
Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values. 


  1. Did you know that that you can make dollars by locking special sections of your blog or site?
    All you need to do is to open an account with AdscendMedia and use their Content Locking tool.