CODEWITHSUNDEEP
oracle-databasedossqlplusbind-variables
Nick.Mc
Nick.Mc

Reputation: 19184

Use a database value in SQLPLUS as a parameter to a batch file

I need to run a batch file and pass in a database value. Depending on the day, a different value needs to be passed into the DOS command. This particular business rule is defined in a view in the database.

Our primary scheduling tool is datastage which runs scripts in SQLPLUS, so this is my preferred method for schedules activities. I could also run a batch file directly taht calls SQLPLUS and gets a value, but there is much more hassle setting it up so I'd prefer not to.

I'm a SQLPLUS amateur.

The SQL script I'm passing into SQLPLUS is below. The problem I'm having is it's passing in :sTriggerName, not the value of it.

set echo on
set feedback on
set termout on


VAR sTriggerName VARCHAR2(100)

SELECT TRIGGERNAME INTO :sTriggerName 
FROM SCHEMA.VIEW 
WHERE CALENDAR_DATE = TRUNC(SYSDATE) AND ROWNUM < 2;
/

HOST "E:\CallScheduleTrigger.CMD :sTriggerName."

quit

Upvotes: 0

Views: 1515

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

In the example above I am using a bind variable.

This link showed me hwo to load a substitution variable from the database and use that instead:

http://www.oracle.com/technetwork/testcontent/sub-var9-086145.html

To load a a database value into a substitution variable called sTriggerName

COLUMN TRIGGERNAME new_value sTriggerName
SELECT TRIGGERNAME FROM SCHEMA.VIEW WHERE CALENDAR_DATE = TRUNC(SYSDATE) AND ROWNUM < 2;

To use this substitution variable in a host command (i.e. as a parameter to a batch file):

HOST "E:\CallScheduleTrigger.CMD &sTriggerName"

Upvotes: 2

Related Questions