Home » Infrastructure » Unix » Problem Passing Variables Into SQL*Plus (Oracle DB 10.2.0.5.0)
Problem Passing Variables Into SQL*Plus [message #610104] Mon, 17 March 2014 12:12 Go to next message
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 #610105 is a reply to message #610104] Mon, 17 March 2014 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59497
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First you must export your variables.
Then check there is a row to update.

Re: Problem Passing Variables Into SQL*Plus [message #610106 is a reply to message #610104] Mon, 17 March 2014 12:18 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
post results from following SQL

SELECT COUNT(*) FROM dp_user.dp_status WHERE where database_name='kcorptst';

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Problem Passing Variables Into SQL*Plus [message #610109 is a reply to message #610106] Mon, 17 March 2014 12:32 Go to previous messageGo to next message
coreyr
Messages: 18
Registered: March 2014
Junior Member
Black Swan -- thanks for providing the link; I've noted the info there for subsequent posts;

Michel -- thank you for you the suggestions; my issue is now resolved.
Re: Problem Passing Variables Into SQL*Plus [message #610123 is a reply to message #610109] Mon, 17 March 2014 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
> I've noted the info there for subsequent posts;
what about #12 for THIS thread?
Re: Problem Passing Variables Into SQL*Plus [message #610209 is a reply to message #610123] Tue, 18 March 2014 08:17 Go to previous messageGo to next message
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 #611107 is a reply to message #610209] Thu, 27 March 2014 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
below would have been simpler
export ORACLE_SID=KCORPTST


is DP_STATUS content CaSeSeSiTiVe?
Re: Problem Passing Variables Into SQL*Plus [message #611110 is a reply to message #611107] Thu, 27 March 2014 13:04 Go to previous messageGo to next message
coreyr
Messages: 18
Registered: March 2014
Junior Member
DP_STATUS should be lower case. And you're right about the Oracle SID -- the only reason I did it that way was because part of the code (which I omitted for clarity) needed it in a lower-case form.
Re: Problem Passing Variables Into SQL*Plus [message #611111 is a reply to message #611110] Thu, 27 March 2014 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
> And you're right about the Oracle SID -- the only reason I did it that way was because part of the code
>(which I omitted for clarity) needed it in a lower-case form.
IMO, fundamental flaw exists & Oracle SID should NEVER be required to be lower case in one part of the code & UPPER case in a different part.
ORACLE_SID is CaSeSeNSiTiVe on any *NIX system & usage should be consistent everywhere it is used,
Re: Problem Passing Variables Into SQL*Plus [message #611112 is a reply to message #611111] Thu, 27 March 2014 13:23 Go to previous message
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.)
Previous Topic: Databases created in solaris
Next Topic: ./dbstart: test: argument expected
Goto Forum:
  


Current Time: Thu Oct 30 10:42:38 CDT 2014

Total time taken to generate the page: 0.11056 seconds