OraFAQ Forum: Windows » windows batch file : variable assignment

Home » Infrastructure » Windows » windows batch file : variable assignment (Windows NT)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

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 #358078 is a reply to message #358077]

Sat, 08 November 2008 05:44

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.

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

Report message to a moderator

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 :

Code: [Select all] [Show/ hide]

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 :

Code: [Select all] [Show/ hide]

cscript test.vbs

A few more examples are here and here

Report message to a moderator

Re: windows batch file : variable assignment [message #358082 is a reply to message #358074]

Sat, 08 November 2008 06:15

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

Report message to a moderator

Re: windows batch file : variable assignment [message #358083 is a reply to message #358082]

Sat, 08 November 2008 06:17

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.

Report message to a moderator

Re: windows batch file : variable assignment [message #358086 is a reply to message #358074]

Sat, 08 November 2008 06:44

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

Report message to a moderator

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.

Report message to a moderator

Re: windows batch file : variable assignment [message #358099 is a reply to message #358086]

Sat, 08 November 2008 09:58

ebrian
Messages: 2794
Registered: April 2006

Senior Member

The following should suffice:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: windows batch file : variable assignment [message #679635 is a reply to message #358074]

Wed, 11 March 2020 15:33

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>

Report message to a moderator

Re: windows batch file : variable assignment [message #679637 is a reply to message #679635]

Wed, 11 March 2020 16:23

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 17:51:38 CDT 2025