creating a table and loading data from flat file

[crsdev]

Hello Friends.. sorry for this basic question..

I was wondering if someone could help me with some suggestions.

I want to create tables for huge amount of data supplied in flat files for reporting purpose. what is the best way to do this. using SQL*Loader? I have TOAD and SQL Developer .. is SQL*Loader one of the tools in TOAD buecause there is something called SQL Loader Wizard in DBA menue...
i'd also read something about external tables..

any suggestions would be great.

Thanks in advance.

 

[pete91z]

Never used Toad, but sql*loader has a command line interface: sqlldr help=y

hope this helps you get started.

 

[SantaMufasa]

CRS,

Here is some sample code to turn a flat file into an externally organised table:

Section 1 -- Flat ASCII data file:

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"

Output file = "tempascii.sql"

Section 2 -- SQL Code to directly access "tempascii.sql" as a table:

create or replace directory MyFlatFiles as 'd:\dhunt\sqldba'
/
drop table dept_ext
/
create table dept_ext
(  id        number,
   name      varchar2(50),
   region_id number
)
organization external
(  type oracle_loader
   default directory MyFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by ',' enclosed by '"'
   )
location ('TempASCII.sql')
)
reject limit 1;

Section 3 -- Query against flat ASCII table:

select * from dept_ext;

 ID NAME             REGION_ID
--- --------------- ----------
 10 Finance                  1
 31 Sales                    1
 32 Sales                    2
 33 Sales                    3
 34 Sales                    4
 35 Sales                    5
 41 Operations               1
 42 Operations               2
 43 Operations               3
 44 Operations               4
 45 Operations               5

Let us know if this helps solve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[crsdev]

That certainly is helpful to some extent.

I am wondering how you ommit some of the data in the flat files from loading to the table.

say: from your example i just want to load id and region_id and ommit the name.

Thanks.

 

[Beilstwh]

create or replace directory MyFlatFiles as 'd:\dhunt\sqldba'
/
drop table dept_ext
/
create table dept_ext
( id number,
name filler,
region_id number
)
organization external
( type oracle_loader
default directory MyFlatFiles
access parameters
(records delimited by newline
fields terminated by ',' enclosed by '"'
)
location ('TempASCII.sql')
)
reject limit 1;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Actually, Bill, "FILLER" is not available on external tables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Darn, I was so used to sql loader control files that I thought that it was also part of external tables. Sorry everyone.

Bill
Oracle DBA/Developer
New York State, USA

 

[Beilstwh]

I found an interesting quote in one of the oracle manuals

"If field_name matches the name of a column in the external table that is referenced in the query, then the field value is used for the value of that external table column. If the name does not match any referenced name in the external table, then the field is not loaded but can be used for clause evaluation (for example WHEN or NULLIF)."

Try naming the column that you want to exclude different from the columns defined in the table definition.

Bill
Oracle DBA/Developer
New York State, USA