| windows batch file : variable assignment [message #358074] |
Sat, 08 November 2008 04:58  |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
I am using Windows to run SQL queries from command prompt.Is there any way to assign the output of a select query to a variable.
I am creating a batch file as mentioned below
c:> type run_oracle.bat
@echo OFF
set MYDIR=C:\oracle\scripts
sqlplus -s user/pwd@DB @%MYDIR%\rpt_dba.sql
exit
the rpt_dba.sql has the below query
SET HEAD OFF
SELECT count(*) from table_name;
While doing the same thing in UNIX i was floowing the below approach
count=`sqlplus -s user/pwd@DB<<EOF
SET HEAD OFF
SELECT count(*) from table_name;
EXIT;
EOF`
export count
How we can create and set count variable in Windows
|
|
|
|
| Re: windows batch file : variable assignment [message #358075 is a reply to message #358074] |
Sat, 08 November 2008 05:29  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
It can't be done in Windows. With the standard *.cmd batch files there is no way to pass a variable back to the script from an executed program.
You need to use another scripting language. VBScript or PERL would be options. There you can connect to the database (With ODBC in VBScript, with the DBI / DBD::Oracle Modules in PERL) and live becomes much easier all of a sudden.
When you use PERL, you usually can even use the same scripts under Windows and Linux.
|
|
|
|
| Re: windows batch file : variable assignment [message #358076 is a reply to message #358074] |
Sat, 08 November 2008 05:34 |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
As i dont have the facility to use VB script or Perl i am thinking of an alternative.I am not sure if it will work or not.
Please help me to understand this.
Can we redirect the output of that sql query in side the batch file to another file(.txt) and my windows will be having another batch script which will read the value from that file and store that in a variables.
Is this possible.
|
|
|
|
| Re: windows batch file : variable assignment [message #358077 is a reply to message #358076] |
Sat, 08 November 2008 05:42 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
If you have Windows (Windows 2000 or later), then you have the facility to use VB script.
Of course, if you insist on trying to screw in a screw with a shovel instead of getting a screwdriver, then you COULD write an *.cmd file with an pl/sql procedure and then execute that *.cmd file.
[Updated on: Sat, 08 November 2008 05:42] Report message to a moderator |
|
|
|
|
|
| Re: windows batch file : variable assignment [message #358079 is a reply to message #358074] |
Sat, 08 November 2008 05:46 |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
Thanks for your reply.
I need to again check the version of Windows.I think its Windows 2000.
But i dont have any idea how to write VB script.Could you please help me in this context what all extra things we need to do here.
How to assign the output to a varibale.
If you can post the sample code i ll be grateful
|
|
|
|
| Re: windows batch file : variable assignment [message #358081 is a reply to message #358079] |
Sat, 08 November 2008 06:09 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Here is one example, which should run on your system :
Dim strConnect
Dim adoConnection
Dim adoRecordset
Dim strSQL
Dim strResults
' Define the SQL
strSQL = "SELECT count(*) from table_name"
' Connect to database
Set adoConnection = CreateObject("ADODB.Connection")
strConnect = "Driver={Microsoft ODBC for Oracle};" & _
"CONNECTSTRING=DB;uid=user;pwd=pwd;"
adoConnection.Open strConnect
' Execute the SQL
strResults = adoConnection.execute(strSQL)
' Open recordset and get first result
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
adoRecordset.Source = strSQL
adoRecordset.Open
strResults = adoRecordset.Fields(0).Value
' Do something with the result
msgbox strResults
' Close everything
adoRecordset.Close
adoConnection.Close
Save that into a file, test.vbs for example, and then run it with :
A few more examples are here and here
|
|
|
|
|
|
|
|
|
|
| Re: windows batch file : variable assignment [message #358088 is a reply to message #358086] |
Sat, 08 November 2008 07:13 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
As you remember, this whole thing started because there is no sane and reliable way to pass values back from other processes to a calling *.cmd file.
There are some workaround options, but they only kinda-sorta work under certain circumstances, so I'm not going to help you dig yourself deeper into that hole.
All those ways would fail and do unexpected stuff without a real error message if the database is down for example. You could wind up with the Oracle error code in the count or something like that.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
