All about triggers


All about triggers

Triggers in Oracle:

A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Types of Triggers:

1] Application Triggers:

Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

2] Database Triggers:

These triggers are fired whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.

There are four types of database triggers:

  • Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event.
  • View-level triggers define what can be done to the view.
  • Database-level triggers can be activated at startup and shutdown of a database.
  • Session-level triggers can be used to store specific information.

Benefits of Database triggers:

  • Improved data security:

          – Provide enhanced and complex security checks

          – Provide enhanced and complex auditing

  • Improved data integrity:

         – Enforce dynamic data integrity constraints

         – Enforce complex referential integrity constraints

         – Ensure that related operations are performed together implicitly

Creating DML Triggers:

A triggering statement contains:

1] Trigger timing:

BEFORE: Execute the trigger body before the triggering DML event on a table.

AFTER: Execute the trigger body after the triggering DML event on a table.

INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

2] Triggering event:

Triggering user event tells which DML statement causes the trigger to execute? You can use any of the following:

• INSERT

• UPDATE

• DELETE

3] Trigger type:

Trigger type tells should the trigger body execute for each row the statement affects or only once?

• Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all.

• Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.

4] Trigger body:

Trigger body tells what action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.

PL/SQL Trigger Execution Hierarchy:

The following hierarchy is followed when a trigger is fired.


1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This event will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

