Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i127EfK11040 for ; Mon, 2 Feb 2004 01:14:41 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i127Eao11032 for ; Mon, 2 Feb 2004 01:14:37 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2CF87394925; Mon, 2 Feb 2004 02:10:20 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 02 Feb 2004 02:09:12 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from web25202.mail.ukl.yahoo.com (web25202.mail.ukl.yahoo.com [217.12.10.62]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 2CB403949A3 for ; Mon, 2 Feb 2004 02:08:53 -0500 (EST) Message-ID: <20040202071528.94005.qmail@web25202.mail.ukl.yahoo.com> Received: from [165.187.10.2] by web25202.mail.ukl.yahoo.com via HTTP; Mon, 02 Feb 2004 07:15:28 GMT Date: Mon, 2 Feb 2004 07:15:28 +0000 (GMT) From: =?iso-8859-1?q?Connor=20McDonald?= Subject: Re: UTL_FILE question - ALMOST DONE To: oracle-l@freelists.org In-Reply-To: <20040201193520.75180.qmail@web13426.mail.yahoo.com> MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 524 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: hamcdc@yahoo.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Once you drop out of your final loop, don't forget to close the currently open file hth connor --- Viktor wrote: > 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 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@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@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@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@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@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@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@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 -----------------------------------------------------------------