Re: DBMS_OUTPUT.PUT_LINE - any alternative

Fraser Boswell wrote:
>
> I'm trying to produce a flat file for a mail merge from a script which
> has four cursors. For each person there is a one-many relationship where
> they may have twenty rows. I'm using
> dbms_output.put_line c1_str||c2_str||c3_str etc,
> but when I have more than 255 characters an error is raised. Any idea
> how I can produce the output.
>
> Thanks in advance
>
> Fraser

One alternative is to use the UTL_FILE package to write an output file. It still imposes a limit on record length, but a larger limit -- 1023 bytes (or is it 1024?) rather than 255.

In one of your other posts you mentioned that you were trying to write records at least 2000 bytes long, so UTL_FILE's limit is still too low. We had the same problem. I wrote a small utility in C to splice short records into long ones. Obviously we needed to adopt some convention to designate which physical record is the beginning of a logical record. It's also something of a nuisance to make sure that the splicing program gets run when it's needed.

Yet another alternative is to use DBMS_PIPE to send your data to another session, run by a program written with embedded SQL. Then that other program can do whatever you need it to do, including writing a file with long records.

Scott McKellar mck9_at_swbell.net
Free PL/SQL utilities at http://home.swbell.net/mck9/pls/ Received on Sat Aug 28 1999 - 14:20:45 CEST