Syntax of 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)
 BEGIN
   --- sql statements
 END;

  • CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF col_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • [ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as : old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Example of DML Statement Trigger:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
    (TO_CHAR(SYSDATE,'HH24:MI')
    NOT BETWEEN '08:00' AND '18:00')
    THEN RAISE_APPLICATION_ERROR (-20500,'You may
                  insert into EMPLOYEES table only during business hours.');
END IF;
END;

What are conditional predicates?

When creating a database trigger, several triggering events can be combined into one by using the INSERTING, UPDATING, and DELETING conditional predicates within the trigger body. In this way, a user can create a trigger that runs a different code based on the type of the triggering statement that fires the trigger. For example, conditional predicates can be used to create a database trigger that restricts all data manipulation events (INSERT, UPDATE, DELETE, etc.) on a table to certain business hours Monday through Friday.

Example of using conditional predicates in triggers:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')

THEN

IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,'You may delete from
EMPLOYEES table only during business hours.');

ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');

ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');

ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');

END IF;

END IF;
END;

What are OLD and NEW qualifiers?

The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.

The OLD and NEW qualifiers can be used only with row triggers. They cannot be used with statement triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.

Example of using OLD and NEW qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

     INSERT INTO audit_emp_table (user_name, timestamp,
     id, old_last_name, new_last_name, old_title,
     new_title, old_salary, new_salary)
     VALUES (USER, SYSDATE, :OLD.employee_id,
     :OLD.last_name, :NEW.last_name, :OLD.job_id,
     :NEW.job_id, :OLD.salary, :NEW.salary );
END;

INSTEAD OF trigger:

The Oracle INSTEAD-OF trigger has the ability to update normally non-updateable views. Simple views are generally updateable via DML statements issued against the view. However, when a view becomes more complex it may lose its “updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.

INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.

If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.

Restrictions on INSTEAD OF Triggers

  • INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
  • You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.

Example of INSTEAD OF trigger:

In this example, an order_info view is created to display information about customers and their orders:

CREATE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o
   WHERE c.customer_id = o.customer_id;

Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.

CREATE OR REPLACE TRIGGER order_info_insert
	INSTEAD OF INSERT ON order_info
DECLARE
	  duplicate_info EXCEPTION;
 	 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
 	 INSERT INTO customers
   	 (customer_id, cust_last_name, cust_first_name)
 	 VALUES (
 	:new.customer_id,
 	:new.cust_last_name,
  	:new.cust_first_name);

	INSERT INTO orders (order_id, order_date, customer_id)
	VALUES (
  	:new.order_id,
  	:new.order_date,
  	:new.customer_id);

EXCEPTION
  	WHEN duplicate_info THEN
    	RAISE_APPLICATION_ERROR (
     	num=> -20107,
      	msg=> 'Duplicate customer or order ID');

END order_info_insert;

You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):

INSERT INTO order_info VALUES
   (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);

 

Difference between Database Triggers and Stored Procedures:

Database Triggers:

  • Database triggers are defined with CREATE TRIGGER statement.
  • The data dictionary that contains the source code is USER_TRIGGERS.
  • They are implicitly invoked
  • COMMIT, SAVEPOINT, and ROLLBACK are not allowed.
  • We cannot pass parameters to Database triggers and they cannot return a value.

Stored Procedures:

  • Stored Procedures are defined with CREATE PROCEDURE statement.
  • The data dictionary that contains the source code is USER_SOURCE.
  • They are explicitly invoked.
  • COMMIT, SAVEPOINT, and ROLLBACK are allowed.
  • We can pass parameters to Stored procedures and they can return a value.

How to know Information about Triggers:

We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.

The below statement shows the structure of the view ‘USER_TRIGGERS’

 DESC USER_TRIGGERS; 

TRIGGER_NAME — Name of the trigger
TRIGGER_TYPE  — The type is BEFORE, AFTER, INSTEAD OF
TRIGGER_EVENT — The DML operation firing the trigger
TABLE_OWNER   — Owner of the table
BASE_OBJECT_TYPE  — Object type
TABLE_NAME    — Name of the database table
COLUMN_NAME – Name of the Column
REFERENCING_NAMES  — Name used for :OLD and :NEW
WHEN_CLAUSE     — The when_clause used
STATUS           — The status of the trigger
DESCRIPTION  — Description
ACTION_TYPE  — Type of action
TRIGGER_BODY — The action to take

We can see PL/SQL Syntax errors (compilation errors) in the ‘USER_ERRORS’ data dictionary view

Managing triggers:

  • Disable or reenable a database trigger:

             ALTER TRIGGER trigger_name DISABLE | ENABLE

  • Disable or reenable all triggers for a table:

             ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

  • Recompile a trigger for a table:

           ALTER TRIGGER trigger_name COMPILE

  • To remove a trigger from the database, use the DROP TRIGGER syntax:

           DROP TRIGGER trigger_name;

Oracle mutating trigger table errors:

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message.

How to solve it?

Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.  While the object-oriented Oracle provides “methods” that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.

Use an “after” trigger – If you must use a trigger, it’s best to avoid the mutating table error by using an “after” trigger. For example, using a trigger “:after update on xxx”, the original update has completed and the table will not be mutating.

Use autonomous transactions – You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.

Exception Handling in Oracle


Exception Handling in Oracle

What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.

An exception is an identifier in PL/SQL that is raised during the execution of a block that terminates its main body of actions. A block always terminates when PL/SQL raises an exception, but can you specify an exception handler to perform final actions.

Types of Exception

There are 3 types of Exceptions.

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions (or Predefined Oracle Server Exceptions) and Unnamed System Exceptions (or Nonpredefined Oracle Server Exceptions) are implicitly raised.

User-defined Exceptions are explicitly raised.

Structure of Exception Handling

DECLARE
   Declaration section
BEGIN
   Exception section
EXCEPTION
  WHEN ex_name1 THEN
     -Error handling statements
  WHEN ex_name2 THEN
     -Error handling statements
  WHEN Others THEN
     -Error handling statements
END;


You can trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised.

The exception-handling section traps only those exceptions that are specified; any other exceptions are not trapped unless you use the OTHERS exception handler.

Exceptions Trapping Rules:

  • Begin the exception-handling section of the block with the EXCEPTION keyword.
  • You can define several exception handlers, each with its own set of actions, for the block.
  • When an exception occurs, PL/SQL processes only one handler before leaving the block.
  • Place the OTHERS clause after all other exception-handling clauses.
  • WHEN OTHERS is the last clause and you can have only one OTHERS clause.

a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

Note: PL/SQL declares predefined exceptions in the STANDARD package.

Few Predefined Exceptions:

  • NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.
  • TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.
  • ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.
  • CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.
  • INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.
  • INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
  • DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.
  • LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.
  • NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.
  • VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.

Named system exceptions are:

  • Not declared explicitly.
  • Raised implicitly when a predefined Oracle error occurs.
  • Caught by referencing the standard name within an exception-handling routine.

For Example:

BEGIN
  Execution section
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;


 

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed system exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.

Steps to be followed to use unnamed system exceptions are

  • They are raised implicitly.
  • If they are not handled in WHEN Others they must be handled explicitly.
  • To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given above and handled referencing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE
   exception_name EXCEPTION;
   PRAGMA
   EXCEPTION_INIT (exception_name, Err_code);
BEGIN
   Execution section
EXCEPTION
   WHEN exception_name THEN
     Handle the exception
END;


For Example:

Let’s trap for Oracle server error number –2292, which is an integrity constraint violation.

1] Declare the name for the exception within the declarative section.

