the value of an environment cannot get with .bset and sqlplus
Hi all,
I ran into issue with .bset and sqlplus, here is the command:
.bset env "COUNTSTR=select count(*) from ${MAINTABLE} where revname='${ENV}'; | sqlplus ${USER}/${CMUSER}@DB"
here is the issue:
select count(*) from REVOLVEMAIN where revname='${ENV}' <----------
The variable ${ENV} is inside '${ENV}' (single quotes) is the same as is in the command.
Any ideas? Please help! Why the value is not there?
Thanks
Michael Dang
6 answers
Ryan,
All I want to achieve is using .bset and sqlplus to get the result from sqlplus to set to a variable for next step. I am unable to get what I want.
i.e. after executing the command, I expect variable like COUNTSTR=2200.
All environment variables are preset either from project or step level.
I have tried "echo select count(*) from ${MAINTABLE} where revname='${ENV}'; | sqlplus ${USER}/${CMUSER}@DB".
The value of the environment ${ENV} does not populate.But it works on Windows (agent) commandline "echo select count(*) from %MAINTABLE% where revname='%ENV%'; | sqlplus %USER%/%CMUSER%@DB".
I have tried ".bset env "COUNTSTR='select count(*) from ${MAINTABLE} where revname='${ENV}'; | sqlplus ${USER}/${CMUSER}@DB'"; but sqlplus does not like it.
Is there another creative way to get what I want?
Thanks
Michael
Hola,
If you are setting the variable via a Build Forge Environment. You can set the NAME as ENV and the value as having single quotes 'hello'.
If you use this it will echo your value has 'hello' just like you want.
If you are setting these variables via a .bset you need to do the same thing.
.bset env "ENV1='OK'" <-- single quotes
That will echo your value as 'hello' like you want. That way when you do this.
Example!
My example is this.
Step 1
.bset env "ENV=Evolve"
.bset env "MAIN=Change"
.bset env "ENV1='OK'"
In a NEW Step. Step 2
.bset env "String=select count(*) from ${MAIN} where revname=${ENV} and ${ENV1}"
Then used this in another NEW STEP (Can't use a .bset in the same step it's declared)
output
select count(*) from Change where revname=Evolve and 'OK'That will give me what I/you are looking for. Just either declare the variable in the environment or in the .bset with having single quote.
Also, I can see or maybe I think anyway looking at it without thinking
Might have some issues. Again, the best way to do this. Is to make "${USER}/${CMUSER}@DB" It's own value. Then just plug in a single value. into the final string. This gives higher level of manipulation and allows you to work on it per section until all results are good. Then throw them together.
I hope this helps. If so, let me know. If not... I can give it another try haha.
Thanks!
Somehow I cannot add comment on your question.
The command is executed with the result is 0, but it does not set to the variable. The variable contains the whole command as is. If next time you echo that variable, it will execute the sqlplus command again.
If I got the ENV to expand properly, it still does not give what I want..
I am thinking a different solution for this.
Any suggestions?
Thanks
Michael
The command is executed with the result is 0, but it does not set to the variable. The variable contains the whole command as is. If next time you echo that variable, it will execute the sqlplus command again.
If I got the ENV to expand properly, it still does not give what I want..
I am thinking a different solution for this.
Any suggestions?
Thanks
Michael
Hey,
If you surround the command in backticks it is suppose to run the command before setting the variable. Thus giving the variable the output. I see that you have backticks in your command already, but I think the two extra ones surrounding ENV are throwing it off. That is all I can think of. So if you put the ' ' in the varaible and out of the command and you still have the issue.
You might try a small script. Aside from that I don't really have a better way at the moment and don't have an env to test on either.
Thanks!
Thanks for info.
I had backticks surround the command. The ' ' (single quotes) are for command that SQLPLUS accepted, if we don't have those single quotes, SQLPLUS will failed. Here is the results after I executed the .bset "
I had backticks surround the command. The ' ' (single quotes) are for command that SQLPLUS accepted, if we don't have those single quotes, SQLPLUS will failed. Here is the results after I executed the .bset "
EXEC .bset env 'COUNTSTR' = 'SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 23 15:21:48 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> COUNT(*)---------- 0SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options' (New Variable)"
If use echo ${COUNTSTR} again in the next step, SQLPLUS start again, because .bset command does not set the result.
Do you have any idea how to escape single quotes in BF?
Thanks
Michael