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