RE: UTL_FILE question

2004-01-31 Thread Niall Litchfield 
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!

Re: RE: utl_file on Redhat Linux Oracle 9 standard engine

2003-07-11 Thread Jared Still 
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

2003-07-09 Thread Matthew Zito 

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

2003-07-09 Thread Richard Ji 
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

2003-07-09 Thread Tanel Poder 
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

2003-07-09 Thread rgaffuri 
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

2003-07-09 Thread John Dunn 
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

2003-07-09 Thread Cary Millsap 
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

2003-07-09 Thread Tanel Poder 
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).

RE: utl_file performance

2003-06-13 Thread Jamadagni, Rajendra 
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

2003-06-13 Thread Igor Neyman 
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).

RE: utl_file performance

2003-06-13 Thread Regis Biassala 
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

2003-03-11 Thread Harrington, Eric 
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

2003-03-11 Thread Ryan 
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

2003-03-11 Thread Andreas . Haunschmidt 
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

2003-03-11 Thread Thomas Day 

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

2003-03-11 Thread Darrell Landrum 
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

2003-03-11 Thread Mercadante, Thomas F 
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

RE: Utl_file and OPENVMS

2002-10-03 Thread Mohammad Rafiq 

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

2002-10-03 Thread Jared . Still 

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

2002-10-03 Thread Mohammad Rafiq 

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

2002-10-03 Thread Gene Sais 

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

2002-10-03 Thread Mohammad Rafiq 

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

2002-10-03 Thread Gene Sais 

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

2002-10-03 Thread Mohammad Rafiq 

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

2002-10-03 Thread Gene Sais 

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

2002-10-03 Thread Mohammad Rafiq 

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

2002-10-03 Thread Gene Sais 

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

2002-10-02 Thread Vergara, Michael (TEM) 

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

2002-10-02 Thread Babu Nagarajan 

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

2002-10-02 Thread Jared . Still 

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

2002-10-01 Thread Ron Rogers 

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_Par_File_Hand  Utl_FIle.File_Type; -- Local variable to

RE: Utl_file and OPENVMS

2002-10-01 Thread Gene Sais 

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_File_Dir,P_Current_Table_Name||'.PAR','w');

RE: Utl_file and OPENVMS

2002-10-01 Thread Baswannappa, Shiva 

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.   
  
Utl_File.Put
(L_Par_File_Hand,'UserId='||P_Load_

Re: Utl_file and OPENVMS

2002-10-01 Thread Gene Sais 

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); 
  
If Not P_Current_Table_Name = 'GLCR

RE: Utl_file and OPENVMS

2002-09-30 Thread Jesse, Rich 

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).

Ang: RE: UTL_FILE limitation

2002-04-14 Thread Roland . Skoldblom 


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

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 (l

RE: UTL_FILE limitation

2002-04-12 Thread Koivu, Lisa 

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).

RE: Utl_file

2002-04-11 Thread Jamadagni, Rajendra 

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

2002-04-11 Thread Brian_P_MacLean 


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

2002-04-11 Thread Igor Neyman 

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

2002-04-11 Thread Connor McDonald 

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).

RE: utl_file example

2001-11-12 Thread G . Plivna 


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

2001-11-12 Thread Szecsy Tamas 
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

2001-11-12 Thread Nirmal Kumar Muthu Kumaran 
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).

RE: utl_file and record delimters

2001-10-24 Thread Thomas, Kevin 

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

2001-10-24 Thread Igor Neyman 

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

2001-10-24 Thread Ramon Estevez 

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]  
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).

RE: UTL_FILE and ksh

2001-09-02 Thread Andrey Bronfin 

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 information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  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 and ksh

2001-08-31 Thread Thomas, Kevin 

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

2001-08-30 Thread Connor McDonald 

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

2001-08-30 Thread Richard Ji 

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

2001-08-30 Thread Charlie Mengler 

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).

RE: UTL_FILE

2001-06-13 Thread Jack C. Applewhite 

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).

RE: UTL_FILE error

2001-04-27 Thread MacGregor, Ian A. 

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 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-- Public Internet access / Mailing Lists
> --

Re: UTL_FILE error

2001-04-27 Thread David A. Barbour 

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-- 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: MacGregor, Ian A.
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538

RE: UTL_FILE error

2001-04-27 Thread MacGregor, Ian A. 

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-- 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: MacGregor, Ian 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).

Re: Utl_file question

2001-03-24 Thread Bryan Sirtosky 

Did you have

END;

on line 11?

Bryan

- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, March 15, 2001 9:42 AM




Hallo all experts,

