DOS/sqlplus "Handle is invalid" error?

Question

I have the following batch script:

sqlplus ms/ms@orcl < drop.sql
sqlplus ms/ms@orcl < create.1.0.sql

This works fine when I double click on the bat file in Windows Explorer and run it.

But when I type the command name from the DOS prompt I get an error:

C:\>create.bat

C:\>sqlplus ms/ms@orcl  0<drop.sql
The handle is invalid.

C:\>sqlplus ms/ms@orcl  0<create.1.0.sql
The handle is invalid

Any ideas?

---

Update: Made the change to use @ instead of <. This gets around the error but now the script only executes the first file and then leaves you at the SQL> prompt. To get the second file to execute you have to type exit at the prompt, then the second file runs. Not sure how to get both files to execute. ??

Answer 1

If you want SQL*PLUS to execute a script, a better way than command-line redirection is the @ syntax:

sqlplus ms/ms@orcl @drop.sql
sqlplus ms/ms@orcl @create.1.0.sql

Also read up on the @@ syntax, which you can use to execute a .sql script from another .sql script in the same directory.

SQL> help @
SQL> help @@

Answer 2

Made the change to use @ instead of <. This gets around the error but now the script only executes the first file and then leaves you at the SQL> prompt. To get the second file to execute you have to type exit at the prompt, then the second file runs. Not sure how to get both files to execute. ??

I ended up writing a python script to run sql plus and then exit it to get around this issue. I have yet to find any way around that limitation. Although you would only have to deal with the issue once if you use @@ as was suggested. You would just have to run the second script from the first script.