Example of Oracle External Table
================================
CREATE OR REPLACE DIRECTORY dir_test AS 'C:\dir_test';
GRANT READ,WRITE ON DIRECTORY dir_test TO scott;
data on text.txt
/*
00100,halim,md,1234566,halim@yahoo.com,58
00200,jamal,khan,456789,jamal@yahoo.com,50
00300,nazu,haque,456789,nazu@yahoo.com,52
00400,nazim,md,456799221,nazim@yahoo.com,90
00500,niger,sultana,123456787,niger@yahoo.com,50
00600,zakir,Dr,465465464,zakir@yahoo.com,17
*/
DROP TABLE external_table_emp;
CREATE TABLE external_table_emp
( empid VARCHAR2(6),
last_name VARCHAR2(100),
first_name VARCHAR2(100),
phone_no VARCHAR2(20),
email VARCHAR2(200),
age NUMBER(3)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY dir_test
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('test.txt')
);
SELECT * FROM external_table_emp;
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment