ExternalTables_OracleDirectory

Hi Folks,

I have a task to create an oracle directory and assign access privileges to the users. And create an external table; load the data through .CSV file.

So that I have a created a directory in oracle and assigned access privileges to the users as below:

General Related Data Dictionary Objects dir$ all_directories ku$_directory_t dba_directories ku$_directory_view System Privileges GRANT create any directory TO <user_name>; GRANT drop any directory TO <user_name>; GRANT create any directory TO uwclass; GRANT drop any directory TO uwclass; Create Directory Create A Directory CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>'; conn / as sysdba desc dba_directories set linesize 121 col owner format a15 col directory_name format a20 col directory_path format a70 SELECT * FROM dba_directories; CREATE OR REPLACE DIRECTORY ctemp AS 'c: emp'; SELECT * FROM dba_directories; Granting Privileges To A Directory Grant Read On A Directory GRANT READ ON DIRECTORY <directory_name> TO <schema_name> col grantor format a20 col grantee format a20 col table_schema format a20 col table_name format a20 col privilege format a10 SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; GRANT READ ON DIRECTORY ctemp TO uwclass; SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; Grant Write On A Directory GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name> GRANT WRITE ON DIRECTORY ctemp TO uwclass; SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'CTEMP'; Revoke Read On A Directory REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name> REVOKE READ ON DIRECTORY ctemp FROM uwclass; Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name> REVOKE WRITE ON DIRECTORY ctemp FROM uwclass; Drop Directory Drop A Directory DROP DIRECTORY <directory_name>; SELECT * FROM dba_directories; DROP DIRECTORY ctemp; SELECT * FROM dba_directories;

Creating an External Table:

Create a directory object pointing to the location of the files.

CREATE OR REPLACE DIRECTORY test2 AS 'D:/test2';

EXAMPLE:

create table forecastforOct2013

(YYYYMM number(20),

YYYYMMDD number(20),

DAY_NUMBER varchar2(25),

ship_day number(20),

channel varchar2(25),

reporting_group varchar2(26),

item_type varchar2(25),

forecast_qty_lbs varchar2(26),

forecast_amt varchar2(25)

)

organization external

(

type oracle_loader

default directory test2

access parameters

(

records delimited by newline

badfile 'forecastforOct2013.bad'

discardfile 'forecastforOct2013.dsc'

logfile 'forecastforOct2013.log'

fields terminated by ','
optionally enclosed by '"'

missing field values are null

)

location ('forecastforOct2013.csv')

)
reject limit unlimited ;

1. 1.      Before executing this script we have created a directory called “test2” under D:/ folder and assigned read, wrote permissions to the oracle user .
2. 2.      Place the file (here: forecastforOct2013) in test2 directory.
3. 3.      Make sure that you have all the access permissions to the test2 directory and forecast... Files.

1. Reference: http://www.oracle-base.com/articles/9i/external-tables-9i.php
2. https://forums.oracle.com/thread/2576750