exception EXCEPTION;

Where: exception is the name of the exception.

2.] Associate the declared exception with the standard Oracle server error number using the

PRAGMA EXCEPTION_INIT statement.

PRAGMA EXCEPTION_INIT(exception, error_number);

Where: exception is the previously declared exception.

error_number is a standard Oracle Server error number.

3] Reference the declared exception within the corresponding exception-handling routine.

DEFINE p_deptno = 10
DECLARE
     e_emps_remaining EXCEPTION;
     PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292);
BEGIN
     DELETE FROM departments
     WHERE department_id = &p_deptno;
     COMMIT;
EXCEPTION
     WHEN e_emps_remaining THEN
     DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
     TO_CHAR(&p_deptno) || '. Employees exist. ');
END;


 

c) User-defined Exceptions

Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

For Example:

DECLARE
     e_invalid_department EXCEPTION;
BEGIN
    UPDATE departments
    SET department_name = '&p_department_desc'
    WHERE department_id = &p_department_number;
    IF SQL%NOTFOUND THEN
      RAISE e_invalid_department;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_invalid_department THEN
    DBMS_OUTPUT.PUT_LINE('No such department id.');
END;


 

Functions for Trapping Exceptions:

When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide which subsequent action to take based on the error.

SQLCODE: Returns the numeric value for the error code. 

SQLERRM: Returns the message associated with the error number.

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:

DECLARE
    err_num NUMBER;
    err_msg VARCHAR2(100);
BEGIN
    ...
EXCEPTION
    ...
  WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
    INSERT INTO errors VALUES (err_num, err_msg);
END;


 

RAISE_APPLICATION_ERROR ( ) :

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

 
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.

RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The returned error is consistent with how the Oracle server produces a predefined, nonpredefined, or user-defined error. The error number and message is displayed to the user.

Executable section:

BEGIN
...
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager');
END IF;
...


 

Exception section:

...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.');
END;


 

What is Exception propagation in Oracle? 

Exceptions which are not handled in a sub block get propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exception occurs in the outermost block, then the whole program gets terminated.

Know ASSAM: My Motherland


Know ASSAM: My Motherland

Assam…. a land of natural beauties, lush green, rivers, lakes, hills, animals -is a gateway to the north East India. It is a land of unexplored opportunities, gifted with abundant natural resources. It is one of the major cities of the North East India. Amazingly Assam is a multi- ethnic society and the mother tongue of the people of Assam is Assamese.

Assam is known as the Land of Evergreen Forest. The scenic beauty of the state can not be described in words. It is situated to the North-Eastern part of India. At the beginning, Assam was known by the name Kamrup when, along with Assam all other North-Eastern states of India were under a single border. But with time all the seven sisters namely Nagaland, Manipur, Mizoram, Arunachal Pradesh, Meghalaya, and Tripura got separated.

Assam can not be called a hilly state; neither can it be termed as a plain. Some parts of the state are hilly whereas others are plains. But all through the state, there is an eye-catching array of trees making the state green. The River Brahmaputra, which is also known as Kameng in Arunachal Pradesh, passes through the entire length of Assam to the Bay of Bengal dividing the state to almost equal halves, enriching the soil and beauty of the state.

WHY I AM SPEAKING ABOUT ASSAM:

Most of the times when I chat with my friends, they ask me a common question, where Assam is? I just reply them that it is towards the North-east of India & is known for its great one horned rhino’s & tea. It is also the land which houses the world’s largest river delta, Majuli. But it is somewhat annoying to answer the same question to everyone whom I met in my course of chatting. It is because, Assam is yet to gain a place in the World Map, and that’s what depresses me. Though our homeland is rich with its mesmerizing natural beauty, historical monuments, few world heritage sites and some National Parks and sanctuaries, it is yet to attract the world tourists. For this reason, I thought of bringing up a picture of our homeland Assam before the world, especially to those who knows a little or nothing about our homeland & its culture. Moreover, everyone in this world loves his or her motherland and I am not an exception.

ASSAM:

