Home » RDBMS Server » Server Utilities » Load from a text file (11G)
Load from a text file [message #563066] Wed, 08 August 2012 04:21 Go to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Hi,

I would like to load a text file into an oracle table. Could you please help me.

Thanks
VIBO
Re: Load from a text file [message #563067 is a reply to message #563066] Wed, 08 August 2012 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Research "SQL*Loader" and "External tables". A quick review is here, on OraFAQ.

SQL*Loader chapter in Oracle 11g R2 Utilities book.
Managing external tables in 11g R2 Administrator's Guide book.

[EDIT: added links]

[Updated on: Wed, 08 August 2012 04:37]

Report message to a moderator

Re: Load from a text file [message #563068 is a reply to message #563067] Wed, 08 August 2012 04:44 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Here is what I am trying but doesn't seem to work. Could some one help?

sqlldr wa@orcl/password control=loader.ctl - (Do I need to run this on the schema?)

load data
infile 'c:\report\report.txt'
into table emp
fields terminated by ":"
( empno, empname, sal, deptno )
Re: Load from a text file [message #563069 is a reply to message #563068] Wed, 08 August 2012 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Do I need to run this on the schema?


You run this on the schema you gave the credentials on the command line.

Regards
Michel
Re: Load from a text file [message #563070 is a reply to message #563069] Wed, 08 August 2012 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vibo
but doesn't seem to work
means exactly what?
Re: Load from a text file [message #563074 is a reply to message #563070] Wed, 08 August 2012 05:58 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Hi I am using an external table instead of SQL Loader. I have place the text file report.txt in a folder C:\Report and have executed the below code. I does not allow me to insert into the security table. Could you possibly find the error.

Create Directory external_dir AS 'C:\Report';



GRANT READ ON DIRECTORY external_dir TO Sys;

CREATE TABLE security_load
(output_path varchar(300),
output_account varchar(300),
output_permission VARCHAR(300))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS (output_path varchar(300),
output_account varchar(300),
output_permission VARCHAR(300)))
LOCATION ('Report.txt'));

create table security ( PATH varchar(300),Accounts varchar(300),permission varchar(300));

insert into security ( PATH, Accounts, permission )
(select output_path, output_account, output_permission from security_load);

select * FROM SECURITY_LOAD;

[Updated on: Wed, 08 August 2012 07:16] by Moderator

Report message to a moderator

Re: Load from a text file [message #563076 is a reply to message #563074] Wed, 08 August 2012 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External tables can only read database LOCAL drives, are you sure that "C:\Report" is on the database server?
And you still don't say what "doesn't seem to work" means.

Just copy and paste your session.
Before, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Edit: Add "database" before LOCAL to clear the point]

[Updated on: Wed, 08 August 2012 07:58]

Report message to a moderator

Re: Load from a text file [message #563077 is a reply to message #563074] Wed, 08 August 2012 06:03 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe you shouldn't really use SYS for your own amusement. Create another user and do whatever you are doing in his schema.

As of external tables: you still didn't say WHAT HAPPENS when you load data - you said that it doesn't work, but that's useless. Did you get any error? Which one? Note that - for external tables - file must reside in a directory located on a DATABASE SERVER, not your own PC (in your case, 'c:\report' must be on a database server).
Re: Load from a text file [message #563078 is a reply to message #563077] Wed, 08 August 2012 06:10 Go to previous messageGo to next message
vibo
Messages: 21
Registered: August 2012
Junior Member
Hey sorry "Doesn't seem to work" Just meant it threw up an error while inserting into security table.
IT says error in executing ODCIEXTTABLEFETCH callout
ORA-30653 reject limit reached

I will chk the option to make sure the directory is located on a database server.

Thanks guys
Re: Load from a text file [message #563082 is a reply to message #563078] Wed, 08 August 2012 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
oerr ora 30653
30653,0000, "reject limit reached"
// *Cause:  the reject limit has been reached.
// *Action: Either cleanse the data, or increase the reject limit.

Your data doesn't match your table definition
Re: Load from a text file [message #563083 is a reply to message #563082] Wed, 08 August 2012 07:17 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... and log file will tell you why so have a look at it.

Regards
Michel
Previous Topic: SQL loader problem
Next Topic: SQLLDR..Delimited file: bypass unwanted data fields (2 threads merged by bb)
Goto Forum:
  


Current Time: Sun Dec 28 17:02:15 CST 2014

Total time taken to generate the page: 0.09049 seconds