|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Subprocedure for Oracle's SET DEFINE OFF commandin an Oracle database. I tried the following: Public Shared Sub SetDefineOff() Dim myconnection As New OracleConnection(ConfigurationSettings.AppSettings("connectionstring")) Dim cmdsetdefineoff As New OracleCommand("SET DEFINE OFF", myconnection) myconnection.Open() cmdsetdefineoff.ExecuteNonQuery() myconnection.Close() End Sub But I recieve the following error: Server Error in '/lvbeporgtest' Application. -------------------------------------------------------------------------------- ORA-00922: missing or invalid option Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OracleClient.OracleException: ORA-00922: missing or invalid option Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [OracleException: ORA-00922: missing or invalid option ] System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +174 System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1919 System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor) +170 System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 Global.SetDefineOff() in C:\Documents and Settings\Nathan Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\Global.asax.vb:32 newsadmin.btnSubmitNotesLink_Click(Object sender, EventArgs e) in C:\Documents and Settings\Nathan Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\newsadmin.aspx.vb:71 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300 Can someone tell me what I am doing wrong or what is causing the error and what I can do to fix it? Thanks. Hi Nathan,
I am no Oracle expert but I can tell you two things: 1. Your code is fine, you aren't doing anything wrong. 2. "SET DEFINE OFF" is not a SQL command, it is a SQL *Plus command. It just won't work Oracle has no idea what you mean. Tim Show quote "Nathan Sokalski" wrote: > I want to create a procedure in ASP.NET to run the "SET DEFINE OFF" command > in an Oracle database. I tried the following: > > Public Shared Sub SetDefineOff() > > Dim myconnection As New > OracleConnection(ConfigurationSettings.AppSettings("connectionstring")) > > Dim cmdsetdefineoff As New OracleCommand("SET DEFINE OFF", myconnection) > > myconnection.Open() > > cmdsetdefineoff.ExecuteNonQuery() > > myconnection.Close() > > End Sub > > > But I recieve the following error: > > Server Error in '/lvbeporgtest' Application. > -------------------------------------------------------------------------------- > > ORA-00922: missing or invalid option > Description: An unhandled exception occurred during the execution of the > current web request. Please review the stack trace for more information > about the error and where it originated in the code. > > Exception Details: System.Data.OracleClient.OracleException: ORA-00922: > missing or invalid option > > Source Error: > > An unhandled exception was generated during the execution of the > current web request. Information regarding the origin and location of the > exception can be identified using the exception stack trace below. > > Stack Trace: > > [OracleException: ORA-00922: missing or invalid option > ] > System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle > errorHandle, Int32 rc) +174 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& > rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1919 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 > System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean > needRowid, OciHandle& rowidDescriptor) +170 > System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 > Global.SetDefineOff() in C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\Global.asax.vb:32 > newsadmin.btnSubmitNotesLink_Click(Object sender, EventArgs e) in > C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\newsadmin.aspx.vb:71 > System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 > System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String > eventArgument) +57 > System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler > sourceControl, String eventArgument) +18 > System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 > System.Web.UI.Page.ProcessRequestMain() +1292 > > > > -------------------------------------------------------------------------------- > Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET > Version:1.1.4322.2300 > > > Can someone tell me what I am doing wrong or what is causing the error and > what I can do to fix it? Thanks. > -- > Nathan Sokalski > njsokal***@hotmail.com > http://www.nathansokalski.com/ > > > I would recommend that rather than making this a separate command he
include this line at the beginning of whatever stored procedure on Oracle that he wants to run. Sort of like in a SQL procedure where you start with SET ANSI OFF. timkling wrote: Show quote > Hi Nathan, > > I am no Oracle expert but I can tell you two things: > > 1. Your code is fine, you aren't doing anything wrong. > 2. "SET DEFINE OFF" is not a SQL command, it is a SQL *Plus command. It > just won't work Oracle has no idea what you mean. > > Tim > > "Nathan Sokalski" wrote: > > >>I want to create a procedure in ASP.NET to run the "SET DEFINE OFF" command >>in an Oracle database. I tried the following: >> >>Public Shared Sub SetDefineOff() >> >>Dim myconnection As New >>OracleConnection(ConfigurationSettings.AppSettings("connectionstring")) >> >>Dim cmdsetdefineoff As New OracleCommand("SET DEFINE OFF", myconnection) >> >>myconnection.Open() >> >>cmdsetdefineoff.ExecuteNonQuery() >> >>myconnection.Close() >> >>End Sub >> >> >>But I recieve the following error: >> >>Server Error in '/lvbeporgtest' Application. >>-------------------------------------------------------------------------------- >> >>ORA-00922: missing or invalid option >>Description: An unhandled exception occurred during the execution of the >>current web request. Please review the stack trace for more information >>about the error and where it originated in the code. >> >>Exception Details: System.Data.OracleClient.OracleException: ORA-00922: >>missing or invalid option >> >>Source Error: >> >> An unhandled exception was generated during the execution of the >>current web request. Information regarding the origin and location of the >>exception can be identified using the exception stack trace below. >> >>Stack Trace: >> >>[OracleException: ORA-00922: missing or invalid option >>] >> System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle >>errorHandle, Int32 rc) +174 >> System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, >>CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& >>rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1919 >> System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, >>CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 >> System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean >>needRowid, OciHandle& rowidDescriptor) +170 >> System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 >> Global.SetDefineOff() in C:\Documents and Settings\Nathan >>Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\Global.asax.vb:32 >> newsadmin.btnSubmitNotesLink_Click(Object sender, EventArgs e) in >>C:\Documents and Settings\Nathan >>Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\newsadmin.aspx.vb:71 >> System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 >> System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String >>eventArgument) +57 >> System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler >>sourceControl, String eventArgument) +18 >> System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 >> System.Web.UI.Page.ProcessRequestMain() +1292 >> >> >> >>-------------------------------------------------------------------------------- >>Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET >>Version:1.1.4322.2300 >> >> >>Can someone tell me what I am doing wrong or what is causing the error and >>what I can do to fix it? Thanks. >>-- >>Nathan Sokalski >>njsokal***@hotmail.com >>http://www.nathansokalski.com/ >> >> >> You might need to wrap the SET DEFINE OFF in a BEGIN/END block:
BEGIN SET DEFINE OFF; END; I've not used Oracle for a couple of years so the syntax might not be correct. Show quote "Nathan Sokalski" wrote: > I want to create a procedure in ASP.NET to run the "SET DEFINE OFF" command > in an Oracle database. I tried the following: > > Public Shared Sub SetDefineOff() > > Dim myconnection As New > OracleConnection(ConfigurationSettings.AppSettings("connectionstring")) > > Dim cmdsetdefineoff As New OracleCommand("SET DEFINE OFF", myconnection) > > myconnection.Open() > > cmdsetdefineoff.ExecuteNonQuery() > > myconnection.Close() > > End Sub > > > But I recieve the following error: > > Server Error in '/lvbeporgtest' Application. > -------------------------------------------------------------------------------- > > ORA-00922: missing or invalid option > Description: An unhandled exception occurred during the execution of the > current web request. Please review the stack trace for more information > about the error and where it originated in the code. > > Exception Details: System.Data.OracleClient.OracleException: ORA-00922: > missing or invalid option > > Source Error: > > An unhandled exception was generated during the execution of the > current web request. Information regarding the origin and location of the > exception can be identified using the exception stack trace below. > > Stack Trace: > > [OracleException: ORA-00922: missing or invalid option > ] > System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle > errorHandle, Int32 rc) +174 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& > rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1919 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 > System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean > needRowid, OciHandle& rowidDescriptor) +170 > System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 > Global.SetDefineOff() in C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\Global.asax.vb:32 > newsadmin.btnSubmitNotesLink_Click(Object sender, EventArgs e) in > C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\newsadmin.aspx.vb:71 > System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 > System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String > eventArgument) +57 > System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler > sourceControl, String eventArgument) +18 > System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 > System.Web.UI.Page.ProcessRequestMain() +1292 > > > > -------------------------------------------------------------------------------- > Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET > Version:1.1.4322.2300 > > > Can someone tell me what I am doing wrong or what is causing the error and > what I can do to fix it? Thanks. > -- > Nathan Sokalski > njsokal***@hotmail.com > http://www.nathansokalski.com/ > > > timkling is correct. SET DEFINE is a SQL*PLUS command not a SQL command.
There is NO way you can use it out side of SQL*PLUS. Can you tell us why you are trying to use SET DEFINE? The SET DEFINE command relates to substitution variables which are only used in SQL*PLUS. From ADO.NET you should be using parameters. Dion. Show quote "Nathan Sokalski" wrote: > I want to create a procedure in ASP.NET to run the "SET DEFINE OFF" command > in an Oracle database. I tried the following: > > Public Shared Sub SetDefineOff() > > Dim myconnection As New > OracleConnection(ConfigurationSettings.AppSettings("connectionstring")) > > Dim cmdsetdefineoff As New OracleCommand("SET DEFINE OFF", myconnection) > > myconnection.Open() > > cmdsetdefineoff.ExecuteNonQuery() > > myconnection.Close() > > End Sub > > > But I recieve the following error: > > Server Error in '/lvbeporgtest' Application. > -------------------------------------------------------------------------------- > > ORA-00922: missing or invalid option > Description: An unhandled exception occurred during the execution of the > current web request. Please review the stack trace for more information > about the error and where it originated in the code. > > Exception Details: System.Data.OracleClient.OracleException: ORA-00922: > missing or invalid option > > Source Error: > > An unhandled exception was generated during the execution of the > current web request. Information regarding the origin and location of the > exception can be identified using the exception stack trace below. > > Stack Trace: > > [OracleException: ORA-00922: missing or invalid option > ] > System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle > errorHandle, Int32 rc) +174 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& > rowidDescriptor, ArrayList& refCursorParameterOrdinals) +1919 > System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, > CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) +32 > System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean > needRowid, OciHandle& rowidDescriptor) +170 > System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +56 > Global.SetDefineOff() in C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\Global.asax.vb:32 > newsadmin.btnSubmitNotesLink_Click(Object sender, EventArgs e) in > C:\Documents and Settings\Nathan > Sokalski\VSWebCache\www.webdevlccc.com\lvbeporgtest\newsadmin.aspx.vb:71 > System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 > System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String > eventArgument) +57 > System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler > sourceControl, String eventArgument) +18 > System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 > System.Web.UI.Page.ProcessRequestMain() +1292 > > > > -------------------------------------------------------------------------------- > Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET > Version:1.1.4322.2300 > > > Can someone tell me what I am doing wrong or what is causing the error and > what I can do to fix it? Thanks. > -- > Nathan Sokalski > njsokal***@hotmail.com > http://www.nathansokalski.com/ > > >  |
|||||||||||||||||||||||
Other interesting topics