1

i have a plsql script, which is not properly terminated:

create or replace package mypackage 
  ... (no semicolon/forward slash at the end)

the script is executed with sqlplus (12.1) on windows powershell:

sqlplus user/pass@host @my_not_properly_ended_file.pks

i would expect sqlplus to terminate with exit code 1 and an error message, but instead it prompts for input.

how can i get an error message and exit code in this situation?

edit: solution should also work with dml statements that are not terminated with a semicolon.

  • Use a shell script to properly log everything. Have a look at this Q&A https://stackoverflow.com/questions/14734131/managing-error-handling-while-running-sqlplus-from-shell-scripts – Thomas G May 15 '18 at 15:17
  • question is not about how to log the output, but about how to force sqlplus to terminate with an error message when running a script with missing semicolon or forward slash at the end –  May 15 '18 at 15:24
  • You might try adding set define off to the top of your script. You might have a & in your script and it thinks you have a variable that needs user input. – Bobby Durrett May 15 '18 at 15:25
  • not getting stuck in the sqlplus session kind of works with set define off, but it does not return an error message –  May 15 '18 at 15:29
  • there is no error, but sqlplus can't proceed on your anon block or create or replace proc until you supply a / – thatjeffsmith May 15 '18 at 15:40
  • otherwise you can tell sqlplus to bork out on errors using the whenever error exit clause – thatjeffsmith May 15 '18 at 15:40

1 Answers1

2

You can use shell redirection instead of @:

sqlplus user/pass@host < my_not_properly_ended_file.pks

This will also prevent it getting 'stuck' if the script doesn't end with an exit command.

However, it won't return an error code to the shell in either case. As far as SQL*Plus is concerned you put the incomplete statement into its buffer but never attempted to execute it (as there was no slash); and as it didn't run, it didn't error. So setting whenever sqlerror or whatever won't make any difference either.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318