FIRST and LAST analytic functions in plsql

The FIRST function (or more properly KEEP FIRST function) is used in a very special situation. Suppose we rank a group of record and found several records in the first rank. Now we want to apply an aggregate function on the records of the first rank. KEEP FIRST enables that.

The general syntax is:

Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>)

Please note that FIRST and LAST are the only functions that deviate from the general syntax of analytic functions. They do not have the ORDER BY inside the OVER clause. Neither do they support any <window> clause. The ranking done in FIRST and LAST is always DENSE_RANK. The query below shows the usage of FIRST function. The LAST function is used in similar context to perform computations on last ranked records.

-- How each employee's salary compare with the average salary of the first

-- year hires of their department?

SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,

TRUNC(

AVG(sal) KEEP (DENSE_RANK FIRST

ORDER BY TO_CHAR(hiredate,'YYYY') )

OVER (PARTITION BY deptno)

     ) AVG_SAL_YR1_HIRE

FROM emp

WHERE deptno IN (20, 10)

ORDER BY deptno, empno, HIRE_YR;

     EMPNO     DEPTNO HIRE        SAL AVG_SAL_YR1_HIRE

---------- ---------- ---- ---------- ----------------

      7782         10 1981       2450             3725

      7839         10 1981       5000             3725

      7934         10 1982       1300             3725

      7369         20 1980        800              800

      7566         20 1981       2975              800

      7788         20 1982       3000              800

      7876         20 1983       1100              800

      7902         20 1981       3000              800

8 rows selected.

What are the mandatory parameters given in Reports?

P_CONC_REQUEST_ID,

P_ORG_ID

Cardinality functions in plsql

In PL/SQL, it has always been trivial to get a count of elements in an array or collection, using the COUNT pseudo-method. The new CARDINALITY function in 10g now makes it trivial to count the elements of a collection in SQL. This function is logically equivalent to the COUNT pseudo-method. In the following examples we'll show simple examples of the function's usage in both PL/SQL and SQL.

DECLARE
TYPE varchar2_ntt IS TABLE OF VARCHAR2(2000);

nt varchar2_ntt;
BEGINnt := varchar2_ntt( 'A','B','C','C');DBMS_OUTPUT.PUT_LINE( 'Count = ' || nt.COUNT );DBMS_OUTPUT.PUT_LINE( 'Cardinality = ' || CARDINALITY(nt) );END;/Count = 4

Cardinality = 4

PL/SQL procedure successfully completed.

SET OPERATORS in PLSQL

·         UNION

·         UNION ALL

·         INTERSECT

·         MINUS

What r the different types of SQL statements?

The tables in the following sections provide a functional summary of SQL commands and are divided into these categories:

1.     Data Definition Language commands (CREATE, ALTER, and DROP)

2.     Data Manipulation Language commands (SELECT, INSERT, UPDATE, LOCK TABLE)

3.     Transaction Control commands (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)

4.     Session Control commands (alter session, set role)

5.     System Control commands (alter system)

6.     Embedded SQL commands (ALLOCATE, CLOSE, DECLARE CURSOR, CONNECT, DECLARE DATABASE, DECLARE STATEMENT, DECLARE TABLE, DESCRIBE, EXECUTE, FETCH, OPEN, WHENEVER)

What is the Package used in Oracle to do the File Operation?

UTL_FILE

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

a) USER_OBJECTS ALL_OBJECTS DBA_OBJECTS
b) USER_SOURCE ALL_SOURCE DBA_SOURCE
c) USER_DEPENCENCIES
d) USER_ERRORS ALL_ERRORS DBA_ERRORS

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.

What are the different types of triggers?

Before

After

For each Row

For each Statement (default)

Instead of Trigger: This trigger is defined on a view rather than a table.

System Triggers: A new feature of Oracle8i, wherein the trigger is fired when the database startup / shutdown process.

Schema Triggers: These triggers are fired whenever a DDL statement is executed.  (Creation or Deletion of any DB Objects)

Order of Trigger Firing:

          Before Statement trigger (If present)

          Each row affected by the statement

(a)        Execute row level trigger (If present)

(b)        Execute the statement itself

(c)        Execute the after row level trigger (If Present)

          After statement trigger (If Present)

What is trigger?

A Database Trigger is a stored procedure that is fired when a DML operation is performed on the table. In total there are 13 types of Triggers

