Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE (Oracle 9i)
UTL_FILE [message #322191] Thu, 22 May 2008 13:44 Go to next message
jsupra89
Messages: 5
Registered: May 2008
Location: New Jersey
Junior Member
Hello,

I have written a script that outputs a formatted text file using utl_file. In the script i have multiple cursors that select the data I need, but I need a way to issue a page break. I have been able to issue a break every iteration through the loop but this is not sufficient as I am running over 58 lines on occassion before a chr(12) is issued. Is there a way in utl_file to issue the break on every 58th line?
Re: UTL_FILE [message #322192 is a reply to message #322191] Thu, 22 May 2008 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way in utl_file to issue the break on every 58th line?
yes, add that logic to your code.
Re: UTL_FILE [message #322195 is a reply to message #322192] Thu, 22 May 2008 14:10 Go to previous messageGo to next message
jsupra89
Messages: 5
Registered: May 2008
Location: New Jersey
Junior Member
I definitely would have done that if I could, the problem is that I have 2 nested blocks each with their own cursor within the "MAIN" cursor. The main cursor loops through locations for example, and then each nested block/cursor loops through claims within those locations so it's hard to maintain a valid count. Right now I am issuing the break every iteration through the "Location" loop but the 58th iteration through that loop could be many pages long.
Re: UTL_FILE [message #322197 is a reply to message #322192] Thu, 22 May 2008 14:11 Go to previous messageGo to next message
jsupra89
Messages: 5
Registered: May 2008
Location: New Jersey
Junior Member
I didn't know if there is a way like in sql*plus to set the max pagesize to 58?
Re: UTL_FILE [message #322199 is a reply to message #322197] Thu, 22 May 2008 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set pagesize 58

Regards
Michel
Re: UTL_FILE [message #322221 is a reply to message #322191] Thu, 22 May 2008 16:18 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
convert your procedure to a post-processing cursor. This means that you load all the information for the current MAIN cursor (and its sub-ordinates) into arrays, and then at the start of the next MAIN cursor item, you can flush the arrays to disk using utl_file.

At this point you can interrogate the stored data to see if you are going to flush too much and break your 58 pages rule, and issue the line break if needed..

Does that make sense?

Another easy way would be to flush all the data to a temp table, and do some parsing on the temp table before flushing the whole lot to disk.

[Updated on: Thu, 22 May 2008 16:24]

Report message to a moderator

Re: UTL_FILE [message #323135 is a reply to message #322221] Tue, 27 May 2008 07:19 Go to previous messageGo to next message
jsupra89
Messages: 5
Registered: May 2008
Location: New Jersey
Junior Member
Thank you for the quick response. From what I understand, i would assign array values ie value1......999 each one being my report value and then after each main loop iteration send it to text file, correct?
Re: UTL_FILE [message #323139 is a reply to message #323135] Tue, 27 May 2008 07:37 Go to previous messageGo to next message
jsupra89
Messages: 5
Registered: May 2008
Location: New Jersey
Junior Member
But to your knowledge, there are no utl_file(pagesize... type of parameters I could institute?

Thanks again!
Re: UTL_FILE [message #323141 is a reply to message #323139] Tue, 27 May 2008 07:41 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is none.

Regards
Michel
Previous Topic: Issue: Different Date formats in the same cloumn in a table
Next Topic: change the format from APR 07 to 042007
Goto Forum:
  


Current Time: Sun Dec 11 05:57:22 CST 2016

Total time taken to generate the page: 0.16999 seconds