i had this unix korn shell code that connects to oracle database and execute the oracle procedure. i need to add a variable that indicates the oracle procedure failed. basically the variable is to check if the oracle procedure failed it will assign 1 and when the variable is equal to 1 it will not move the files.
below is the code snipet.
Code:
NUMFILES=`ls -1 *.* | wc -l`
if (( ${NUMFILES} == 0 )); then
echo "WARNING: There are no files in process directory.\n" >> $LOG_FILE
exit 1
fi
#
for filename in $(ls -1 *.*)
do
echo "Processing $filename \n" >> $LOG_FILE
#
echo "Executed PL/SQL package for $filename\n" >> $LOG_FILE
SQL_LOG_FILE=${LOG_DIR}/${filename}_${LOGDATE}.log
DB_RSLT=`sqlplus -s <<EOF >> $SQL_LOG_FILE >> $LOG_FILE
$USERID
set serveroutput on size 999999;
declare
v_result varchar2(200);
begin
$sql_error=0 <-- i tried to add this and there was an error
bdms_app.pa_bdms_upload_load_rating.upload('$filename');
exception
when others then
$sql_error=1; <-- i tried to add this and there was an error
end;
/
set serveroutput off;
exit 0
EOF`
wait
#
RC=$?
#
echo $DB_RSLT >> $LOG_FILE
echo "$filename \n" >> $RATING_LOG
#
if (( $sql_error == 0 )); then <-- if the variable is equal to 0 proceed to moving the file
echo "Copied $filename to $ARCHIVE_DIR\n" >> $LOG_FILE
cp $filename $ARCHIVE_DIR
echo "Delete $filename from $INPUT_DIR\n" >> $LOG_FILE
rm -f $filename
fi
#
done
echo "End of Step 2\n" >> $LOG_FILE
this is the error that i am getting.
Code:
******************************
Run date: 12/21/2011 09:57:36
Step 1 Check for files to process
files to process 2
End of Step 1
Step 2 Process the files
Processing asd.csv
Executed PL/SQL package for asd.csv
=0
*
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
You can't insert arbitrary shell code inside SQL and expect it to work. Shell code won't be understood inside SQL. No external program can reach back into your shell and set shell variables for you anyway.
The shell already has a special variable to tell you whether the last command failed, and you're already using it: RC=$? ..but you aren't checking it anywhere.
I'm not sure why you're running that command in backticks because you redirect all its output anyway, leaving nothing to capture. You redirect it into several different files, actually. I'm not sure which one it actually ends up in. You need to pick one and only one.
That ending EOF must be at the beginning of the line! If it isn't, your program may malfunction.
You should put RC=$? immediately after running your SQL statement, otherwise the next command may overwrite $? and return a false success.
Try this:
Code:
...
SQL_LOG_FILE=${LOG_DIR}/${filename}_${LOGDATE}.log
sqlplus -s <<EOF >> $SQL_LOG_FILE # >> $LOG_FILE
$USERID
set serveroutput on size 999999;
declare
v_result varchar2(200);
begin
bdms_app.pa_bdms_upload_load_rating.upload('$filename');
exception
when others then
end;
/
set serveroutput off;
exit 0
EOF
RC=$?
wait # What are you waiting for? I see no background commands anyway!
...
if [ "$RC" -eq 0 ]
then
echo "query succeeded"
else
echo "query failed"
fi
...
Hi all,
Hopefully you can help. This is what I'm trying to achieve:
Obtain a list of usernames out of an Oracle Database
Based on this list, link each username with an Oracle Internet Directory (OID) GUID
Using the username and GUID perform a database update for all users
Here are the... (7 Replies)
Hello Experts,
This script to delete a file is submitted from an SAP system which has 2 servers. When it happens to run on server 1, the delete is successful. When it runs on server 2, the delete always fails. All user accounts and permissions have been adjusted to match on both servers. Is it... (5 Replies)
Hi all,
Hereby wish to have your advise for below:
Main concept is
I intend to get current directory of my script file.
This script file will be copied to /etc/init.d.
A string in this copy will be replaced with current directory value.
Below is original script file:
... (6 Replies)
I am passing a varaible to from Shell to awk then I am doing some maniplation for that variable inside awk. I want that maniplated variable value back to shell , Is this possible .Please let me know. (12 Replies)
Hi,
I am running a script from a client machine X which does "SSH" to around 100 other machines in a farm and invokes a local script on each of those machines. Local script localscript.sh on each of those 100 target machines, does some machine specific function like fetch the specific machine's... (1 Reply)
hi ,
how to pass unix variable to oracle
code is .............
#! /bin/ksh
echo enter date vale
read date1
sqlplus x/y@oracle
select * from emp where statrt_date= $date1
is this is correct way... (1 Reply)
hi fellows,
can any body tell me how to pass unix variables to oracle
code is...
#! /bin/ksh
echo ENTER DATE VALUE's
read START_DATE END_DATE
sqlplus xyx/abc@oracle
select * from table1 where coloumn1 between $START_DATE and $END_DATE;
is this is correct way...........
Thanks in... (1 Reply)
Hi,
I am calling an oracle function that returns a number (either 0 or 2), how do I pass that pass to the wrapping shell script as I would like to do other things based on the value returned by the oracle function.
Your help will be appreciated.
--------------------------
sqlplus / <<... (3 Replies)
Hi all,
I am running AIX version 4. I have a shell script that is calling another script. I want the called script to obtain a value and pass it back to the calling script. So far, I know that to pass a parameter to a called script is as such:
sh proc2.sh $1 $2 etc.
What I don't know how... (11 Replies)