Home » RDBMS Server » Server Utilities » Porting data from text file (10g)
Porting data from text file [message #510907] Wed, 08 June 2011 05:46 Go to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Hi,

I have a data in text file which has around 7 laks lines. I need to import those data into a table in oracle then do some process.

Since this job is done by end user, i have created a form with the following process.





    Create a single text file from multiple flat files in client system.
    Run the application from the application server [ which is unix based ]
    once the text file is uploaded to the server, read the text file line by line and insert into the table.


The above process works perfectly in test eniviroment which I have as stand alone system. When I move this to application server and execute it, the session got killed after an hour and I got network inturpted error.

I would like to know is there any other method instead of reading line by line and insert into a table. I do not want to use the tools like sql Loader etc since I do not want my user to do lot of things.

Can anyone help me.

Sethu Murugan
Re: Porting data from text file [message #510911 is a reply to message #510907] Wed, 08 June 2011 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Loader or external table to load files.

Regards
Michel
Re: Porting data from text file [message #510912 is a reply to message #510907] Wed, 08 June 2011 05:54 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
What about external tables?
Re: Porting data from text file [message #510928 is a reply to message #510911] Wed, 08 June 2011 06:39 Go to previous messageGo to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Since the user is directly going to do this task, I doubt whether SQL* loader will be use full.

I do not know about external_tables. Let me explore it. If I have any doubt let me buzz here.

Thank you
Re: Porting data from text file [message #510935 is a reply to message #510907] Wed, 08 June 2011 07:21 Go to previous messageGo to next message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Am sorry.

I have not gone thru the utilities of SQL loader. I think that should be the solution for my problem.

But how to do it in the following condition.

My application server is in Unix platform
I do not have access to the live database and I do not know whether SQL loader is available or not
i do not have comma seperated files instead a fixed lenght file which will be 22 charactors continiously


Sethu

[Updated on: Wed, 08 June 2011 07:29]

Report message to a moderator

Re: Porting data from text file [message #510937 is a reply to message #510935] Wed, 08 June 2011 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want or can't use Oracle tools you have to write your own and so have to do with your programming skills.

Regards
Michel
Re: Porting data from text file [message #510993 is a reply to message #510937] Wed, 08 June 2011 23:19 Go to previous message
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
Member
Hi,

Thanks for the clue. I have done a form to use sql loader option and succeded. But need some more help to complete it which I have posted in forms section.


http://www.orafaq.com/forum/t/171939/153421/

Sethu Murugan
Previous Topic: Export Utility
Next Topic: Tables moved using export and import
Goto Forum:
  


Current Time: Sun Apr 20 01:43:12 CDT 2014

Total time taken to generate the page: 0.05072 seconds