How to read from txt file and use that as an array
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