Global Temporary Tables

Global Temporary Tables

Global Temporary tables are session dependant tables which could be used as temporary storage for calculations, sorting etc. What I mean by Session dependant is, the data being stored in the Global Temporary table is not written into the database or stored anywhere. Once the session ends (in which the Global Temporary table is used), the data also vanishes.

However the structure would still be available even after the Session is logged out. Also, the structure is available to other sessions even when one session is using it, but not the data. i.e multiple sessions could use the same Global Temporary table without interfering the data. Each session could insert/update/delete their own data into the same Global Temporary table as if the table is available to only that session. Any data inserted in one session is not available to another.

Now, why do we need Global Temporary tables? Well, imagine a requirement where you need to fetch some data from the database, do some kind of calculations, aggregations and provide the Result Set (many records) to a Front End. Again, in the Front End, you need to fetch the Result set may times, for some purpose. Then you could make use of the Global Temporary table. Until the user gets disconnected from that Database session, the data is available for him in the memory.

Well, that depends on what you have instructed the Global Temporary table to do while a commit is begin issued. And depending on that

there are 2 different methods of creating a Global Temporary table.

1. ON COMMIT DELETE ROWS

CREATE GLOBAL TEMPORARY TABLE GTT_EX (

Var_int1 NUMBER,

Var_char2 VARCHAR2

) ON COMMIT DELETE ROWS;

This is just opposite of what happens to regular tables. If a Global Temporary table is created with ON COMMIT DELETE ROWS option, it removes all data when a commit is issued.

Now you might think why somebody needs such a table which removes all data when a commit happens. Well, sometimes you need a temporary table to do some kind of calculation on 3-4 columns, some kind of summation, then aggregation etc, record by record.

All these results, could be stored in the Temporary table column. And you need to fetch only once to get the whole data and store in some other Reporting Table (Target). So, after the calculation, you don t need the data in the Temporary table to be preserved anywhere, but you need the data in the Reporting table. Then, we make use of ON COMMIT DELETE ROWS Global Temporary table, such that the commit writes the Data for Reporting table into the DB and the data in Global Temporary table is removed.

2. ON COMMIT PRESERVE ROWS

CREATE GLOBAL TEMPORARY TABLE GTT_EX (

Var_int1 NUMBER,

Var_char2 VARCHAR2

) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS Global Temporary tables do not remove the data once a commit is issued. It keeps the data until the end of the session. Remember, preserve rows doesn t mean, the data is stored anywhere. The data is removed once the Session ends.

Questions:

1. Can we do insert/update/delete on a Global Temporary table?

Yes we can do insert/update/delete on a Global Temporary table as to a normal Table.

2. What happens if we TRUNCATE a Global Temporary table?

The data is removed only from that specific session where the TRUNCATE is issued. It wont affect the data in the same Global Temporary table in other sessions.

3. Can we drop a Global Temporary table using the Drop command.

Yes, we can. Create and Drop commands work as usual as to a normal Table

4.What happens to the data in a Global Temporary table if the session ends abnormally?

Well, as I said earlier, no matter if the session ends normally or abnormally, the data will never be available once the session ends.

5.Can we have all other features of a normal table on a Global Temporary table.

Yes, we can have most of the features as that of a normal table on a Global Temporary table. We could create indexes, Triggers, Views etc.

Sequence

What is a sequence?
A sequence is nothing but a number generator in sequence as the name says. It could be used for generating unique keys alone and concatenating to other field values.
How to create a sequence?
Create sequence seq01
Start with 1
Increment by 1
Maxvalue 100
Nocycle
Cache 20
Usage:
Sequence is initialized when the user calls Seq01.nextval.
Questions:
1. What will be the output if user tries to use Seq01.currval before Sequence is initialized?
Error: ORA-08002: Sequence SEQ01.CURRVAL is not yet initialized in this session.
2. What will be the output of Seq01.nextval when Sequence is initialized?
Start value of the sequence. Here it is 1
3. How to change sequence attibutes?
All sequence attributes could be changed even after it is created and used.
Eg: Alter sequence seq01 increment by 1000
Alter sequence seq01 nocache
Alter sequence seq01 cycle
Alter sequence seq01 maxvalue 1000

Triggers

                                                      Triggers*

Triggers are actions defined on tables.

1. Why are triggers required?

If we want to perform certain actions whenever an insert/update/delete happens on a table, we could achieve this by using a trigger. Remember, only these 3 DMLs - Insert/Update/delete could fire a trigger.

Truncate or drop or create or any other commands could not fire a trigger on a particular table.

2. Different types of Classification for Triggers:

First type of classification ::

a. Row Level Trigger:

