I'm using korn shell to connect to oracle, retrieve certain values, put them in a list, and iterate through them. While this method works, I can't help but think there is an easier method.
If you know of one, please suggest a shorter, more efficient method.
PHP Code:
############### FUNCTIONS ####################################################################
check_for_sqlplus_error()
{
# $1 = $? (results of last command)
# $2 = user defined error code
if [ $1 -ne 0 ]; then
write_log " ERROR - sqlplus execution failure at: "$2
exit 1
fi
return 0
}
run_query()
{
# $1 - db login
# $2 - user defined error code
# $3 - SQL to execute
# $4 - logfile to re-direct to
$SQLPLUS -s $1<<EOF | sed '/^$/d' > $4
set HEADING OFF
set FEED OFF
set TRIMOUT ON
set TRIMSPOOL ON
$3;
EOF
check_for_sqlplus_error $? "code "$2
return 0
}
run_proc()
{
# $1 - db login
# $2 - user defined error code
# $3 - SQL to execute
# $4 - logfile to re-direct to
$SQLPLUS -s $1<<EOF | sed '/^$/d' > $4
set pagesize 0
set AUTOT ON
set serveroutput on SIZE 50000
$3;
EOF
################# MAIN ########################################################################
# SQL*PLUS code:200 - Get the list of batches for this load
write_log " Get the Batch List..."
run_query $ODS 200 "select distinct batch_num from cr_batch where load_desc = '$SOURCE_SYSTEM_DESC'" $LOGPATH$SOURCE_SYSTEM_DESC.batch.lst
if [ -s $LOGPATH$SOURCE_SYSTEM_DESC.batch.lst ]; then
write_log " Begin Processing Batches..."
while read BATCH_NUM
do
if [ $BATCH_NUM ]; then
# SQL*PLUS code:300 - Run the batch load stored procedure for this batch
write_log " Calling p_stage_load with $SOURCE_SYSTEM_DESC , $LOAD_RUN_NUM, $BATCH_NUM "
run_proc $STAGING 300 "exec p_stage_load('$SOURCE_SYSTEM_DESC', $LOAD_RUN_NUM, $BATCH_NUM )" $LOGPATH$SOURCE_SYSTEM_DESC.$BATCH_NUM.log &
else
write_log " No batches found for '$SOURCE_SYSTEM_DESC'"
fi
done < $LOGPATH$SOURCE_SYSTEM_DESC.batch.lst
else
# if there are no batches, lets just exit
write_log " No batches for "$SOURCE_SYSTEM_DESC" were found!...Exiting"
exit
fi
First, FYI: sqlplus will return exit code 0 when the database isn't up; you can try it yourself. Add "WHENEVER SQLERROR" to force a non zero exit code.
Quote:
Originally Posted by SelectSplat
Second: You certainly could combine your sqlplus calls into one.
Personally, I prefer to use this kind of construct:
If you results exceed 4098, you won't want to use an array if you Korn shell is KSH88. I believe that the newer Korn shells will allow more the 4098 elements.
The first two tips are GREAT thanks. SQL*Plus is new to me, as nearly all my experience has been with ISQL.
Your last suggestion is extremly interesting to me, as I can barely follow what's going on. In fact, I don't understand at all.
Is 'RESULTS' an array of strings being assigned the result set from sqlplus?
What is IFS?
What is /nolog ?
I'm completly lost with the 'if'.
If I use this method, will I still have each 'word' in each row in it's own variable?
Can you psudo code document it for me?
Also, I'm not exactly sure what version of korn we're using, but I did notice that VAR=$(command) doesn't work as I'd expect. It looks like I have the same funcationality with VAR=`command`, but I'm not 100% sure.
Sorry for all the questions. Thanks for you reply, and in advance for your further elaboration.
Quote:
Originally Posted by tmarikle
Personally, I prefer to use this kind of construct:
If you results exceed 4098, you won't want to use an array if you Korn shell is KSH88. I believe that the newer Korn shells will allow more the 4098 elements.
[code]
# Read whole lines IFS defines what field seperators consist of. Normally they are space, tab, newlines, etc. I am redefining the field
seperators to newlines only so for and while loops process lines instead of words.
IFS='
' This is defining an array from sqlplus' output plus my print command. "/nolog" just keeps sqlplus from
attempting to log in from any point except from a "connect" command from the SQL.
Will this still work in my version of ksh, even when it appears that $(command) isn't working? Or do I need to do the obvious, and replace the $(command) with `command` ?
Also, 2 other important questions regarding this technique.
Using the 'while read', I'm able to capture each 'word' of a line in a seperate variable, while processing all of the lines in the file. It appears that your technique is putting the whole line in an element of the array. If I do that, I'd need to use awk, or something similar, to address a particular 'word' in the line, correct?
Also, in this loop, I'm spawning batches off in the background. Each spawned batch load a list of tables sequentially. If one of the tables in the batch fails to load, the desired effect is to continue on with the next table. So, from your explaination, I gather that this statement...
if [[ "RC=0" != ${RESULST[(( ${#RESULTS[@]} - 1} ))] ]]
Regarding KSH: I'm using KSH88, which is about as old as they get I think. I do not know why $() doesn't work but back ticks should work fine. Bourne and Bash use back ticks; are use certain that you aren't using one of these shells?
Regarding words vs lines: this is what happened with we changed IFS. You can reset IFS to use spaces at any time letting you process words in a read loop.
Regarding the if statement: mine is only testing whether sqlplus exited with a non-zero result so I don't know whether that would be relavent in your case; perhaps.
Hello guys
My requirement is to read a file with parent-child relationship
we need to iterate through each row to find its latest child.
for eg. parent child
ABC PQR
PQR DEF
DEF XYZ
Expected Output
ABC XYZ
PQR XYZ
DEF XYZ
Script Logic :
read parent from file
seach child... (4 Replies)
Hi Experts,
I've been trying simple grep to search for a string in a huge number of files in a directory.
grep <pattern> *
this gives the search results as well as the following -
grep: <filename>: Permission denied
grep: <filename>: Permission denied
for files which I don't have... (4 Replies)
I want to match the red portion:
9784323456787-Unknown Phrase with punctuation "Some other PhrASE."
Is this the best regex to match this?
'978\{10\}-*' (4 Replies)
some of the data i receive has been typed in manually due to which there are often places where i find 8 instead of ( and the incorrect use of case
what according to you is the best way to correct such data.
The data has around 20,000 records.
The value i want to change is in the 4th field.... (2 Replies)
Hi,
i have 2 files each with 200K lines. Each line contains a number. Now, i need to get the list of numbers existing in one fine and NOT in other file. I'm doing this by reading each number from 1 file and grepping on other file. But this taking LOT of time. Is there any efficient way of doing... (14 Replies)
I have the following code.
printf "Test Message Report" > report.txt
while read line
do
msgid=$(printf "%n" "$line" | cut -c1-6000| sed -e 's///g' -e 's|.*ex:Msg\(.*\)ex:Msg.*|\1|')
putdate=$(printf "%n" "$line" | cut -c1-6000| sed -e 's///g' -e 's|.*PutDate\(.*\)PutTime.*|\1|')... (9 Replies)
Hello,
We have a directory with 15 sub-directories where each sub-directory contains 1.5 to 2 lakhs of files in it. Daily, around 300-500 files will be uploaded to each sub-directory.
Now, i need to get the list of files received today in most efficient way. I tried using "find with newer... (16 Replies)
Hi
I have the following at the end of a service shutdown script used in part of an active-passive failover setup:
###
# Shutdown all primary Network Interfaces
# associated with failover
###
# get interface names based on IP's
# and shut them down to simulate loss of
# heartbeatd
... (1 Reply)
Hi,
Can someone let me know if the below AWK can be made much simpler / efficient ?
I have 200 fields, I need to substr only the last fields.
So i'm printing awk -F~ 'print {$1, $2, $3....................................$196,$197 , susbstr($198,1,3999), substr($199,1,3999)..}'
Is there a... (4 Replies)
Does anyone know what's new with Efficient dispatching in the Solaris 2.8 release (vs Solaris 2.6) release?
Specifically, does anyone know of a good website to get detailed information on thread dispatching using efficient dispatching in solaris 2.8?
Thank you. (1 Reply)