Re: UTL_FILE Utility

From: Kenneth C Stahl <BlueSax_at_Unforgettable.com>
Date: Tue, 02 Nov 1999 09:47:06 -0500
Message-ID: <381EF96A.CCA6D50@Unforgettable.com>

amerar_at_ci.chi.il.us wrote:
>
> Hello,
>
> Regarding the utility UTL_FILE.......if I open up a file in Append mode
> and the file does not exist, will Oracle create it or will it give me an
> error?
>
> Arthur
> amerar_at_ci.chi.il.us
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

If you use 'a', then the file must exist. If you use 'w' then Oracle will create it for you but it will be created with Oracle permissions. If that causes problems (assuming you are working in Unix), do something like this:

#!/bin/ksh
#set -x

typeset -r Fpath='/home/xyzzy';
typeset -r Fname='mydata.dat';
typeset -r Fullname="${Fpath}/${Fname}";
typeset -r SqlScript='myprog.sql';

set -x
trap "if [[ -f ${Fullname} ]];then \

          chmod 600 ${Fullname};\
          if [ $? -ne 0 ];then \
              print -u2 'Unable to reset permissions on '${Filename};\
          fi \
      fi" EXIT
trap 'if [[ -f ${Fullname} ]];then \
          rm ${Fullname}; \
          exit 1; \
      fi' HUP INT QUIT PIPE TERM ERR;

# Create empty file
>"${Fullname}"
if [ $? -ne 0 ];then

    print -u2 "Unable to create ${Fullname}"     exit 1;
fi

# Set write permissions
chmod 222 "${Fullname}";
if [ $? -ne 0 ];then

    print -u2 "Unable to set write permissions for ${Fullname}";     exit 1;
fi

# Invoke sqlplus to run pl/sql program. Path and file name are passed as # parameters and read as &1 and &2 in script. sqlplus -s scott/tiger @${SqlScript} ${Fpath} ${Fname} if [ $? -ne 0 ];then

    print -u2 "Fatal error in ${SqlScript}";     exit 1;
fi
exit 0; Received on Tue Nov 02 1999 - 08:47:06 CST