Home » Other » Client Tools » SQL*Plus not executing scripts, goes into an editor mode(!?) instead (SQL*Plus 11.2.0.1.0 OraClient 11g)
SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634900] Tue, 17 March 2015 17:39 Go to next message
medialint
Messages: 4
Registered: March 2015
Location: Oakland, CA
Junior Member
I broke SQLPlus and I don't know how. (SQL*Plus 11.2.0.1.0 OraClient 11g)

Behavior: whenever I try to execute a script like
SQL> @h:\test.sql

Instead of running the script I get an editor of some sort displaying a line number that starts with what would be the next line in the script file, so if the script is 10 lines long it would display like:
SQL> @h:\test.sql
11 _ <- cursor

nothing runs, and it doesn't display the contents of the file, just drops into that mode. I can type, nothing seems to happen though and it does not write to the file. The only thing that works to get out of it is a single period .

It gets weirder:
Happens also on my VDI virtual machine which has it's own client install
Happens no matter what database I log into, or username I use to log in
Same behavior using TOAD Editor -> Execute SQL via SQL*Plus command
Same behavior using AQT Run -> Run Using SQL*Plus
Same behavior whether using @ or start

I Rebooted, looked at settings, searched for files modified today that could be holding this setting, including on the "my documents" which would seem about the only place the two separate machines would see the same config

It was working and I cannot explain what happened. I may have screwed up a spool command somewhere around the time the behavior went haywire but I have tried all the obvious spool off, etc.

If anyone can solve this I'll buy you a beer. Or ice cream. Or beer flavored ice cream.

Cheers
Tim

Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634904 is a reply to message #634900] Tue, 17 March 2015 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post content of @h:\test.sql & make sure that the last line in the file is just "EXIT"

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634906 is a reply to message #634904] Tue, 17 March 2015 18:56 Go to previous messageGo to next message
medialint
Messages: 4
Registered: March 2015
Location: Oakland, CA
Junior Member
It doesn't matter much what script I try to run the behavior remains the same, including when executing in SQL+ from TOAD and AQT. It started on a different script I created test.sql just to keep it simple and that was:

DECLARE sName varchar2(255);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'HOST') INTO sName FROM DUAL;
DBMS_OUTPUT.PUT_LINE('HOST = ' || sName);
END;
EXIT 


I added EXIT at the end but it has no effect as it never even attempts to execute anything, it just goes into that weird editor mode.
Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634907 is a reply to message #634906] Tue, 17 March 2015 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL language is different from PL/SQL.
You posted anonymous PL/SQL procedure
SQL> !cat b.sql
DECLARE sName varchar2(255);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'HOST') INTO sName FROM DUAL;
DBMS_OUTPUT.PUT_LINE('HOST = ' || sName);
END;
/

SQL> @b.sql
HOST = localhost.localdomain

PL/SQL procedure successfully completed.

Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634909 is a reply to message #634907] Tue, 17 March 2015 21:34 Go to previous messageGo to next message
medialint
Messages: 4
Registered: March 2015
Location: Oakland, CA
Junior Member
My problem is not the procedure it's getting SQL+ to execute it and not show this(here with a 310 line create procedure script you'd think it would at least throw an error):

/forum/fa/12554/0/
Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634910 is a reply to message #634909] Tue, 17 March 2015 21:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
at that point try entering a single slash character ("/") as shown in my example above.

posting the actual last few lines in the file might provide needed additional clues.

prior to invoking any file, do as below

SQL> SET TERM ON ECHO ON

[Updated on: Tue, 17 March 2015 21:53]

Report message to a moderator

Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634919 is a reply to message #634910] Wed, 18 March 2015 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
BlackSwan
]
at that point try entering a single slash character ("/") as shown in my example above.


+1
Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634921 is a reply to message #634906] Wed, 18 March 2015 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I added EXIT at the end but it has no effect as it never even attempts to execute anything, it just goes into that weird editor mode.


A PL/SQL block ends with a '/' alone on a line.

PL/SQL User's Guide and Reference

Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634982 is a reply to message #634921] Wed, 18 March 2015 10:20 Go to previous messageGo to next message
medialint
Messages: 4
Registered: March 2015
Location: Oakland, CA
Junior Member
Thanks guys I found the errant device between the keyboard and chair and rebooted it.

/resolved
Re: SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634983 is a reply to message #634982] Wed, 18 March 2015 10:23 Go to previous message
gazzag
Messages: 1114
Registered: November 2010
Location: Bedwas, UK
Senior Member
Thanks for the feedback Laughing
Previous Topic: how to paste huge code in SQLPLUS
Next Topic: How to access database with Toad , No Oracle Software Installed
Goto Forum:
  


Current Time: Mon Mar 18 23:12:54 CDT 2024