How to store the results from my query into a variable in a batch file?

Question

I have a script getCount.sql that I use to extract the count of records from the table. the query is as below.

connect user/pwd@DB
spool C:\filepath\filename.txt

select count(distinct(column name)) 
from table name 
where condition
/

spool off
exit;

I call this from getCount.bat file using the command

sqlplus /nolog @C:\filepath\getCount.sql

I am able to get the output into the file filename.txt. I need assistance to store this into a variable in the getCount.bat batch file and use it for further computing.

`for /f "usebackq tokens=*" %%a in (´sqlplus /nolog @C:\filepath\getCount.sql´) do set "yourVar=%%a"`. See `for /?` for more help in parsing whatever the output of this command may be. — elzooilogico, Apr 28 '17 at 10:19
Please show the output of this command, that is the content of `C:\filepath\filename.txt` — Magoo, Apr 28 '17 at 13:14
Possible duplicate of [Assign Command output to Variable in Batch file](http://stackoverflow.com/questions/16203629/assign-command-output-to-variable-in-batch-file) — aschipfl, Apr 29 '17 at 14:03
@Magoo - the result looks as below. I need to check if the result holds a value greater than 0 and perform my next set of actions based on this. COUNT(COLUMN NAME) ---------------------------------- 1 — Ramprasad N, May 03 '17 at 11:29
Please edit this data into your question as it's not possible to tell the exact format of the data in comments. — Magoo, May 03 '17 at 12:51

Prescott Chartier · Answer 1 · 2017-05-03T22:19:30.533

0

Declare a variable of type Number, select count into that variable:

connect user/pwd@DB
spool C:\filepath\filename.txt

   Declare MyNumber Number;
BEGIN
    select count(distinct(Column_Name)) 
    into MyNumber
    from table_name
    where condition;
    IF MyNumber > 0 THEN
        -- Do Stuff Here ....
    END IF;
END;
/
spool off
exit;

edited May 03 '17 at 22:19

answered Apr 28 '17 at 19:09

Prescott Chartier

1,180
2
14
24

We were unable to run the above modified query. What i would need to be able to do is compare the result of the query (the count from the query) and if it is greater than 0, i need to run the next query. – Ramprasad N May 03 '17 at 11:33
My bad, I shouldn't write code off the top of my head. I modified my answer above. – Prescott Chartier May 03 '17 at 22:20
In the above query ,we need to call another batch file inside the IF statement. Could you please help us out with this? – Ramprasad N May 24 '17 at 12:07
I'm confused, why do you need to shell out from the procedure? Assuming that the code you want to run is PL/Sql code, just run the code you want from within the If statement. Or is there a specific operating system thing you need to do? – Prescott Chartier May 24 '17 at 13:25
In the above code, if the variable #MyNumber >0 then it should call another the other batch file, which consists of another Sql Query. – Ramprasad N Jun 08 '17 at 09:52

score 0 · Answer 2 · edited Apr 29 '17 at 16:14

0

You can just use SQL to create a new .bat file and then run that.

edited Apr 29 '17 at 16:14

Bhargav Rao

41,091
27
112
129

answered Apr 28 '17 at 19:10

Roger Cornejo

1,452
1
8
7

score 0 · Answer 3 · answered Apr 29 '17 at 11:56

0

just change the file extension of file. The result will be save in .bat file

connect user/pwd@DB spool C:\filepath\filename.bat

select count(distinct(column name)) from table name where condition /

spool off exit;

answered Apr 29 '17 at 11:56

gulfaraz anis

1
1

How to store the results from my query into a variable in a batch file?

3 Answers3