Home » Infrastructure » Unix » Unable to interpret environmental variable (Solaris)
Unable to interpret environmental variable [message #395502] Wed, 01 April 2009 12:12 Go to next message
unixmylife
Messages: 2
Registered: April 2009
Location: india
Junior Member
Hi All,
I am coding a script which is intended to do the following :
1. I fetch a column from the oracle table which has sql statement.
For ex: select column1 from table1 where column2=${env_var_value}

2. I have set the environmental variable in my Solaris OS.
I fetch this column value into a variable and I have to run the statement.

Now the problem is that when I fetch the column value into a variable in my program, the environmental variable doesn't get interpreted.
It just places the column value i.e., select column1 from table1 where column2=${env_var_value} without substituting the env_var_value..

Request your precious guidance and help in this regard.
Your reply would be of great help in my code execution..
Thanks in advance
Re: Unable to interpret environmental variable [message #395506 is a reply to message #395502] Wed, 01 April 2009 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

You have table & DDL. We don't
You have data & DML. We don't.
You have code. We don't.

You have ALL the information & can't debug the problem.
Why do you expect others, who have NO details, to solve your mystery?

use CUT & PASTE to show us what you see & exact response!
Re: Unable to interpret environmental variable [message #395507 is a reply to message #395506] Wed, 01 April 2009 12:29 Go to previous messageGo to next message
unixmylife
Messages: 2
Registered: April 2009
Location: india
Junior Member
Hi All,
Here is a detailed explanation of the issue what I am facing :

I am writing a script that accesses the Oracle table wherein I have the Oracle statement saved.
The statement is something like this:
select col1,col2 from table_1 where insert_date='${env_var1}'
The above statement is stored in one of the column called "SQL" my table Table_driver.

In my shell program, I fetch this column and assign it to a variable.
Code looks like this:
#!/bin/ksh -x
. ~/.profile
SQL_STMT=$(echo `sqlplus -s ${DB_ID}@${NAME}/${DB_PSWD}<<EOF
set feedback off
set heading off
set serveroutput off
SELECT SQL from Table_driver 

/
EOF`)

#in the above statement here the ${env_var1} is not getting #replaced by its value

#I run this SQL#
sqlplus -s -m "html on entmap off" -s ${DB_ID}@${NAME}/${DB_PSWD}<<EOF > FILE.dat
set feedback off
${SQL_STMT}
/
EOF
....
....
#This information I embed in my mailx command and send it to recipients list# 
....


Now the problem is that the environment variable "env_var1" is not being set in the variable "SQL_STMT"


Request your help and suggestion in this regard.

Thanks in advance.


Edit: Mahesh Rajendran
Added CODE tags

[Updated on: Wed, 01 April 2009 12:48]

Report message to a moderator

Re: Unable to interpret environmental variable [message #395513 is a reply to message #395502] Wed, 01 April 2009 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
One of the Posting Guidelines direct folks to SEARCH before posting.

If you SEARCH this subforum you will find more than 30 threads which contain "EOF" & some will show proper use of HERE DOCUMENT & SQL*Plus

You are close to obtaining a workable solution.
Re: Unable to interpret environmental variable [message #396453 is a reply to message #395513] Mon, 06 April 2009 16:28 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can evaluate a variable whose name is held in another variable, but I'm not sure that can solve this problem. maybe.

unixdev:>>export X=1       
unixdev:>>Y=X              
unixdev:>>echo $X          
1
unixdev:>>echo $Y          
X
unixdev:>>eval "echo \$$Y"
1



The easiest way may be just search & replace:

unixdev:>>SQL='select $V1 from dual;'
unixdev:>>echo $SQL
select $V1 from dual;
unixdev:>>V1="'XXX'"
unixdev:>>echo $V1
'XXX'
unixdev:>>echo $SQL | sed -e "s/\$V1/$V1/" | read SQL2
unixdev:>>echo $SQL2
select 'XXX' from dual;
unixdev:>>
Previous Topic: What's the maximal memory of PGA in Oracle 10g R2 on Solaris 9 or Linux
Next Topic: Please tell me the "ux2dos"(HP-UX) equivalent for AIX ???
Goto Forum:
  


Current Time: Thu Mar 28 09:08:49 CDT 2024