Subprocedure for Oracle's SET DEFINE OFF command

[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
OracleConnectio n(Configuration Settings.AppSet tings("connecti onstring"))

Dim cmdsetdefineoff As New OracleCommand(" SET DEFINE OFF", myconnection)

myconnection.Op en()

cmdsetdefineoff .ExecuteNonQuer y()

myconnection.Cl ose()

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.Ora cleClient.Oracl eException: 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.Ora cleClient.Oracl eConnection.Che ckError(OciErro rHandle
errorHandle, Int32 rc) +174
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle&
rowidDescriptor , ArrayList& refCursorParame terOrdinals) +1919
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor ) +32
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQueryIntern al(Boolean
needRowid, OciHandle& rowidDescriptor ) +170
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQuery() +56
Global.SetDefin eOff() in C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...bal.asax.vb:32
newsadmin.btnSu bmitNotesLink_C lick(Object sender, EventArgs e) in
C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...min.aspx.vb:71
System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
eventArgument) +57
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
System.Web.UI.P age.ProcessRequ estMain() +1292

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.2300; ASP.NET
Version:1.1.432 2.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
nj********@hotm ail.com
http://www.nathansokalski.com/

[timkling]

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
OracleConnectio n(Configuration Settings.AppSet tings("connecti onstring"))

Dim cmdsetdefineoff As New OracleCommand(" SET DEFINE OFF", myconnection)

myconnection.Op en()

cmdsetdefineoff .ExecuteNonQuer y()

myconnection.Cl ose()

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.Ora cleClient.Oracl eException: 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.Ora cleClient.Oracl eConnection.Che ckError(OciErro rHandle
errorHandle, Int32 rc) +174
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle&
rowidDescriptor , ArrayList& refCursorParame terOrdinals) +1919
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor ) +32
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQueryIntern al(Boolean
needRowid, OciHandle& rowidDescriptor ) +170
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQuery() +56
Global.SetDefin eOff() in C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...bal.asax.vb:32
newsadmin.btnSu bmitNotesLink_C lick(Object sender, EventArgs e) in
C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...min.aspx.vb:71
System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
eventArgument) +57
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
System.Web.UI.P age.ProcessRequ estMain() +1292

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.2300; ASP.NET
Version:1.1.432 2.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
nj********@hotm ail.com
http://www.nathansokalski.com/

[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.
timkling wrote:

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
OracleConnect ion(Configurati onSettings.AppS ettings("connec tionstring"))

Dim cmdsetdefineoff As New OracleCommand(" SET DEFINE OFF", myconnection)

myconnection. Open()

cmdsetdefineo ff.ExecuteNonQu ery()

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.Ora cleClient.Oracl eException: 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.Ora cleClient.Oracl eConnection.Che ckError(OciErro rHandle
errorHandle , Int32 rc) +174
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavi or behavior, Boolean isReader, Boolean needRowid, OciHandle&
rowidDescript or, ArrayList& refCursorParame terOrdinals) +1919
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavi or behavior, Boolean needRowid, OciHandle& rowidDescriptor ) +32
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQueryIntern al(Boolean
needRowid, OciHandle& rowidDescriptor ) +170
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQuery() +56
Global.SetDefin eOff() in C:\Documents and Settings\Nathan
Sokalski\VSWe bCache\http://www.webdevlccc.com\lvbeporgte...bal.asax.vb:32
newsadmin.btnSu bmitNotesLink_C lick(Object sender, EventArgs e) in
C:\Document s and Settings\Nathan
Sokalski\VSWe bCache\http://www.webdevlccc.com\lvbeporgte...min.aspx.vb:71
System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
eventArgument ) +57
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl , String eventArgument) +18
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
System.Web.UI.P age.ProcessRequ estMain() +1292

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.2300; ASP.NET
Version:1.1.4 322.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
nj********@ho tmail.com
http://www.nathansokalski.com/

[Simon]

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" 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
OracleConnectio n(Configuration Settings.AppSet tings("connecti onstring"))

Dim cmdsetdefineoff As New OracleCommand(" SET DEFINE OFF", myconnection)

myconnection.Op en()

cmdsetdefineoff .ExecuteNonQuer y()

myconnection.Cl ose()

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.Ora cleClient.Oracl eException: 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.Ora cleClient.Oracl eConnection.Che ckError(OciErro rHandle
errorHandle, Int32 rc) +174
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle&
rowidDescriptor , ArrayList& refCursorParame terOrdinals) +1919
System.Data.Ora cleClient.Oracl eCommand.Execut e(OciHandle statementHandle ,
CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor ) +32
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQueryIntern al(Boolean
needRowid, OciHandle& rowidDescriptor ) +170
System.Data.Ora cleClient.Oracl eCommand.Execut eNonQuery() +56
Global.SetDefin eOff() in C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...bal.asax.vb:32
newsadmin.btnSu bmitNotesLink_C lick(Object sender, EventArgs e) in
C:\Documents and Settings\Nathan
Sokalski\VSWebC ache\http://www.webdevlccc.com\lvbeporgte...min.aspx.vb:71
System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
eventArgument) +57
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
System.Web.UI.P age.ProcessRequ estMain() +1292

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.432 2.2300; ASP.NET
Version:1.1.432 2.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
nj********@hotm ail.com
http://www.nathansokalski.com/