Re: UTL_FILE question - ALMOST DONE
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 2 Feb 2004 07:15:28 +0000 (GMT)
Once you drop out of your final loop, don't forget to close the currently open
file
hth
connor
--- Viktor <stant_98@xxxxxxxxx> 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 <stant_98@xxxxxxxxx> 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@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //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@xxxxxxxxx
>
> "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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxx
"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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
