| Problem Passing Variables Into SQL*Plus [message #610104] |
Mon, 17 March 2014 12:12  |
coreyr
Messages: 18
Registered: March 2014
|
Junior Member |
|
|
I've been testing this code snippet for a while, trying (unsuccessfully) to get the values of the DP_STATUS and ORACLE_SID variables to appear in the WHERE clause inside the SQL*Plus block.
Would appreciate any pointers y'all might have.
Thanks.
______________________________________________________________________________________________________________________
CODE
------------------------------------------
#!/bin/bash -xv
export ORACLE_SID=kcorptst
export ORAENV_ASK=NO;
. oraenv;
echo $ORACLE_SID
PWD=`tail -1 /opt/oracle/scripts/.pwd.txt`
DP_STATUS=trowel
echo $DP_STATUS
sqlplus -S /nolog <<EOF 2>&1
connect dp_user/$PWD@rman2
update dp_user.dp_status
set datapump_status='$DP_STATUS'
where database_name='$ORACLE_SID';
exit;
EOF
OUTPUT
-----------------------------------------
export ORACLE_SID=kcorptst
+ export ORACLE_SID=kcorptst
+ ORACLE_SID=kcorptst
export ORAENV_ASK=NO;
+ export ORAENV_ASK=NO
+ ORAENV_ASK=NO
. oraenv;
+ . oraenv
echo $ORACLE_SID
+ echo kcorptst
kcorptst
PWD=`tail -1 /opt/oracle/scripts/.pwd.txt`
tail -1 /opt/oracle/scripts/.pwd.txt
++ tail -1 /opt/oracle/scripts/.pwd.txt
+ PWD=xxxxxxx
DP_STATUS=success
+ DP_STATUS=success
echo $DP_STATUS
+ echo success
success
sqlplus -S /nolog <<EOF 2>&1
connect dp_user/$PWD@rman2
update dp_user.dp_status
set datapump_status='$DP_STATUS'
where database_name='$ORACLE_SID';
exit
EOF
+ sqlplus -S /nolog
0 rows updated.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem Passing Variables Into SQL*Plus [message #610209 is a reply to message #610123] |
Tue, 18 March 2014 08:17  |
coreyr
Messages: 18
Registered: March 2014
|
Junior Member |
|
|
Sorry about not posting solution specifics earlier.
Per Michel's comments above, I made sure to export the variables used in the SQL script and found that I had to set the value I was passing to the ORACLE_SID variable to upper case (without doing that, my WHERE condition would have never been evaluated.)
Shown below is the updated version of my code (changes are in bold):
export ORACLE_SID=kcorptst
export ORAENV_ASK=NO;
. oraenv;
PWD=`tail -1 /opt/oracle/scripts/.pwd.txt`
DP_STATUS=succeeded
export DP_STATUS
UPPER_SID=`echo $ORACLE_SID | tr '[:lower:]' '[:upper:]'`
export UPPER_SID
sqlplus -S /nolog <<EOF 2>&1
connect dp_user/$PWD@rman2
update dp_user.dp_status
set datapump_status='$DP_STATUS'
where database_name='$UPPER_SID';
exit;
|
|
|
|
|
|
|
|
|
|
| Re: Problem Passing Variables Into SQL*Plus [message #611112 is a reply to message #611111] |
Thu, 27 March 2014 13:23 |
coreyr
Messages: 18
Registered: March 2014
|
Junior Member |
|
|
|
Definitely right. I should've said in my previous post that the other part of the code didn't actually REQUIRE a change in case of the SID, it was just a choice on my part to change it (for display purposes inside a table.)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
