Home » SQL & PL/SQL » SQL & PL/SQL » upload a text file in oracle table
upload a text file in oracle table [message #438700] Mon, 11 January 2010 22:43 Go to next message
lungibaba
Messages: 9
Registered: January 2010
Location: Delhi
Junior Member
hi,
i want to upload a text file (kept on other machine), so as to upload the data in a oracle table.
i did this using UTL_FILE package but it will work only on server side. I want to upload the file (kept in network,not necessarily server) in the oracle table.

Please give suggestions?/
Re: upload a text file in oracle table [message #438701 is a reply to message #438700] Mon, 11 January 2010 22:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Its a FAQ please search

sriram Smile
Re: upload a text file in oracle table [message #438703 is a reply to message #438700] Mon, 11 January 2010 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version


It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

You should be able to use SQL*Loader documented at

http://tahiti.oracle.com in Utilities Manual
Re: upload a text file in oracle table [message #438710 is a reply to message #438700] Mon, 11 January 2010 23:17 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

so, please read SQL *Loader in Oracle Material.
Re: upload a text file in oracle table [message #438712 is a reply to message #438700] Mon, 11 January 2010 23:18 Go to previous messageGo to next message
lungibaba
Messages: 9
Registered: January 2010
Location: Delhi
Junior Member
below is the output for v$version :--

1 Oracle8i Enterprise Edition Release 8.1.7.0.0 -Production
2 PL/SQL Release 8.1.7.0.0 - Production
3 CORE 8.1.7.0.0 Production
4 TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
5 NLSRTL Version 3.4.1.0.0 - Production

i do not want to do it using UTL_FILE.

Data is not that huge.

i m sorry if this query already existed but I searched the forums but didnt get any comfortable solution.

i still hav a ORACLE FAQ !
Re: upload a text file in oracle table [message #438715 is a reply to message #438712] Mon, 11 January 2010 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>i do not want to do it using UTL_FILE.
Good, because you can't use UTL_FILE between/across 2 systems.

We do not know what you have.
We do not know what needs to be done to the data.

Good Luck!
Re: upload a text file in oracle table [message #438725 is a reply to message #438700] Mon, 11 January 2010 23:47 Go to previous messageGo to next message
lungibaba
Messages: 9
Registered: January 2010
Location: Delhi
Junior Member
i just have a .psv file which has certain data,one record in each row(columns seperated by "|") which needs to be uploaded in a oracle table. That's it.

This file can be kept anywhere in the network.

Re: upload a text file in oracle table [message #438727 is a reply to message #438725] Mon, 11 January 2010 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>This file can be kept anywhere in the network
Then keep it on the DB server & use UTL_FILE.

Otherwise, You're On Your Own (YOYO)!

alternatively use SQL*Loader
Re: upload a text file in oracle table [message #438750 is a reply to message #438700] Tue, 12 January 2010 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your version and conditions:
- SQL*Loader
- write your own program

Regards
Michel

Re: upload a text file in oracle table [message #438772 is a reply to message #438700] Tue, 12 January 2010 03:11 Go to previous messageGo to next message
lungibaba
Messages: 9
Registered: January 2010
Location: Delhi
Junior Member
yeah thanks Michael..
Re: upload a text file in oracle table [message #438790 is a reply to message #438772] Tue, 12 January 2010 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some of the information provided so far is not entirely accurate.

If Oracle is running under Windows then you can definitely get UTL_FILE to access files on other servers - you just need to have the Listener running under an Domain Admin user, and you can refer to remote files using a standard UNC file path.

To be honest, I can't swear that this will work with a version of Oracle as obsolete as yours.


I remember that there is a way of doing the same thing with Oracle on Linux, but I can't remember the details.

Re: upload a text file in oracle table [message #438792 is a reply to message #438790] Tue, 12 January 2010 05:03 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you just need to have the Listener running under an Domain Admin user

Shocked
As a security admin I should be very shocked if a DBA asks me the authorization to start the listener with a domain administrator account, this is a serious security hole.

Regards
Michel

[Updated on: Tue, 12 January 2010 05:04]

Report message to a moderator

Previous Topic: Triggers
Next Topic: Sql to get the value from a string
Goto Forum:
  


Current Time: Tue Sep 27 09:16:48 CDT 2016

Total time taken to generate the page: 0.61422 seconds