Home » SQL & PL/SQL » SQL & PL/SQL » Import File
Import File [message #420768] Wed, 02 September 2009 02:41 Go to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
HI

I recently exported a file using UTL_FILE method.
I now need to import the file back into Oracle.
Is the best method for this to use UTL again?

Thanks

Report message to a moderator

Re: Import File [message #420770 is a reply to message #420768] Wed, 02 September 2009 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the file format.
Have a look at SQL*Loader.

Regards
Michel

Report message to a moderator

Re: Import File [message #420773 is a reply to message #420768] Wed, 02 September 2009 02:55 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
The file format is CSV.
The syntax for sql loader doesnt get recognised in my Oracle SQL Developer program. IS there a reason for this?
Thanks for your reply.

Report message to a moderator

Re: Import File [message #420774 is a reply to message #420773] Wed, 02 September 2009 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know "Oracle SQL Developer".
If the file is in CSV format then it is loadable with SQL*Loader.

Regards
Michel

Report message to a moderator

Re: Import File [message #420775 is a reply to message #420768] Wed, 02 September 2009 03:06 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Hi

OK

I have tried the following code:

load data
infile 'test.txt' "var 3"
into table test
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))

and I get the error message

SQL Error: ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"

Is this because I'm using oracle sql developer or because the syntax is wrong?

Any help would be great.
Thanks so far.

Report message to a moderator

Re: Import File [message #420777 is a reply to message #420775] Wed, 02 September 2009 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this because I'm using oracle sql developer or because the syntax is wrong?

Use what I said.

Regards
Michel

Report message to a moderator

Re: Import File [message #420778 is a reply to message #420768] Wed, 02 September 2009 03:14 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Quote:
Use what I said.


I did. I used some example text which would use SQL Loader and it didnt work, hence why I pasted the code into my message and asked why it didnt work.
I think I misunderstood what you said.

Report message to a moderator

Re: Import File [message #420780 is a reply to message #420778] Wed, 02 September 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste what you did and post loader log file.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section and use code tags.

Regards
Michel

Report message to a moderator

Re: Import File [message #420781 is a reply to message #420768] Wed, 02 September 2009 03:33 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Thanks for the reply.
Do I need to enable a loader file or something then to get it to work?
This sounds like a lot of work just to import a file. Is there no way to do using UTL. I used UTL to export and it was so easy, but there doesnt seem to be many examples to import using UTL.

Report message to a moderator

Re: Import File [message #420784 is a reply to message #420768] Wed, 02 September 2009 04:12 Go to previous messageGo to next message
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise that sqlloader is a program like sqlplus?
It's not in the database and you can't run it from other tools.
You run it direct from the command line.

Report message to a moderator

Re: Import File [message #420790 is a reply to message #420768] Wed, 02 September 2009 04:49 Go to previous messageGo to next message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
I used this link in the end
http://www.java2s.com/Code/Oracle/System-Packages/UseUTLFILEtoreadfromafileandinsertintothelecturertable.htm

This worked fine and was what I was looking for in the first place.
Michel take note of this link so that if anyone in future asks about UTL they can use this rather then you recomending they go off on a different route.

Report message to a moderator

Re: Import File [message #420804 is a reply to message #420790] Wed, 02 September 2009 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take note that your question was: "Is the best method for this to use UTL again?" and that the answer is NO THE BEST METOHD IS TO USE SQL*LOADER.
Take note to follow and study what we tell you before saying silly things.

Regards
Michel

[Updated on: Wed, 02 September 2009 06:04]

Report message to a moderator

Re: Import File [message #420808 is a reply to message #420768] Wed, 02 September 2009 06:14 Go to previous message
GuestUser32
Messages: 26
Registered: August 2009
Junior Member
Apologies as I forgot how I worded my original question.

Report message to a moderator

Previous Topic: How to populate the dynamic recordset while passing the tablename dynamically
Next Topic: Newbie two table update question
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 10:20:23 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.