PL/SQL: What is PRAGMA in PL SQL

 

PRAGMA in Oracle PL SQL

In Oracle PL/SQL, PRAGMA refers to the compiler directive and it is used to provide an instruction to the compiler. PRAGMA are processes at compile time, not run time. They do not affect the meaning of program, they simply convey information to the compiler. This is the generally asked interview question. There are five types of PRAGMA in oracle PL SQL

PRAGMA AUTONOMOUS_TRANSACTION:

This PRAGMA instructs the PL/SQL compiler to mark routine as autonomous i.e. independent. An Autonomous transaction is an independent transaction started by another transaction (main transaction). It let you suspend main transaction, do SQL Operations, commit or rolled back those operations, then resume the main transaction. It does not affect the main transaction.

PRAGMA EXCEPTION_INIT:

This PRAGMA associates an exception name with an oracle error number. It let you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

PRAGMA SERIALLY_REUSABLE:

This PRAGMA tells the oracle that the state of package is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim the memory.

PRAGMA RESTRICT_REFERENCES:

It defines the purity level of packaged program. After oracle 8i, this is no longer required.

PRAGMA INLINE(Introduced in Oracle 11g):

This directive tells compiler that whether to to subprogram call to be inlined or not. Inlining replaces a subprogram call with a copy of the called subprogram.

PL/SQL PRAGMA EXCEPTION_INIT

PL/SQL user named exception. you can define your own error message and error number using Pragma EXCEPTION_INIT or RAISE_APPLICATION_ERROR function.

PL/SQL pragma EXCEPTION_INIT

pragma EXCEPTION_INIT: Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument,

1. exception_name
2. error_number

You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program.

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

exception_name and error_number define on yourself, where exception_name is character string up to 2048 bytes support and error_number is a negative integer range from -20000 to -20999.

Syntax

DECLARE
    user_define_exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number);
BEGIN
    statement(s);
    IF condition THEN
        RAISE user_define_exception_name;
    END IF;
EXCEPTION
    WHEN user_define_exception_name THEN
        User defined statement (action) will be taken;
END;

Example

user-named_exp.sql

SQL>edit user-named_exp
DECLARE
    myex EXCEPTION;
    PRAGMA EXCEPTION_INIT(myex,-20015); 
    n NUMBER := &n;
BEGIN
    FOR i IN 1..n LOOP
        dbms_output.put.line(i);
        IF i=n THEN
            RAISE myex;
        END IF;
    END LOOP;
EXCEPTION
    WHEN myex THEN
        dbms_output.put.line('loop finish');
END;
/

Result

SQL>@user-named_exp
n number &n= 5
1
2
3
4
5
loop finish

PL/SQL procedure successfully operation

PL/SQL : Type of the exceptions raised in PL/SQL ?

 Summary: in this tutorial, you will learn how to use the PL/SQL RAISE statement to raise a user-defined exception, internally defined exception, and reraising an exception.

To raise an exception explicitly, you use the RAISE statement. The RAISE statement allows you to:

• Raise a user-defined exception.
• Raise an internally defined exception.
• Reraising the current exception.

Raising a user-defined exception

A user-defined exception is defined by users like you or other developers in the declaration section of a block or subprogram.

Declaring a user-defined exception

To define a user-defined exception, you use the following syntax:

DECLARE
    exception_name EXCEPTION;
Code language: SQL (Structured Query Language) (sql)

Similar to the variable declaration, you declare an exception in the declaration section of a block.

A user-defined exception must have assigned error_code . To do it, you use the EXCEPTION_INIT pragma as follows:

PRAGMA EXCEPTION_INIT (exception_name, error_code)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the error_code is an integer that ranges from -20,999 to -20,000. And the message is a character string with a maximum length of 2,048 bytes.

The entire syntax for declaring a user-defined exception is as follows:

DECLARE
    exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT (exception_name, error_number);
Code language: SQL (Structured Query Language) (sql)

Raising a user-defined exception example

The following example illustrates how to declare a user-defined exception and associate it with an error code.

DECLARE
    e_credit_too_high EXCEPTION;
    PRAGMA exception_init( e_credit_too_high, -20001 );
    l_max_credit customers.credit_limit%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
    l_credit customers.credit_limit%TYPE := &credit_limit;
BEGIN
    -- get the meax credit limit
    SELECT MAX(credit_limit) 
    INTO l_max_credit
    FROM customers;
    
    -- check if input credit is greater than the max credit
    IF l_credit > l_max_credit THEN 
        RAISE e_credit_too_high;
    END IF;
    
    -- if not, update credit limit
    UPDATE customers 
    SET credit_limit = l_credit
    WHERE customer_id = l_customer_id;
    
    COMMIT;
