Toad World® Forums

Issue with SQLPLUS commands in TOAD Editor


#1

I’m currently using TOAD for Oracle 9.7.2 (I know, that’s what they gave me) and I have an issue with the TOAD editor. I develop scripts that ultimately will be executed (by the DBA team) in SQLPLUS, so I need to include certain SQLPLUS commands in the script. Most commands I don’t have a problem with, but SET SQLBLANKLINES ON seems to mess up the TOAD editor. The script runs in SQLPLUS, but if I try executing it in the TOAD editor (either with F5 or placing my cursor on the MERGE statement and using F9), I get an error message, in this case:

merge into test t

Error at line 12

ORA-02012: missing USING keyword

Here’s a test script:

SET SQLBLANKLINES ON

prompt testing 1, 2, 3…

DROP TABLE test;

CREATE TABLE test (f1 NUMBER, f2 DATE)

/

ALTER TABLE test ADD (f3 varchar2(3));

merge into test t

using (select 1 as t1,trunc(sysdate) as t2,‘xyz’ as t3 from dual) z

on (z.t1=t.f1)

when matched then

update set t.f2=z.t2, t.f3=z.t3

when not matched then

insert (f1,f2,f3) values (t1,t2,t3);

If I place my cursor on the MERGE statement and hit F9, it fails with the above error. If I comment out the SET SQLBLANKLINES ON line, it works.

I’ve tried it with a number of other SQLPLUS commands and none of them make it fail, just SQLBLANKLINES.

By the way, if I move the USING clause to the end of the previous line, it gives me the error that the “ON” keyword is missing.

I’m using Oracle 11.2 with the Oracle 11g client on a windows 7 pro machine. One other thing: I can’t get it to happen with this test script, but when I open up the actual problem script in the TOAD editor, it displays a “syntax check” error just opening it.

Any ideas what could be causing this and how to fix it?

TIA…Tom


#2

It very well could be a bug in that version of Toad. We’re currently developing Toad 12 so 9.7 is quite a few versions out of date and when I ran your script, I have no issues in the current GA (11.6.1) or the current beta (12.0.0.19) so most likely the bug has been fixed in one of the previous versions.

As far as a workaround, you can just execute your scripts via SQLPlus directly from the editor. The “Execute as Script” button is a drop down button with two additional options - Execute via QSR and Execute via SQLPlus. If you select SQLPlus, the contents of the editor are sent to a new SQLPlus window.

Hope that helps.

Greg