External Table

External Tables

Sometimes you want to access data that resides outside of the database in the text format, but you want to use it as if it were a table in the db. You could use a utility such as SQL*Loader to load the table into the database, the data may be quite volatile or your user base expertise might not in include executing SQL * Loader at win or unix.

Example:

SQL> create directory tst as 'f:\';

Directory created.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\Documents and Settings\Administrator>f:

F:\>type babu.sql

10,babu
20,taj

F:\>

F:\>exit

SQL> create table emp ( eno_id int,ename varchar2(50))
2 organization external
3 (type oracle_loader
4 default directory tst
5 access parameters
6 (records delimited by newline
7 fields terminated by ','
8 (eno_id char,ename char)
9 )
10 location ('babu.sql')
11 );

Table created.

SQL> select * from emp;

ENO_ID ENAME
---------- --------------------------------------------------
10 babu
20 taj

2 rows selected.

Table

Global Temporary Tables

They are temporary tables is the sense of the data that is stored in the table, not in definition of the table itself. The command Create Global temporary table created temp tables. all users who have permissions on the table itself can perform DML on a temporary table.

There are two different flavors of temp data in a temp table.

1. Temporary for the duration of the transaction.

2. Temporary for the duration of the session.

On commit delete rows - Removes all rows from the temp. tale when a commit or rollback issued.

On commit preserve rows - Keeps the rows in the table beyond the tran. boundary. However the users session terminated all of the user row's in the temp. table removed.

Example:

SQL> create global temporary table babu ( eno int) on commit delete rows;

Table created.

SQL> insert into babu values (10);

1 row created.

SQL> insert into babu values (20);

1 row created.

SQL> insert into babu values (30);

1 row created.

SQL> select * from babu;

ENO
----------
10
20
30

SQL> commit;

Commit complete.

SQL> select * from babu;

no rows selected