| Conditionals in a SQLPlus batch file [message #569253] |
Tue, 23 October 2012 12:56  |
 |
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 #569306 is a reply to message #569277] |
Wed, 24 October 2012 10:18  |
|
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 #569312 is a reply to message #569307] |
Wed, 24 October 2012 11:39 |
ThomasG
Messages: 3212
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 |
|
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  .
Please know that your help, and your patience, is appreciated.
|
|
|
|
|
|
|
|
| Re: Conditionals in a SQLPlus batch file [message #569330 is a reply to message #569317] |
Wed, 24 October 2012 15:24 |
|
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 #570453 is a reply to message #569316] |
Fri, 09 November 2012 15:03 |
 |
LKBrwn_DBA
Messages: 487
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
