Hi Guys

How u all doing?

I am having tough time to achieve this I have a unix .ksh script which calls
sql script

Right now I harcoded column id's in sql script but I want to read them from a txt file
1084,1143,1074,1080,1091,1090,1101,1069,1104,1087,1089,1081

I want to read this file as an array in my sql script


My ksh script is



#!/usr/bin/ksh
DIRNAME=`dirname $0`
# set up environment for oracle based SMO database instance
. $DIRNAME/defaults_env.ksh

echo "Enter the region (ie. 001 or 002): "
read sdzone;
export userFile="cvsusers.txt"
LOGFILE=$DIRNAME/cvs_users_defaults.log
TIME=`date`

#checks to see if the log files is present, if not then creates it
if [ -a $DIRNAME/cvs_users_defaults.log ]
then
:
else
touch $DIRNAME/cvs_users_defaults.log
fi

#Checking to see if a data file(cvsusers.txt) exists for a load.
if [ -a $DIRNAME/$userFile ]
then
# The list of userIds is supplied from userId file


INFILE=$userFile;
echo "Using file $INFILE as input for user id";
echo " ";
echo "Started Process on $TIME" >> $LOGFILE
echo "Calling procedure to initialize users"
for userId in `cat $INFILE`
do
echo "Storing User Defaults for User " $userId >> $LOGFILE
userIdLength=`expr length $userId`
if [ $userIdLength -gt 8 ]
then
echo "Length of UserID " $userId " is too long">> $LOGFILE
else
echo "connecting to SQL">> $LOGFILE

if [[ $sdzone = '001' ]]; then
sqlplus -s $ZONE1_USERNAME_525/$ZONE1_PASSWRD_525@$ZONE1_DBNAME_525 \@$DIRNAME/sql/cvs_users_defaults.sql $userId >> LOGFILE
echo "connected to SQL">> $LOGFILE


elif [[ $sdzone = '002' ]]; then
sqlplus -s $ZONE2_USERNAME_525/$ZONE2_PASSWRD_525@$ZONE2_DBNAME_525 \@$DIRNAME/sql/cvs_users_defaults.sql $userId >> LOGFILE
echo "connected to SQL">> $LOGFILE


else
echo "Invalid region code!"
exit;
fi

fi
done


#rename the file after processing
logtime=`date "+%y%m%d%H%M%S"`
newUserFile="cvs_users_processed_"$logtime".txt"
mv $userFile $newUserFile

echo "Ending process of setting user defaults at $TIME " >> $LOGFILE
echo "Check the log file at $LOGFILE"
exit 0
else
#IF "custsoncologyusers.txt" file does not exist in DIRNAME then end gracefully
echo "No user default file ($userFile) is available to load"
echo "No user default file ($userFile) is available to load" >> $LOGFILE
echo "Ending user default process with a return code 0 at $TIME" >> $LOGFILE
exit 0
fi


This is my sql script( cvs_users_defaults.sql)

WHENEVER SQLERROR EXIT SQL.SQLCODE;
set serveroutput on;
set verify off;
set feedback off;

declare
userId s_user_cust_acct.user_id%TYPE := '&1';
TYPE t_col_id_tab IS TABLE OF number;
my_col_id t_col_id_tab;
TYPE t_col_seq_num_tab IS TABLE OF number;
my_col_seq_num t_col_seq_num_tab;
TYPE t_all_col_id_tab IS TABLE OF number;
all_col_id t_all_col_id_tab;
tcount number;
begin
select count(*)
into tcount
from s_column_atrb
where user_id = UPPER(userId)
and screen_typ_cd = 0;
if (tcount > 0) THEN
dbms_output.put_line('updating s_column_atrb' || userId);

-- For Orders(I hard coded the array here but I want to read this from .ksh script by feeding it with a txt file)
all_col_id := t_all_col_id_tab(1069,1071,1072,1073,1074,1075,1076,1077,1078,1080,1081,1082,1083,1084,1085,1086,108 7,1088,1089,1090,1091,1101,1102,1103,1104,1114,1115,1116,1117,1118,1119,1120,1122,1123,1124,1125,112 6,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,114 6,1147,1148,1149,1150,1151,1152,1154,1169,1178,1182,1183,1195);
my_col_id := t_col_id_tab(1084,1143,1074,1080,1091,1090,1101,1069,1104,1087,1089,1081);

my_col_seq_num := t_col_seq_num_tab(4,12,2,6,7,8,10,1,9,3,5,11);

For i in 1..12 LOOP
dbms_output.put_line('my_col_id(' || i || ') is ' || my_col_id(i));
update s_column_atrb
set show_hide_ind = 'Y', column_seq_num = my_col_seq_num(i)
where user_id = UPPER(userId)
and screen_typ_cd = 0
and col_id = my_col_id(i);
END LOOP;
end;
/
exit


can any body help to get this done
Thanks in advance
Pinky

The simplest way would be to build "cvs_users_defaults.sql" on the fly before
you execute it.

For example: the file "head" would contain

The file "tail" would contain:

and the file "orders" would contain the "modifiable code"

You would then modify your shell script to build the sql script as follows:

cat head orders tail > cvs_users_defaults.sql

before invoking cvs_users_defaults.sql

I appreciate your help but I totally got confused and posted a new thread related to this
Thanks for answering

hey Guys

I wanted to ask my question in detail what I wanted to achieve is

I have txt file
which looks like this

x.txt
5
8
10
15
17
19

I have abc.ksh and xyz.sql scripts

I want to read x.txt file into an array in abc.ksh (shell script) and pass that array to xyz.sql (sql script) where I wanted to read the values of that array and use those values in logic in xyz.sql (sql script).


Thanks
Apprciate any help
I am desperate and needed help
Pinky

Hey fpmurphy

if I want to build my .sql file on the fly do I have to save the head and tail and orders as head.sql , tail.sql , and orders.sql
can u plz elaborate

I am just wondering whether there is a way that we can send arrays as parameters from shell script to sql script

Thanks a lot for all ur help
Pinky