I have written this procedure but get this error message. What is wrong?


  1  DECLARE
  2  emp_data UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  emp_data := UTL_FILE.FOPEN ('c:\', 'pelle.xls', 'W');
  5  FOR BAKERS IN (
  6  select AVDNR, DATO
  7  FROM BAKERS) LOOP
  8  UTL_FILE.PUT_LINE (emp_data, TO_CHAR(BAKERS.DATO ) ||',"'||
BAKERS.AVDNR || '"');
  9  END LOOP;
 10* UTL_FILE.FCLOSE (emp_data)

DECLARE
*
ERROR at line 1:
ORA-06550: line 11, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
:= . ( % ;


Hope anyone can help me.

Roland Sköldblom



--
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: Bryan Sirtosky
  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 question

2001-03-15 Thread William Rogge 

It appears that something is missing (or maybe 2 things).

1. Line terminator (;) on line 10.
2. End of block indicator (end).

-Original Message-
From:   [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, March 15, 2001 9:43 AM
To: Multiple recipients of list ORACLE-L
Subject:Utl_file question



Hallo all experts,

I have written this procedure but get this error message. What is wrong?


  1  DECLARE
  2  emp_data UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  emp_data := UTL_FILE.FOPEN ('c:\', 'pelle.xls', 'W');
  5  FOR BAKERS IN (
  6  select AVDNR, DATO
  7  FROM BAKERS) LOOP
  8  UTL_FILE.PUT_LINE (emp_data, TO_CHAR(BAKERS.DATO ) ||',"'|| BAKERS.AVDNR || '"');
  9  END LOOP;
 10* UTL_FILE.FCLOSE (emp_data)

DECLARE
*
ERROR at line 1:
ORA-06550: line 11, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
:= . ( % ;


Hope anyone can help me.

Roland Skoldblom



--
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: William Rogge
  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 question

2001-03-15 Thread Patrick Housholder 

did you forget the END;

Begin
..

..
end;

===
Patrick Housholder
Sr. Staff Anl Tech Spt Design
United Airlines Flight Training Center
Denver CO


*>-Original Message-
*>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Diana
*>Duncan
*>Sent: Thursday, March 15, 2001 12:36 PM
*>To: Multiple recipients of list ORACLE-L
*>Subject: RE: Utl_file question
*>
*>
*>Well, maybe you just forgot to paste it in, but you need a
*>semicolon at the
*>end of line 10, and a forward slash in the first position of line 11.  ;-)
*>
*>Or is that too simple?  Otherwise, it looks good to me...
*>
*>Diana
*>
*>-Original Message-
*>Sent: Thursday, March 15, 2001 9:43 AM
*>To: Multiple recipients of list ORACLE-L
*>
*>
*>
*>
*>Hallo all experts,
*>
*>I have written this procedure but get this error message. What is wrong?
*>
*>
*>  1  DECLARE
*>  2  emp_data UTL_FILE.FILE_TYPE;
*>  3  BEGIN
*>  4  emp_data := UTL_FILE.FOPEN ('c:\', 'pelle.xls', 'W');
*>  5  FOR BAKERS IN (
*>  6  select AVDNR, DATO
*>  7  FROM BAKERS) LOOP
*>  8  UTL_FILE.PUT_LINE (emp_data, TO_CHAR(BAKERS.DATO ) ||',"'||
*>BAKERS.AVDNR || '"');
*>  9  END LOOP;
*> 10* UTL_FILE.FCLOSE (emp_data)
*>
*>DECLARE
*>*
*>ERROR at line 1:
*>ORA-06550: line 11, column 0:
*>PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
*>following:
*>:= . ( % ;
*>
*>
*>Hope anyone can help me.
*>
*>Roland Sköldblom
*>
*>
*>
*>--
*>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: Diana Duncan
*>  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: Patrick Housholder
  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 question

2001-03-15 Thread Cale, Rick T (Richard) 

Can you show the entire script. There is no semi-colon at end of statement
on line 11.

Rick

> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, March 15, 2001 9:43 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Utl_file question
> 
> 
> 
> Hallo all experts,
> 
> I have written this procedure but get this error message. What is wrong?
> 
> 
>   1  DECLARE
>   2  emp_data UTL_FILE.FILE_TYPE;
>   3  BEGIN
>   4  emp_data := UTL_FILE.FOPEN ('c:\', 'pelle.xls', 'W');
>   5  FOR BAKERS IN (
>   6  select AVDNR, DATO
>   7  FROM BAKERS) LOOP
>   8  UTL_FILE.PUT_LINE (emp_data, TO_CHAR(BAKERS.DATO ) ||',"'||
> BAKERS.AVDNR || '"');
>   9  END LOOP;
>  10* UTL_FILE.FCLOSE (emp_data)
> 
> DECLARE
> *
> ERROR at line 1:
> ORA-06550: line 11, column 0:
> PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
> following:
> := . ( % ;
> 
> 
> Hope anyone can help me.
> 
> Roland Sköldblom
> 
> 
> 
> -- 
> 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: Cale, Rick T (Richard)
  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 question

2001-03-15 Thread Diana Duncan 

Well, maybe you just forgot to paste it in, but you need a semicolon at the
end of line 10, and a forward slash in the first position of line 11.  ;-)

Or is that too simple?  Otherwise, it looks good to me...

Diana

-Original Message-
Sent: Thursday, March 15, 2001 9:43 AM
To: Multiple recipients of list ORACLE-L




Hallo all experts,

I have written this procedure but get this error message. What is wrong?


  1  DECLARE
  2  emp_data UTL_FILE.FILE_TYPE;
  3  BEGIN
  4  emp_data := UTL_FILE.FOPEN ('c:\', 'pelle.xls', 'W');
  5  FOR BAKERS IN (
  6  select AVDNR, DATO
  7  FROM BAKERS) LOOP
  8  UTL_FILE.PUT_LINE (emp_data, TO_CHAR(BAKERS.DATO ) ||',"'||
BAKERS.AVDNR || '"');
  9  END LOOP;
 10* UTL_FILE.FCLOSE (emp_data)

DECLARE
*
ERROR at line 1:
ORA-06550: line 11, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
:= . ( % ;


Hope anyone can help me.

Roland Sköldblom



-- 
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: Diana Duncan
  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).