Assam, the “Land of Evergreen Forest” or the “Gateway of North-East India”, has something special, something amazing, something celestial to offer. A voyage through this “Land of Red River” means to stir up your dormant poetic mind and free-flying like a kite in your imagination. The beauty of the nature alone is enough to enthrall you with romance & mystic satisfaction. Some of the tourists who visit our state call it a Green Paradise whereas the natives call it a Magic Land.

LOOKING BACK TO THE NAME:

The name of the state may be derived from the Sanskrit word “ASOM”, which means uneven, undulated or not leveled. The name may be due to the rugged surface of the state. The numerous hills and rivers make the surface of our state rugged. Some claims the ruling of AHOMS for a long tenure to be the reason for the name of our state. The Ahoms ruled Assam, for around 600 years until the early part of the 19th century. The words Ahom & Asom resembles phonetically to a greater extent thus the possibility of conferring the name Asom to our homeland.

LOCATION IN THE WORLD MAP:

Assam is located to the North-Eastern part of India. It has a surface area of 78,438 square kilometers (30,285 square miles). Assam is bounded to the North by the Kingdom of Bhutan & the state of Arunachal Pradesh; to the East by the states of Nagaland & Manipur; to the South by the states of Mizoram & Tripura and to the West by the states of Meghalaya, West Bengal & the Bangladesh.

ABOUT THE PEOPLE:

Our state is in fact a land of exquisite craftsmanship of Lord Vishwakarma pouring all his emotions while creating the land. Assam is the land of cultural richness & human wealth apart from her natural beauty. The land has a rich legacy of culture and human civilization behind her. Our Motherland is a home to different races of men – Aryan, Aurtric, Dravidian & Mongolians, who ventured different part of our hills in different points of time. Therefore, our homeland has developed a composite culture of variegated color.

LITTLE MORE ABOUT THE STATE:

Assam, along with its native states (the SEVEN SISTERS, as known to be), has evidences of human settlement from the stone ages itself. It is a land with an illustrious recorded history as early as the 4th century BC. Our homeland was an independent kingdom throughout its history till the end of first quarter of the 19th century, when the British conquered the Kingdom and annexed it to British India, called the Bengal Presidency. In between 1905-1912, Assam was separated & erected as a separate Province of Assam. During the Indian Independence, Assam constituted of all the states of the present North-East, excluding Manipur & Tripura. However, regional cultural variations were too distinct for the entire land to stay clubbed under a single political administration. This led to the separation of states from erstwhile Assam, one after another, starting with Nagaland in the year 1963, than Meghalaya & Mizoram in the year 1971 and finally Arunachal Pradesh in the year 1972.

The state capital of Assam, which was shifted from Shillong while united to Dispur at Guwahati, is under the Kamrup District. Guwahati was known in ancient times as Pragjyotishpura or The Eastern City of Light, was the capital of Kamrup, which finds frequent mention in the Great Hindu Epic the Mahabharata & other Sanskrit volumes and historical lore’s.

NOW LITTLE ABOUT THE IMPORTANT PLACES:

Kaziranga National Park:

Kaziranga National Park, The WORLD HERITAGE SITE, holds the worlds only one horned rhinos, being the natural home for these endangered species. The other important wildlife at Kaziranga includes wild buffaloes, hornbills and a number of free flying birds during the winter season. At the present time, i.e.; from sixth of February, an elephant festival is undergoing at this World Heritage Site, which one might not have heard in their lifetime. Hundreds of domestic elephants are marched to this beautifying homeland of one-horned rhinos and a numbers of wildlife just to show the harmony of people across the globes. There is a tourist village around the Kaziranga National Park to house those visiting tourists from across the globe. The visitors experience a complete Assamese lifestyle by living in a mud hut or thatch hut with pure Assamese dishes, which are known for free from oils and artificial spices.

The ride through the forests over the back of the elephant or an Elephant Safari across the jungle will give you a number of opportunities to encounter wildlife grazing or jumping around playfully, much nearer to click with your digital camera. Just imagine the natural appeal you will find when free flying birds will sing in their unique rhythm with their ear touching voices at the time of sunset over the water spread area inside the forest. The sun slips gently in to the water spreads and you can touch the image over the surface while sailing on small boats, along with the varied birds swimming around you.

Manas National Park:

Some of the one horned rhinos are shifted to this National Park cum Sanctuary, a World Heritage site too, because of the flood de-homing the inhabitants of Kaziranga. I forgot to talk about the wild deer and other species of animals found at Kaziranga, which are also found in this wonderland.

