Hi,

I am completley new to Unix and shell scripts. I need to write a script that does the following:

selects 2 values from a table on an oracle database (directory structure)
ftps a .csv file from a Win server to a Unix server (directories from above sql)
changes directory
uses a utility called mload to upload data

I know how to write the select statement but do I write it in my script or do I write a separate sql script that I call from my shell script? if so, how do I pass the values I retrieve from the database back to the calling shell script.

I am also unclear on how to use ftp in my script to ftp the csv file.

The rest I know how to do.

If someone could give me some hints, point me in the right direction, or point me to some samples, I would greatly appreciate it.

thanks,
KKC

Hi,

this script (Korn shell) targets these 2 problems:

# [BEGIN script] -----------------------------------------

# 1. Extract the data from the DB

typeset DB_VALUEs=$(sqlplus -s YOUR_USER_NAME/YOUR_USER_PASSWORD@YOUR_DB_NAME <<ENDSQL
set echo off
set lines 120 pages 120 feedback off
SET HEADING OFF

select YOUR_COLUMNs from YOUR_TABLE;
ENDSQL
)

# Remove newline characters
DB_VALUEs=$(echo ${DB_VALUEs})
#echo "DB_VALUEs = '${DB_VALUEs}'"


# 2. FTP a single file

# Create a temporary file for the FTP dialog
typeset TMP_FILE=$(mktemp); RC=$?
if [[ ${RC} -ne 0 ]]; then
echo "ERROR: cannot create a temporary file... (RC=${RC})"
exit ${RC}
fi

# Create a temporary file for the FTP output
typeset OUT_FILE=$(mktemp); RC=$?
if [[ ${RC} -ne 0 ]]; then
echo "ERROR: cannot create a temporary file... (RC=${RC})"
exit ${RC}
fi

# Run the FTP command ()
cat >${TMP_FILE} <<!
open YOUR_FTP_SERVER_NAME
user YOUR_FTP_USER_NAME YOUR_FTP_USER_PASSWORD
binary
lcd YOUR_LOCAL_FILE_DIRECTORY
put YOUR_FILE_NAME
close
!
ftp -n <${TMP_FILE} > ${OUT_FILE}

echo "RESULT code: '$?'"

rm ${TMP_FILE}

echo "RESULTs:"
cat ${OUT_FILE}
rm ${OUT_FILE}

# [END script] -------------------------------------------

where the 'YOUR_xxx' strings should be customized.

Please tell me if it works and if it fits your needs.

C.