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

Question

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

Nick.Mc · Accepted Answer · 2013-05-30 02:14:05Z

2

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"

answered May 30, 2013 at 2:14

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related