Why is SQLPlus executing commands from previous script multiple times when running following script?

Question

I am having an issue during the execution of my scripts.

It seems as if a command from the previous script execution is left in the memory and ran multiple times upon execution of my next script.

For example, I have 2 scripts named "DROP.SQL" and "CREATE.SQL" one being used to drop all tables, and the other to create tables, not including constraints which is a different file that I am not using at the moment. The problem I am having is this:

@CREATE.SQL - Tables created

@DROP.SQL - Script runs and drops tables showing success.

@CREATE.SQL - This is where the problem occurs. Once I try to run this script again, it acts as if there is a drop command left over in the memory that didn't get executed (my DROP.LOG says otherwise) and it attempts to execute it. In the screen shot below you can see that it has attempted to execute this command from the drop script 3 times.

I have also attempted running a drop command manually prior to running the CREATE.SQL script file which created the same result. This happens when I am running other scripts also. I attempted running a script to add constraints to the tables and had similar results.

Thanks for the heads up, I will remember that next time I make a post. — Morgan
– Morgan, Commented Feb 16, 2017 at 12:50

Alex Poole · Accepted Answer · 2017-02-16 12:51:04Z

2

This is because you don't have whitespace between the comment delimiters and the comment text. If you had a comment like:

/* Compile Terminal Output To log File */

... then SQL*Plus would recognise the /* as the start of a comment. But when you leave out the whitespace:

/*Compile Terminal Output To log File*/

it isn't recognising it as a comment, and treats the slash as a buffer-submission command (despite of, and ignoring, the rest of the characters after it). The last thing in the buffer, in this case, is the last drop table command from the previous script - so it does what it thinks you told it to and executes that buffer again, attempting to re-drop the table.

(Native SQL*Plus commands like run/@ and set don't go into the buffer, so it still contains the last SQL command, which was the drop table.)

From the first repeated drop, it looks like your create script has a similar comment with no whitespace before it issues set echo on, so the comment itself isn't shown on your screen - but the buffer is still executed by that too.

The SQL*Plus documentation does say:

You must enter a space after the slash-asterisk(/*) beginning a comment.

If you change all the comments to have that required space then they will no longer execute whatever happens to be left in the SQL buffer.

edited Feb 16, 2017 at 12:51

answered Feb 16, 2017 at 12:44

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Morgan Over a year ago

I actually remember reading that there needed to be a space between the /* and the comment information now that you mention it. I adjusted my SQL files to reflect this and it corrected the issue that I was having. Thank you for the help.

Collectives™ on Stack Overflow

Why is SQLPlus executing commands from previous script multiple times when running following script?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related