Syntax for creating a trigger:

            CREATE OR REPLACE TRIGGER <TRIGGERNAME> before / after

                        [INSERT / UPDATE / DELTE ] ON <TABLE NAME>

                        {For each Statement / Row}

                        {When <condition…..>}

How can you tell if a database object is invalid?

By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

Select * from all_objects where object_name =’PACKAGE_NAME’;

Dynamic SQL (Execute Immediate) in PLSQL

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 users friendly. Though DBMS_SQL is still available, it is advisable to use EXECUTE IMMEDIATE calls because of its benefits over the package.

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.

5. For Forms Developers, this feature will not work in Forms 6i front-end as it is on PL/SQL 8.0.6.3.

declare

 l_depnam varchar2(20) := 'testing';

 l_loc    varchar2(10) := 'Dubai';

begin

 execute immediate 'insert into dept values (:1, :2, :3)'

   using 50, l_depnam, l_loc;

 commit;

end;

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.

Which system tables contain information on privileges granted and privileges obtained?

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

Which system table contains information on constraints on all the tables created?

USER_CONSTRAINTS.

Normalization / De-Normalization

Normalization is the process of efficiently organizing data in a database.  There are two goals of the normalization process:  eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).  Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

1.     Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.

2.     Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3.     Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.

4.     Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.

5.     Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.

1st Normal Form (1NF)

A table (relation) is in 1NF if

1. There are no duplicated rows in the table.

2. Each cell is single-valued (i.e., there are no repeating groups or arrays).

3. Entries in a column (attribute, field) are of the same kind.

Note: The order of the rows is immaterial; the order of the columns is immaterial.

Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).

2nd Normal Form (2NF)

A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."

3rd Normal Form (3NF)

A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Boyce-Codd Normal Form (BCNF)

A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

4th Normal Form (4NF)

A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

5th Normal Form (5NF)

A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF)

A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

De-Normalization:

De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply De-normalization in the process of deriving a physical data model from a logical form.

What is Normalization?

Normalization is the process that helps analysis or database designer to design table structure for an application. The focus of normalization is to attempt to reduce redundant table data to very minimum.

Normalization is carried out for the following reason:

1.     To structure the data between tables so that maintenance is simplified.

2.     To re allow data retrieval at optimal speed.

3.     To simplify data maintenance through update, insert & deletes.

4.     To reduce the need to restructure table as new application requirement arise.

5.     To improve the quality of design for an application by rationalization of table data.

Normalization is technique that:

1.     Decompose data into two dimensional tables

2.     Eliminate any relationship in which table data does fully depend upon the primary key of record.

3.     Eliminate any relationships that contain transitive dependencies.

FIRST NORMAL FROM:

When a table is decomposed into two dimensional table with all repeating group of data eliminated the table data is said to be in its first normal form.

The repetitive portion of data belonging to the record is treated as repeating groups.

A table is in 1^st normal form if:

·         There are no repeating groups.

·         All the key attribute are defined.

·         All attribute are dependent on a primary key.

To convert a table to its First Normal Form:

1.     The un-normalized data in the first table is the entire table.

2.     A key that will uniquely identify each record should be assigned to the table. This key has to be unique because it should be capable of identifying any specific row from the table for extracting information for use. This key is called the table’s primary key.

SECOND NORMAL FROM:

A table is said to be in its second normal form when each record in the table is in the first normal form & each column in the record is fully dependent on its primary key.

A table is in 2^nd normal form if:

·         It’s in 1^st normal form.

·         It include no partial dependencies (where as attribute is dependent on only a part of primary key)

To convert a table to its Second Normal Form:

1.     Find & remove fields that are related to the only part of the key

2.     Group the remove item in the another table

3.     Assign the new table with the key i.e. part of a composite key.

THIRD NORMAL FROM:

Table data is said to be in third normal form when all transitive dependencies are remove from this data.

A table is in 3^rd normal form if:

1.     It’s in 2^nd normal form.

2.     It contains no transitive dependencies (where a non-key attribute is dependent on another non –key attribute)

Differences Between Standard PO, Planned PO, Blanket PA and Contract PA

	Standard PO	Planned PO	Blanket PA	Contract PA
Terms & Conditions Known	Yes	Yes	Yes	Yes
Goods or Services Known	Yes	Yes	Yes	No
Pricing Known	Yes	Yes	May be	No
Quantity Known	Yes	Yes	No	No
Account Distributions Known	Yes	Yes	No	No
Delivery Schedule Known	Yes	May be	No	No
Can be Encumbered	Yes	Yes	No	No
Can encumber releases	N/A	Yes	Yes	N/A