CODEWITHSUNDEEP
oracle-databasebatch-filesqlplus
mahacoder
mahacoder

Reputation: 913

Single command from batch file with sqlplus not working in some cases

I have the following command in a batch file.

set tableName=%1
select count(1) from %tableName% where to_char(DATEVALUE,'yyyy-mm-dd hh24:mi:ss')^>(select to_char(max(DATEVALUE),'yyyy-mm-dd hh24:mi:ss') from FOO_TABLE); | sqlplus !connectionString!

This statement doesn't work. I can see that it connects to the database and then disconnects. But the following works:

select count(1) from %tableName% where to_char(DATEVALUE,'yyyy-mm-dd hh24:mi:ss')=(select to_char(max(DATEVALUE),'yyyy-mm-dd hh24:mi:ss') from FOO_TABLE); | sqlplus !connectionString!

I am guessing the problem could be with the greater than > symbol. I tried ^>,> and \>. None of them works. How can I get this sql statement to work.

(I have connectionString already set in my batch file in earlier lines).

The output in the command line is

Connected to:
Oracle Database ... (more db info)

SQL> Disconnected from Oracle Database ... (more db info)

Upvotes: 0

Views: 434

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

It looks like you need to escape the ^ escape character as well; depending on exactly how you're running this, either:

... where to_char(DATEVALUE,'yyyy-mm-dd hh24:mi:ss')^^>(select ...

or

... where to_char(DATEVALUE,'yyyy-mm-dd hh24:mi:ss')^^^>(select ...

In a batch file where the query is echoed and piped the triple-escape works:

@setlocal EnableDelayedExpansion
@set connectionString=x/y@z
@set tableName=bar
@echo select count(1) from %tableName% where to_char(DATEVALUE,'yyyy-mm-dd hh24:mi:ss')^^^>(select to_char(max(DATEVALUE),'yyyy-mm-dd hh24:mi:ss') from FOO_TABLE); | sqlplus !connectionString!

Running that batch script shows the statement being run (and erroring in my case with ORA-00942, which is expected). With a single or double ^ it has nothing to run at the SQL prompt and a file is created instead, which seems to be what you're seeing.

Upvotes: 1

Related Questions