Home » SQL & PL/SQL » SQL & PL/SQL » where to create external table (Oracle10g,Linux)
where to create external table [message #291092] Thu, 03 January 2008 00:19 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi all,
I need a External table.Where i create a external table whether <user_schema> i.e scott or system schema..


Thanks,
Michael
Re: where to create external table [message #291093 is a reply to message #291092] Thu, 03 January 2008 00:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can create wherever you want.... But your schema should have permissions to create a directory or you need to use the existing directory (to access the flat file).
Why do you want to create it in system schema?

By
Vamsi
Re: where to create external table [message #291096 is a reply to message #291092] Thu, 03 January 2008 00:27 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Choose an (operating system) directory into which you'll put a file that contains data you'd like to use as an external table. Connected as a privileged user (SYSTEM, for example), CREATE DIRECTORY (this time it is an Oracle term representing an Oracle object) and give READ (and/or WRITE) privileges on it to user which will use this file (Scott, for example). Then connect as Scott and create an external table. Here's an example:
CONNECT SYSTEM/pwd@ora10

CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\temp';

GRANT READ, WRITE ON DIRECTORY ext_dir TO scott;

CONNECT scott/pwd@ora10

CREATE TABLE EXT_TABLE
(  col1  VARCHAR2(10),
   ...
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('my_input_file.txt')
)
REJECT LIMIT UNLIMITED
/

Here is the CREATE DIRECTORY and CREATE TABLE syntax and explanation. On the same server, you'll find documentation about external tables as well.

Previous Topic: Diffrence between 'IS' and 'AS' in Procedure creation
Next Topic: Join two tables
Goto Forum:
  


Current Time: Sun Dec 04 04:38:40 CST 2016

Total time taken to generate the page: 0.04325 seconds