Home » SQL & PL/SQL » Client Tools » Conditionals in a SQLPlus batch file (Oracle 10.2.0.1.0 running on Windows Server 2003)
Conditionals in a SQLPlus batch file [message #569253] Tue, 23 October 2012 12:56 Go to next message
c2walter
Messages: 5
Registered: October 2012
Junior Member
Hello,

From within a DOS batch file I make the following call:

sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]

Within the file batch_script.sql I have the following lines:

spool output_batch.lst

connect &&2/[password]@&&1;

@SomeOtherFile.sql

I want to change the last line to something like this:

if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;

I get the following result

SQL> if &&1 = "something" then
SP2-0734: unknown command beginning "if &&1 = "..." - rest of line ignored.

I took a shot at using the "If" statement and missed. Is there any way of doing what I require?

I searched in vain.

Thank you for your assistance.
Re: Conditionals in a SQLPlus batch file [message #569260 is a reply to message #569253] Tue, 23 October 2012 13:51 Go to previous messageGo to next message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about making that decision in DOS batch file and calling the appropriate SQL file?
Re: Conditionals in a SQLPlus batch file [message #569265 is a reply to message #569260] Tue, 23 October 2012 14:51 Go to previous messageGo to next message
c2walter
Messages: 5
Registered: October 2012
Junior Member
Thank you for the response except that your solution (which is my back-up plan) avoids the issue rather than solves it. Originally, I had multiple batch files for each of my environments. Moving from a "BAT" file to a "VBS" file gave me the flexibility I needed and the ability to pass parameters set up in the VBS file to the SQLPlus batch file. Now, for each of those environments, I have batch_[environs].sql file and they can be numerous. My goal, if possible, is to also reduce that number to one.
Re: Conditionals in a SQLPlus batch file [message #569277 is a reply to message #569265] Wed, 24 October 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus has no IF THEN ELSE or any procedural language elements. SQL*Plus is just an interface to transmit SQL or PL/SQL statements and display the result returned.

Quote:
is to also reduce that number to one


Note that if you do:
Quote:
if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;

You have 2 @something files, you will not have more if you make the test in the batch file (BAT or VBS).

However here's a trick to (awfully) do this in SQL*Plus.

set termout off
set pages 0
set feedb off
spool tmp.sql
select decode('&1','Something','@SomeOtherFile1.sql','@SomeOtherFile2.sql') from dual;
spool off
set termout on
set feedback on
set pages ...
@tmp

Regards
Michel
Re: Conditionals in a SQLPlus batch file [message #569306 is a reply to message #569277] Wed, 24 October 2012 10:18 Go to previous messageGo to next message
c2walter
Messages: 5
Registered: October 2012
Junior Member
Merci Michel.

Please note that my example of the "If..then" was for illustration purposes only.

It is the batch files that I want to reduce from many to one not the SQL script files. Therefore, there would be only one DOS batch file which would craft the call to SqlPlus with the proper designation for server and user and one SQLPlus batch file which would then process any number of SQL script files depending on the environment it finds itself in. I have one situation where there are approximately 20 script files but some target deployments call for only 10 of those to be run while other targets call for a varied amount between those 10 and the full 20. A conditional within the SQLPlus batch file would allow me to craft one batch file rather than one each for each target environment.

Thank you for the assist.

Follow up: The "trick" does not work as required. The net effect of the trick is to display the selected file name. It does not cause the file to be processed by SQL Plus.

SQL> SELECT DECODE ('&1',
2 'Something', '@..\db\SomeOtherFile1.sql',
3 '@..\db\DM_SomeOtherFile2.sql')
4 FROM DUAL;
old 1: SELECT DECODE ('&&1',
new 1: SELECT DECODE ('VCPDSORCDEV',

DECODE('Something','Something')
--------------------------------
@..\db\SomeOtherFile1.sql

[Updated on: Wed, 24 October 2012 10:37]

Report message to a moderator

Re: Conditionals in a SQLPlus batch file [message #569307 is a reply to message #569306] Wed, 24 October 2012 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are a couple of others parameters you have to set (and are set in my default environment, so I forgot them):
set verify off
set echo off
set timing off

And if you set "set pages 0" as I said you should not have the header lines you showed.
And note that this script should be in a... script file as must NOT be executed interactively.

By the way, Please How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: Conditionals in a SQLPlus batch file [message #569312 is a reply to message #569307] Wed, 24 October 2012 11:39 Go to previous messageGo to next message
ThomasG
Messages: 3091
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you go for a temporary tmp.sql file anyway, it *might* be more readable to create the tmp.sql in the batch file. For example:

@echo off
set env_var=%1

echo set lines 9000             > tmp.sql
echo set pages 0                >>tmp.sql

if %env_var% EQU 1 (            
 echo select 1 from dual;       >>tmp.sql
)

if %env_var% EQU 2 (
 echo select 2 from dual;       >>tmp.sql
)

if %env_var% EQU 3 (
  echo select 1 from dual;      >>tmp.sql
  echo select 2 from dual;      >>tmp.sql
)

echo exit; >>tmp.sql

sqlplus -s user/password@db @tmp.sql


U:\>test.cmd 1
         1

U:\>test.cmd 2
         2

U:\>test.cmd 3
         1
         2

U:\>


(put the @SomeOtherFile.sql calls instead of the selects from dual)
That way there would only be the "SQL script files" and the "DOS batch file" to maintain, not the additional "SQLPlus batch file"

[Updated on: Wed, 24 October 2012 11:40]

Report message to a moderator

Re: Conditionals in a SQLPlus batch file [message #569315 is a reply to message #569307] Wed, 24 October 2012 12:01 Go to previous messageGo to next message
c2walter
Messages: 5
Registered: October 2012
Junior Member
Admonition accepted.

As I stated above, the call to the SQLPlus batch file is as follows:

sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]

batch_script.sql has the following content
SPOOL output_batch.lst

SELECT SYSDATE, TO_CHAR (SYSDATE, 'HH24:MI:SS') StartTime FROM DUAL;

CONNECT &&2/[password]@&&1;

set echo off
set verify off
set timing off
set termout off
set pages 0
set feedb off

SELECT   DECODE ('&&1',
                 'something', '@something1.sql',
                 '@something2.sql')
  FROM   DUAL;

set feedb on
set pages 1
set termout on
set timing on
set verify on
set echo on

SELECT SYSDATE, TO_CHAR (SYSDATE, 'HH24:MI:SS') EndTime FROM DUAL;

EXIT


the output of that is as follows:

SYSDATE   STARTTIM                                                              
--------- --------                                                              
24-OCT-12 12:48:20                                                              

Connected.
@something1.sql                                                 
SQL> 
SQL> SELECT SYSDATE, TO_CHAR (SYSDATE, 'HH24:MI:SS') EndTime FROM DUAL;

24-OCT-12 12:48:20                                                              

1 row selected.

Elapsed: 00:00:00.00
SQL> 
SQL> EXIT


The script that is something1.sql is not executed. From the strength of your statement, I presume that I am doing something wrong or simply misunderstanding. For that, I will blame my misspent youth Smile.

Please know that your help, and your patience, is appreciated.
Re: Conditionals in a SQLPlus batch file [message #569316 is a reply to message #569315] Wed, 24 October 2012 12:17 Go to previous messageGo to next message
ThomasG
Messages: 3091
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The thing you are missing is, that the batch_script.sql doesn't run anything by itself at the moment.

It could just create a temporary *.sql file based on the result of the decode, that you THEN have to run in a second step.

[Updated on: Wed, 24 October 2012 12:18]

Report message to a moderator

Re: Conditionals in a SQLPlus batch file [message #569317 is a reply to message #569315] Wed, 24 October 2012 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed some part of what I posted:
spool tmp.sql
...
spool off
@tmp

There were not there just for your eyes but also to be executed.

Regards
Michel
Re: Conditionals in a SQLPlus batch file [message #569330 is a reply to message #569317] Wed, 24 October 2012 15:24 Go to previous messageGo to next message
c2walter
Messages: 5
Registered: October 2012
Junior Member
Yes I did miss those. However, My eyes elided over that because I was already spooling to a file but missed that you where dynamically creating a script file and then executing it. Please understand that what I am coding and the sample I provided has to be different for propriety reasons. Also, while I realize it may be an imposition, a quick couple of words would have gone a long way for clarity.

I added in the elements that I missed and it works as expected. Again, my thanks and, sarcasm aside, my appreciation.

[Updated on: Wed, 24 October 2012 15:25]

Report message to a moderator

Re: Conditionals in a SQLPlus batch file [message #569339 is a reply to message #569330] Wed, 24 October 2012 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was just a joke, take it as it, no offense intended.

Regards
Michel
Re: Conditionals in a SQLPlus batch file [message #570453 is a reply to message #569316] Fri, 09 November 2012 15:03 Go to previous message
LKBrwn_DBA
Messages: 427
Registered: July 2003
Location: WPB, FL
Senior Member
There is no need to create "temporary" file, just do this:
SPOOL output_batch.lst

SELECT SYSDATE, TO_CHAR ( SYSDATE, 'HH24:MI:SS') starttime FROM DUAL;

CONNECT &&2/[password]@&&1;

COL SQLSCRIPT NEW_VALUE sqlscript
BREAK ON REPORT

SET ECHO OFF VERIFY OFF TIMING OFF TERMOUT OFF PAGES 0 FEEDB OFF

SELECT DECODE ('&&1', 'something', 'something1.sql', 'something2.sql')
         sqlscript
  FROM DUAL;
SET FEEDB ON PAGES 1 TERMOUT ON TIMING ON VERIFY ON ECHO ON

@@&&SQLSCRIPT

SELECT SYSDATE, TO_CHAR ( SYSDATE, 'HH24:MI:SS') endtime FROM DUAL;

EXIT

[Updated on: Sat, 10 November 2012 11:07] by Moderator

Report message to a moderator

Previous Topic: SQLPLUS - then /else run script.sql
Next Topic: Error when trying to connect Sybase through Sql developer:
Goto Forum:
  


Current Time: Fri Aug 01 08:17:44 CDT 2014

Total time taken to generate the page: 0.11401 seconds