For each row affected by the Insert/Update/Delete operation, the trigger is fired and thus the action associate with it.

Delete from emp where deptno=30; -- deletes 10 records

For eg: If you want to log all the Employee Ids if they gets deleted, you could create an After Delete Row Level Trigger such that for each ID getting deleted they could be inserted into an Audit Table. So, for the above delete operation the trigger is fired 10 times.

b. Statement Level Trigger:

The trigger will be fired only once for the Insert/Update/Delete operation happened on a table.

For eg:   Update emp set sal=sal+1050 where deptno=10;

This update statement updates 30 records in a single update operation. But if the Trigger on table Emp is statement level, the update action would fire the trigger only once!

Second type of classification :: Before and After triggers

Before Trigger : The trigger gets fired before an Insert/Update/Delete operation happens on the table

After Trigger : The trigger gets fired after an Insert/Update/Delete operation happens on the table

Based on the 2 types of classifications, we could have 12 types of triggers.

•  Before Insert row level
•  After Insert row level
•  Before Delete row level
•  After Delete row level
•  Before Update row level
•  After Update row level
•  Before Insert Statement Level
•  After Insert Statement Level
•  Before Delete Statement Level
•  After Delete Statement Level
•  Before Update Statement Level
•  After Update Statement Level

Frequently Asked Questions:

1.What is a Mutating table error (MTE)?

Oracle manages read consistent view of data. The error occurs when a row-level trigger accesses the same tables on which it is based while executing.

2. How to get rid of Mutating table error?

In the Row level trigger we cannot update, insert or select from the mutating table. So we create a package with a collection type in it. Then in the Row level trigger store the values (to be used) into the Collection type. Then add a Statement level trigger to the same table which calls the Package uses the stored data to do the data manipulation.

Eg: create package pack1

is

arr VARRAY(100);

end;

create or replace trigger row_trg1

before update on table1

for each row

begin

pack1.arr(0):= :old.col1;

---

---

end;

create or replace trigger stmt_trg1

before update on table1

declare

v_col number;

begin

v_col:= pack1.arr(0);

---

end;

3. Maximum Trigger size in Oracle : 32k (but functions, procedures etc could be called inside)

4. Can we create trigger on views?

No. But we can use INSTEAD OF Triggers

Eg: create or replace trigger trg_logon

Instead of update on view_v1

For each row

Begin

-- can call a procedure or a function too.

End;

/

5. Can we do commit or rollback in a trigger?

No

Exception : If the trigger is an AUTONOMOUS TRANSACTION we can commit or rollback

6. When a table or view is dropped, what happens to the associated triggers?

Triggers associated to the dropped Tables, Views also gets DROPPED

7. What is an INSTEAD OF Trigger in Oracle?

Views cannot have triggers. So, when someone tries to update a view, you can re-direct the data to the underlying table using an INSTEAD OF trigger.

Eg: create or replace trigger ins_trigger

INSTEAD OF update on emp_view

For each row

Begin

if :new.sal<:old.sal then

update emp set sal=:old.sal where empno=:new.empno;

else

update emp set sal=:new.sal where empno=:new.empno;

end if;

End;

*When INSTEAD OF trigger is written, even if the view is directly updateable, it updates using the trigger only.

8. Does Truncate on a table fires a Trigger?

No. A Truncate table operation wont fire the trigger even if it is defined as a delete trigger . Only a

delete operation could fire a trigger and when the Trigger is a Delete trigger .

Oracle views

1. Can we define an index on a view?
An index cannot be defined on a view. Because view is virtual table, which consists of a subset of columns from one more tables.
And to be precise, view is just a query saved in the Metadata. We cannot index a query.

Restrictions imposed on views:
1. A view can be created only in the current database.
2. A view can be created only if there is a SELECT permission on its base table
3. A Trigger or an Index cannot be defined on a view.
4. A view cannot derive its data from temporary tables.
5. The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

2) How can we create a view on a non existing table -

Use the keyword force with the create command.

Eg: create force view v222
as select * from emp111; -- Note : view name should not be existing

3) Even if default values are there, to use "insert into table values" option, once should provide all column names to be inserted.

Eg> table: Vacation
(empid varchar2(7) primary key,
ename varchar2(50) not null,
annual_leave number(4) default 0,
casual_leave number(4) default 0,
Lop number(4) default 0,
Year number(4) check (year between 2007 and 2008),
status varchar2(1) default 'N')


Wrong: insert into vacation values('2877','James',2007,'A');
-- Note: All column names ae not given

Output: ERROR at line 1: ORA-00947: not enough values

Workaround:
Correct : insert into vacation (empid,ename,Year,status) values('267834','JOse Mathew',2007,'A');