CODEWITHSUNDEEP
oracle-databasedossqlplusbatch-file
Marcus Leon
Marcus Leon

Reputation: 56699

Including pipe running Oracle sql from DOS batch script

We use the approach below to run SQL from a DOS batch script.

The model works fine but this specific code doesn't work. I believe because of the || characters. I tried using ^|^| but this didn't work.

Any ideas?

(
echo update mytable set file_path = 'C' || substr(file_path, 2);
echo commit;
echo exit
) | sqlplus x/x@orcl

Upvotes: 1

Views: 2022

Answers (3)

René Nyffenegger
René Nyffenegger

Reputation: 40533

Escaping the || with ^|^| leaves you with yet another problem: cmd.exe thinks that the closing parenthesis of substr(file_path, 2); belongs to the opening parenthesis in the first line. It is therefore not printed to SQL*Plus, thus rendering the update statement to something like update mytable set file_path = 'C' || substr(file_path, 2 which obviously cannot be interpreted by Oracle.

You can solve this if you put the entire update statement into double quotes and feed this to (yet another) cmd.exe, like so:

(
@echo select * from mytable;
@cmd /c "echo update mytable set file_path = 'C' ^|^| substr (file_path, 2);"
@echo commit;
@echo exit
) | sqlplus x/x@orcl

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

You can use CONCAT instead of the || operator

Upvotes: 1

Seva Alekseyev
Seva Alekseyev

Reputation: 61378

Store the SQL as file and redirect SQL Plus's input:

sqlplus x/x@orcl <sql.txt

Upvotes: 1

Related Questions