External table Use

How the DBA Oracle can use external table. Here is a small example

-- ################################################
-- # Creator: Vincent Fenoll (DBA Oracle)
-- # Created: 2011/11/08
-- # Name: External table Use
-- ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # Insert data into a table with my data in a
-- # csv (coma separated) file: Use of external table
-- #
-- ################################################
 

alter session set current_schema=my_schema;

select * from dba_directories;
--> ORA_DUMP  pour /oracle/export/ORCL


CREATE TABLE my_table_ext
(
	ID	NUMBER(7),
	LIBELLE	VARCHAR2(255)
)
	ORGANIZATION EXTERNAL
	(
	  TYPE ORACLE_LOADER
	  DEFAULT DIRECTORY	ORA_DUMP
	  ACCESS PARAMETERS
	  (
		records delimited by newline
		badfile	 ORA_DUMP:'my_table_ext.bad'
		logfile ORA_DUMP:'my_table_ext.log'
		fields terminated by ','
		missing field values are null
		(ID, LIBELLE)
	  ) 
	  LOCATION ('my_table.txt')
	)
	PARALLEL
	REJECT LIMIT UNLIMITED ;

SELECT * FROM my_table_ext ;


insert into my_table select * from my_table_ext;
commit;
exit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.