Dibru-Saikhowa National Park:

It is the house of wild horses. It is believed that, after British left Assam, they did not carried their horses and left them open in this Forest, which with time acquired the name of wild horses. I am not sure how far this is true.

Tea Belt of Upper Assam:

Assam has been divided as Upper Assam and Lower Assam with the height of the land. The Upper Assam has a large tea belt. You can smell the ascent of tea across the greens spread up to your HORIZON. Whichever way you may through your gaze, your eyes would be full of green bushes of tea of varied age groups. India’s 60 percent of tea production is met by my Homeland and yet it is neglected by the center.

Assam is one of the major exporters of Orthodox Tea and its major consumer is United Kingdom. One can say that every household of Upper Assam owns a tea garden.

LITTLE HISTORY ABOUT SETTING UP OF TEA GARDENS:

While British were ruling India, they had been attracted by the wilderness and the scenic beauty of this State and started establishing their shelter over here. They took up tea plantation in the rich soil of Assam Valley and that was the beginning of Tea Plantation in our part of the Nation. Whatever Tea Gardens they had started, they are still continuing to operate in Assam.

The river Brahmaputra, which flows through the entire length of the state to the Bay of Bengal, has enriched the soil for cultivation of varied agricultural products. The land is rich with natural manures and so people used to be healthier for their life time in our part of the country. There were sufficient natural resources for the people of the state. But, due to influx of un-authorized immigrants from neighbouring Bangladesh has spoiled a lot of our natural beauty. The trees has been cut for consumption making the hills dry and the bank of Brahmaputra has been occupied to build refugee houses by these unauthorized immigrants.

Oil and Natural Gases:

Assam is also known for its store of oil and natural gases. There are a number of oil fields across the state which has an enormous production capacity. Moreover, there are coal fields too. Although these resources are available in our state, we are deprived of their revenues as they are drained out of our state to contribute to the Central Revenue of which we get a very little share.

Historical Monuments:

There are a numbers of historical monuments across the state and of those the ones that are widely popular are Jaidaul, Shivadaul, Rong Ghar, Kareng Ghar, and Talatal Ghar, all speaking the history of Ahom Rule in Assam. The main pillar of Assamese community was built by Mahapurush Srimanta Shankardeva. His place of “Kirton”, Bordowa is a place for pilgrims. Moreover, there is a single hill in the Districts of Goalpara, which stocks in it more than one lakh of idols of different God and Goddess of Hindu Mythology. River Brahmaputra has the worlds largest Delta in it, The MAJULI, which itself is a dreamland, but due to ignorance of Government, the island is eroding itself to the heart of Brahmaputra.

FESTIVAL:

There are a number of tribes and sub-tribes in Assamese community with distinct and varied cultural differences, yet uniting together in harmony in this beautiful land. But, all together are tagged to Assamese Community and the major festival of this community is BIHU. There are three types of Bihu’s called Magh Bihu or Bhogali Bihu as this festival is celebrated after harvesting of crops in the month of January. The next is Kati Bihu or Kangali Bihu, which is celebrated in a famine stage of the land. There will be scarcity of feeds and all and the earth will appear dull during that season. It is celebrated in the months of October.

The most celebrated and well known Bihu festival of Assamese Community is the Bohag Bihu or Rangali Bihu, which falls in the month of April. This festive season lasts for a month and is celebrated extensively all across the State and over at many places of the world among the Assamese Community. It is the festival of Love and Romance. The minds of people get romanticized during this season. The Bihu is marked by a dance called Bihu Dance, which borne the image of Assamese people in the cultural world. The dance is very attractive and does not have any fixed constraints except for the dress code. There is a special dress for the Bihu Dancers, which are knitted out of MUGA SILK (the latest fashion sensation of world designers). The lips of the dancers should be painted in red and they wore an orchid in their hairs. (Native Name of the orchid is KOPOU FUL).

If I keep on telling about my motherland, I would never end up. As there are no established tourist projects to showcase our state, we don’t get many tourists except for the Annual Elephant Festival at Kaziranga during the month of February and to JAATINGA, the bird mystery of the globe during winter months. Some tourists slip in to the river delta Majuli, which is the house of Assamese Culture, to know the Assamese Community better. Moreover, there is nearby Arunachal Pradesh, which in turn is known for its Natural Beauty, and is called as the Switzerland of India. Shillong has the Mousinram, where you can push the clouds to make out your way ahead in the world’s highest rainfall area.

