OraFAQ Forum: Client Tools » set scan off error

Home » Other » Client Tools » set scan off error

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

set scan off error [message #246496]

Thu, 21 June 2007 02:03

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

Report message to a moderator

Re: set scan off error [message #246498 is a reply to message #246496]

Thu, 21 June 2007 02:12

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

SET SCAN OFF is a SQL*Plus command, I don't think TOAD recognises it.

Report message to a moderator

Re: set scan off error [message #246501 is a reply to message #246498]

Thu, 21 June 2007 02:27

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..

Report message to a moderator

Re: set scan off error [message #246504 is a reply to message #246496]

Thu, 21 June 2007 03:01

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

Yes, it ignored the first statement and processed the second one.

Report message to a moderator

Re: set scan off error [message #246505 is a reply to message #246501]

Thu, 21 June 2007 03:07

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

Report message to a moderator

Re: set scan off error [message #246508 is a reply to message #246496]

Thu, 21 June 2007 03:17

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.

Report message to a moderator

Re: set scan off error [message #246514 is a reply to message #246505]

Thu, 21 June 2007 03:23

Maaher
Messages: 7065
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