RE: UTL_FILE question
Title: Message I would create a new version of the procedure that took the date from and to as parameters and output a file named appropriately for the date range. You would then just call this 24 times. Niall -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ViktorSent: 31 January 2004 00:24To: Multiple recipients of list ORACLE-LSubject: UTL_FILE question 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!
UTL_FILE question
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!
Re: really annoying utl_file problem
Why not just trim the new line of the data, and allow put_line to do it. hth connor --- [EMAIL PROTECTED] wrote: > Im reading from user_source and writing the text of > packages to files. Problem is that if i use > UTL_FILE.PUT_LINE it adds an extra '/n'(end of line > character or space) into the file. since the code > itself is stored in the database with an end of line > character. > > If I use UTL_FILE.PUT or UTL_FILE.PUTF and attempt > to write it out after about 700 rows nothing else > gets written to the file. So I assumed the buffer > filled up, so every 200 rows I flushed the buffer. > > Same problem. I even tried opening and closing the > file handler. Same problem. > > any ideas on how to get around this? I know I can > just do a .sql and redirect it to a file in unix, > but Id prefer to keep it in the database. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "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" Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
really annoying utl_file problem
Im reading from user_source and writing the text of packages to files. Problem is that if i use UTL_FILE.PUT_LINE it adds an extra '/n'(end of line character or space) into the file. since the code itself is stored in the database with an end of line character. If I use UTL_FILE.PUT or UTL_FILE.PUTF and attempt to write it out after about 700 rows nothing else gets written to the file. So I assumed the buffer filled up, so every 200 rows I flushed the buffer. Same problem. I even tried opening and closing the file handler. Same problem. any ideas on how to get around this? I know I can just do a .sql and redirect it to a file in unix, but Id prefer to keep it in the database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: utl_file on Redhat Linux Oracle 9 standard engine
On Wednesday 09 July 2003 09:59, Matthew Zito wrote: > Plus the syntax is much more flexible (read: lazier) than C, so it saves > time. Interestingly enough, there are organizations that are starting > to decide that the perl's syntactical flexibility is a negative - look > at Yahoo's choice of PHP for its long-term application platform. They > said, among other things, that they were concerned about enforcing > coding standards in a Perl environment. Weenies. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: utl_file on Redhat Linux Oracle 9 standard engine
Those things, and its got a much better return on investment in terms of
extensibility. As a systems chappie, I started out writing things in
shell scripts, small C programs, etc. But I noticed that every time I
did anything that provided information (ran a report, data aggregation,
log mining, etc.), people always wanted it extended - "Oh, that's a
really neat bandwidth report, Matt. Now could you make it into a web
application?" "Oh, that web app is neat - could you have it page people
when the current bandwidth utilization exceeds a certain amount?", etc.
etc. Well, a lot of those things are much harder in C or shell scripts
than they are in Perl. So I just started writing everything in Perl if
I thought it was going to be run more than a few times - it just makes
it much easier to grow your scripts to add functionality you never
initially anticipated.
Plus the syntax is much more flexible (read: lazier) than C, so it saves
time. Interestingly enough, there are organizations that are starting
to decide that the perl's syntactical flexibility is a negative - look
at Yahoo's choice of PHP for its long-term application platform. They
said, among other things, that they were concerned about enforcing
coding standards in a Perl environment.
Thanks,
Matt
--
Matthew Zito
GridApp Systems
Email: [EMAIL PROTECTED]
Cell: 646-220-3551
Phone: 212-358-8211 x 359
http://www.gridapp.com
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> Behalf Of Richard Ji
> Sent: Wednesday, July 09, 2003 10:55 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: utl_file on Redhat Linux Oracle 9 standard engine
>
>
> Simpler, portability
>
> Richard
>
> -Original Message-
> Sent: Wednesday, July 09, 2003 10:04 AM
> To: Multiple recipients of list ORACLE-L
>
>
> seems like alot of long time DBAs prefer using perl over
> pro*c to do data loads and unloads. is it just because its
> simpler? or is it more robust? or other reasons?
>
>
> >
> > From: "Cary Millsap" <[EMAIL PROTECTED]>
> > Date: 2003/07/09 Wed AM 09:44:25 EDT
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: RE: utl_file on Redhat Linux Oracle 9 standard engine
> >
> > John,
> >
> > UTL_FILE is one of the worst designed functions I've ever tried to
> > use.
> >
> > In my opinion, it's a major design flaw to use the newline
> character
> > ('\n') as a packet delimiter. If UTL_FILE gets input lines that are
> > "too long" (too many bytes between '\n' characters), you'll get an
> > error. If you have short lines in your input (like
> > "Heading\nSubHeading\nLine1\nLine2\n..."), then you'll have lots of
> > nearly empty packets flying across your network, which creates a
> > horrible performance problem for the program using
> UTL_FILE, and for
> > others who have to compete against the traffic.
> >
> > Check out the trcfiled.pl part of Sparky
> > (www.hotsos.com/products/sparky). It's open source Perl
> that does file
> > transfers (and a few other things) on the order of 100x faster than
> > UTL_FILE. It's a free download.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
> > - Hotsos Symposium 2004, March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -Original Message-
> > Dunn
> > Sent: Wednesday, July 09, 2003 8:24 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > I have a problem with utl_file in Oracle 9 on Linux, standard
> > engine
> >
> > It does not seem to want to read lines longer than 997
> characters. It
> > works fine if the line is 997 characters or less.
> >
> > I get a utl_file.write_error exception if the line is
> longer than 997
> > charcaters!!! Why a write error when I am reading?
> >
> > I have set the line size in the utl_file.FOPEN and
> utl_file.read_line
> > to 998
> >
> >
> > Is this a bug?
> >
> >
> > original_kic_file_handle :=
> > utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998);
> >
> >
> >
> > utl_file.get_line(original_kic_file_handle,var_current_line,998);
> >
> >
> > John
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: h
RE: RE: utl_file on Redhat Linux Oracle 9 standard engine
Simpler, portability
Richard
-Original Message-
Sent: Wednesday, July 09, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L
seems like alot of long time DBAs prefer using perl over pro*c to do data loads and
unloads. is it just because its simpler? or is it more robust? or other reasons?
>
> From: "Cary Millsap" <[EMAIL PROTECTED]>
> Date: 2003/07/09 Wed AM 09:44:25 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: utl_file on Redhat Linux Oracle 9 standard engine
>
> John,
>
> UTL_FILE is one of the worst designed functions I've ever tried to use.
>
> In my opinion, it's a major design flaw to use the newline character ('\n')
> as a packet delimiter. If UTL_FILE gets input lines that are "too long" (too
> many bytes between '\n' characters), you'll get an error. If you have short
> lines in your input (like "Heading\nSubHeading\nLine1\nLine2\n..."), then
> you'll have lots of nearly empty packets flying across your network, which
> creates a horrible performance problem for the program using UTL_FILE, and
> for others who have to compete against the traffic.
>
> Check out the trcfiled.pl part of Sparky (www.hotsos.com/products/sparky).
> It's open source Perl that does file transfers (and a few other things) on
> the order of 100x faster than UTL_FILE. It's a free download.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
> - Hotsos Symposium 2004, March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -Original Message-
> Dunn
> Sent: Wednesday, July 09, 2003 8:24 AM
> To: Multiple recipients of list ORACLE-L
>
> I have a problem with utl_file in Oracle 9 on Linux, standard engine
>
> It does not seem to want to read lines longer than 997 characters. It works
> fine if the line is 997 characters or less.
>
> I get a utl_file.write_error exception if the line is longer than 997
> charcaters!!! Why a write error when I am reading?
>
> I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998
>
>
> Is this a bug?
>
>
> original_kic_file_handle :=
> utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998);
>
>
> utl_file.get_line(original_kic_file_handle,var_current_line,998);
>
>
> John
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: John Dunn
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Ji
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5
Re: utl_file on Redhat Linux Oracle 9 standard engine
Hi! Actually I read your post more thorougly, it seems that you have specified max linesize already. But this var_current_line, how is this one defined? If it's reduce it's size to varchar2 (to 998) as well. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 4:24 PM > I have a problem with utl_file in Oracle 9 on Linux, standard engine > > It does not seem to want to read lines longer than 997 characters. It works > fine if the line is 997 characters or less. > > I get a utl_file.write_error exception if the line is longer than 997 > charcaters!!! Why a write error when I am reading? > > I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998 > > > Is this a bug? > > > original_kic_file_handle := > utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998); > > > utl_file.get_line(original_kic_file_handle,var_current_line,998); > > > John > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: John Dunn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: utl_file on Redhat Linux Oracle 9 standard engine
seems like alot of long time DBAs prefer using perl over pro*c to do data loads and
unloads. is it just because its simpler? or is it more robust? or other reasons?
>
> From: "Cary Millsap" <[EMAIL PROTECTED]>
> Date: 2003/07/09 Wed AM 09:44:25 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: utl_file on Redhat Linux Oracle 9 standard engine
>
> John,
>
> UTL_FILE is one of the worst designed functions I've ever tried to use.
>
> In my opinion, it's a major design flaw to use the newline character ('\n')
> as a packet delimiter. If UTL_FILE gets input lines that are "too long" (too
> many bytes between '\n' characters), you'll get an error. If you have short
> lines in your input (like "Heading\nSubHeading\nLine1\nLine2\n..."), then
> you'll have lots of nearly empty packets flying across your network, which
> creates a horrible performance problem for the program using UTL_FILE, and
> for others who have to compete against the traffic.
>
> Check out the trcfiled.pl part of Sparky (www.hotsos.com/products/sparky).
> It's open source Perl that does file transfers (and a few other things) on
> the order of 100x faster than UTL_FILE. It's a free download.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
> - Hotsos Symposium 2004, March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -Original Message-
> Dunn
> Sent: Wednesday, July 09, 2003 8:24 AM
> To: Multiple recipients of list ORACLE-L
>
> I have a problem with utl_file in Oracle 9 on Linux, standard engine
>
> It does not seem to want to read lines longer than 997 characters. It works
> fine if the line is 997 characters or less.
>
> I get a utl_file.write_error exception if the line is longer than 997
> charcaters!!! Why a write error when I am reading?
>
> I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998
>
>
> Is this a bug?
>
>
> original_kic_file_handle :=
> utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998);
>
>
> utl_file.get_line(original_kic_file_handle,var_current_line,998);
>
>
> John
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: John Dunn
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: utl_file on Redhat Linux Oracle 9 standard engine
Thought I already was??? I set max_linesize to 998. I have also tried with 32767. utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',32767); Still no joy -Original Message- Sent: 09 July 2003 14:40 To: Multiple recipients of list ORACLE-L Hi! Desc utl_file shows: FUNCTION FOPEN RETURNS RECORD Argument Name TypeIn/Out Default? -- --- -- ID BINARY_INTEGER OUT DATATYPE BINARY_INTEGER OUT LOCATION VARCHAR2IN FILENAME VARCHAR2IN OPEN_MODE VARCHAR2IN MAX_LINESIZE BINARY_INTEGER IN DEFAULT Starting from 8.0.5 or so the max linesize was 32767 chars, but the default max linesize has remained same (1023) So use FOPEN with max_linesize. Btw, I found this answer from metalink with first search: utl_file line size Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 4:24 PM > I have a problem with utl_file in Oracle 9 on Linux, standard engine > > It does not seem to want to read lines longer than 997 characters. It works > fine if the line is 997 characters or less. > > I get a utl_file.write_error exception if the line is longer than 997 > charcaters!!! Why a write error when I am reading? > > I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998 > > > Is this a bug? > > > original_kic_file_handle := > utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998); > > > utl_file.get_line(original_kic_file_handle,var_current_line,998); > > > John > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: John Dunn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file on Redhat Linux Oracle 9 standard engine
John,
UTL_FILE is one of the worst designed functions I've ever tried to use.
In my opinion, it's a major design flaw to use the newline character ('\n')
as a packet delimiter. If UTL_FILE gets input lines that are "too long" (too
many bytes between '\n' characters), you'll get an error. If you have short
lines in your input (like "Heading\nSubHeading\nLine1\nLine2\n..."), then
you'll have lots of nearly empty packets flying across your network, which
creates a horrible performance problem for the program using UTL_FILE, and
for others who have to compete against the traffic.
Check out the trcfiled.pl part of Sparky (www.hotsos.com/products/sparky).
It's open source Perl that does file transfers (and a few other things) on
the order of 100x faster than UTL_FILE. It's a free download.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Dunn
Sent: Wednesday, July 09, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L
I have a problem with utl_file in Oracle 9 on Linux, standard engine
It does not seem to want to read lines longer than 997 characters. It works
fine if the line is 997 characters or less.
I get a utl_file.write_error exception if the line is longer than 997
charcaters!!! Why a write error when I am reading?
I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998
Is this a bug?
original_kic_file_handle :=
utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998);
utl_file.get_line(original_kic_file_handle,var_current_line,998);
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Dunn
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary Millsap
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: utl_file on Redhat Linux Oracle 9 standard engine
Hi! Desc utl_file shows: FUNCTION FOPEN RETURNS RECORD Argument Name TypeIn/Out Default? -- --- -- ID BINARY_INTEGER OUT DATATYPE BINARY_INTEGER OUT LOCATION VARCHAR2IN FILENAME VARCHAR2IN OPEN_MODE VARCHAR2IN MAX_LINESIZE BINARY_INTEGER IN DEFAULT Starting from 8.0.5 or so the max linesize was 32767 chars, but the default max linesize has remained same (1023) So use FOPEN with max_linesize. Btw, I found this answer from metalink with first search: utl_file line size Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 4:24 PM > I have a problem with utl_file in Oracle 9 on Linux, standard engine > > It does not seem to want to read lines longer than 997 characters. It works > fine if the line is 997 characters or less. > > I get a utl_file.write_error exception if the line is longer than 997 > charcaters!!! Why a write error when I am reading? > > I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998 > > > Is this a bug? > > > original_kic_file_handle := > utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998); > > > utl_file.get_line(original_kic_file_handle,var_current_line,998); > > > John > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: John Dunn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
utl_file on Redhat Linux Oracle 9 standard engine
I have a problem with utl_file in Oracle 9 on Linux, standard engine It does not seem to want to read lines longer than 997 characters. It works fine if the line is 997 characters or less. I get a utl_file.write_error exception if the line is longer than 997 charcaters!!! Why a write error when I am reading? I have set the line size in the utl_file.FOPEN and utl_file.read_line to 998 Is this a bug? original_kic_file_handle := utl_file.FOPEN(var_transfer_dir,var_file_name||'.KIC','r',998); utl_file.get_line(original_kic_file_handle,var_current_line,998); John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file performance
Title: RE: utl_file performance John, http://tinyurl.com/e8d1 ... not much info there. But mostly performance cannot be general, because everyone's processign is different. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, June 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am told that utl_file performance is improved under Oracle 9 . Does anyone have any comparisons of UTL_FILE performance between 8.1.7 and 9.2? John This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: utl_file performance
I thought question was about performance comparison... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Regis Biassala Sent: 13. júna 2003 9:09 To: Multiple recipients of list ORACLE-L You could do alter session set UTL_FILE_DIR for instancebut the ora docs has it all -Original Message- Sent: Friday, June 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am told that utl_file performance is improved under Oracle 9 . Does anyone have any comparisons of UTL_FILE performance between 8.1.7 and 9.2? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
utl_file performance
I am told that utl_file performance is improved under Oracle 9 . Does anyone have any comparisons of UTL_FILE performance between 8.1.7 and 9.2? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file performance
You could do alter session set UTL_FILE_DIR for instancebut the ora docs has it all -Original Message- Sent: Friday, June 13, 2003 2:30 PM To: Multiple recipients of list ORACLE-L I am told that utl_file performance is improved under Oracle 9 . Does anyone have any comparisons of UTL_FILE performance between 8.1.7 and 9.2? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UTL_FILE
Title: UTL_FILE
Thanks Tom, Tom and Andreas. I was
dreading that answer. At least now I can present this info with various
options. I did get most of what I needed via DBMS_OUTPUT, however, that is not
a good long-term solution.
I noticed the TEXT_IO package and that will
be interesting to pursue. I'm relatively new to Forms but it can't
be that hard, right :)
Eric Harrington
-Original Message-
From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 4:10
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: UTL_FILE
Hi Eric!
-Original
Message-
From: Harrington, Eric
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 8:30
PM
To: Multiple recipients of list
ORACLE-L
Subject: UTL_FILE
Any help would be appreciated...
Is it possible to write a file to the local OS if the
database is remote? Should I be using UTL_FILE or another package?
Not possible as
far as I know with UTL_FILE. Some Oracle client software (e.g.: Forms) uses a
similar
package
'TEXT_IO', which allows access to local ( client side ) files.
I'm attempting to write to a file on my local OS
(Windows NT 4.0). The database version is 9i r2 and resides on a
HP-UX box. I've created a directory object as follows:
CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\';
The directory
you with files for UTL_FILE access must
be on the server
side (your HP-UX box).
So you could do
something like
CREATE OR REPLACE
DIRECTORY utl_file_dir AS '/tmp';
The pathname of the file should
respect the server's OS conventions, on
UNIX you don't use drive
letters like 'c:', the UNIX directory separator is '/' not '\'
This is why you get
ORA-29280.
Of course you
could mount a remote share (NFS, SAMBA for MS-Windows...)
on your UNIX
machine to allow the Oracle server to read/write files,
but I'd prefer
local drives ( bad performance, network problems...).
The output from the SQL statement:
select
* from all_directories; follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
- --
SYS
UTL_FILE_DIR c:\
File handle code from my procedure follows:
l_FileHandle :=
UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the
following error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
regards
Andreas
Re: UTL_FILE
yes, bufferedReader and printWriter should do it. they work alot like
UTL_FILE. there is sample code for these at sun.com
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, March 11, 2003 3:28 PM
> I think there are java classes which can write across the network, but I'm
unsure of how those would be implemented, especially in Oracle. You might
try posting a question about this at devtrends.oracle.com or do a web
search.
>
> <<< [EMAIL PROTECTED] 3/11 2:25p >>>
> Eric
>
> Utl_file only write to the database server. You can use Dbms_Output and
> spool the results to the local machine. Or 'select' the data to be
spooled
> out.
>
> Hope this helps!
>
> Tom Mercadante
> Oracle Certified Professional
>
> -Original Message-
> Sent: Tuesday, March 11, 2003 2:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Any help would be appreciated...
>
> Is it possible to write a file to the local OS if the database is remote?
> Should I be using UTL_FILE or another package?
>
> I'm attempting to write to a file on my local OS (Windows NT 4.0). The
> database version is 9i r2 and resides on a HP-UX box. I've created a
> directory object as follows:
>
> CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\';
>
> The output from the SQL statement: select * from all_directories; follows:
>
> OWNER DIRECTORY_NAME DIRECTORY_PATH
>
> - --
>
> SYS UTL_FILE_DIR c:\
>
> File handle code from my procedure follows:
>
> l_FileHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
>
> When executing the associated procedure I get the following error:
>
> ORA-29280: invalid directory path
>
> Thanks, Eric Harrington
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Darrell Landrum
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ryan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: UTL_FILE
Title: UTL_FILE
Hi Eric!
-Original Message-From: Harrington, Eric
[mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003
8:30 PMTo: Multiple recipients of list ORACLE-LSubject:
UTL_FILE
Any help would be
appreciated...
Is it possible to write a file to
the local OS if the database is remote? Should I be using UTL_FILE or another
package?
Not possible as far as I know with UTL_FILE. Some
Oracle client software (e.g.: Forms) uses a similar
package 'TEXT_IO', which allows access to local (
client side ) files.
I'm attempting to write to a
file on my local OS (Windows NT 4.0). The database version is 9i r2 and resides on a HP-UX
box. I've created a directory object as
follows:
CREATE OR REPLACE DIRECTORY
utl_file_dir AS 'c:\';
The directory you with
files for UTL_FILE access must
be on the server side (your
HP-UX box).
So you could do something
like
CREATE OR REPLACE DIRECTORY utl_file_dir AS
'/tmp';
The pathname of the file should respect the server's
OS conventions, on
UNIX you don't use drive letters like 'c:', the
UNIX directory separator is '/' not '\'
This is why you get
ORA-29280.
Of course you could mount a
remote share (NFS,
SAMBA for MS-Windows...)
on
your UNIX machine
to allow the Oracle server to read/write
files,
but I'd prefer local drives
( bad performance, network problems...).
The output from the SQL statement: select * from
all_directories; follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
-
--
SYS
UTL_FILE_DIR
c:\
File handle code from my procedure follows:
l_FileHandle :=
UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the following
error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
regards
Andreas
RE: UTL_FILE
He needs to make his Windows C: drive into a network mounted drive on the
HP box.
I remember back in a mixed AIX/WinNT environment I was able to mount a
CD-ROM drive on the AIX box as a network drive on the WinNT box. I don't
know if it's possible to do it the other way round, especially in an
HP/Windows environment.
If it can be done, that's what you need to do. Then define the path as the
HP server would see it, not as your Windows client would see it.
"Mercadante,
Thomas F"To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: root
03/11/2003 02:53
PM
Please respond
to ORACLE-L
Eric
Utl_file only write to the database server. You can use Dbms_Output and
spool the results to the local machine. Or 'select' the data to be spooled
out.
Hope this helps!
Tom Mercadante
Oracle Certified Professional
-Original Message-
From: Harrington, Eric [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 2:30 PM
To: Multiple recipients of list ORACLE-L
Subject: UTL_FILE
Any help would be appreciated...
Is it possible to write a file to the local OS if the database is
remote? Should I be using UTL_FILE or another package?
I'm attempting to write to a file on my local OS (Windows NT 4.0).
The database version is 9i r2 and resides on a HP-UX box. I've
created a directory object as follows:
CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\';
The output from the SQL statement: select * from all_directories;
follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
- --
SYS UTL_FILE_DIR c:\
File handle code from my procedure follows:
l_FileHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the following error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: UTL_FILE
I think there are java classes which can write across the network, but I'm unsure of
how those would be implemented, especially in Oracle. You might try posting a
question about this at devtrends.oracle.com or do a web search.
<<< [EMAIL PROTECTED] 3/11 2:25p >>>
Eric
Utl_file only write to the database server. You can use Dbms_Output and
spool the results to the local machine. Or 'select' the data to be spooled
out.
Hope this helps!
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, March 11, 2003 2:30 PM
To: Multiple recipients of list ORACLE-L
Any help would be appreciated...
Is it possible to write a file to the local OS if the database is remote?
Should I be using UTL_FILE or another package?
I'm attempting to write to a file on my local OS (Windows NT 4.0). The
database version is 9i r2 and resides on a HP-UX box. I've created a
directory object as follows:
CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\';
The output from the SQL statement: select * from all_directories; follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
- --
SYS UTL_FILE_DIR c:\
File handle code from my procedure follows:
l_FileHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the following error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: UTL_FILE
Title: UTL_FILE
Eric
Utl_file only write to the database server. You
can use Dbms_Output and spool the results to the local machine. Or
'select' the data to be spooled out.
Hope
this helps!
Tom Mercadante Oracle Certified Professional
-Original Message-From: Harrington, Eric
[mailto:[EMAIL PROTECTED]Sent: Tuesday, March 11, 2003
2:30 PMTo: Multiple recipients of list ORACLE-LSubject:
UTL_FILE
Any help would be
appreciated...
Is it possible to write a file to the
local OS if the database is remote? Should I be using UTL_FILE or another
package?
I'm attempting to write to a file on my
local OS (Windows NT 4.0). The database version
is 9i r2 and resides on a HP-UX
box. I've created a directory object as
follows:
CREATE OR REPLACE DIRECTORY utl_file_dir
AS 'c:\';
The output from the SQL statement: select * from
all_directories; follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
-
--
SYS
UTL_FILE_DIR
c:\
File handle code from my procedure follows:
l_FileHandle :=
UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the following
error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
UTL_FILE
Title: UTL_FILE
Any help would be appreciated...
Is it possible to write a file to the local OS if the database is remote? Should I be using UTL_FILE or another package?
I'm attempting to write to a file on my local OS (Windows NT 4.0). The database version is 9i r2 and resides on a HP-UX box. I've created a directory object as follows:
CREATE OR REPLACE DIRECTORY utl_file_dir AS 'c:\';
The output from the SQL statement: select * from all_directories; follows:
OWNER DIRECTORY_NAME DIRECTORY_PATH
- --
SYS UTL_FILE_DIR c:\
File handle code from my procedure follows:
l_FileHandle := UTL_FILE.FOPEN('UTL_FILE_DIR','role.txt','r');
When executing the associated procedure I get the following error:
ORA-29280: invalid directory path
Thanks, Eric Harrington
Oracle 9.2 - cyclical overwrite of files written with UTL_FILE in
Hi! We've encountered a strange phenomenon since we changed from Oracle 8.1.7 to Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production We use the UTL_FILE package to write log messages into files on the oracle server (WIN2K). Putting a single line of text into the logfile consists of basically ... UTL_FILE.FOPEN... UTL_FILE.PUT_LINE... -- always in append mode -- FFLUSH is a bit paranoid, FCLOSE should do, but it worked before. -- We tried without FFLUSH, using the new -- 9.x FOPEN parameter autoflush instead of: same thing... UTL_FILE.FFLUSH... UTL_FILE.FCLOSE... ... Before the release change we could inspect the logfiles ( remotely with win2k clients using a file share ) with any text editor *while* the oracle process was appending lines to the logfiles without any problem. Repeatedly opening - closing - reopening the file - even (re)moving or modifying it - worked perfectly, as the file was recreated or appended to as soon as the next log entry occurred. But now in 9.2, if we open-close-reopen a logfile ( without modifying the file in the editor ) while it is being appended to, it will be periodically truncated after having grown up to approximately 1000 lines (20-100 characters per line on average). Before the truncation happens, the last line contains dozens of ASCII 0 characters. ( We were able to catch this peculiarity using the free PFE-Editor, which can detect that a file has been changed by another application while you are viewing the file.) After truncation the former last line with the ASCII NULLS has disappeared. However, if we don't "touch" the file with an editor - we tried a few different ones, including Notepad - while it is being written, truncation does not happen. This "phenomenon" is rather annoying, we are used to inspect the logfiles to monitor our oracle jobs' performance/progress/errors, moreover we use UTL_FILE to create CSV files for exporting data to other applications. If anybody opens such a file unaware of the fact, that the file is being currently written to, data loss is very likely... Please excuse my poor English. Any ideas or similar experiences or just another Oracle bug? Thanks in advance for your thoughts, Andreas -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PL/SQL - Exception Handling for utl_file
Perhaps someone on the list will know the answer as I have not found anything in the manual (though I might have missed it). If a procedure is performing file i/o to more than one file and an exception is raised related to the utl_file package how can I determine which file had the exception? eg. begin ... utl_file.put_line(fh_extract,v_extract_dataline); utl_file.put_line(fh_log,v_logmsg); ... exception when utl_file.write_error then ... end; David _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: david davis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: National characters and UTL_FILE
Hello Vladimir I think that you need to take a look at the file before and after applying "unix2dos". Since you work with CE characters, maybe the conversion program can not handle this. BTW - in Oracle 9.2 you can use utl_file to write Unicode to the file. Maybe this will help you. Yechiel AdarMehish - Original Message - From: Vladimir Barac To: Multiple recipients of list ORACLE-L Sent: Friday, December 06, 2002 1:58 PM Subject: National characters and UTL_FILE Hello to everyone I want to write national characters (Central European) to ASCII file, by using utl_file. UTL_FILE is called within unix script, and afterwards ascii file (after applying "unix2dos") is sent as an attachment (by using "uuenview"). Both NLS_CHARACTERSET (EE8ISO8859P2) parameters are same within script (env.variable) and database. But, instead of those specific letters I receive mail with useless content. I have then tried binary ftp of ASCII file, but to no avail. I'm entering and reading those characters trough Forms with no problem at all. So, is it possible to write CE characters (DOS extended character set actually) into ascii file? Regional settings at Windows client (where mail or ftp are received) are appropriate (meaning, they are not set as USA...)...
Re: National characters and UTL_FILE
Vladimir, Have you had any opportunity to look at the file immediately after it is produced from Oracle on the server (before being attached to the mail) - this might eliminate "uuenview"? Can you display the special characters using dbms_output.put_line? If the problem is isolated to utl_file then perhaps you can consider spooling a statement to a file from SQL*Plus - In the past I have found this to give better performance anyway, although if you have a lot of complex logic you might need to create a temporary table with the results and then spool that table. hth, Mark. "Vladimir Barac" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: National characters and UTL_FILE Sent by: [EMAIL PROTECTED] om 06/12/2002 22:58 Please respond to ORACLE-L Hello to everyone I want to write national characters (Central European) to ASCII file, by using utl_file. UTL_FILE is called within unix script, and afterwards ascii file (after applying "unix2dos") is sent as an attachment (by using "uuenview"). Both NLS_CHARACTERSET (EE8ISO8859P2) parameters are same within script (env.variable) and database. But, instead of those specific letters I receive mail with useless content. I have then tried binary ftp of ASCII file, but to no avail. I'm entering and reading those characters trough Forms with no problem at all. So, is it possible to write CE characters (DOS extended character set actually) into ascii file? Regional settings at Windows client (where mail or ftp are received) are appropriate (meaning, they are not set as USA...)... <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
National characters and UTL_FILE
Hello to everyone I want to write national characters (Central European) to ASCII file, by using utl_file. UTL_FILE is called within unix script, and afterwards ascii file (after applying "unix2dos") is sent as an attachment (by using "uuenview"). Both NLS_CHARACTERSET (EE8ISO8859P2) parameters are same within script (env.variable) and database. But, instead of those specific letters I receive mail with useless content. I have then tried binary ftp of ASCII file, but to no avail. I'm entering and reading those characters trough Forms with no problem at all. So, is it possible to write CE characters (DOS extended character set actually) into ascii file? Regional settings at Windows client (where mail or ftp are received) are appropriate (meaning, they are not set as USA...)...
RE: Utl_file and OPENVMS
Jared, Thanks..very nice. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 14:13:46 -0800 It is documented: http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1219.htm#945570 "Mohammad Rafiq" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/03/2002 02:11 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS I am reproducing below an email from John Kanagaraj for your info.. It might help you further... Regards Rafiq Date: Thu, 15 Aug 2002 00:08:35 -0800 Kathy, This is probably not documented, but you can have multiple lines of UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any gaps / lines inbetween them (in which case the last set overrides the previous ones). I have the following in a 7.3.4 Db, all at the end (and I bet that you are recognizing your favorite ERP system therein - Oops my version is showing :) # # VERY VERY IMPORTANT *** # *** Keep ALL lines for UTL_FILE_DIR *** # *** together at the end of the file *** # *** Otherwise only the last set is *** # *** effective *** # VERY VERY IMPORTANT *** # utl_file_dir = /u001/app/smartdb/files/data/AR utl_file_dir = /u01/home/arftp/edi utl_file_dir = /u01/home/xxusc4xx utl_file_dir = /u009/app//R10.7/xxloftware/loftware/error utl_file_dir = /u009/app//R10.7/xxloftware/loftware/log utl_file_dir = /u009/app//R10.7/xxloftware/loftware/processed utl_file_dir = /u009/app//R10.7/xxloftware/loftware I believe you will have to either name specific directories or use a single '*' (the latter is _not_ a good idea!) John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 12:45:17 -0800 so right you are! i just tested in sqlplus and it shows both. thanks. one thing noticed, is that if you include another file w/ utl_file_dir setting to something, it ignores the original setting, i.e. part of my problem. all my initsid.ora files include a common 8i init.ora. in the common init.ora file i have this set, but it gets reset not appended to when i set utl_file_dir it in the initsid.ora file. thanks again. >>> [EMAIL PROTECTED] 10/03/02 04:03PM >>> Here you are right. You will see here only one because of width. Please don't rely on svrmgrl for such info. Instead use sqlplus and check it from v$parameter result is PARAMETER -- VALUE utl_file_dir /u327/applmgr/10_7/finprod_output/hfs_data, /u327/applmgr/10_7/har/1.0.0/mm_cbda ta Both entries are appearing here, HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 11:13:42 -0800 hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last. >>> [EMAIL PROTECTED] 10/03/02 01:13PM >>> Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared
RE: Utl_file and OPENVMS
It is documented:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch1219.htm#945570
"Mohammad Rafiq" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/03/2002 02:11 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Utl_file and OPENVMS
I am reproducing below an email from John Kanagaraj for your info..
It might help you further...
Regards
Rafiq
Date: Thu, 15 Aug 2002 00:08:35 -0800
Kathy,
This is probably not documented, but you can have multiple lines of
UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any
gaps
/ lines inbetween them (in which case the last set overrides the
previous
ones). I have the following in a 7.3.4 Db, all at the end (and I bet
that
you are recognizing your favorite ERP system therein - Oops my version
is
showing :)
#
# VERY VERY IMPORTANT ***
# *** Keep ALL lines for UTL_FILE_DIR ***
# *** together at the end of the file ***
# *** Otherwise only the last set is ***
# *** effective ***
# VERY VERY IMPORTANT ***
#
utl_file_dir = /u001/app/smartdb/files/data/AR
utl_file_dir = /u01/home/arftp/edi
utl_file_dir = /u01/home/xxusc4xx
utl_file_dir = /u009/app//R10.7/xxloftware/loftware/error
utl_file_dir = /u009/app//R10.7/xxloftware/loftware/log
utl_file_dir = /u009/app//R10.7/xxloftware/loftware/processed
utl_file_dir = /u009/app//R10.7/xxloftware/loftware
I believe you will have to either name specific directories or use a
single
'*' (the latter is _not_ a good idea!)
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 12:45:17 -0800
so right you are! i just tested in sqlplus and it shows both. thanks. one
thing noticed, is that if you include another file w/ utl_file_dir setting
to something, it ignores the original setting, i.e. part of my problem.
all my initsid.ora files include a common 8i init.ora. in the common
init.ora file i have this set, but it gets reset not appended to when i
set
utl_file_dir it in the initsid.ora file. thanks again.
>>> [EMAIL PROTECTED] 10/03/02 04:03PM >>>
Here you are right. You will see here only one because of width. Please
don't rely on svrmgrl for such info. Instead use sqlplus and check it from
v$parameter
result is
PARAMETER
--
VALUE
utl_file_dir
/u327/applmgr/10_7/finprod_output/hfs_data,
/u327/applmgr/10_7/har/1.0.0/mm_cbda
ta
Both entries are appearing here,
HTH
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 11:13:42 -0800
hmm, when you go into svrmgrl connect internal and show parameters, do
both
show up or just the last one? on my system, i only see the last.
>>> [EMAIL PROTECTED] 10/03/02 01:13PM >>>
Like these
utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data
utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata
and these are the last entries in initSID.ora file.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 07:58:38 -0800
What does your utl_file_dir parameter look like in your init.ora? I am
using AIX.
>>> [EMAIL PROTECTED] 10/03/02 11:43AM >>>
What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no
problem with separate entries as mentioned by Jared. However, don't keep
gap
between these entries...and define it the way it was explained by Jared.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 03 Oct 2002 05:28:22 -0800
On unix, multiple entries in the init.ora file results in the last entry
being the only valid value (i.e. last time variable is set). I tested
below
and only dir3 is listed as a utl_file_dir parameter. But if you comma
delimit them w/ 1 instance of the variable then all dir's are listed.
Maybe
NT is different, fortunately never had to support Oracle on NT :).
Gene
>>> [EMAIL PROTECTED] 10/02/02 07:13PM >>>
Gene,
> utl_file_dir = D:\directory name1
> utl_file_dir = D:\directory name2
> utl_file_dir = D:\directory name3
The multiple lines shown are actually the documented method for doing
this.
The single line with comma delimited entries may also work, though I'm
not sure about it.
Jared
"Gene Sais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/01/2002 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of li
RE: Utl_file and OPENVMS
I am reproducing below an email from John Kanagaraj for your info.. It might help you further... Regards Rafiq Date: Thu, 15 Aug 2002 00:08:35 -0800 Kathy, This is probably not documented, but you can have multiple lines of UTL_FILE_DIR _anywhere_ as _long_as_ you bunch them all up without any gaps / lines inbetween them (in which case the last set overrides the previous ones). I have the following in a 7.3.4 Db, all at the end (and I bet that you are recognizing your favorite ERP system therein - Oops my version is showing :) # # VERY VERY IMPORTANT *** # *** Keep ALL lines for UTL_FILE_DIR *** # *** together at the end of the file *** # *** Otherwise only the last set is *** # *** effective *** # VERY VERY IMPORTANT *** # utl_file_dir = /u001/app/smartdb/files/data/AR utl_file_dir = /u01/home/arftp/edi utl_file_dir = /u01/home/xxusc4xx utl_file_dir = /u009/app//R10.7/xxloftware/loftware/error utl_file_dir = /u009/app//R10.7/xxloftware/loftware/log utl_file_dir = /u009/app//R10.7/xxloftware/loftware/processed utl_file_dir = /u009/app//R10.7/xxloftware/loftware I believe you will have to either name specific directories or use a single '*' (the latter is _not_ a good idea!) John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 12:45:17 -0800 so right you are! i just tested in sqlplus and it shows both. thanks. one thing noticed, is that if you include another file w/ utl_file_dir setting to something, it ignores the original setting, i.e. part of my problem. all my initsid.ora files include a common 8i init.ora. in the common init.ora file i have this set, but it gets reset not appended to when i set utl_file_dir it in the initsid.ora file. thanks again. >>> [EMAIL PROTECTED] 10/03/02 04:03PM >>> Here you are right. You will see here only one because of width. Please don't rely on svrmgrl for such info. Instead use sqlplus and check it from v$parameter result is PARAMETER -- VALUE utl_file_dir /u327/applmgr/10_7/finprod_output/hfs_data, /u327/applmgr/10_7/har/1.0.0/mm_cbda ta Both entries are appearing here, HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 11:13:42 -0800 hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last. >>> [EMAIL PROTECTED] 10/03/02 01:13PM >>> Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file
RE: Utl_file and OPENVMS
so right you are! i just tested in sqlplus and it shows both. thanks. one thing noticed, is that if you include another file w/ utl_file_dir setting to something, it ignores the original setting, i.e. part of my problem. all my initsid.ora files include a common 8i init.ora. in the common init.ora file i have this set, but it gets reset not appended to when i set utl_file_dir it in the initsid.ora file. thanks again. >>> [EMAIL PROTECTED] 10/03/02 04:03PM >>> Here you are right. You will see here only one because of width. Please don't rely on svrmgrl for such info. Instead use sqlplus and check it from v$parameter result is PARAMETER -- VALUE utl_file_dir /u327/applmgr/10_7/finprod_output/hfs_data, /u327/applmgr/10_7/har/1.0.0/mm_cbda ta Both entries are appearing here, HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 11:13:42 -0800 hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last. >>> [EMAIL PROTECTED] 10/03/02 01:13PM >>> Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LO
RE: Utl_file and OPENVMS
Here you are right. You will see here only one because of width. Please don't rely on svrmgrl for such info. Instead use sqlplus and check it from v$parameter result is PARAMETER -- VALUE utl_file_dir /u327/applmgr/10_7/finprod_output/hfs_data, /u327/applmgr/10_7/har/1.0.0/mm_cbda ta Both entries are appearing here, HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 11:13:42 -0800 hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last. >>> [EMAIL PROTECTED] 10/03/02 01:13PM >>> Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Cu
RE: Utl_file and OPENVMS
hmm, when you go into svrmgrl connect internal and show parameters, do both show up or just the last one? on my system, i only see the last. >>> [EMAIL PROTECTED] 10/03/02 01:13PM >>> Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In
RE: Utl_file and OPENVMS
Like these utl_file_dir=/u327/applmgr/10_7/finprod_output/hfs_data utl_file_dir=/u327/applmgr/10_7/har/1.0.0/mm_cbdata and these are the last entries in initSID.ora file. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 07:58:38 -0800 What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In
RE: Utl_file and OPENVMS
What does your utl_file_dir parameter look like in your init.ora? I am using AIX. >>> [EMAIL PROTECTED] 10/03/02 11:43AM >>> What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- O
RE: Utl_file and OPENVMS
What flavor of unix...ON HP-UX 10.20/11 Oracle ver 7.3.4.5 we have no problem with separate entries as mentioned by Jared. However, don't keep gap between these entries...and define it the way it was explained by Jared. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 03 Oct 2002 05:28:22 -0800 On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','
RE: Utl_file and OPENVMS
On unix, multiple entries in the init.ora file results in the last entry being the only valid value (i.e. last time variable is set). I tested below and only dir3 is listed as a utl_file_dir parameter. But if you comma delimit them w/ 1 instance of the variable then all dir's are listed. Maybe NT is different, fortunately never had to support Oracle on NT :). Gene >>> [EMAIL PROTECTED] 10/02/02 07:13PM >>> Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file. Utl_File.Put (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load _Service_Name); Utl_File.New_Line(L_Par_File_Hand);
RE: Utl_file and OPENVMS
I did it like the control_files entry:
utl_file_dir = (C:\TEMP,
D:\OraNT\Archive)
...and it works just fine.
Cheers,
Mike
-Original Message-
Sent: Wednesday, October 02, 2002 4:14 PM
To: Multiple recipients of list ORACLE-L
Gene,
> utl_file_dir = D:\directory name1
> utl_file_dir = D:\directory name2
> utl_file_dir = D:\directory name3
The multiple lines shown are actually the documented method for doing
this.
The single line with comma delimited entries may also work, though I'm
not sure about it.
Jared
"Gene Sais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/01/2002 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Utl_file and OPENVMS
i believe its
utl_file_dir=dir1,dir2,dir3,...
in your example, dir 3 would be the only valid dir.
hth,
gene
>>> [EMAIL PROTECTED] 10/01/02 12:53PM >>>
Make sure you have the directory name entry covered in INIT.ORA
e.g.
utl_file_dir = D:\directory name1
utl_file_dir = D:\directory name2
utl_file_dir = D:\directory name3
You need one entry per directory that you want to write to using UTL_FILE
Package
Hope this helps
Regards
Shiva
-Original Message-
Sent: Tuesday, October 01, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
the vms user oracle needs rights to the directory.
>>> [EMAIL PROTECTED] 09/30/02 10:53AM >>>
List,
I have a package that creates files on the server. The directory
location and file name are obtained from tables in oracle. The procedure
works as designed on Novell 7.3.4 and no changes were needed when the
database way loaded on Linux Oracle 8.1.7. I am trying to move the
database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
get the package to write the files to the OS directory.
The package is created by the Oracle user DTSUSER and executed by
DTSUSER. There is no OPENVMS user DTSUSER.
The sysadmin assures me that the permissions are correct to write to
the directory.
I have place a Dbms_output in the package to display the directory
information and it looks correct.
Is there anything different that has to be done to an OPENVMS server
that will allow a package to write to a directory using the Utl_File
package?
Listing from the Oracle tables:
DTS_PARAMETER_NAME DTS_PARAMETER_VALUE
-- --
LOAD_USERIDLOADITUP
LOAD_PASSWORD ILOADIT
LOAD_SERVICE_NAME GLC_ALPHADEV-TCP
LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]
LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]
LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]
LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]
LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]
LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]
Listing from the package that writes the parameter file:
Procedure Create_New_Par_File
(
P_Current_Table_Name In Varchar2
,
P_Run_DateIn Date,
P_Load_Userid In Varchar2
,
P_Load_Password In Varchar2
,
P_Load_Service_Name In Varchar2
,
P_Load_Par_File_Dir In Varchar2
,
P_Load_Data_File_Dir In Varchar2
,
P_Load_Control_File_Dir In Varchar2 ,
P_Load_Log_File_Dir In Varchar2
,
P_Load_Bad_File_Dir In Varchar2
,
P_Load_Discard_File_Dir In Varchar2
)
as
Begin
Declare
L_Par_File_Hand Utl_FIle.File_Type; -- Local
variable to
hold the File Pointer for the parameter file.
Begin
I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
EN TO.
-- Open a new parameter file
L_Par_File_Hand :=
Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');
-- Print the following lines into the parameter file.
Utl_File.Put
(L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
_Service_Name);
Utl_File.New_Line(L_Par_File_Hand);
If Not P_Current_Table_Name = 'GLCRET'
Then
Utl_File.Put
(L_Par_File_Hand,'Errors=1');
Else
Re: Utl_file and OPENVMS
I have seen comma delimited entries working... Babu - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 02, 2002 6:13 PM Gene, > utl_file_dir = D:\directory name1 > utl_file_dir = D:\directory name2 > utl_file_dir = D:\directory name3 The multiple lines shown are actually the documented method for doing this. The single line with comma delimited entries may also work, though I'm not sure about it. Jared "Gene Sais" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/01/2002 11:25 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Utl_file and OPENVMS i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_DateIn Date, P_Load_Userid In Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file. Utl_File.Put (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load _Service_Name); Utl_File.New_Line(L_Par_File_Hand); If Not P_Current_Table_Name = 'GLCRET' Then Utl_File.Put (L_Par_File_Hand,'Errors=1'); Else Utl_File.Put (L_Par_File_Hand,'Errors=50'); End If; Utl_File.New_Line(L_Par
RE: Utl_file and OPENVMS
Gene,
> utl_file_dir = D:\directory name1
> utl_file_dir = D:\directory name2
> utl_file_dir = D:\directory name3
The multiple lines shown are actually the documented method for doing
this.
The single line with comma delimited entries may also work, though I'm
not sure about it.
Jared
"Gene Sais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/01/2002 11:25 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Utl_file and OPENVMS
i believe its
utl_file_dir=dir1,dir2,dir3,...
in your example, dir 3 would be the only valid dir.
hth,
gene
>>> [EMAIL PROTECTED] 10/01/02 12:53PM >>>
Make sure you have the directory name entry covered in INIT.ORA
e.g.
utl_file_dir = D:\directory name1
utl_file_dir = D:\directory name2
utl_file_dir = D:\directory name3
You need one entry per directory that you want to write to using UTL_FILE
Package
Hope this helps
Regards
Shiva
-Original Message-
Sent: Tuesday, October 01, 2002 9:48 AM
To: Multiple recipients of list ORACLE-L
the vms user oracle needs rights to the directory.
>>> [EMAIL PROTECTED] 09/30/02 10:53AM >>>
List,
I have a package that creates files on the server. The directory
location and file name are obtained from tables in oracle. The procedure
works as designed on Novell 7.3.4 and no changes were needed when the
database way loaded on Linux Oracle 8.1.7. I am trying to move the
database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't
get the package to write the files to the OS directory.
The package is created by the Oracle user DTSUSER and executed by
DTSUSER. There is no OPENVMS user DTSUSER.
The sysadmin assures me that the permissions are correct to write to
the directory.
I have place a Dbms_output in the package to display the directory
information and it looks correct.
Is there anything different that has to be done to an OPENVMS server
that will allow a package to write to a directory using the Utl_File
package?
Listing from the Oracle tables:
DTS_PARAMETER_NAME DTS_PARAMETER_VALUE
-- --
LOAD_USERIDLOADITUP
LOAD_PASSWORD ILOADIT
LOAD_SERVICE_NAME GLC_ALPHADEV-TCP
LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR]
LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles]
LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL]
LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG]
LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD]
LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD]
Listing from the package that writes the parameter file:
Procedure Create_New_Par_File
(
P_Current_Table_Name In Varchar2
,
P_Run_DateIn Date,
P_Load_Userid In Varchar2
,
P_Load_Password In Varchar2
,
P_Load_Service_Name In Varchar2
,
P_Load_Par_File_Dir In Varchar2
,
P_Load_Data_File_Dir In Varchar2
,
P_Load_Control_File_Dir In Varchar2 ,
P_Load_Log_File_Dir In Varchar2
,
P_Load_Bad_File_Dir In Varchar2
,
P_Load_Discard_File_Dir In Varchar2
)
as
Begin
Declare
L_Par_File_Hand Utl_FIle.File_Type; -- Local
variable to
hold the File Pointer for the parameter file.
Begin
I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE
PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT
EN TO.
-- Open a new parameter file
L_Par_File_Hand :=
Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w');
-- Print the following lines into the parameter file.
Utl_File.Put
(L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load
_Service_Name);
Utl_File.New_Line(L_Par_File_Hand);
If Not P_Current_Table_Name = 'GLCRET'
Then
Utl_File.Put
(L_Par_File_Hand,'Errors=1');
Else
Utl_File.Put
(L_Par_File_Hand,'Errors=50');
End If;
Utl_File.New_Line(L_Par_File_Hand);
Utl_File.Put
(L_Par_File_Hand,'Control='||P_Load
RE: Utl_file and OPENVMS
Thanks to all of you for the assistance. I have the package working by coding the directory into the utl_file.fopen command and the files are being created okay. I got the batch procedure to work with the SQLLDR command( I did not know you had to set noon and each line starts with a $). It works okay as a database. It takes 5 min 48 sec to load 20 different tables with a total of 178000 rows. When I get the database up to date the developers will test their applications and I will start making the production server. Thanks, Ron >>> [EMAIL PROTECTED] 10/01/02 02:25PM >>> i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir I n Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_P
RE: Utl_file and OPENVMS
i believe its utl_file_dir=dir1,dir2,dir3,... in your example, dir 3 would be the only valid dir. hth, gene >>> [EMAIL PROTECTED] 10/01/02 12:53PM >>> Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par
RE: Utl_file and OPENVMS
Make sure you have the directory name entry covered in INIT.ORA e.g. utl_file_dir = D:\directory name1 utl_file_dir = D:\directory name2 utl_file_dir = D:\directory name3 You need one entry per directory that you want to write to using UTL_FILE Package Hope this helps Regards Shiva -Original Message- Sent: Tuesday, October 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file.
Re: Utl_file and OPENVMS
the vms user oracle needs rights to the directory. >>> [EMAIL PROTECTED] 09/30/02 10:53AM >>> List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file. Utl_File.Put (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load_Service_Name); Utl_File.New_Line(L_Par_File_Hand);
RE: Utl_file and OPENVMS
Hey Ron, If I'm not mistaken, access to the UTL_FILE directories needs to be given to the account that started the Oracle instance and not an Oracle schema name. Also, make sure that your entries for the filenames and directories in the UTL_FILE packages are UPPER CASE, or at least match the init.ora parameter UTL_FILE_DIR in case. ODS-2 volumes in VMS (the only one supported by Oracle for 8i) only allow UPPER CASE file names. It's probably a good idea to uppercase the RMS filenames and directories in your procedure and in the init.ora Make sure the file you are creating is a valid VMS name. From DCL in the Oracle instance's account (default is "ORACLE"), try "CREATE myfilename", where "myfilename" is the exact name that would be passed to UTL_FILE, with the directory. If this succeeds, you can terminate the CREATE statement with a or in most cases. Also, beware that because DCL automagically uppercases everything in the command line that isn't in quotes, the CREATE is not a good test for case-sensitivity. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Ron Rogers [mailto:[EMAIL PROTECTED]] > Sent: Monday, September 30, 2002 9:53 AM > To: Multiple recipients of list ORACLE-L > Subject: Utl_file and OPENVMS > > > List, > I have a package that creates files on the server. The directory > location and file name are obtained from tables in oracle. > The procedure > works as designed on Novell 7.3.4 and no changes were needed when the > database way loaded on Linux Oracle 8.1.7. I am trying to move the > database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't > get the package to write the files to the OS directory. > The package is created by the Oracle user DTSUSER and executed by > DTSUSER. There is no OPENVMS user DTSUSER. > The sysadmin assures me that the permissions are correct to write to > the directory. > I have place a Dbms_output in the package to display the directory > information and it looks correct. > Is there anything different that has to be done to an OPENVMS server > that will allow a package to write to a directory using the Utl_File > package? [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Utl_file and OPENVMS
List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file. Utl_File.Put (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load_Service_Name); Utl_File.New_Line(L_Par_File_Hand); If Not P_Current_Table_Name = 'GLCRET'
Re: SQL*Plus/UTL_FILE vs. 3'rd Party Data Extraction
Perl is very fast, very flexible, and quite inexpensive. :) See my post earlier today that includes the TOC from "Perl for Oracle DBA's" Jared "Post, Ethan" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/02/2002 09:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:SQL*Plus/UTL_FILE vs. 3'rd Party Data Extraction I have some people who are asking about 3'rd party data extraction tools to pull data from Oracle into text files for loading a data warehouse. I am not sure what the size of the project is but I think it is pretty big. The DW is Terradata. Anyway, they are stating Oracle tools are too slow. They do not define slow so it is hard to argue with them. My opinion is that it is very dependent on hardware (CPU/Disk) in most cases. What are your opinions on the matter, do you need 3'rd party? What tools are you using (I am aware of Informatica and such but I think they are looking for something a little cheaper)? Thanks, Ethan Post -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL*Plus/UTL_FILE vs. 3'rd Party Data Extraction
I have some people who are asking about 3'rd party data extraction tools to pull data from Oracle into text files for loading a data warehouse. I am not sure what the size of the project is but I think it is pretty big. The DW is Terradata. Anyway, they are stating Oracle tools are too slow. They do not define slow so it is hard to argue with them. My opinion is that it is very dependent on hardware (CPU/Disk) in most cases. What are your opinions on the matter, do you need 3'rd party? What tools are you using (I am aware of Informatica and such but I think they are looking for something a little cheaper)? Thanks, Ethan Post -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to pick the lastes file using UTL_FILE package ?
Title: RE: how to pick the lastes file using UTL_FILE package ? Rahul, here is how you'd do it on a Unix box: ls -t|line ls -t would give you a list of files most recent first, then the line command would give you back the first filename only. In 2K the first part of that is DIR /O:-D, not sure how you get just the first filename from the list (the latest file will be first), but you could redirect that command to a file then read the first filename from the file! (DIR /O:-D > files) HTH, Rich -Original Message- From: Rahul [mailto:[EMAIL PROTECTED]] Sent: Monday, May 27, 2002 2:18 AM To: Multiple recipients of list ORACLE-L Subject: how to pick the lastes file using UTL_FILE package ? list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to pick the lastes file using UTL_FILE package ?
Well, in that case use loader to process the file into a tmptable, and forget UTL_FILE. aRNO -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, May 27, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Rahul, This isn't exactly what you're asking but, If OEM is set up: You can use the intelligent agent on win2k. Write a script that identifies the file and then calls the PL/SQL procedure. Schedule the job in OEM. Chaim Rahul <[EMAIL PROTECTED]>@fatcity.com on 05/27/2002 02:18:18 AM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Disser, Arno INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: how to pick the lastes file using UTL_FILE package ?
Rahul, This isn't exactly what you're asking but, If OEM is set up: You can use the intelligent agent on win2k. Write a script that identifies the file and then calls the PL/SQL procedure. Schedule the job in OEM. Chaim Rahul <[EMAIL PROTECTED]>@fatcity.com on 05/27/2002 02:18:18 AM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to pick the lastes file using UTL_FILE package ?
Rahul - Just a thought here. This would be a simple task in Unix. One idea would be to take a look at the Unix/Posix type interface packages. Another thought would be to ask this question on a W2K forum. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, May 27, 2002 1:18 AM To: Multiple recipients of list ORACLE-L list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to pick the lastes file using UTL_FILE package ?
1. Run a Cron Job that every Hour Runs the Ls command with the necessary switches to sort the file listing by the date and redirect the filename to a Flat file. 2. Run a Job In Oracle that will open that particular file and read the contents of that file and open the file mentioned therein. There are always other ways to achieve this by means of a Java Stored Proc running inside Oracle without a Cron Job. You can search for Executing external Commands inside a Procedure in http://asktom.oracle.com [Which I blv is down today] HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Monday, May 27, 2002 10:18 AM To: Multiple recipients of list ORACLE-L list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: how to pick the lastes file using UTL_FILE package ?
>- Original Message - >From: Rahul <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Sun, 26 May 2002 22:18:18 > >list, the requirement is to use the UTL_FILE >package on the server side to >open and process the files as they are created >(each hour) in a server's >directory !! >could anyone suggest a logic to pick up the the >lastest file created in that >dir. ? > >TIA > >8.1.6 on win2k > The best solution I can think of is to make the filename predictable - if it contains some time of timestamp (eg YYMMDDHH24) the Oracle side can deduce in advance from SYSDATE which file it must open. Short of that, you can have a 'utility' file in which some procedure keeps track of the latest file - Read it first, then open the actual data file. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
how to pick the lastes file using UTL_FILE package ?
list, the requirement is to use the UTL_FILE package on the server side to open and process the files as they are created (each hour) in a server's directory !! could anyone suggest a logic to pick up the the lastest file created in that dir. ? TIA 8.1.6 on win2k -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Ang: RE: UTL_FILE limitation
When you intend to load a file into a table, the best way to do this is to use sqlloader. Roland "Koivu, Lisa" <[EMAIL PROTECTED]>@fatcity.com den 2002-04-12 11:28 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Kieran, I have to wonder... why are you using utl_file instead of sql*loader? Lisa Koivu Oracle Database Tank Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Kieran Murray [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 2:13 PM > To: Multiple recipients of list ORACLE-L > Subject: UTL_FILE limitation > > Hi, > I'm trying to load a table with 37 columns, from a flat file. I'm getting > ORA-06502 error after about 400 rows have been added to the file. When I > read read from other tables this works fine (although they don't have as > many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure > (setting max_linesize to 32767) but to no avail. The strange thing is, if > I > run a wc -c on any of the lines created they average about 170 bytes, > which > doesn't even come close to the 1023 bytes limit. Would anyone know of a > workaround? > > Cheers, > Kieran Murray > Norkom Technologies, > 43 Upper Mount Street, > Dublin 2, Ireland > > P.S. relevant commands are : > fileid := utl_file.fopen(file_dir,file_name,'w',32767); > v_data := ; > utl_file.put_line(fileid, v_data); > utl_file.fclose(fileid); > > Table description is: > CREATE TABLE CDM_RESULTS_FILE ( > CUS_IDNUMBERNOT NULL, > BAN NUMBERNOT NULL, > CTN VARCHAR2 (12), > CNAME1VARCHAR2 (100), > CNAME2VARCHAR2 (100), > CMTITLE VARCHAR2 (50), > CMADD1VARCHAR2 (50), > CMADD2VARCHAR2 (50), > CMCNTYVARCHAR2 (50), > CMCITYVARCHAR2 (50), > CMZIP VARCHAR2 (50), > CMSTATE VARCHAR2 (50), > CATTN VARCHAR2 (50), > CWPH VARCHAR2 (50), > CWPHEXVARCHAR2 (50), > CHPH VARCHAR2 (50), > COTHPHON VARCHAR2 (50), > SCORE NUMBER, > SCORE10 NUMBER, > SCORE20 NUMBER, > SCORE50 NUMBER, > SCORE100 NUMBER, > REASON1 NUMBER, > REASON2 NUMBER, > REASON3 NUMBER, > REASON4 NUMBER, > REASON5 NUMBER, > REASON6 NUMBER, > REASON7 NUMBER, > REASON8 NUMBER, > REASON9 NUMBER, > REASON10 NUMBER, > MCABA NUMBER, > MSRATANUMBER, > MSRTA NUMBER, > MSRACANUMBER, > MROAM NUMBER) > > > > > The information contained in this e-mail transmission is confidential > and may be privileged. It is intended only for the addressee(s) stated > above. If you are not an addressee, any use, dissemination, distribution, > publication, or copying of the information contained in this e-mail is > strictly prohibited. If you have received this e-mail in error, please > immediately notify our IT Department by telephone at 353-1-6769333 > or e-mail [EMAIL PROTECTED] and delete the e-mail from your > system. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kieran Murray > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists ---
RE: UTL_FILE limitation
Kieran, I have to wonder... why are you using utl_file instead of sql*loader? Lisa Koivu Oracle Database Tank Fairfield Resorts, Inc. 954-935-4117 > -Original Message- > From: Kieran Murray [SMTP:[EMAIL PROTECTED]] > Sent: Friday, April 12, 2002 2:13 PM > To: Multiple recipients of list ORACLE-L > Subject: UTL_FILE limitation > > Hi, > I'm trying to load a table with 37 columns, from a flat file. I'm getting > ORA-06502 error after about 400 rows have been added to the file. When I > read read from other tables this works fine (although they don't have as > many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure > (setting max_linesize to 32767) but to no avail. The strange thing is, if > I > run a wc -c on any of the lines created they average about 170 bytes, > which > doesn't even come close to the 1023 bytes limit. Would anyone know of a > workaround? > > Cheers, > Kieran Murray > Norkom Technologies, > 43 Upper Mount Street, > Dublin 2, Ireland > > P.S. relevant commands are : > fileid := utl_file.fopen(file_dir,file_name,'w',32767); > v_data := ; > utl_file.put_line(fileid, v_data); > utl_file.fclose(fileid); > > Table description is: > CREATE TABLE CDM_RESULTS_FILE ( > CUS_IDNUMBERNOT NULL, > BAN NUMBERNOT NULL, > CTN VARCHAR2 (12), > CNAME1VARCHAR2 (100), > CNAME2VARCHAR2 (100), > CMTITLE VARCHAR2 (50), > CMADD1VARCHAR2 (50), > CMADD2VARCHAR2 (50), > CMCNTYVARCHAR2 (50), > CMCITYVARCHAR2 (50), > CMZIP VARCHAR2 (50), > CMSTATE VARCHAR2 (50), > CATTN VARCHAR2 (50), > CWPH VARCHAR2 (50), > CWPHEXVARCHAR2 (50), > CHPH VARCHAR2 (50), > COTHPHON VARCHAR2 (50), > SCORE NUMBER, > SCORE10 NUMBER, > SCORE20 NUMBER, > SCORE50 NUMBER, > SCORE100 NUMBER, > REASON1 NUMBER, > REASON2 NUMBER, > REASON3 NUMBER, > REASON4 NUMBER, > REASON5 NUMBER, > REASON6 NUMBER, > REASON7 NUMBER, > REASON8 NUMBER, > REASON9 NUMBER, > REASON10 NUMBER, > MCABA NUMBER, > MSRATANUMBER, > MSRTA NUMBER, > MSRACANUMBER, > MROAM NUMBER) > > > > > The information contained in this e-mail transmission is confidential > and may be privileged. It is intended only for the addressee(s) stated > above. If you are not an addressee, any use, dissemination, distribution, > publication, or copying of the information contained in this e-mail is > strictly prohibited. If you have received this e-mail in error, please > immediately notify our IT Department by telephone at 353-1-6769333 > or e-mail [EMAIL PROTECTED] and delete the e-mail from your > system. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kieran Murray > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UTL_FILE limitation
Hi, I'm trying to load a table with 37 columns, from a flat file. I'm getting ORA-06502 error after about 400 rows have been added to the file. When I read read from other tables this works fine (although they don't have as many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure (setting max_linesize to 32767) but to no avail. The strange thing is, if I run a wc -c on any of the lines created they average about 170 bytes, which doesn't even come close to the 1023 bytes limit. Would anyone know of a workaround? Cheers, Kieran Murray Norkom Technologies, 43 Upper Mount Street, Dublin 2, Ireland P.S. relevant commands are : fileid := utl_file.fopen(file_dir,file_name,'w',32767); v_data := ; utl_file.put_line(fileid, v_data); utl_file.fclose(fileid); Table description is: CREATE TABLE CDM_RESULTS_FILE ( CUS_IDNUMBERNOT NULL, BAN NUMBERNOT NULL, CTN VARCHAR2 (12), CNAME1VARCHAR2 (100), CNAME2VARCHAR2 (100), CMTITLE VARCHAR2 (50), CMADD1VARCHAR2 (50), CMADD2VARCHAR2 (50), CMCNTYVARCHAR2 (50), CMCITYVARCHAR2 (50), CMZIP VARCHAR2 (50), CMSTATE VARCHAR2 (50), CATTN VARCHAR2 (50), CWPH VARCHAR2 (50), CWPHEXVARCHAR2 (50), CHPH VARCHAR2 (50), COTHPHON VARCHAR2 (50), SCORE NUMBER, SCORE10 NUMBER, SCORE20 NUMBER, SCORE50 NUMBER, SCORE100 NUMBER, REASON1 NUMBER, REASON2 NUMBER, REASON3 NUMBER, REASON4 NUMBER, REASON5 NUMBER, REASON6 NUMBER, REASON7 NUMBER, REASON8 NUMBER, REASON9 NUMBER, REASON10 NUMBER, MCABA NUMBER, MSRATANUMBER, MSRTA NUMBER, MSRACANUMBER, MROAM NUMBER) The information contained in this e-mail transmission is confidential and may be privileged. It is intended only for the addressee(s) stated above. If you are not an addressee, any use, dissemination, distribution, publication, or copying of the information contained in this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify our IT Department by telephone at 353-1-6769333 or e-mail [EMAIL PROTECTED] and delete the e-mail from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kieran Murray INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Utl_file
search on asktom.oracle.com for owa_sylk package, this is much better to export as excel format than csv format. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Utl_file
I don't think you will be able to do what you want with utl_file unless you
know the entire Excel file formatting details. I don't think BOLDING,
RIGHT JUSTIFY, etc. can be part of a .cvs file.
I know that Oracle has several products or options for $$$ that can do a
lot of the Excel formation things for you. I have never had to deal
directly with them and I am not even sure of all the available solutions.
But in an effort to point you in the correct direction (maybe) look into
"Objects for OLE", "Web DB", and "Oracle Reports". There is no simple
solution and you are going to need to do some research.
Overall, I think is more pain than it's worth for just some fancy
formatting (tell them to highlight and justify it themselves).
Another option might be to put it in html table format, easy with utl_file,
and then open/import that into Excel (just an idea)
I tried to help, if all this is wrong, then just remember that it was for
free;-)
Brian P. MacLean
Oracle DBA, OCP8i
Roland.Skoldbl
[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] Subject: Utl_file
om
04/11/02 05:28
AM
Please respond
to ORACLE-L
Hallo,
Is it possible to do a select from some tables in the database and then use
utl_file package to create an excel file. And also to make the headers in
the columns bold and right positioned. Please give me an easy example on
how to write the code.
Thanks in advance
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Utl_file
Connor, Do you really think, that Roland will be satisfied with your reply ?:-) Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 11, 2002 9:23 AM > utl_file.put_line(file_id, >col1||','|| >col2||','|| >col3); > etc > > for a CSV file > > hth > connor > > > --- [EMAIL PROTECTED] wrote: > Hallo, > > > > Is it possible to do a select from some tables in > > the database and then use utl_file package to create > > an excel file. And also to make the headers in the > > columns bold and right positioned. Please give me an > > easy example on how to write the code. > > > > Thanks in advance > > > > > > Roland > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California-- Public Internet > > access / Mailing Lists > > > > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > = > Connor McDonald > http://www.oracledba.co.uk (mirrored at > http://www.oradba.freeserve.co.uk) > > "Some days you're the pigeon, some days you're the statue" > > __ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Connor=20McDonald?= > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Utl_file
utl_file.put_line(file_id, col1||','|| col2||','|| col3); etc for a CSV file hth connor --- [EMAIL PROTECTED] wrote: > Hallo, > > Is it possible to do a select from some tables in > the database and then use utl_file package to create > an excel file. And also to make the headers in the > columns bold and right positioned. Please give me an > easy example on how to write the code. > > Thanks in advance > > > Roland > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Utl_file
Hallo, Is it possible to do a select from some tables in the database and then use utl_file package to create an excel file. And also to make the headers in the columns bold and right positioned. Please give me an easy example on how to write the code. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL using UTL_FILE
a) take a look at v$session_event to see where you are losing the time b) throw a trace on it and look for long running sql's Is it possible an analyze or equivalent has changed your access paths hth connor --- "Rusnak, George A." <[EMAIL PROTECTED]> wrote: > Group, > I have a stored procedure that runs from schema A on > machine X, joins a > table from another schema on machine X and a table > from machine Y via a > dblink and writes a file via UTL_FILE on machine X > that is used in SQL > Loader to insert data into a table for research. > Pulls about 90 meg. For the > past several months the procedure had been running > in about 20 mins., in the > last 2 weeks the time has jumped to 7 hours. > As far as I can determine there have been no changes > (I know, yeah, right > !!) . > My hardware is a SUN Solaris 5.7. > > ANY (sane) suggestions ? > > TIA > > Al Rusnak > 804-734-8453 > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Rusnak, George A. > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL using UTL_FILE
Try and determine what it is doing/waiting for. Set up a 10046 trace at level 8. You may get lucky and see the problem within the first few minutes rather than wait the full 7 hours. Paul -Original Message- Sent: Wednesday, March 27, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Group, I have a stored procedure that runs from schema A on machine X, joins a table from another schema on machine X and a table from machine Y via a dblink and writes a file via UTL_FILE on machine X that is used in SQL Loader to insert data into a table for research. Pulls about 90 meg. For the past several months the procedure had been running in about 20 mins., in the last 2 weeks the time has jumped to 7 hours. As far as I can determine there have been no changes (I know, yeah, right !!) . My hardware is a SUN Solaris 5.7. ANY (sane) suggestions ? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL using UTL_FILE
Group, I have a stored procedure that runs from schema A on machine X, joins a table from another schema on machine X and a table from machine Y via a dblink and writes a file via UTL_FILE on machine X that is used in SQL Loader to insert data into a table for research. Pulls about 90 meg. For the past several months the procedure had been running in about 20 mins., in the last 2 weeks the time has jumped to 7 hours. As far as I can determine there have been no changes (I know, yeah, right !!) . My hardware is a SUN Solaris 5.7. ANY (sane) suggestions ? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UTL_FILE is slow
Title: UTL_FILE is slow Hi everybody, We have a few applications that use utl_fiile to create rather large output files on a 8.1.6.0.0 database. The output via utl_file seems significantly slower than using spool. I see alot of other complaints on Metalink about utl_file in 8.1.6 being slow, but I don't see that it was ever decided that it really is a bug, or if the speed is improved in later versions. Does anyone know if utl_file is any faster in versions beyond 8.1.6.0.0? By slow, I mean its writing about 2MB/min. thanks, Beth
FW: utl_file example
Thanks Gints,
this was the problem!
Tamas
Just in case
Do You have added correctly entries in utl_file_dir?
If I remeber correctly there was a difference between Unix and NT. On one
You need to put slash on the end, on another not.
Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
Szecsy Tamas
TRIA.hu> cc:
Sent by: Subject: RE: utl_file example
[EMAIL PROTECTED]
om
2001.11.12
16:15
Please respond
to ORACLE-L
Nirmal,
do I have to set some OS acces rights? I get the following exception:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 4
If I handle the exception it turns out that it is invalide path. The only
thing I can think about is, that the sys user does not have access right to
the c:\ drive. Do you know, under what account is the utl_file accessing
the OS filesystem?
Tamas
-Original Message-
From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 1:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: utl_file example
Hi,
Verify "UTL_FILE_DIR" init.ora parameter is defined properly.
In FOPEN function specify, filepath and filename as separate.
[EMAIL PROTECTED]> ;
1 DECLARE
2 fid UTL_FILE.FILE_TYPE;
3 BEGIN
4 fid := UTL_FILE.FOPEN('c:\','test.log','w');
5 UTL_FILE.PUT_LINE(fid, 'Test file for usage of UTL_FILE
package');
6 UTL_FILE.FCLOSE(fid);
7* END;
[EMAIL PROTECTED]> /
PL/SQL procedure successfully completed.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
HTH.
Rgds,
Nirmal,
-Original Message-
From: Szecsy Tamas [SMTP:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject:utl_file example
Hi,
Sorry for the previous post with a wrong subject in it.
Could somebody help me out with a very short utl_file example for
Windows
NT? I would like write error data into a simple text file, but I
get
exceptions.
TIA,
Tamas Szecsy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Szecsy Tamas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858)
538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Szecsy Tamas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: utl_file example
Just in case
Do You have added correctly entries in utl_file_dir?
If I remeber correctly there was a difference between Unix and NT. On one
You need to put slash on the end, on another not.
Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/
Szecsy Tamas
TRIA.hu> cc:
Sent by: Subject: RE: utl_file example
[EMAIL PROTECTED]
om
2001.11.12
16:15
Please respond
to ORACLE-L
Nirmal,
do I have to set some OS acces rights? I get the following exception:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 4
If I handle the exception it turns out that it is invalide path. The only
thing I can think about is, that the sys user does not have access right to
the c:\ drive. Do you know, under what account is the utl_file accessing
the OS filesystem?
Tamas
-Original Message-
From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 1:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: utl_file example
Hi,
Verify "UTL_FILE_DIR" init.ora parameter is defined properly.
In FOPEN function specify, filepath and filename as separate.
[EMAIL PROTECTED]> ;
1 DECLARE
2 fid UTL_FILE.FILE_TYPE;
3 BEGIN
4 fid := UTL_FILE.FOPEN('c:\','test.log','w');
5 UTL_FILE.PUT_LINE(fid, 'Test file for usage of UTL_FILE
package');
6 UTL_FILE.FCLOSE(fid);
7* END;
[EMAIL PROTECTED]> /
PL/SQL procedure successfully completed.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
HTH.
Rgds,
Nirmal,
-Original Message-
From: Szecsy Tamas [SMTP:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject:utl_file example
Hi,
Sorry for the previous post with a wrong subject in it.
Could somebody help me out with a very short utl_file example for
Windows
NT? I would like write error data into a simple text file, but I
get
exceptions.
TIA,
Tamas Szecsy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Szecsy Tamas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858)
538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5
RE: utl_file example
Title: RE: utl_file example
Nirmal,
do I
have to set some OS acces rights? I get the following
exception:
ORA-06510: PL/SQL: unhandled user-defined exceptionORA-06512: at
"SYS.UTL_FILE", line 98ORA-06512: at "SYS.UTL_FILE", line 157ORA-06512:
at line 4
If I
handle the exception it turns out that it is invalide path. The only thing I can
think about is, that the sys user does not have access right to the c:\ drive.
Do you know, under what account is the utl_file accessing the OS
filesystem?
Tamas
-Original Message-From: Nirmal Kumar Muthu Kumaran
[mailto:[EMAIL PROTECTED]]Sent: Monday, November 12, 2001 1:00
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
utl_file example
Hi,
Verify "UTL_FILE_DIR"
init.ora parameter is defined properly.
In FOPEN function specify,
filepath and filename as separate.
[EMAIL PROTECTED]>
; 1
DECLARE
2 fid UTL_FILE.FILE_TYPE; 3 BEGIN 4
fid := UTL_FILE.FOPEN('c:\','test.log','w'); 5
UTL_FILE.PUT_LINE(fid, 'Test file for usage of UTL_FILE package');
6 UTL_FILE.FCLOSE(fid); 7* END; [EMAIL PROTECTED]> /
PL/SQL procedure successfully
completed.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
HTH.
Rgds, Nirmal,
-Original Message- From: Szecsy Tamas [SMTP:[EMAIL PROTECTED]] Sent: Monday, November 12, 2001 1:55 PM To: Multiple recipients of list ORACLE-L Subject: utl_file example
Hi,
Sorry for the previous post with a wrong subject
in it.
Could somebody help me out with a very short
utl_file example for Windows NT? I would
like write error data into a simple text file, but I get exceptions.
TIA,
Tamas Szecsy -- Please see the official
ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy
Tamas INET:
[EMAIL PROTECTED]
Fat City Network Services --
(858) 538-5051 FAX: (858) 538-5051 San Diego, California --
Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send
an E-Mail message to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing:
UNSUB ORACLE-L (or the name of mailing
list you want to be removed from). You may also send the HELP command for other information (like
subscribing).
RE: utl_file example
Title: RE: utl_file example
Hi,
Verify "UTL_FILE_DIR" init.ora parameter is defined properly.
In FOPEN function specify, filepath and filename as separate.
[EMAIL PROTECTED]> ;
1 DECLARE
2 fid UTL_FILE.FILE_TYPE;
3 BEGIN
4 fid := UTL_FILE.FOPEN('c:\','test.log','w');
5 UTL_FILE.PUT_LINE(fid, 'Test file for usage of UTL_FILE package');
6 UTL_FILE.FCLOSE(fid);
7* END;
[EMAIL PROTECTED]> /
PL/SQL procedure successfully completed.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
HTH.
Rgds,
Nirmal,
-Original Message-
From: Szecsy Tamas [SMTP:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject: utl_file example
Hi,
Sorry for the previous post with a wrong subject in it.
Could somebody help me out with a very short utl_file example for Windows
NT? I would like write error data into a simple text file, but I get
exceptions.
TIA,
Tamas Szecsy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Szecsy Tamas
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
utl_file example
Hi, Sorry for the previous post with a wrong subject in it. Could somebody help me out with a very short utl_file example for Windows NT? I would like write error data into a simple text file, but I get exceptions. TIA, Tamas Szecsy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy Tamas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file and record delimters
Does utl_file not just read the file a line at a time and then it is upto you to programmatically divide the data up into it's chunks using substr etc. Kev. "hit any user to continue" __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 24 October 2001 15:30 To: Multiple recipients of list ORACLE-L I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: utl_file and record delimters
You can not specify the record delimiter with UTL_FILE. You can use UTL_FILE.GET_LINE to read records terminated with LF. LF will not be included in the return string. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 24, 2001 10:30 AM > I need to use utl_file on a Unix server to process DOS format(CRLF) files. > can I sepcify to utl_file waht the record delimiter is...or do I need to > convert the files to Unix format before utl_file will read them correctly? > > John > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Dunn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: utl_file and record delimters
Include it n your select statement select emp||','||name||','||sal from emp; This would return 10,MILLS,9 Here using comma as a delimiter Ramon E. Estevez [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 809-565-3121 -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de John Dunn Enviado el: Wednesday, 24 October, 2001 9:30 AM Para: Multiple recipients of list ORACLE-L Asunto: utl_file and record delimters I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
utl_file and record delimters
I need to use utl_file on a Unix server to process DOS format(CRLF) files. can I sepcify to utl_file waht the record delimiter is...or do I need to convert the files to Unix format before utl_file will read them correctly? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: init.ora file and UTL_FILE package
cuell!!! ;o) -Original Message- Sent: 03 October 2001 18:06 To: Multiple recipients of list ORACLE-L Yep, as long as they have OS level authority. That's why I said 'development server'.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 03, 2001 2:30 AM > Remember this has security implications however as anyone can read/write > files from otherwise "private" directories. > > Kev. > > "hit any user to continue" > __ > > Kevin Thomas > Technical Analyst > Deregulation Services > Calanais Ltd. > (2nd Floor East - Weirs Building) > Tel: 0141 568 2377 > Fax: 0141 568 2366 > http://www.calanais.com > > > -Original Message- > Sent: 03 October 2001 05:50 > To: Multiple recipients of list ORACLE-L > > > utl_file=* > > On the development server.. > > Steve > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 5:55 PM > > > > I have a question concerning the init.ora file and using the supplied > > package UTL_FILE > > > > I am finding it a real inconvenience to bounce the database everytime I > need > > to add a UTL_FILE directory, especially on the development machine. Since > > the coding is very dynamic and many people are developing on the same > > database, when I add a UTL_FILE directory, I need to notify everyone > > involved, have them stop what they are doing and bounce the database... > yuk! > > > > Is there any possible way to add UTL_FILE directories on the fly without > > bouncing the database (alter session, alter system, etc) and then at a > > more convenient time add all of the changes at once to the init.ora file > and > > bounce the database once. > > > > Any suggestions would be useful. > > > > Thanks in advance > > > > ReedK > > "just another Montana DBA" > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Kempf, Reed > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Smith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thomas, Kevin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
RE: init.ora file and UTL_FILE package
Correct. Its not adjustable on the fly. (at least on 8.0.5) -Original Message- Sent: Wednesday, October 03, 2001 1:06 PM To: Multiple recipients of list ORACLE-L I knew about the security hole utl_file_dir=* but wanted to keep the production and development enviroments the same so I decided that wasn't a great option. I take it the utl_file parameter cannot be changed on the fly, huh? thanks for everybody's reponses ReedK -Original Message- Sent: Wednesday, October 03, 2001 11:05 AM To: Multiple recipients of list ORACLE-L Yep, as long as they have OS level authority. That's why I said 'development server'.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 03, 2001 2:30 AM > Remember this has security implications however as anyone can read/write > files from otherwise "private" directories. > > Kev. > > "hit any user to continue" > __ > > Kevin Thomas > Technical Analyst > Deregulation Services > Calanais Ltd. > (2nd Floor East - Weirs Building) > Tel: 0141 568 2377 > Fax: 0141 568 2366 > http://www.calanais.com > > > -Original Message- > Sent: 03 October 2001 05:50 > To: Multiple recipients of list ORACLE-L > > > utl_file=* > > On the development server.. > > Steve > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 5:55 PM > > > > I have a question concerning the init.ora file and using the supplied > > package UTL_FILE > > > > I am finding it a real inconvenience to bounce the database everytime I > need > > to add a UTL_FILE directory, especially on the development machine. Since > > the coding is very dynamic and many people are developing on the same > > database, when I add a UTL_FILE directory, I need to notify everyone > > involved, have them stop what they are doing and bounce the database... > yuk! > > > > Is there any possible way to add UTL_FILE directories on the fly without > > bouncing the database (alter session, alter system, etc) and then at a > > more convenient time add all of the changes at once to the init.ora file > and > > bounce the database once. > > > > Any suggestions would be useful. > > > > Thanks in advance > > > > ReedK > > "just another Montana DBA" > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Kempf, Reed > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Smith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thomas, Kevin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the
RE: init.ora file and UTL_FILE package
I knew about the security hole utl_file_dir=* but wanted to keep the production and development enviroments the same so I decided that wasn't a great option. I take it the utl_file parameter cannot be changed on the fly, huh? thanks for everybody's reponses ReedK -Original Message- Sent: Wednesday, October 03, 2001 11:05 AM To: Multiple recipients of list ORACLE-L Yep, as long as they have OS level authority. That's why I said 'development server'.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 03, 2001 2:30 AM > Remember this has security implications however as anyone can read/write > files from otherwise "private" directories. > > Kev. > > "hit any user to continue" > __ > > Kevin Thomas > Technical Analyst > Deregulation Services > Calanais Ltd. > (2nd Floor East - Weirs Building) > Tel: 0141 568 2377 > Fax: 0141 568 2366 > http://www.calanais.com > > > -Original Message- > Sent: 03 October 2001 05:50 > To: Multiple recipients of list ORACLE-L > > > utl_file=* > > On the development server.. > > Steve > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 5:55 PM > > > > I have a question concerning the init.ora file and using the supplied > > package UTL_FILE > > > > I am finding it a real inconvenience to bounce the database everytime I > need > > to add a UTL_FILE directory, especially on the development machine. Since > > the coding is very dynamic and many people are developing on the same > > database, when I add a UTL_FILE directory, I need to notify everyone > > involved, have them stop what they are doing and bounce the database... > yuk! > > > > Is there any possible way to add UTL_FILE directories on the fly without > > bouncing the database (alter session, alter system, etc) and then at a > > more convenient time add all of the changes at once to the init.ora file > and > > bounce the database once. > > > > Any suggestions would be useful. > > > > Thanks in advance > > > > ReedK > > "just another Montana DBA" > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Kempf, Reed > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Smith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thomas, Kevin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
Re: init.ora file and UTL_FILE package
Yep, as long as they have OS level authority. That's why I said 'development server'.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 03, 2001 2:30 AM > Remember this has security implications however as anyone can read/write > files from otherwise "private" directories. > > Kev. > > "hit any user to continue" > __ > > Kevin Thomas > Technical Analyst > Deregulation Services > Calanais Ltd. > (2nd Floor East - Weirs Building) > Tel: 0141 568 2377 > Fax: 0141 568 2366 > http://www.calanais.com > > > -Original Message- > Sent: 03 October 2001 05:50 > To: Multiple recipients of list ORACLE-L > > > utl_file=* > > On the development server.. > > Steve > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 5:55 PM > > > > I have a question concerning the init.ora file and using the supplied > > package UTL_FILE > > > > I am finding it a real inconvenience to bounce the database everytime I > need > > to add a UTL_FILE directory, especially on the development machine. Since > > the coding is very dynamic and many people are developing on the same > > database, when I add a UTL_FILE directory, I need to notify everyone > > involved, have them stop what they are doing and bounce the database... > yuk! > > > > Is there any possible way to add UTL_FILE directories on the fly without > > bouncing the database (alter session, alter system, etc) and then at a > > more convenient time add all of the changes at once to the init.ora file > and > > bounce the database once. > > > > Any suggestions would be useful. > > > > Thanks in advance > > > > ReedK > > "just another Montana DBA" > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Kempf, Reed > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Steve Smith > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Thomas, Kevin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: init.ora file and UTL_FILE package
i prefer utl_file=/tmp/oracle/$sid. you don't want to give rights everywhere. also, developers can access their stuff in /tmp/... dir. >>> [EMAIL PROTECTED] 10/03/01 12:50AM >>> utl_file=* On the development server.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 02, 2001 5:55 PM > I have a question concerning the init.ora file and using the supplied > package UTL_FILE > > I am finding it a real inconvenience to bounce the database everytime I need > to add a UTL_FILE directory, especially on the development machine. Since > the coding is very dynamic and many people are developing on the same > database, when I add a UTL_FILE directory, I need to notify everyone > involved, have them stop what they are doing and bounce the database... yuk! > > Is there any possible way to add UTL_FILE directories on the fly without > bouncing the database (alter session, alter system, etc) and then at a > more convenient time add all of the changes at once to the init.ora file and > bounce the database once. > > Any suggestions would be useful. > > Thanks in advance > > ReedK > "just another Montana DBA" > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kempf, Reed > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: init.ora file and UTL_FILE package
Remember this has security implications however as anyone can read/write files from otherwise "private" directories. Kev. "hit any user to continue" __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 03 October 2001 05:50 To: Multiple recipients of list ORACLE-L utl_file=* On the development server.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 02, 2001 5:55 PM > I have a question concerning the init.ora file and using the supplied > package UTL_FILE > > I am finding it a real inconvenience to bounce the database everytime I need > to add a UTL_FILE directory, especially on the development machine. Since > the coding is very dynamic and many people are developing on the same > database, when I add a UTL_FILE directory, I need to notify everyone > involved, have them stop what they are doing and bounce the database... yuk! > > Is there any possible way to add UTL_FILE directories on the fly without > bouncing the database (alter session, alter system, etc) and then at a > more convenient time add all of the changes at once to the init.ora file and > bounce the database once. > > Any suggestions would be useful. > > Thanks in advance > > ReedK > "just another Montana DBA" > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kempf, Reed > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: init.ora file and UTL_FILE package
utl_file=* On the development server.. Steve - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 02, 2001 5:55 PM > I have a question concerning the init.ora file and using the supplied > package UTL_FILE > > I am finding it a real inconvenience to bounce the database everytime I need > to add a UTL_FILE directory, especially on the development machine. Since > the coding is very dynamic and many people are developing on the same > database, when I add a UTL_FILE directory, I need to notify everyone > involved, have them stop what they are doing and bounce the database... yuk! > > Is there any possible way to add UTL_FILE directories on the fly without > bouncing the database (alter session, alter system, etc) and then at a > more convenient time add all of the changes at once to the init.ora file and > bounce the database once. > > Any suggestions would be useful. > > Thanks in advance > > ReedK > "just another Montana DBA" > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kempf, Reed > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: init.ora file and UTL_FILE package
Title: RE: init.ora file and UTL_FILE package (see answer below) > -Original Message- > From: Kempf, Reed [mailto:[EMAIL PROTECTED]] > > I have a question concerning the init.ora file and using the supplied > package UTL_FILE > > I am finding it a real inconvenience to bounce the database > everytime I need > to add a UTL_FILE directory, especially on the development > machine. Since > the coding is very dynamic and many people are developing on the same > database, when I add a UTL_FILE directory, I need to notify everyone > involved, have them stop what they are doing and bounce the > database... yuk! > > Is there any possible way to add UTL_FILE directories on the > fly without > bouncing the database (alter session, alter system, etc) > and then at a > more convenient time add all of the changes at once to the > init.ora file and > bounce the database once. > > Any suggestions would be useful. This isn't exactly what you're looking for, but did you know about this feature? Metalink Doc ID: Note:1016653.4 Bulletin: Steps to verify setup for UTIL_FILE Package on Windows NT ... The security and directory accessibility are controlled by the UTL_FILE_DIR parameter. If the INIT.ORA file contains UTL_FILE_DIR = * then database permissions are disable and all files and directories are accessible to all users. ... Of course, when saying UTL_FILE_DIR = *, developpers can overwrite any file on which the oracle executable has write privileges.
init.ora file and UTL_FILE package
I have a question concerning the init.ora file and using the supplied package UTL_FILE I am finding it a real inconvenience to bounce the database everytime I need to add a UTL_FILE directory, especially on the development machine. Since the coding is very dynamic and many people are developing on the same database, when I add a UTL_FILE directory, I need to notify everyone involved, have them stop what they are doing and bounce the database... yuk! Is there any possible way to add UTL_FILE directories on the fly without bouncing the database (alter session, alter system, etc) and then at a more convenient time add all of the changes at once to the init.ora file and bounce the database once. Any suggestions would be useful. Thanks in advance ReedK "just another Montana DBA" -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kempf, Reed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UTL_FILE and ksh
Hi Thomas ! I'm very interested in knowing the way of executing an OS command from with a PL/SQL block too. Would U please post it or mail it to me . Thanks a lot . Andrey. DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Friday, August 31, 2001 11:06 AM To: Multiple recipients of list ORACLE-L I got hold of some code that allows you to execute and Unix OS command from within Oracle...sorry I haven't fully read this email but is this something you are looking for ? "hit any user to continue" __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 30 August 2001 22:47 To: Multiple recipients of list ORACLE-L I'd look at java or equivalent to actually run a OS command to do 'ls -l' and get this result back. hth connor --- "Post, Ethan" <[EMAIL PROTECTED]> wrote: > I have a directory that another process will be > placing files in and I will > be processing them using the UTL_FILE package. I > wish there was a way to > list the contents of a directory from PLSQL but I > don't think that there is. > My solution at this point is cron out a ksh script > that will check the > directory and call the procedure to handle the file. > I need to check if the > file is still being written to. I am checking to > see if the file is > writable but even when the file was opened by > another session using VI it > came back as writable so that does not appear to > work. What are your > solutions to this problem. Some of my other > thoughts are to have the > processes writing the file to only make the file > readable to other processes > once it is complete? > > Thanks, > Ethan > http://www.geocities.com/epost1 > > -- > This e-mail is intended for the use of the > addressee(s) only and may contain privileged, > confidential, or proprietary information that is > exempt from disclosure under law. If you have > received this message in error, please inform us > promptly by reply e-mail, then delete the e-mail and > destroy any printed copy. Thank you. > > == > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other informat
RE: UTL_FILE and ksh
I got hold of some code that allows you to execute and Unix OS command from within Oracle...sorry I haven't fully read this email but is this something you are looking for ? "hit any user to continue" __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 30 August 2001 22:47 To: Multiple recipients of list ORACLE-L I'd look at java or equivalent to actually run a OS command to do 'ls -l' and get this result back. hth connor --- "Post, Ethan" <[EMAIL PROTECTED]> wrote: > I have a directory that another process will be > placing files in and I will > be processing them using the UTL_FILE package. I > wish there was a way to > list the contents of a directory from PLSQL but I > don't think that there is. > My solution at this point is cron out a ksh script > that will check the > directory and call the procedure to handle the file. > I need to check if the > file is still being written to. I am checking to > see if the file is > writable but even when the file was opened by > another session using VI it > came back as writable so that does not appear to > work. What are your > solutions to this problem. Some of my other > thoughts are to have the > processes writing the file to only make the file > readable to other processes > once it is complete? > > Thanks, > Ethan > http://www.geocities.com/epost1 > > -- > This e-mail is intended for the use of the > addressee(s) only and may contain privileged, > confidential, or proprietary information that is > exempt from disclosure under law. If you have > received this message in error, please inform us > promptly by reply e-mail, then delete the e-mail and > destroy any printed copy. Thank you. > > == > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: UTL_FILE and ksh
I'd look at java or equivalent to actually run a OS command to do 'ls -l' and get this result back. hth connor --- "Post, Ethan" <[EMAIL PROTECTED]> wrote: > I have a directory that another process will be > placing files in and I will > be processing them using the UTL_FILE package. I > wish there was a way to > list the contents of a directory from PLSQL but I > don't think that there is. > My solution at this point is cron out a ksh script > that will check the > directory and call the procedure to handle the file. > I need to check if the > file is still being written to. I am checking to > see if the file is > writable but even when the file was opened by > another session using VI it > came back as writable so that does not appear to > work. What are your > solutions to this problem. Some of my other > thoughts are to have the > processes writing the file to only make the file > readable to other processes > once it is complete? > > Thanks, > Ethan > http://www.geocities.com/epost1 > > -- > This e-mail is intended for the use of the > addressee(s) only and may contain privileged, > confidential, or proprietary information that is > exempt from disclosure under law. If you have > received this message in error, please inform us > promptly by reply e-mail, then delete the e-mail and > destroy any printed copy. Thank you. > > == > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: UTL_FILE and ksh
Or you can have the script that's creating the file generate an indicator file after finishing writing the file. So if the indicator file doesn't exist then the file is still being written to. >>> [EMAIL PROTECTED] 08/30/01 04:16PM >>> One solution for the "make sure the file is complete problem" is for the script that is actually doing the file creatation is to write/create it into a subdirectory of the target folder. Once the file is closed & complete, the script simply moves or renames the file into the target directory. This method "guarentees" that if/when it exists that the file is closed. Another solution if for an external process to maintain file which contains the names of files are are complete and ready for PL/SQL processing. HTH & YMMV! "Post, Ethan" wrote: > > I have a directory that another process will be placing files in and I will > be processing them using the UTL_FILE package. I wish there was a way to > list the contents of a directory from PLSQL but I don't think that there is. > My solution at this point is cron out a ksh script that will check the > directory and call the procedure to handle the file. I need to check if the > file is still being written to. I am checking to see if the file is > writable but even when the file was opened by another session using VI it > came back as writable so that does not appear to work. What are your > solutions to this problem. Some of my other thoughts are to have the > processes writing the file to only make the file readable to other processes > once it is complete? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: UTL_FILE and ksh
One solution for the "make sure the file is complete problem" is for the script that is actually doing the file creatation is to write/create it into a subdirectory of the target folder. Once the file is closed & complete, the script simply moves or renames the file into the target directory. This method "guarentees" that if/when it exists that the file is closed. Another solution if for an external process to maintain file which contains the names of files are are complete and ready for PL/SQL processing. HTH & YMMV! "Post, Ethan" wrote: > > I have a directory that another process will be placing files in and I will > be processing them using the UTL_FILE package. I wish there was a way to > list the contents of a directory from PLSQL but I don't think that there is. > My solution at this point is cron out a ksh script that will check the > directory and call the procedure to handle the file. I need to check if the > file is still being written to. I am checking to see if the file is > writable but even when the file was opened by another session using VI it > came back as writable so that does not appear to work. What are your > solutions to this problem. Some of my other thoughts are to have the > processes writing the file to only make the file readable to other processes > once it is complete? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UTL_FILE and ksh
I have a directory that another process will be placing files in and I will be processing them using the UTL_FILE package. I wish there was a way to list the contents of a directory from PLSQL but I don't think that there is. My solution at this point is cron out a ksh script that will check the directory and call the procedure to handle the file. I need to check if the file is still being written to. I am checking to see if the file is writable but even when the file was opened by another session using VI it came back as writable so that does not appear to work. What are your solutions to this problem. Some of my other thoughts are to have the processes writing the file to only make the file readable to other processes once it is complete? Thanks, Ethan http://www.geocities.com/epost1 -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Brio/utl_file/oracle
I'm asking this for someone else, as Brio, that name sounds like a cheese to me :) "using Brio Portal 7 has anyone executed a Pl/SQL program that has built in UTL_FILE output functionality and have BRIO bring back the output. we use utl_file because they dont have to use BRIO .. we have if logic to handle both in side PL/SQL. The utl_file creates a CSV format they can connect to extranet site and use Excel interface to view before goes to client." thanks, joe -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UTL_FILE
Someone was pointing out that UTL_FILE provides sequential access to OS files and wanted to know of any tool that provided random access. Just after I read and deleted that message I thought about a possible solution using Oracle's built-in tools. How about creating a little table with a BFile column. Make the external document a BFile, then you could use all the DBMS_LOB procedures and functions on it. I've not done that, but theoretically it would work, as long as the file is less than 4GB. Anybody used DBMS_LOB procedures on large BFiles? Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
AW: UTL_FILE pkg related issue
The term of NUMBER OF ROWS is not quite appliable in case of binary file.
Anyway wc -l yourfilename (i suggest you are in unix shell , otherwise you
can compile the '\n' counter like this
--
#include
main()
{
int counter=0,c;
while((c=getchar())!=EOF)
if(c=='\n')
counter++;
printf("%d\n",counter);
}
-
)
HTH
Maxim Demenko
Database Specialist
LOYALTY PARTNER GmbH
Landshuter Allee 12-14
80637 München
Tel: +49(0)89-99741-628
Fax: +49(0)89-99741-629
email: [EMAIL PROTECTED]
www.loyaltypartner.com
www.payback.de
-Ursprüngliche Nachricht-
Von: Vikas Kawatra [mailto:[EMAIL PROTECTED]]
Gesendet: Montag, 4. Juni 2001 21:32
An: Multiple recipients of list ORACLE-L
Betreff: UTL_FILE pkg related issue
I use the UTL_FILE pkg to read a bin file and load the data into our
database tables (insert or update) . The program works fine - but since we
do updates too- we would like to protect out table - by checking the no of
rows in the input file - The file has a Header and Tail - CAn someone
suggest - how I can scan the binary file - and count the no of rows - before
- processing the file ?
thanks
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Maxim Demenko
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
UTL_FILE pkg related issue
I use the UTL_FILE pkg to read a bin file and load the data into our database tables (insert or update) . The program works fine - but since we do updates too- we would like to protect out table - by checking the no of rows in the input file - The file has a Header and Tail - CAn someone suggest - how I can scan the binary file - and count the no of rows - before - processing the file ? thanks vikas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Kawatra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
UTL_FILE Problem Solved
Thanks for the input Ian, I figured out the problem which really involved how I was logging into the database. There was a note on Metalink where somebody was having the same problem I was experiencing - the file would be created, but either nothing would be written, or you'd get a write error. The difference was that they were executing their procedure via a dblink and got the problem fixed when they replaced and restarted their listener. Thinking through this, I realized that this particular database had been installed by a contractor who started everything as the SAP admin user, and not as the Oracle user who "owns" the database and the listener. ps -ef showed all the core oracle processes(pmon, smon, dbwr, etc.) were owned by p01adm. So I switched to that user, went to the Oracle bin directory and started sqlplus from there, again not as the UNIX Oracle user, but as the UNIX SAP user. Logged in as normal, and voila - it works. Seems UTL_FILE picks up permissions/groups from the UNIX user that either started the database or started the listener. Hard won knowledge, but hopefully it will save somebody else the pain I've been through the last several hours. Hope everyone has a great weekend. David A. Barbour Oracle DBA, OCP (feeling a little lighter as I trod the path) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David A. Barbour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: UTL_FILE error
Are you out of space or quota? I assume this UFS filesystem. We use AFS here for
quite a bit of stuff. AFS permissions are more granular; a person could have
permissions to create a file but not write to it. The chmod command is still
available, it just doesn't do anything on AFS files.
It is hard to tell from your system system prompt, grendel:orap01>, that the file was
created in the /oracle/P01 directory
Ian
-Original Message-
Sent: Friday, April 27, 2001 2:36 PM
To: Multiple recipients of list ORACLE-L
Thanks IAN,
It confirmed what I already expected - it's a "write error." I can't
figure out why though. This directory is owned by the oracle user. It
creates the file, but the refuses to write to it! I've changed the
specification to 'A" and given /oracle and /P01 full permissions.
Touched the file and chmod 777 the file. Still the error.
Reran catproc.sql (I'm really out there now). If I execute the sql
statement: select osuser from v$session where audsid =
userenv('sessionid'); I get orap01, which should be the right guy.
Searched Metalink (response time isn't bad right now) with no luck.
This is on AIX 4.2.1 with which I used to pride myself on having some
sort of familiarity. Pride goeth before a fall.
Any other ideas?
David A. Barbour
Oracle DBA, OCP(and still searching for enlightenment)
This is
"MacGregor, Ian A." wrote:
>
> You need to handle the exception, for example
>
> Exception
>When no_data_found then
> dbms_output.put_line ('no data found');
>When utl_file.internal_error then
> dbms_output.put_line('internal error');
>When utl_file.invalid_filehandle then
> dbms_output.put_line('invalid filehandle');
>when utl_file.invalid_mode then
> dbms_output.put_line('invalid mode');
>when utl_file.invalid_operation then
> dbms_output.put_line('invalid operation');
>when utl_file.invalid_path then
> dbms_output.put_line('invalid path');
>when utl_file.read_error then
> dbms_output.put_line('read error');
>when utl_file.write_error then
> dbms_output.put_line('write error');
>when value_error then
> dbms_output.put_line('value error');
>when others then
> dbms_output.put_line('unspecified exception raised');
>
> --
>
> Setting the utl_file parameter to * is a dangerous thing to do. It allows writes to
>any file to which Oracle can write.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
> -Original Message-
> Sent: Friday, April 27, 2001 10:56 AM
> To: Multiple recipients of list ORACLE-L
>
> Morning all,
>
> I've got a problem with the UTL_FILE package on a 7.3.4 database. The
> UTL_FILE_DIR initialization parameter is set to *. I define my
> filespecs as follows:
>
> file_one UTL_FILE.FILE_TYPE;
> BEGIN
>
> file_one:= UTL_FILE.FOPEN('/oracle/P01','Jul_Dec96.csv','W');
>
> The file gets created okay:
>
> grendel:orap01> ls -al *.csv
> -rw-r--r-- 1 orap01 dba0 Apr 27 11:18 Jul_Dec96.csv
>
> I perform some selects and various data stuff then I go to add a
> line(output severely reduced for testing purposes):
>
> UTL_FILE.PUT_LINE(file_one,v_zcoanum);
> I get :
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 87
> ORA-06512: at "SYS.UTL_FILE", line 218
> ORA-06512: at "SAPR3.COAPRODX3", line 123
> ORA-06512: at line 1
>
> The variable v_zcoanum is ALWAYS present - DBMS_OUTPUT.PUT_LINE shows it
> just fine. I've bounced the instance, re-run the utlfile.sql, and tried
> changing target directories. I've even tried a PUTF -
> UTL_FILE.PUTF(file_one,'%s', v_zcoanum); but get the same type of
> errors:
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 87
> ORA-06512: at "SYS.UTL_FILE", line 188
> ORA-06512: at "SYS.UTL_FILE", line 273
> ORA-06512: at "SYS.UTL_FILE", line 299
> ORA-06512: at "SAPR3.COAPRODX3", line 123
> ORA-06512: at line 1
>
> I'm wondering if I need to re-run catproc.sql? This same procedure
> works on a test instance on a different box (same O/S, same
> init.ora). Generally I'd see these types of errors when the
> UTL_FILE pac
Re: UTL_FILE error
Thanks IAN,
It confirmed what I already expected - it's a "write error." I can't
figure out why though. This directory is owned by the oracle user. It
creates the file, but the refuses to write to it! I've changed the
specification to 'A" and given /oracle and /P01 full permissions.
Touched the file and chmod 777 the file. Still the error.
Reran catproc.sql (I'm really out there now). If I execute the sql
statement: select osuser from v$session where audsid =
userenv('sessionid'); I get orap01, which should be the right guy.
Searched Metalink (response time isn't bad right now) with no luck.
This is on AIX 4.2.1 with which I used to pride myself on having some
sort of familiarity. Pride goeth before a fall.
Any other ideas?
David A. Barbour
Oracle DBA, OCP(and still searching for enlightenment)
This is
"MacGregor, Ian A." wrote:
>
> You need to handle the exception, for example
>
> Exception
>When no_data_found then
> dbms_output.put_line ('no data found');
>When utl_file.internal_error then
> dbms_output.put_line('internal error');
>When utl_file.invalid_filehandle then
> dbms_output.put_line('invalid filehandle');
>when utl_file.invalid_mode then
> dbms_output.put_line('invalid mode');
>when utl_file.invalid_operation then
> dbms_output.put_line('invalid operation');
>when utl_file.invalid_path then
> dbms_output.put_line('invalid path');
>when utl_file.read_error then
> dbms_output.put_line('read error');
>when utl_file.write_error then
> dbms_output.put_line('write error');
>when value_error then
> dbms_output.put_line('value error');
>when others then
> dbms_output.put_line('unspecified exception raised');
>
> --
>
> Setting the utl_file parameter to * is a dangerous thing to do. It allows writes to
>any file to which Oracle can write.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
> -Original Message-
> Sent: Friday, April 27, 2001 10:56 AM
> To: Multiple recipients of list ORACLE-L
>
> Morning all,
>
> I've got a problem with the UTL_FILE package on a 7.3.4 database. The
> UTL_FILE_DIR initialization parameter is set to *. I define my
> filespecs as follows:
>
> file_one UTL_FILE.FILE_TYPE;
> BEGIN
>
> file_one:= UTL_FILE.FOPEN('/oracle/P01','Jul_Dec96.csv','W');
>
> The file gets created okay:
>
> grendel:orap01> ls -al *.csv
> -rw-r--r-- 1 orap01 dba0 Apr 27 11:18 Jul_Dec96.csv
>
> I perform some selects and various data stuff then I go to add a
> line(output severely reduced for testing purposes):
>
> UTL_FILE.PUT_LINE(file_one,v_zcoanum);
> I get :
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 87
> ORA-06512: at "SYS.UTL_FILE", line 218
> ORA-06512: at "SAPR3.COAPRODX3", line 123
> ORA-06512: at line 1
>
> The variable v_zcoanum is ALWAYS present - DBMS_OUTPUT.PUT_LINE shows it
> just fine. I've bounced the instance, re-run the utlfile.sql, and tried
> changing target directories. I've even tried a PUTF -
> UTL_FILE.PUTF(file_one,'%s', v_zcoanum); but get the same type of
> errors:
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 87
> ORA-06512: at "SYS.UTL_FILE", line 188
> ORA-06512: at "SYS.UTL_FILE", line 273
> ORA-06512: at "SYS.UTL_FILE", line 299
> ORA-06512: at "SAPR3.COAPRODX3", line 123
> ORA-06512: at line 1
>
> I'm wondering if I need to re-run catproc.sql? This same procedure
> works on a test instance on a different box (same O/S, same
> init.ora). Generally I'd see these types of errors when the
> UTL_FILE package isn't installed or there are too many open file handles
> tried to eliminate this by bouncing the instance). Does anybody have a
> clue here?
>
> Thanks,
>
> David A. Barbour
> Oracle DBA, OCP (Obviously not a path to enlightenment)
> Worn to a frazzle staring at my own code and out of ideas
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David A. Barbour
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California--
