Home » Infrastructure » Windows » windows batch file : variable assignment (Windows NT)
windows batch file : variable assignment [message #358074] Sat, 08 November 2008 04:58 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #358078 is a reply to message #358077] Sat, 08 November 2008 05:44 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
PS: Just saw that you use NT4, and I have checked. VBScript should be there, too.
Re: windows batch file : variable assignment [message #358079 is a reply to message #358074] Sat, 08 November 2008 05:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 :

cscript test.vbs


A few more examples are here and here
Re: windows batch file : variable assignment [message #358082 is a reply to message #358074] Sat, 08 November 2008 06:15 Go to previous messageGo to next message
dr46014
Messages: 49
Registered: February 2007
Member
where i am assigning the varibale count in the script.as i dont have any idea about the VB script not able to understand it
Re: windows batch file : variable assignment [message #358083 is a reply to message #358082] Sat, 08 November 2008 06:17 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You have read the "Open recordset and get first result" and "Do something with the result" comments?

The count(*) is in the strResults variable.
Re: windows batch file : variable assignment [message #358086 is a reply to message #358074] Sat, 08 November 2008 06:44 Go to previous messageGo to next message
dr46014
Messages: 49
Registered: February 2007
Member
can i set the value of strResults in the Windows env.i.e when i ll type echo %strResults% will it display the count ?
As i ahve a batch file where %count% will be used
Re: windows batch file : variable assignment [message #358088 is a reply to message #358086] Sat, 08 November 2008 07:13 Go to previous messageGo to next message
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.
Re: windows batch file : variable assignment [message #358099 is a reply to message #358086] Sat, 08 November 2008 09:58 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The following should suffice:

E:\>type test.cmd
@echo off
for /f %%i in ('sqlplus -s test/test @test.sql') do @set count=%%i
echo %count%

E:\>type test.sql
set pages 0
select count(*) from dual;
exit

E:\>test.cmd
1
Re: windows batch file : variable assignment [message #679635 is a reply to message #358074] Wed, 11 March 2020 15:33 Go to previous messageGo to next message
RainbowsAndJiz
Messages: 1
Registered: March 2020
Junior Member
SQL> host echo define username=%USERNAME% > dumb.sql
SQL> @dumb
SQL> define username
DEFINE USERNAME = "Jiz.Man" (CHAR)
SQL>
Re: windows batch file : variable assignment [message #679637 is a reply to message #679635] Wed, 11 March 2020 16:23 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Previous Topic: Would you please help with connection to Oracle through Power Shell ?
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Sun Dec 08 18:03:02 CST 2024