END;
/
Code language: SQL (Structured Query Language) (sql)

In this example,

• First, declare a user-defined exception e_credit_too_high and associates it with the error number -20001.
• Second, select maximum credit from the customers table using the MAX() function and assign this value to the l_max_credit variable.
• Third, check if the input credit with the maximum credit, if the input credit is greater than the max, then raise the e_credit_too_high exception.
• Finally, update the customer whose id is entered by the user with the new credit limit.

Here is the output if you enter customer id 100 and credit limit 20000:

ORA-20001:

If you want to include a custom message, you can replace the line:

RAISE e_credit_too_high;
Code language: SQL (Structured Query Language) (sql)

by the following line:

raise_application_error(-20001,'Credit is too high');
Code language: SQL (Structured Query Language) (sql)

And execute the code block again, you will receive the following error:

ORA-20001: Credit is too high
Code language: SQL (Structured Query Language) (sql)

Raising an internally defined exception

Typically, the runtime system raises internally defined exceptions implicitly when they occur. Besides, you can explicitly raise an internally defined exception with the RAISE statement if the exception has a name:

RAISE exception_name;
Code language: SQL (Structured Query Language) (sql)

This example shows how to raise an internally defined exception INVALID_NUMBER:

DECLARE
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the meax credit limit
    IF l_customer_id < 0 THEN
        RAISE invalid_number;
    END IF;
END;
/
Code language: SQL (Structured Query Language) (sql)

If you execute the block and enter the customer id -10, you will get the following error:

ORA-01722: invalid number
Code language: SQL (Structured Query Language) (sql)

Reraising the current exception

You can re-raise the current exception with the RAISE statement. Reraising an exception passes it to the enclosing block, which later can be handled further. To reraise an exception, you don’t need to specify the exception name.

DECLARE
    e_credit_too_high EXCEPTION;
    PRAGMA exception_init( e_credit_too_high, -20001 );
    l_max_credit customers.credit_limit%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
    l_credit customers.credit_limit%TYPE     := &credit_limit;
BEGIN
    BEGIN
        -- get the max credit limit
        SELECT MAX(credit_limit) 
        INTO l_max_credit
        FROM customers;
        
        -- check if input credit is greater than the max credit
        IF l_credit > l_max_credit THEN 
            RAISE e_credit_too_high;
        END IF;
        EXCEPTION
            WHEN e_credit_too_high THEN
                dbms_output.put_line('The credit is too high' || l_credit);
                RAISE; -- reraise the exception
    END;
EXCEPTION
    WHEN e_credit_too_high THEN
        -- get average credit limit
        SELECT avg(credit_limit) 
        into l_credit
        from customers;
        
        -- adjust the credit limit to the average
        dbms_output.put_line('Adjusted credit to ' || l_credit);
    
        --  update credit limit
        UPDATE customers 
        SET credit_limit = l_credit
        WHERE customer_id = l_customer_id;
   
        COMMIT;
END;
/
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, get the max credit limit from the customers table.
• Second, compare the max credit with the user-input credit. If the user-input credit is greater than the max credit, then raise the e_credit_too_high exception.
• Third, display a message and reraise the exception in the exception-handling section in the inner block.
• Finally, in the outer block, reassign the average credit to the l_credit variable and update the customer with the newly adjusted credit.

If you enter the customer id 100 and credit limit 10000, the credit limit of the customer will be updated to the average credit.

SELECT * FROM customers 
WHERE customer_id = 100;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned how to use the PL/SQL RAISE statement to explicitly raise a user-defined exception, internally defined exception, and reraising an exception.

PL/SQL : What is Trigger ? and Types of Triggers ?

 Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

• A database definition (DDL) statement (CREATE, ALTER, or DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes −

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

Creating Triggers

The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Where,

• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.

• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.

• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

• [OF col_name] − This specifies the column name that will be updated.

• [ON table_name] − This specifies the name of the table associated with the trigger.

• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.

• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

• WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Example

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values −

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/ 

When the above code is executed at the SQL prompt, it produces the following result −

Trigger created.

The following points need to be considered here −

• OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.

• If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

• The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 ); 

When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −

Old salary: 
New salary: 7500 
Salary difference:

Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table −

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2; 

When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result −

Old salary: 1500 
New salary: 2000 
Salary difference: 500 

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

• Classification based on the timing
  • BEFORE Trigger: It fires before the specified event has occurred.
  • AFTER Trigger: It fires after the specified event has occurred.
  • INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for DML )
• Classification based on the level
  • STATEMENT level Trigger: It fires one time for the specified event statement.
  • ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)
