Re: SQL*Plus: How can I write each record to a separate file

From: <zrwcvhsosoik001_at_sneakemail.com>
Date: 8 Sep 2006 11:24:45 -0700
Message-ID: <1157739885.680891.90430@m79g2000cwm.googlegroups.com>

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

>


> 1. Please do not top post. Scroll to the bottom before replying.

> 2. Good. I am glad your SAs and DBAs have kept everyone off the servers
> 3. If you want this to write to the client machine ... then the version
> of Oracle, the front-end tools, and many other factors come into play:
> None of which you have disclosed.
> 4. And I still think it sounds like a bad idea. What is the business case?
> --
> Puget Sound Oracle Users Group

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