Home » SQL & PL/SQL » Client Tools » set scan off error
set scan off error [message #246496] Thu, 21 June 2007 02:03 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I am just doing R&d. I am trying to insert & character in table.

SET SCAN OFF;
insert into tmp1 values ('Yash''&Thakur') 


When i am selecting both sql statements and execute both command as 'excute as a script' button on toad, record got insert in table. When i am executing both command one by one i am getting error in first command in toad

ORA-00922: missing or invalid option 


plz suggest me why i am getting error.

--Yash
Re: set scan off error [message #246498 is a reply to message #246496] Thu, 21 June 2007 02:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SET SCAN OFF is a SQL*Plus command, I don't think TOAD recognises it.
Re: set scan off error [message #246501 is a reply to message #246498] Thu, 21 June 2007 02:27 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
but when i am excuting both statment in one shot record got inserted in toad only..
Re: set scan off error [message #246504 is a reply to message #246496] Thu, 21 June 2007 03:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Yes, it ignored the first statement and processed the second one.
Re: set scan off error [message #246505 is a reply to message #246501] Thu, 21 June 2007 03:07 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Yes, it ignored the first statement and processed the second one.


If it is ignore the first statement then second statement should ask for substitute variable value due to & character..but it is not asking for the same ..


--Yash
Re: set scan off error [message #246508 is a reply to message #246496] Thu, 21 June 2007 03:17 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
"Set scan off" appears to work in my version (8.6.1), but you may have a different version with different functionality. Have a look in the TOAD options and see if you can switch off the variable substitution there.
Re: set scan off error [message #246514 is a reply to message #246505] Thu, 21 June 2007 03:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
TOAD can process statements as a SQL script. If you choose that execution method, it knows the SQL SET commands (not all of them if I remember correctly, but you got the TOAD help for details).

But TOAD can also process single statements. Then it won't recognize SQL*Plus commands. You can simulate this in SQL plus by using EXECUTE IMMEDIATE:
SQL> begin
  2    execute immediate 'set scan off';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 2


My conclusion is: if it is a script, execute it as a script. Wink

MHE
Re: set scan off error [message #246520 is a reply to message #246514] Thu, 21 June 2007 03:49 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Maaher!! You are my last technical word in oracle Cool
So what ever you are saying ..i will do the same
Re: set scan off error [message #247687 is a reply to message #246498] Tue, 26 June 2007 18:03 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
rleishman wrote on Thu, 21 June 2007 08:12
SET SCAN OFF is a SQL*Plus command, I don't think TOAD recognises it.

A deprecated SQL*Plus command at that Wink
(The linked document is dated 1996.)
Previous Topic: execute a procedure returning ref cursor in toad
Next Topic: SQL Plus, suppress these lines
Goto Forum:
  


Current Time: Sat Dec 10 07:01:57 CST 2016

Total time taken to generate the page: 0.05838 seconds