I am happy that I am a child of this magnificent state of Greens and Invite you all to drink the scenic beauty, intoxicating yourself in the free-floating romance, all round you, amid the hills, tea gardens, butterflies, un-identified birds, clean and clear rivers, wilderness of animals, and most importantly in the heart of innocent, hospitable Assamese People.

N.B. Reference from Internet.

Images from Assam Tourism

Mulshi Dam: Amidst greenery, hills, lake and waterfalls!


Mulshi Dam: Amidst greenery, hills, lake and waterfalls!

I recently visited a place called Mulshi which is in the outskirts of Pune, India. The place was awesome with full of greenery, hills, lake, waterfalls etc. It was a one and half hour drive from Pune on bike. The roads are bit narrow but are surrounded by all natural beauties. It was a great relaxation in the weekends.

Mulshi is to the west of Pune and close to Lonavala as well as Konkan region. It is the hilly region of Pune, with one of the biggest mountain ranges in Maharashtra i.e. Sahyadri.

Mulshi dam and the surroundings are full of natural beauties that include a dam, hilly region of Sahyadri’s deep forests and forts like Dhangad and Koraigadh. Water of Mulshi is used for generating electricity. It is one of the major electricity projects in Maharashtra.

It is the best place for birdwatchers and photographers. Especially during the rainy season the lush green area becomes an irresistible temptation.

If you wish to spend some quiet moments in your life in the company of greenery and scenery and if you want to set out on a one day picnic around Pune, then Mulshi Dam is the perfect destination for you.

Mulshi is very near to Pune and a short trip here can be arranged. One must enjoy the marvelous lake, the dam, forest and hilly region of Mulshi. If you are lucky then you can enjoy boating here as well. Thus, it is a newly developing picnic spot near Pune.

 

Script to get all the Concurrent Program Request details


Script to get all the Concurrent Program Request details

Below is the script to get the Concurrent Program Request details by Various Users in a Particular Day.

select
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name Request_Name,
    fcpt.user_concurrent_program_name program_name,
    DECODE(fcr.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running') phase,
    DECODE(fcr.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A') status,
    round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
    round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) * 1440),2) wait_time,
    fu.User_Name Requestor,
    fcr.argument_text parameters,
    to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
    to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,
    to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,
    fcr.completion_text

From

    apps.fnd_concurrent_requests fcr,
    apps.fnd_concurrent_programs fcp,
    apps.fnd_concurrent_programs_tl fcpt,
    apps.fnd_user fu

Where 1=1
    -- and fu.user_name = 'DJKOCH' '
    -- and fcr.request_id = 1565261
    -- and fcpt.user_concurrent_program_name = 'Payables Open Interface Import''
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcp.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.program_application_id = fcp.application_id
    and fcp.application_id = fcpt.application_id
    and fcr.requested_by = fu.user_id
    and fcpt.language = 'US'
    and fcr.actual_start_date like sysdate
    -- and fcr.phase_code = 'C'
    -- and hold_flag = 'Y'
    -- and fcr.status_code = 'C'

GROUP BY
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name,
    fcr.requested_start_date,
    fu.User_Name,
    fcr.argument_text,
    fcr.actual_completion_date,
    fcr.actual_start_date,
    fcr.phase_code,
    fcr.status_code,
    fcr.resubmit_interval,
    fcr.completion_text,
    fcr.resubmit_interval,
    fcr.resubmit_interval_unit_code,
    fcr.description

Order by 1 desc;

ABOUT INTERFACES


 

ABOUT INTERFACES:

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.

A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are

• Transfer Program

• Interface Table and

• Import Program

A] Transfer Program:

If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.

What they do?

  • It maps the columns of source table with the columns of Interface Tables.
  • It performs Row Level and Column Level validations.
  • It transfers the data from Source to the Interface Table.

B] Interface Tables:

The Interface tables basically have 4 types of columns.

  1. Mandatory Columns.
  2. Conditionally Required Columns.
  3. Optional Columns.
  4. Internal Processing Columns.

Mandatory Columns:

These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.

Conditionally Required Columns:

The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.

Optional Columns:

These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.

Internal Processing Columns:

Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.

C] Import Program:

For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.