• Classification based on the Event
  • DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
  • DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
  • DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

So each trigger is the combination of above parameters.

How to Create Trigger

Below is the syntax for creating a trigger.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

DWH Type of Dimension Tables in dataware Housing

 

Types of Dimensions

 

Dimension

A dimension table typically has
two types of columns, primary keys to fact tables and
textual\descriptive data.

Eg: Time, Customer

 

 

Types of Dimensions

 

1.         
Slowly Changing Dimensions

2.         
Rapidly Changing Dimensions

3.         
Junk Dimensions

4.         
Inferred Dimensions

5.         
Conformed Dimensions

6.         
Degenerate Dimensions

7.         
Role Playing Dimensions

8.         
Shrunken Dimensions

9.         
Static Dimensions 

 

Slowly
Changing Dimensions

Dimension attributes that change slowly
over a period of time rather than changing regularly is grouped as SCDs.
 Attributes like name, address can change but not too often.

These attributes can change over a
period of time and that will get combined as a slowly changing dimension.
Consider an example where a person is changing from one city to another. Now
there are 3 ways to change the address;

Type 1  is to over write the old
value, Type 2 is to add a new row and Type 3 is to create a new column.

Type 1

The advantage of type 1 is that it is
very easy to follow and it results  in huge space savings and hence cost
savings. The disadvantage is that no history is maintained.

Type 2

The advantage of type 2 is that the
complete history is maintained. The only disadvantage lies in the huge space
allocation because the entire history right from the start has to be
maintained.

 

Type 3

The best approach could be to add a new
column where you add two new columns. In this case keeping a tracking of the
history becomes very easy.

 

Rapidly Changing Dimensions[fast changing dim]

A dimension attribute that changes
frequently is a rapidly changing attribute. Fast changing dimensions are those
dimensions if one or more of its attributes changes frequently and in many
rows. A fast changing dimension can grow very large if we use the Type-2
approach to track numerous changes. These dimensions some time called rapidly
changing dimensions.

Examples of fast changing dimensions are

Age

Income

Test score

Rating

Credit history score

Customer account

status

Weight

 

Junk Dimensions

A junk dimension is a single table with
a combination of different and unrelated attributes to avoid having a large
number of foreign keys in the fact table. Junk dimensions are often created to
manage the foreign keys created by rapidly changing dimensions.

 

Inferred Dimensions (late coming dimension)

 

While loading fact records, a dimension
record may not yet be ready. One solution is to generate a surrogate key with
null for all the other attributes. This should technically be called an
inferred member but is often called an inferred dimension.

 

Conformed Dimensions

A dimension that is used in multiple
locations is called a conformed dimension. A conformed dimension may be used
with multiple fact tables in a single database, or across multiple data marts
or data warehouses.

 

For example, there are two fact tables. Fact table 1 is to
determine the number of products sold by geography. This table will calculate
just the number of products by geography and fact table 2 will determine the
revenue generated by customer. Both are dependent on the product which contains
product Id, name and source.

 

There is the geography dimension and customer dimension which are
being shared by two fact tables. The revenue fact gives the revenue generated
by both the geography and the customer, while the product units fact gives
number of units sold in the geography to a customer.

 

Degenerate Dimensions

 

A degenerate dimension is when the
dimension attribute is stored as part of fact table, and not in a separate
dimension table. These are essentially dimension keys for which there are no
other attributes. In a data warehouse, these are often used as the result of a
drill through query to analyze the source of an aggregated number in a report.
You can use these values to trace back to transactions in the OLTP system.

 

Role Playing Dimensions

A role-playing dimension is one where
the same dimension key — along with its associated attributes — can be joined
to more than one foreign key in the fact table. For example, a fact table may
include foreign keys for both ship date and delivery date. But the same date
dimension attributes apply to each foreign key, so you can join the same
dimension table to both foreign keys. Here the date dimension is taking
multiple roles to map ship date as well as delivery date, and hence the name of
role playing dimension.

 

 

Shrunken Dimensions

 

A shrunken dimension is a subset of
another dimension. For example, the orders fact table may include a foreign key
for product, but the target fact table may include a foreign key only for
productcategory, which is in the product table, but much less granular.
Creating a smaller dimension table, with productcategory as its primary key, is
one way of dealing with this situation of heterogeneous grain. If the product
dimension is snowflaked, there is probably already a separate table for
productcategory, which can serve as the shrunken dimension.

Static Dimensions

Static dimensions are not extracted
from the original data source, but are created within the context of the data
warehouse. A static dimension can be loaded manually — for example with status
codes — or it can be generated by a procedure, such as a date or time
dimension.