Re: UTL_FILE question

From: Viktor <stant_98_at_yahoo.com>
Date: Sun, 1 Feb 2004 11:35:20 -0800 (PST)
Message-ID: <20040201193520.75180.qmail@web13426.mail.yahoo.com>

I got the procedure to write files as I want it - Thanks Connor!, except that data for last month in the range selected isn't being written - file is empy.Here is the code:

CREATE OR REPLACE
PROCEDURE get_holidays_broken_monthly (

out_sql_code OUT NUMBER, out_sql_error_message OUT VARCHAR2 )
IS

/* Local variables */

   l_holidays           holidays%ROWTYPE;
   l_monthly_date       CHAR (6);
   l_filedir            VARCHAR2 (100)  DEFAULT '/usr01/oracle';
   l_filename           VARCHAR2 (100)  DEFAULT 'holidays';
   l_procedure_called   VARCHAR2 (100);
   prev_mth number := -1;
   v_filehandle         UTL_FILE.file_type;

   /* Cursor declarations: */
   CURSOR c_holidays
   IS

      SELECT *
        FROM holidays
      where holiday_date >= '01/01/1999'
        and holiday_date <= '12/31/2004' ;

BEGIN
dbms_session.set_nls('nls_date_format','''mm/dd/yyyy'''); l_procedure_called := 'get_holidays_broken_monthly'; /* Open the output file */

v_filehandle := UTL_FILE.fopen (l_filedir, l_filename, 'w'); for i in c_holidays loop if to_number(to_char(i.holiday_date,'MM')) != prev_mth then if utl_file.is_open(v_filehandle) then UTL_FILE.fclose (v_filehandle); end if; l_filename := 'holidays'||'_'||to_char(i.holiday_date,'YYYYMM')||'.txt'; v_filehandle := UTL_FILE.fopen (l_filedir, l_filename, 'w'); prev_mth := to_number(to_char(i.holiday_date,'MM')); end if; UTL_FILE.put_line (v_filehandle, i.holiday_date); end loop; EXCEPTION WHEN UTL_FILE.invalid_path THEN raise_application_error (-20001,
'INVALID_PATH: File location or filename was
invalid.' ); WHEN UTL_FILE.invalid_mode THEN raise_application_error (-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was
invalid.' ); WHEN UTL_FILE.invalid_filehandle THEN raise_application_error (-20002,
'INVALID_FILEHANDLE: The file handle was invalid.'
); WHEN UTL_FILE.invalid_operation THEN raise_application_error (-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.' ); WHEN UTL_FILE.read_error THEN raise_application_error (-20004,
'READ_ERROR: An operating system error occurred
during the read operation.' ); WHEN UTL_FILE.write_error THEN raise_application_error (-20005,
'WRITE_ERROR: An operating system error occurred
during the write operation.' ); WHEN UTL_FILE.internal_error THEN raise_application_error (-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.'
); WHEN OTHERS THEN IF c_holidays%ISOPEN THEN CLOSE c_holidays; END IF; out_sql_code := SQLCODE; out_sql_error_message := SQLERRM || 'Error occurred in procedure ' || l_procedure_called || '!'; end; /

All data is written to files, except Dec 2004, which is the last month data. What am I missing?

Thanks again!
Viktor <stant_98_at_yahoo.com> wrote:
Thanks Connor,

I'll give it a try. but i am still not quite sure how i can manipulate the file name so that for month1, filename is month2 - Thanks again for your help!
Connor McDonald wrote:
declare
prev_mth number := -1;
begin
for i in my_big_24mth_cursor loop
if to_number(to_char(i.date_of_interest,'MM')) != prev_mth then if utl_file.is_open(f) then
close file;
end if;
open file;
prev_mth := to_number(to_char(i.date_of_interest,'MM')); end if;
write line to file
end loop;
end;

or thereabouts

hth
connor

Viktor wrote: > Hello, > > I have a procedure that open a cursor and dumps some data for last 24 months to a file. The date > range is static condition defined inside the cursor. Procedure is working great, but I what I'm > having problem figuring out is if there is a way to create not one file with all the data, but > multiple files with monthly sets of data. This way data for month1 is created as > and so forth. > > Thanks much! > > Viktor > > --------------------------------- > Do you Yahoo!? > Yahoo! SiteBuilder - Free web site building tool. Try it! > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request_at_freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > -----------------------------------------------------------------

Connor McDonald

Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net

email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun Feb 01 2004 - 13:35:20 CST

Re: UTL_FILE question - ALMOST DONE