Re: Q: UTL_FILE and 2500 character rows

Chad Thompson wrote:
>
> I've run into a problem with UTL_FILE writing a file. I need to
> write several thousand rows to a flat file, all 2500 characters per
> row. PUT_LINE has a max of 1023 characters, and combining multiple
> PUT with FFLUSH still dies at 1k characters. I've also tried
> OPEN/PUT/FFLUSH/CLOSE and tried to append each time, but each append
> starts on a new line.
> Anyone have an idea where I'm making a mistake, or how to write out
> a flat file of 2500 characters per row in a PLSQL package? It's not
> something that can be manually spooled in SQLPlus.

We ran into the same brick wall in our application. We never broke through it so we found a way around it. We write a long logical record as a series of short physical records which we splice together later, outside of PL/SQL, using a small utility written in C. It's a pain but it works.

Another workaround is to use DBMS_PIPE to send the data to another session, written in C or whatever, which can write the record as needed.

In Oracle 8 you can direcly call an external procedure, written in C or whatever, which can do whatever you need it to do (or so I've heard -- we're still on Oracle 7).

Scott McKellar mck9_at_swbell.net
Free PL/SQL utilities at http://home.swbell.net/mck9/pls/ Received on Sun Sep 12 1999 - 16:49:04 CDT