Subprocedure for Oracle's SET DEFINE OFF command

N

Nathan Sokalski

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

Guest

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 said:
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.
Click to expand...
 
J

John A. Bailo

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.

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

:

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.
Click to expand...
Click to expand...
 
G

Guest

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.

Nathan Sokalski said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

asp.net 2.0 with Oracle delete command 0
Error when uploading file 5
Very strange SqlClient error in page code? 4
Object variable or With block variable not set 6
Object reference not set to an instance of an object 3
Object reference not set to an instance of an object. 3
Problem with queries. Call for help. 3
ConfigurationManager.connectionStrings = Object reference not set 0

Members online

Total: 43 (members: 3, guests: 40)
Robots: 464

Forum statistics

Threads
473,768
Messages
2,569,575
Members
45,054
Latest member
LucyCarper

Latest Threads

Top