What they do?

  • It maps the columns of the Interface Table with one or more columns in the destination table.
  • It performs row level and column level validation.
  • It imports the data from Interface Table to the Destination tables, if the records validated successfully.
  • It deletes all the successfully validated records from Interface Table.
  • If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.

Interface Vs. Application Program Interface (API):

Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.

Previous Post:

 

Invoking SQL*Loader


Invoking SQL*Loader

On Unix systems, the command used to invoke SQL*Loader is sqlldr. On Windows systems running Oracle8i, release 8.1 or higher, the command is also sqlldr.

The command to execute SQL*Loader is as follows:

sqlldr USERID = <username>/<password> CONTROL = <control file name> LOG = <log file name>….

Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed. Command-line parameters are usually keyword/value pairs, and may be any combination of the following:

USERID = {username[/password] [@net_service_name]|/} Specifies the username and password to use when connecting to the database.

CONTROL = control_ file_name  Specifies the name, which may include the path, of the control file. The default extension is .ctl.

LOG = path_ file_name Specifies the name of the log file to generate for a load session. You may include a path as well. By default, the log file takes on the name of the control file, but with a .log extension, and is written to the same directory as the control file.

BAD = path_ file_name Specifies the name of the bad file. You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a .bad extension, and is written to the same directory as the control file.

DATA = path_ file_name Specifies the name of the file containing the data to load. You may include a path as part of the name. By default, the name of the control file is used, but with the .dat extension.

DISCARD = path_ file_name Specifies the name of the discard file. You may include a path as part of the name. By default, the discard file takes the name of the control file, but it has a .dis extension.

Other Parameters are:

DISCARDMAX=logical_record_count

SKIP=logical_record_count

SKIP_INDEX_MAINTENANCE={TRUE | FALSE}

SKIP_UNUSABLE_INDEXES={TRUE | FALSE}

LOAD=logical_record_count

ERRORS=insert_error_count

ROWS=rows_in_bind_array

BINDSIZE=bytes_in_bind_array

SILENT=[(]keyword[,keyword...][)]

DIRECT={TRUE | FALSE}

PARFILE=path_file_name

PARALLEL={TRUE | FALSE}

READSIZE=bytes_in_read_buffer

FILE=database_datafile_name

My Previous Posts:

Google has Done it Again !


Google has Done it Again !

Google is well known for making searching everything in life easier. They launched a new way of searching called Google goggles, the app is based on using pictures to get information. The application runs the picture you take through Google’s database and then it looks for a match and returns that to your device all in good time. The information is then turned into a search and there you go, the name of that huge build, right on your phone.  The best part of the app is it uses your GPS and Compass information to better tell you what it is.

Here is the press release: 

When you connect your phone’s camera to datacenters in the cloud, it becomes an eye to see and search with. It sees the world like you do, but it simultaneously taps the world’s info in ways that you can’t. And this makes it a perfect answering machine for your visual questions.

Perhaps you’re vacationing in a foreign country, and you want to learn more about the monument in your field of view. Maybe you’re visiting a modern art museum, and you want to know who painted the work in front of you. Or maybe you want wine tasting notes for the Cabernet sitting on the dinner table. In every example, the query you care about isn’t a text string, or a location — it’s whatever you’re looking at. And today we’re announcing a Labs product for Android 1.6+ devices that lets users search by sight: Google Goggles.

Guess what you can do with a touch screen, camera, scanner, WiFi, and google maps .View a building through it, and it gives you all the information about that building.

  

Choose a building and touch a floor and it tells you more details of the building. You can use it when you want to know a car model, an insect name, what kind of food is served at a restaurant and how much, who built a bridge, etc. etc.

It’s got a scanner built in.

So you can use it this way when you want to check the meaning of a word in the newspaper, book, magazine, etc. It would be much easier to read a real book. You can use the dictionary, wikipedia, thesaurus and anything else available on the web. What do you think?


Indoor guide:Works in a building, airport, station, hospital, etc.

Automatic simultaneous translation: here Latin to English.

Search keyword: Helpful when you want to find out a word from a lot of text in newspaper/book.

Nutrition: This kind of function would be helpful for health freaks..

 

SQL*Loader Environment


SQL*Loader Environment

SQL*Loader takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.

Execution Steps:

Step1    Executes from the command prompt or parameter file.

Step2    Reads the control file.

