I am working on code which runs sql scripts with SqlPlus. Most of the scripts have DDL in them, which requires a / (forward slash) to run the buffer. Some of the scripts are missing the slash. I have tried running the script like @script.sql and then /, but it sometimes tries to create something twice. I want to run the show errors command at the end as well, and sometimes that caused trouble for me if the / was missing.
So, I changed to @script . (period) show errors. This works when the / is present in the file, but now if the / is missing in the file, if messes up by not creating the object.
We tried to have some logic to check for ends with / in the file, but that is a messy option in my opinion. I had false positives, and it isn't a perfect solution.
I thought about trying to run with another tool, but since the company I work for has released this already, and customers expect it to run with sqlplus, and may include commands that only work in sqlplus; that are not JDBC compliant, I don't think that is an adequate solution.
I am thinking now that if I could check if / had been run on the current buffer and do / only if it had not, that would be a great solution, perhaps perfect, but I can't find any documentation that such a command exists. Is there another way to solve the problem? Does such a command exist? Thanks!
SQLPlus is pretty limited. There's no control mechanism to do an IF/THEN even if you determine whether the buffer had been sent.
One thing you can do is, before a script is run, you call
exec begin raise login_denied; end;
After the script, call
#show sqlcode
If that still shows 1017 (the error code for login_denied), then you can be pretty sure that the script didn't execute any SQL statement. You can use a user defined error code if you prefer, such as raise_application_error(-20123,'dummy');
Note that I included a # prefix in the show statement. That runs the command without affecting the contents of the buffer so if you find nothing has been executed, you could try running the buffer contents with the slash.
If you have a single script that contains multiple DDLs (eg a table plus indexes/grants/constraints...) then you still run the risk of not running a final command if it didn't have the slash.