Pass parameter value to sql file from batch file using batch command

Question

I have a sql file update_qde.sql which contains the following code

UPDATE profile_const
set parameter_value = '04_2015'
where profile_name = 'MIDAS_MTH_ALL_Panels'
and parameter_name = 'PERIODS_TO';
commit;

quit;

I have another batch file test.bat

I want to pass the parameter_value as a variable from a batch file using batch command.

Could you please show me any way to do this?

Answer 1

Use SQLCMD variables, so change test.bat to call SQLCMD rather than ISQL

@echo off
sqlcmd -E -S myserver -i update_qde.sql -v PARM1=2015-01-01

Change the SQL to use the $() notation

UPDATE profile_const
SET parameter_value = '$(PARM1)'
WHERE profile_name = 'MIDAS_MTH_ALL_Panels'
AND parameter_name = 'PERIODS_TO';
COMMIT;

Answer 2

Its pretty simple.

Create a batch file with the below 3 line of code. To create a batch file, create a new .txt file, save the below code inside the file and rename the extension of this file from .txt to .bat.

In the 2nd line below code value1 is the variable name which holds the value you need to update. Third line of code is where you will pass this variable value to the sql file.

@echo off    
SET value1=04_2015    
sqlplus db_username/db_password@db_schema @sample.sql %value1%

The code for sample.sql is given below. The &&1 in the below code accepts the parameter that you are sending from the batch file and will be stored in the variable param1. Later you can use this param1 variable inside the BEGIN block to be used for UPDATE query.

SET SERVEROUTPUT ON
SET DEFINE ON
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 200
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET term off 
SET verify off
set echo off
SPOOL "C:\Test\Logfile.log" append;

var param1 VARCHAR2(20);
BEGIN
:param1 := '&&1';   
END;
/

SET DEFINE OFF

BEGIN

    UPDATE profile_const
    set parameter_value = :param1
    where profile_name = 'MIDAS_MTH_ALL_Panels'
    and parameter_name = 'PERIODS_TO';
    commit;     

END;
/

quit;
/

SPOOL OFF;
SET DEFINE ON
SET SERVEROUTPUT OFF

I verified the above code, which works fine.