Step3    Reads the data from the control file or from one or more datafiles.

Step4    Loads the data in the database and logs the information in the log file.

Step5    Places the rejected records in the bad file.

Step6    Places the discarded records in the discard file.

1] The parameter file:

The command line information can be saved in a parameter file. This parameter file is executed from the command prompt. A parameter file has a .par extension. Following is a sample parameter file.

Steps to create a parameter file:

  • Open a text editor.
  • Type in each parameter with its corresponding value as shown in the parameter file.
  • Save the file with the .par extension.

How do you execute the parameter file?

At the command prompt type sqlldr PARFILE = <parfile name>.

2] The Control File:

  • The control file is a text file written in a language that SQL*Loader understands.
  • The control file describes the task that the SQL*Loader is to carry out. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more.
  • It also contains the names and locations of the bad file and the discard file.
  • Some of above information (such as name and location of the input file) can also be passed to SQL*Loader as command-line parameters.
  • It’s also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around.

 A sample control file is given below:

In general, the control file has three main sections, in the following order:

A] Session-wide information:

The session-wide information contains the names of the input/output files for the data load session. Apart from this, other SQL*Loader parameters can also be listed in this section.

  • The LOAD DATA statement is required at the beginning of the control file.
  • INFILE * specifies that the data is found in the control file and not in an external data file.
  • BADFILE ‘example1.bad’ indicates that all erroneous records must be stored in the file example1.bad.
  • DISCARDFILE ‘example1.dsc’ indicates that all discarded records must be stored in the file example1.dsc.

B] Table and Field_List Information:

The INTO TABLE statement specifies the table into which data should be loaded. In this case it is the dept table. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but can also be enclosed by quotation marks. Data types for all fields default to CHAR.

The names of columns to load are enclosed in parentheses. Because no data type or length is specified, the default is type CHAR with a maximum length of 255.

C] Input Data:

BEGINDATA specifies the beginning of the data. The data to be loaded is present below the BEGINDATA command.

3] Input Datafiles:

  • The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
  • The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.

A] Fixed Length Format:

A file is in the fixed record format when all the records in the datafile have the same byte length. This format is not flexible but offers very good performance.then the syntax for the INFILE command is – INFILE student.dat “fix 15”

The syntax for letting SQL*Loader know that the data is in the fixed length format is:

INFILE datafile_name “fix n”

Here INFILE datafile_name refers to the file that contains the data to be loaded. “fix n” implies that each record in the datafile has a fixed byte length of n.

For example if the name of the following datafile is student.dat and the byte length of a record is 15 bytes
0001, —–Rina, 0002, —-Harry, 0003,—–Sudha

B] Variable Length Format:

A file is in the variable record format when the length of each record varies. The length of each record is included at the beginning of the record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format.
For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:

INFILE “datafile_name” “var n”

Here n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. If n is specified larger than 40 it results in an error. The following datafile is random.dat and the value for n is 3.

009hello,cd,010world,im,
012my,name is,

SQL*Loader reads the first 3 bytes to gather the length of the record. Here the first record is 9 bytes long. After SQL*Loader has read 9 bytes, it reads the next 3 bytes to find the size of this record which is 10 bytes long. It reads the next 10 bytes of the record and then finds the third record is 12 bytes long and so on.

C] Stream Record Format:

A file is in the stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance. The syntax for specifying the stream record format is as follows:

INFILE datafile_name [“str terminator_string”]

The terminator_string can be a ‘char_string’  which is a string of characters enclosed in single or double quotation marks or a ‘hex_string’ which is a byte string in hexadecimal format.

4] The Log File:

The log file is a record of SQL*Loader’s activities during a load session. It contains information such as the following:

  • The names of the control file, log file, bad file, discard file, and data file
  • The values of several command-line parameters
  • A detailed breakdown of the fields and datatypes in the data file that was loaded
  • Error messages for records that cause errors
  • Messages indicating when records have been discarded
  • A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

5] The Bad File:

Whenever you insert data into a database, you run the risk of that insert failing because of some types of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.

  • If one or more records are rejected, the bad file is created and the rejected records are logged.
  • If no records are rejected, then the bad file is not created.

6] The Discard File:

While SQL*Loader is being executed it creates a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. A discarded record is never inserted into the Oracle table.

A discard file is created according to the following rules:

  • You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
  • If no records are discarded, then a discard file is not created.

May also like to read: