Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL*Plus: How can I write each record to a separate file
DA Morgan wrote:
> zrwcvhsosoik001_at_sneakemail.com wrote:
> > Well, the example is hypothetical of course. But UTL_FILE will not
> > work, because I need the output on the client. We have no access to the
> > server filesystem.
> >
> > DA Morgan wrote:
> >> zrwcvhsosoik001_at_sneakemail.com wrote:
> >>> Hi,
> >>>
> >>> I need to select a set of records, and then write each record to a
> >>> separate file.
> >>> Something like this (in pseudo code). The idea is to write the salary
> >>> of each employee to a separate file with the name of the employee.
> >>>
> >>> FOR rec in select * from emp LOOP
> >>> create file with filename rec.ename
> >>> write rec.sal to file
> >>> close file
> >>> END LOOP
> >>>
> >>> Is this possible using SQL*Plus? Or would you recommend another tool.
> >>>
> >>> Thanks!
> >> Use UTL_FILE. You can find a demo of this in Morgan's Library at
> >> www.psoug.org. You will need to modify it, however, to create each
> >> individual file and name it.
> >>
> >> I wonder how well this has been thought out from the business
> >> requirements standpoint. You are going to end up with a lot of
> >> separate files sitting on the server (where no one should be mucking
> >> about) that will then need to be moved or further processed. Why not
> >> just go directly to whatever end result is desired?
> >> --
> >> Daniel Morgan
> >> University of Washington
> >> Puget Sound Oracle Users Group
>> 2. Good. I am glad your SAs and DBAs have kept everyone off the servers
> 1. Please do not top post. Scroll to the bottom before replying.
Daniel,
I purposely kept the example simple. What I want to do, is to create
DDL scripts (create-table) by using DBMS_METADATA. I require one script
per table, for purposes of version control. The name of the file will
reflect the table name. I know tools like TOAD can do this but as far
as I know you need to perform this interactively. I need an automated
(scripted) solution that I can also somewhat tailor.
I hope this explanation helps.
The solution using an SQL*Plus script that creates an SQL*Plus script
sounds doable although rather crude. For example it leaves this
temporary script on the file system (I don't know of a portable way to
delete a file from SQL*Plus).
Thanks.
Received on Fri Sep 08 2006 - 13:24:45 CDT
![]() |
![]() |