OraFAQ Forum: Client Tools » SQL*Plus not executing scripts, goes into an editor mode(!?) instead

Home » Other » Client Tools » SQL*Plus not executing scripts, goes into an editor mode(!?) instead (SQL*Plus 11.2.0.1.0 OraClient 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Plus not executing scripts, goes into an editor mode(!?) instead [message #634900]

Tue, 17 March 2015 17:39

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

Report message to a moderator

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

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/

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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/

Attachment: sqlplusissue.jpg
(Size: 31.47KB, Downloaded 2967 times)

Report message to a moderator

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

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Thanks for the feedback Laughing

Report message to a moderator

Previous Topic:	how to paste huge code in SQLPLUS
Next Topic:	How to access database with Toad , No Oracle Software Installed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 23:50:04 CST 2024