Home » Infrastructure » Windows » Set Define OFF for Ampersands: Missing or Invalid Option
Set Define OFF for Ampersands: Missing or Invalid Option [message #144851] Fri, 28 October 2005 10:42 Go to next message
wachaca
Messages: 17
Registered: October 2005
Junior Member
Hello,
The followin code works fine in SQL*Plus but I cannot get it work under .Net

set define OFF
 declare  
num int;
 begin
     select  count(*) into num from lookup_attributes  where id_attribute = 696922;
     if num = 0 then
         insert into lookup_attributes values(696922,1,'view_entity-10001.asp?proceso_entity=0&Action=edit&type_entity=2052',1000) ; 
     end if;
 end;


When I execute it under .Net I get the error Msg
ORA-00922: missing or invalid option

I get this same error when I try to execute the code in a "SQL Widow" in PL/SQL Developer.

An alternative would be to replace & with somethig else when I insert and remember to replace it back when I do a select...

Thanks for your help

Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144856 is a reply to message #144851] Fri, 28 October 2005 10:56 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
SET DEFINE OFF is an SQL*Plus command. Loose it.

MHE
Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144857 is a reply to message #144856] Fri, 28 October 2005 10:59 Go to previous messageGo to next message
wachaca
Messages: 17
Registered: October 2005
Junior Member

So how do I get around the fact that I have ampersands (&) in my insert command?
If I lose "SET DEFINE OFF" then I get prompted for the values of "Action" and "type_entity"
Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144860 is a reply to message #144857] Fri, 28 October 2005 11:05 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
In .NET too?

MHE
Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144861 is a reply to message #144860] Fri, 28 October 2005 11:07 Go to previous messageGo to next message
wachaca
Messages: 17
Registered: October 2005
Junior Member

in .Net I get the same error with or without out the "Set Define" statement.
Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144866 is a reply to message #144861] Fri, 28 October 2005 11:21 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
First things first: SET is, as said, an SQL*Plus command. It's also SQL*Plus that prompts for the values. SQL*Plus is just a command line interpreter. But you can find that out yourself if you look into the SQL*Plus User's Guide and Reference. It's available online.

Second of all: you are running an anonymous block in .NET, how are you doing that?

BTW: I'm moving this thread to "Windows and DOS", perhaps someone with some .NET experience can help you out further. I believe it's the fact that you are running an anonymous block that's causing the problem, but I'm no .NET specialist...

MHE

[Updated on: Fri, 28 October 2005 11:24]

Report message to a moderator

Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144868 is a reply to message #144866] Fri, 28 October 2005 11:31 Go to previous messageGo to next message
wachaca
Messages: 17
Registered: October 2005
Junior Member

Thanks for re-orientating me to the proper group.
I'm not sure what you refer to as an "anonymous block"
This is the code in .Net if it helps

Connection.ConBdOracle Conn = new Connection.ConBdOracle();
OracleConnection ConBdOracle;
ConBdOracle = Conn.ConnOpen();

OracleCommand myCommand = new OracleCommand(myScalarQuery);
myCommand.Connection=ConBdOracle;
try
{
	myCommand.ExecuteNonScalar();
}
catch(Exception ex)
{
	string mes = ex.Message;
}

Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144870 is a reply to message #144868] Fri, 28 October 2005 11:55 Go to previous messageGo to next message
wachaca
Messages: 17
Registered: October 2005
Junior Member

Something interesting...
If I just execute the insert command without the begin/end block, it works OK. I just let it fall into the catch{} when it already exists but at least it inserts.
Re: Set Define OFF for Ampersands: Missing or Invalid Option [message #144873 is a reply to message #144870] Fri, 28 October 2005 12:27 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
That was what I was expecting. .NET allows a SQL statement to pass, but a PL/SQL block (DECLARE..BEGIN..END) is a bridge too far. I called it an anonymous block because the block has no name, where a stored procedure/function/package has a name.

You were in fact trying to execute a SQL script.

Glad you found it. Perhaps anyone else here (with some .NET background) can shine his light on this.

MHE
Previous Topic: Error "Invalid precision for decimal data type"
Next Topic: ORACLE not available
Goto Forum:
  


Current Time: Fri Nov 22 02:18:30 CST 2024