OraFAQ Forum: SQL & PL/SQL » file input and output

Home » SQL & PL/SQL » SQL & PL/SQL » file input and output (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

file input and output [message #598451]

Tue, 15 October 2013 01:56

saurau
Messages: 3
Registered: October 2013
Location: Bangalore

Junior Member

Hello Everybody,

I am very new to oracle and having a query, i need a script which can take input from a file and give output to other txt file, something like below :

input.txt

001
002
003
004

Query:

select name from employee where employeecode=<ENTRY FROM INPUT.TXT FILE>

Result should go to OUTPUT.TXT File.

Your help is highly appreciated.

Regards,
saurau.

Report message to a moderator

Re: file input and output [message #598452 is a reply to message #598451]

Tue, 15 October 2013 01:59

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

INPUT.TXT should be an external table. You'd SELECT from it and SPOOL result into OUTPUT.TXT.

Report message to a moderator

Re: file input and output [message #598453 is a reply to message #598452]

Tue, 15 October 2013 02:03

saurau
Messages: 3
Registered: October 2013
Location: Bangalore

Junior Member

It's on client and we dont have access to create table.

Report message to a moderator

Re: file input and output [message #598454 is a reply to message #598451]

Tue, 15 October 2013 02:20

tigsav
Messages: 49
Registered: April 2012

Member

Hi,
You can try using utl_file to read from file and also write back to the file.But then again you would require permissions to create a directory and read and write access to that directory path.

Report message to a moderator

Re: file input and output [message #598456 is a reply to message #598454]

Tue, 15 October 2013 02:34

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If it is on a client computer, then load file contents into a table (heck, if you can't create ANY TABLE at all, I have no idea how to do that) with SQL*Loader. The rest remains the same - spool SELECT's results into an output file.

Report message to a moderator

Re: file input and output [message #598458 is a reply to message #598456]

Tue, 15 October 2013 02:42

saurau
Messages: 3
Registered: October 2013
Location: Bangalore

Junior Member

hi tigsav,

can you please explain more , i mean the usage of utl_file ..

Report message to a moderator

Re: file input and output [message #598459 is a reply to message #598458]

Tue, 15 October 2013 02:45

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What would you do with UTL_FILE? It operates with files on a database server, not your local computer.

[Updated on: Tue, 15 October 2013 02:48]

Report message to a moderator

Re: file input and output [message #598460 is a reply to message #598458]

Tue, 15 October 2013 02:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Why UTL_FILE? No need in your case.

If it is in the client side, then simply use sqlldr to load into the existing table and then use SPOOL to create an output file based on your select query. You could write a script to automate all these steps into one.

[Updated on: Tue, 15 October 2013 02:48]

Report message to a moderator

Previous Topic:	Dynamic column in SQL Output
Next Topic:	Insert as select with clob field

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 22:07:26 CDT 2024