Oracle9i introduced new date and time related data types.
1. TIMESTAMP
2. TIMESTAMP WITH TIME ZONE
3. TIMESTAMP WITH LOCAL TIME ZONE
4. INTERVAL DAY TO SECOND
5. INTERVAL YEAR TO MONTH
We can also change the database timezone as below. We can use v$timezone_names dictionary to view all the time zone information
ALTER SESSION SET TIME_ZONE = 'PST'
TIMESTAMP datatype is very much like the DATE datatype. it can store both the date and time values. It is not like DATE datatype, you can also specify the precision of the factional seconds value in the TIMESTAMP datatype. The default is 6 digit, but we can specify a value within the range from 0 through 9.
SQL> create table calltrack(
2 callnum integer,
3 calltime TIMESTAMP(4));
Table created.
We can change the NLS_TIMESTAMP_FORMAT as we required...
SQL> alter session set nls_timestamp_format = 'MMDDYYHH24MISSFF';
Session altered.
Now insert one record and select the timestamp field.
SQL> insert into calltrack values(1,sysdate);
1 row created.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
1024080953590000
SQL>
TIMESTAMP WITH TIME ZONE data type stores time zone displacement which needs additional bytes of storage. We can also change the NLS_TIMESTAMP_TZ_FORMAT at session level.
SQL> create table calltime(
2 callnum integer,
3 calltime timestamp(4) with time zone);
Table created.
SQL> insert into calltime values(1,sysdate);
1 row created.
SQL> select calltime from calltime;
CALLTIME
-------------------------------------------------------
04-APR-09 11.10.40.0000 PM -04:00
SQL> select vsize(calltime) from calltime;
VSIZE(CALLTIME)
---------------
13
TIMESTAMP WITH LOCAL TIME ZONE includes time zone information, but unlike the TIMESTAMP WITH TIME ZONE datatype. It does not store the time zone displacement in additional bytes of storage. Instead, it stores the time values normalized in terms of the database time zone. When user attempts to retrieve this information, the database dispalys the information in terms of local time zone of the user's session.
Let us create a table with this data type.
SQL> create table calltrack(
2 callnum integer,
3 calltime timestamp(4) with local time zone);
Table created.
SQL> insert into calltrack values(1,sysdate);
1 row created.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
24-OCT-08 10.58.34.0000 AM
SQL>
Now the date is displaying with Eastern US time. Now let us change the database time zone to Pacific time on session level.
SQL> ALTER SESSION SET TIME_ZONE = 'PST';
Session altered.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
24-OCT-08 07.58.34.0000 AM
SQL>
INTERVAL DAY TO SECOND can store a time interval value in terms of days, hours, minutes and seconds. We can specify the precision for the number of days in a range from 0 to 9, the default being 2. We can also specify the precision for the fractional seconds in a range from 0 to 9, the default being 6.
SQL CREATE TABLE promotions (
2 promotion_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL DAY(3) TO SECOND (4))
5 /
Table created.
SQL
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (1, '10% off Z Files', INTERVAL '3' DAY);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (2, '20% off Pop 3', INTERVAL '2' HOUR);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (3, '30% off Modern Science', INTERVAL '25' MINUTE);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (4, '20% off Tank War', INTERVAL '45' SECOND);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (5, '10% off Chemistry', INTERVAL '3 2:25' DAY TO MINUTE);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (6, '20% off Creative Yell',
3 INTERVAL '3 2:25:45' DAY TO SECOND);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '123 2:25:45.12' DAY(3) TO SECOND(2));
1 row created.
scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2));
INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01873: the leading precision of the interval is too small
scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
4 select duration from promotions;
INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01852: seconds must be between 0 and 59
scott@orcl>
SQL select duration from promotions;
DURATION
-------------------------------------------------------------------------
+003 00:00:00.0000
+000 02:00:00.0000
+000 00:25:00.0000
+000 00:00:45.0000
+003 02:25:00.0000
+003 02:25:45.0000
+123 02:25:45.1200
7 rows selected.
SQL
INTERVAL YEAR TO MONTH can store time interval value in terms of years and months. We can specify the precision for the number of years in a range from 0 to , default being 2.
SQL> CREATE TABLE coupons (
2 coupon_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL YEAR(3) TO MONTH);
Table created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (1, '$1 off Z Files', INTERVAL '1' YEAR);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (2, '$2 off Pop 3', INTERVAL '11' MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (3, '$3 off Modern Science', INTERVAL '14' MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (4, '$2 off Tank War', INTERVAL '1-3' YEAR TO MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (5, '$1 off Chemistry', INTERVAL '0-5' YEAR TO MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (6, '$2 off Creative Yell', INTERVAL '123' YEAR(3));
1 row created.
SQL>
SQL> SELECT duration FROM coupons;
DURATION
---------------------------------------------------------------------------
+001-00
+000-11
+001-02
+001-03
+000-05
+123-00
6 rows selected.
SQL>
Thursday, October 23, 2008
Merge statement in Oracle
Merge statement is introduced in oracle9i and it has enhanced in oracle10g. It is beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database. In data warehouse environment, when we refresh FACT table periodically, the source system cannot distinguish between newly inserted or changed information during the extraction. We need to scan the FACT table multiple times and it is becoming performance issue.
Prior to Oracle9i, these operations were expressed either as a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops deciding, for each row, whether to insert or update the data.
In oracle9i, it overcomes this problem and we can write both INSERT, UPDATE in one statement called MERGE. It reduces multiple scans and it improves the performance.
Here is sample Merge statement in oracle9i. The source_table is created as the same structure of ALL_OBJECTS with out any data.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHEN NOT MATCHED THEN
7 INSERT (object_id, status)
8 VALUES (b.object_id, b.status);
22278 rows merged.
SQL>
In oracle10g, merge statement is enhanced. The new enhancements are
1. Optional INSRET or UDPATE clause
2. Conditional operations for INSERT and UPDATE
3. We can use the delete statement on Merge
Optional INSRET or UDPATE clause
The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
VALUES (b.object_id, b.status)
*
ERROR at line 6:
ORA-00905: missing keyword
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
UPDATE SET a.status = b.status
*
ERROR at line 5:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
28603 rows merged.
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
28603 rows merged.
SQL>
Conditional operations for INSERT and UPDATE
Conditional inserts and updates are now possible by using a WHERE clause on these statements
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 7:
ORA-00905: missing keyword
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
33 rows merged.
SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
0 rows merged.
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
33 rows merged.
SQL>
Delete statement on Merge
An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
33 rows merged.
SQL>
Restriction On Merge : We cannot update a column that is referenced in the ON condition clause.
Prior to Oracle9i, these operations were expressed either as a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops deciding, for each row, whether to insert or update the data.
In oracle9i, it overcomes this problem and we can write both INSERT, UPDATE in one statement called MERGE. It reduces multiple scans and it improves the performance.
Here is sample Merge statement in oracle9i. The source_table is created as the same structure of ALL_OBJECTS with out any data.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHEN NOT MATCHED THEN
7 INSERT (object_id, status)
8 VALUES (b.object_id, b.status);
22278 rows merged.
SQL>
In oracle10g, merge statement is enhanced. The new enhancements are
1. Optional INSRET or UDPATE clause
2. Conditional operations for INSERT and UPDATE
3. We can use the delete statement on Merge
Optional INSRET or UDPATE clause
The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
VALUES (b.object_id, b.status)
*
ERROR at line 6:
ORA-00905: missing keyword
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
UPDATE SET a.status = b.status
*
ERROR at line 5:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
28603 rows merged.
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
28603 rows merged.
SQL>
Conditional operations for INSERT and UPDATE
Conditional inserts and updates are now possible by using a WHERE clause on these statements
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 7:
ORA-00905: missing keyword
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
33 rows merged.
SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
0 rows merged.
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
33 rows merged.
SQL>
Delete statement on Merge
An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword
SQL>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
33 rows merged.
SQL>
Restriction On Merge : We cannot update a column that is referenced in the ON condition clause.
Oracle External Tables
Oracle External tables are not like regular table. We can apply DML statement on regular table. But in external table, you can not apply DML statement except SELECT. We can not create index on external table. In oracle9i, read only operations are permitted, but in oracle10g, we can also write out data to an external table, although you can't write to an existing table.
Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the DB into a file in an Oracle-proprietary format, and back into the database from files of that format.
This article is written in oracle10g on windows.
Steps to create an External table
Step 1.
Create the directory on server as well as database.
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( type oracle_datapump
8 default directory extdir
9 location ('emp.dmp')
10 );
Table created.
SQL> set linesize 100
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from import_load_ext;
EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10
14 rows selected.
Restriction on external tables :
1. An external table does not allow INSERT, UPDATE, DELETE statement. We get ORA-30657: operation not supported on external organized table error when apply DML statement except SELECT.
2. An external table does not describe how data is stored in the external source.
3. An external table cannot load data into a LONG column.
4. Column name or table names are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, it must be enclosed in double quotation marks.
If you want to read more on external tables... Please read here..
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229
Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the DB into a file in an Oracle-proprietary format, and back into the database from files of that format.
This article is written in oracle10g on windows.
Steps to create an External table
Step 1.
Create the directory on server as well as database.
C:\>mkdir ext
C:\>cd ext
C:\ext>
SQL> connect sys as sysdba
Enter password: ********
Connected.
SQL> create directory extdir
2 as 'c:/ext';
Directory created.
SQL> grant read,write on directory extdir to scott;
Grant succeeded.
SQL>
Step2
Place the text file on directory c:/ext location and create the external table as below.
Here is the text file content.
7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
7698,BLAKE,MANAGER,30
7782,CLARK,MANAGER,10
7788,SCOTT,ANALYST,20
7839,KING,PRESIDENT,10
7844,TURNER,SALESMAN,30
7876,ADAMS,CLERK,20
7900,JAMES,CLERK,30
7902,FORD,ANALYST,20
7934,MILLER,CLERK,10
SQL> show user
USER is "SCOTT"
SQL> create table emptable
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( default directory extdir
8 access parameters
9 ( records delimited by newline
10 fields terminated by ',')
11 location ('emp.txt'));
Table created.
SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from emptable;
EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10
14 rows selected.
SQL>
We could add log file, bad file, discard file for the external table.
SQL> alter table emptable
2 access parameters
3 ( records delimited by newline
4 badfile extdir:'emp.bad'
5 logfile extdir:'emp.log'
6 discardfile extdir:'emp.dsc'
7 fields terminated by ','
8 ) ;
Table altered.
SQL>
Loading data from external table to Regular table
We can load the data from external table to oracle table as below. We can do the same job through SQLLOADER. So, now the question is, when do we use the sqlloader and when do we use the external table for loading data from flat file to oracle table. External table has good filtering capability while loading data from external table to oracle table.
In the below example, we are loading data only for deptno 10 and 20. For this situation, it is worth to try external table... If we want to load only specific set of data from flat file, external table is good option.
SQL> create table empload_db as select * from
2 emptable where deptno in(10,20);
Table created.
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from empload_db;
EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7566 7566 MANAGER 20
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7876 7876 CLERK 20
7902 7902 ANALYST 20
7934 7934 CLERK 10
8 rows selected.
SQL>
Loading data from regular table to external table.
This feature is introduced in oracle10g. We shoud use oracle_datapump driver to load data into external table.
SQL> create table load_ext
2 organization external
3 ( type oracle_datapump
4 default directory extdir
5 location ('emp.dmp')
6 ) as select * from emptable
7 /
Table created.
SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from load_ext;
EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10
14 rows selected.
SQL>
You can now move the file you just created, emp.dmp, to another system and create an external table to read the data:
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( type oracle_datapump
8 default directory extdir
9 location ('emp.dmp')
10 );
Table created.
SQL> set linesize 100
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from import_load_ext;
EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10
14 rows selected.
Restriction on external tables :
1. An external table does not allow INSERT, UPDATE, DELETE statement. We get ORA-30657: operation not supported on external organized table error when apply DML statement except SELECT.
2. An external table does not describe how data is stored in the external source.
3. An external table cannot load data into a LONG column.
4. Column name or table names are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, it must be enclosed in double quotation marks.
If you want to read more on external tables... Please read here..
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229
Subscribe to:
Posts (Atom)