Oracle Database 11g Top New Features:

Oracle Database 11g Top New Features:
Database Replay
Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.
Transaction Management
Get an introduction to Flashback Data Archive and explore Enterprise Manager's Log Miner interface.
SQL Plan Management
Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.
SQL Access Advisor
Get advice about optimal table design based on actual use of the table, not just data.-->
RMAN
Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.
Automatic Storage Management
Learn about new SYSASM role, variable extent sizes, and other ASM improvements
Schema Management
Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.
SQL Performance Analyzer
Accurately assess the impact of rewriting of SQL statements and get suggested improvements. --> -->
Manageability
Explore automatic memory management, multicolumn statistics, online patching, and more features.
PL/SQL: Efficient Coding
Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.

About : Trigger Creation

Hi,

Try to answer the following question.

Is it possible to create a trigger using “CREATE OR REPLACE TRIGGER TRIGGER_NAME”, where the trigger_name is already being used by another trigger under some other table ?. If your answer is YES, then this article may help you.

In general we used to say that, if we create a trigger using Create or replace statement, then the trigger will be altered with the new trigger code. But if use CREATE OR REPLACE statement to create a trigger and if the trigger name is already being used by a trigger under another table, then ORACLE will raise an ORA-04095 exception.

The following code will give you some more clarity on the above explanation:

Table 1 Creation :

create table trig1(id number(3));

Trigger 1 creation for the above table :

create or replace trigger trg_trig1

before insert on trig1

begin

null;

end;

/

Table 2 Creation :

create table trig1(id number(3));

Trigger 2 creation for the above table :

create or replace trigger trg_trig1

before insert on trig2

begin

null;

end;

/

create or replace trigger trg_trig1

*

ERROR at line 1:

ORA-04095: trigger 'TRG_TRIG1' already exists on another table, cannot replace

It

Explaination :

Thus the above code creates two table called trig1 and trig2. Trig1 has a before insert trigger called trg_trig1.Then we have the code for trg_trig1 trigger on trig2. Oracle doesn’t allow you to create the trigger by raising ORA-04095 exception.

About - Mutating and Constraining table in Oracle

Hi,

I would like to share, some details on “Mutating and Constraining table in Oracle”. Mutating error won’t occur when we go for statement level trigger. In general, it is said, that Mutating error will occur, if we refer the mutating table at the trigger body, in a row level trigger. There is an exceptional case, where the mutating error won’t occur, if we refer the mutating table in the trigger body. It is for before insert, at row level. The concept here is, if we perform a single row insert (by using Values clause), and if we refer the same table (the mutating table) at the trigger body, then we won’t get any mutating table error. Also the insert should be a single row one. So if you perform an multiple row insert(say : insert into tableA select * from table B), then Mutating error will occur. Also if multiple row insert statement, inserts only one record, we will get the mutating error. So in general, only for the before insert statement (with single row) at row level, won’t raise the mutating error.

The following table will explain you about the occurrences of mutating error.

Operation		Event / Trigger Type	Is Mutating
insert		before/statement-level	No
insert		after/statement-level	No
update		before/statement-level	No
update		after/statement-level	No
delete		before/statement-level	No
delete		after/statement-level	No
insert		before/row-level	Single row Multi-row
			No Yes
insert		after/row-level	Yes
update		before/row-level	Yes
update		after/row-level	Yes
delete		before/row-level	Yes
delete		after/row-level	Yes

Example :

Table Creation :

create table tab_trig(id number(3));

Trigger Creation:

create or replace trigger trig1

before insert on tab_trig

for each row

declare

v_mx tab_trig.id%type;

begin

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value : 'v_mx);

update tab_trig set id=:new.id+7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Incrementing Id by 7 : 'v_mx);

update tab_trig set id=:new.id-7;

select nvl(max(id),0) into v_mx from tab_trig;

dbms_output.put_line('Max Value After Decrementing Id by 7: 'v_mx);

end;

/

Sample Execution:

SQL> insert into tab_trig values(&id);

Enter value for id: 1

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(1)

Max Value : 0

Max Value After Incrementing Id by 7 : 0

Max Value After Decrementing Id by 7: 0

1 row created.

SQL> /

Enter value for id: 2

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(2)

Max Value : 1

Max Value After Incrementing Id by 7 : 9

Max Value After Decrementing Id by 7: -5

1 row created.

SQL> /

Enter value for id: 3

old 1: insert into tab_trig values(&id)

new 1: insert into tab_trig values(3)

Max Value : 2

Max Value After Incrementing Id by 7 : 10

Max Value After Decrementing Id by 7: -4

1 row created.

Oracle - About Inserting Date Values

We all know, that the default format support by Oracle (from 9i) is DD-Mon-RR.

Does this mean, we need to give the same format, while inserting records to the table?.

If your answer is Yes, then continue reading the article.

In general, oracle assumes that the date needs to be given in dd, followed by Mon followed by RR/YYYY.

So in between the data components, we are allowed to use any character. So the following insert statements

will insert records in to the table dat1(with 1 column, called col1 of type date), without any error.

Note: FYI, In all the cases, to_date( ) function is not used.

insert into dat1 values('12?Jan?2007')

insert into dat1 values('12/Jan?2007')

insert into dat1 values('12!Jan#2007')

insert into dat1 values('12$jan%2007')

insert into dat1 values('12^Jan&2007')

insert into dat1 values('12/Jan?2007')

SQL> select * from dat1;

COL1

---------

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

6 rows selected.