SPL*Plus and the Enter key 1

[jessicatx]

Hope that someone can provide an answer to a puzzle that I have. One of my co-workers can get into SQL*Plus and use the enter key with no impact. She merely goes on to the next line. If I do that, it interprets that I have code all ready to go - and of course I get error messages when it tries to execute right away.

Can someone point me in the right direction? Should I change one of the Environment options or what?

Thanks!

 

[SantaMufasa]

Jessica,

When you are in SQL*Plus, there are three sets of commands that that environment can process:

1) SQL*Plus commands,
2) PL/SQL blocks, and
2) SQL commands

In SQL*Plus, when you issue a SQL command, such as SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, RENAME, GRANT, REVOKE, et cetera, SQL*Plus will not execute such a command until you enter either a semi-colon (";") as the last character on a command line, or enter a forward slash ("/") on a line of its own. For example, you can enter the command:

SELECT
*
FROM
my_table
;

...and it will not exectute the command until it sees the semi-colon.

PL/SQL code blocks begin with either a "DECLARE" or a
"BEGIN" header, contain as many lines of PL/SQL code as your logic requires, and terminate with "END;" followed by a "/" on a line of its own. When SQL*Plus encounters the "/" character, it processes all lines of PL/SQL code in the SQL buffer.

SQL*Plus commands, however, bear absolutely no similarity or resemblance in form, structure, or syntax to SQL or PL/SQL commands. SQL*Plus commands execute upon your entry of an [Enter] (unless you use a line-continuation character, "-"). So, when I issue a SQL*Plus command such as:

COL x heading "Name" format a20
break on report
compute sum of salary on report
...et cetera

...each of the above SQL*Plus commands execute immediately upon sensing a carriage return.

Could this explain the anomalies that you are seeing?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jessicatx]

Dave -

We are both in SQL*plus. I ran into this while trying to type in a Spool command as the first statement. Hit enter, and away it ran. She did the same thing and was able to go on her way entering more code.

When I keyed in Select, I was able to (as you said would happen) continue coding until the ; was typed in.

When I key in the Spool command, I do not type in a ; because it does not require one. But the only way that I can execute a Spool command is to copy in some text that I have produced elsewhere. Kind of a pain, really, especially when the next cubicle-dweller can key at will.

Thanks for your quick response. Any other thoughts?

 

[SantaMufasa]

Jessica,

Can you please post a copy-and-paste example of the "offending" code as it appeared when "away it ran"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jessicatx]

I was just in the process of doing that when your last request came in. Here it is --

SQL> spool c:\spoolfiles\listtable.txt

press Enter and this pops up -
SP2-0332: Cannot create spool file.

I never even get to the Select statement.

 

[jessicatx]

Dave -

I debugged it and found out that I am a little too quick at the ol' keyboard (but not accurate - you saw the title on this problem "SP[/color red]L*Plus" - s/b SQL!). I mistyped the directory name and SQL*Plus must obviously look for the directory name right away, even before it begins spooling. So that's what happened -- and when I typed it in correctly, it let me continue.

Thank you - and I will give you a star for your support.

 

[Beilstwh]

Your right, a spool command open a file using the path and file name specified to capture the output. If the directory doesn't exist, then you get the error you saw. It doesn't wait to open the file until there is something to spool.

Bill
Oracle DBA/Developer
New York State, USA