asked on

Updating Oracle DB using an Update command in VB.NET

Using VB.NET with ODP.NET OracleConnection. Need to update data in Oracle DB.

I thought I could just create an OracleCommand with a SQL update statement as the CommandText. Here is the SQL command:

update Rig_Dates set Est_Rig_Off='25-Mar-18', Est_Rig_On='09-Mar-18' Where XPrime='6913B5020BCECE4CB54370CC1F21A00A'

I run this in Oracle and it works fine. However, when I run it using an OracleCommand, it just locks up the process and I have to kill the code.

After doing more research, I find others that use parameters. I changed my code to:

Dim ocmd As New OracleCommand
ocmd.CommandType = CommandType.Text
ocmd.BindByName = True
Dim oconn As New OracleConnection
oconn.ConnectionString = "DATA SOURCE=xs-bhm-dbd-1:1521/WLCD;USER ID=user;Password=password"
oconn.Open()
ocmd.Connection = oconn
sql = "update Rig_Dates set "
sql = sql & "Est_Rig_On= :EstRigOn, Est_Rig_Off= :EstRigOff Where XPrime= :XPrime"
ocmd.Parameters.Add("EstRigOn", OracleDbType.Date).Value = Format(CDate(currentRow("Est_Rig_On")), "dd-MMM-yy")
ocmd.Parameters.Add("EstRigOff", OracleDbType.Date).Value = Format(CDate(currentRow("Est_Rig_Off")), "dd-MMM-yy")
ocmd.Parameters.Add("XPrime", OracleDbType.Varchar2).Value = currentRow("XRig_Dates").ToString
ocmd.CommandText = sql
Try
ocmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

This also locks up at the ocmd.ExecuteNonQuery() as well. Any suggestions?

slightwv (䄆 Netminder)

Nothing jumps out at me that is wrong.

Have you tried that exact update with the same bind variables using sql developer or sqlplus? If so, how long does it take to execute? You might just be updating a LOT of rows.

>>I find others that use parameters

Yes, bind variables is the correct way to do this.

I might use strings on the VB.Net side and convert them to dates on the DB side:
sql = "update Rig_Dates set "
sql = sql & "Est_Rig_On= to_date(:EstRigOn,'MM/DD/YYYY'), Est_Rig_Off= to_date(:EstRigOff,'MM/DD/YYYY') Where XPrime= :XPrime"

Then declare the parameters as varchar2 and pass them in using the correct format. If the app uses a specific format, change the TO_DATE data mask to match. Then you don't have to mes around wight formatting on the app side.

slightwv (䄆 Netminder)

Remember from your previous question I don't have IIS set up.

Here is a Powershell version showing it working. I had to tweak the Parameters.Add a little to get it working. the syntax you posted didn't work in PowerShell and I don't have access to my old VB.Net code. I couldn't get any similar syntax working so I went with what ran. Going from Memory, the syntax you posted seems like it should work. I remember adding the value and data type length inside the parans but there are several overloaded versions.

I changed the table name due to my drop and insert statements.

My setup SQL:

drop table myRig_Dates purge;
create table myRig_Dates(est_rig_on date, est_rig_off date, xprime varchar2(10));

insert into myRig_Dates values(to_date('01/01/2001'),to_date('01/02/2001'),'a');
insert into myRig_Dates values(to_date('02/03/2002'),to_date('02/04/2002'),'b');
commit;

Open in new window

My Powershell code:

#Just for me since red sucks...
$host.PrivateData.ErrorForegroundColor='Green'

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

Unblock-File -Path C:\bud\ps1_scripts\Oracle.ManagedDataAccess.dll
Add-Type -Path 'C:\ee\Oracle.ManagedDataAccess.dll'

$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=Scott;Password=Tiger;Data Source=localhost:1521/ORCL")
$cmd=new-object Oracle.ManagedDataAccess.Client.OracleCommand
$cmd=$con.CreateCommand()
$cmd.BindByName = "True"

$cmd.CommandText= "update myRig_Dates set Est_Rig_On= to_date(:EstRigOn,'MM/DD/YYYY'), Est_Rig_Off= to_date(:EstRigOff,'MM/DD/YYYY') Where XPrime= :XPrime"

$cmd.Parameters.Add("EstRigOn","03/03/2003")
$cmd.Parameters.Add("EstRigOff","04/04/2004")
$cmd.Parameters.Add("XPrime","a")

$con.Open()
$str=$cmd.ExecuteNonQuery()
$con.Close()

Open in new window

The run:

ArrayBindSize           :
ArrayBindStatus         :
CollectionType          : None
DbType                  : String
Direction               : Input
IsNullable              : False
Offset                  : 0
OracleDbType            : Varchar2
OracleDbTypeEx          : Varchar2
ParameterName           : EstRigOn
Precision               : 0
Scale                   : 0
Size                    : 0
SourceColumn            :
SourceColumnNullMapping : False
SourceVersion           : Current
Status                  : Success
UdtTypeName             :
Value                   : 03/03/20

ArrayBindSize           :
ArrayBindStatus         :
CollectionType          : None
DbType                  : String
Direction               : Input
IsNullable              : False
Offset                  : 0
OracleDbType            : Varchar2
OracleDbTypeEx          : Varchar2
ParameterName           : EstRigOf
Precision               : 0
Scale                   : 0
Size                    : 0
SourceColumn            :
SourceColumnNullMapping : False
SourceVersion           : Current
Status                  : Success
UdtTypeName             :
Value                   : 04/04/20

ArrayBindSize           :
ArrayBindStatus         :
CollectionType          : None
DbType                  : String
Direction               : Input
IsNullable              : False
Offset                  : 0
OracleDbType            : Varchar2
OracleDbTypeEx          : Varchar2
ParameterName           : XPrime
Precision               : 0
Scale                   : 0
Size                    : 0
SourceColumn            :
SourceColumnNullMapping : False
SourceVersion           : Current
Status                  : Success
UdtTypeName             :
Value                   : a

Open in new window

The database after:

SQL> select * from myRig_Dates;

EST_RIG_ON          EST_RIG_OFF         XPRIME
------------------- ------------------- ----------
03/03/2003 00:00:00 04/04/2004 00:00:00 a
02/03/2002 00:00:00 02/04/2002 00:00:00 b

Open in new window

bbaldwin

ASKER

Hey S,

Great to hear from you again!

Does it matter the format of the text that is being sent in for the date on the parameter? I am configuring it to be dd-MMM-yy (21-Feb-18) because Oracle sql required it. I see how you did it and will change mine to match yours and Test

Why do you think it just locks up? It is only updating a single record. Maybe the date format but you would think that it would return an error.

B

slightwv (䄆 Netminder)

>>Why do you think it just locks up? It is only updating a single record. Maybe the date format but you would think that it would return an error.

A single row update shouldn't hang. I wonder if a previous attempt at an update locked the row and your last session was waiting for the previous transaction to commit?

>>Does it matter the format of the text that is being sent in for the date on the parameter?

No. Oracle can convert just about any string format into a date.

>>am configuring it to be dd-MMM-yy (21-Feb-18) because Oracle sql required it.

First: I believe you have the format wrong. The default date format for Oracle is 'DD-MON-YY'. Oracle also doesn't require it.

If you do not explicitly convert data types Oracle will try to do it for you.

For example, adding a string to a number:
select '1' + 1 from dual;

Oracle looks at it ans assumes you want to add two numbers so it will convert the first string to a number for you.

That is bad coding practice. ALWAYS to explicit data type conversion!!!! If you have a string and want a date, convert it. Do not rely on Oracle to do it for you.

That said:
To recap: By default Oracle will convert a string to a date and back using 'DD-MOM-YY'. You can change the default using NLS_DATE_FORMAT. But again, NEVER rely on implicit type conversion.

See if these examples make sense:

SQL> select sysdate from dual;

SYSDATE
---------
04-FEB-18

SQL> select to_date('01-JAN-00') from dual;

TO_DATE('
---------
01-JAN-00

SQL> select to_date('01/01/2000') from dual;
select to_date('01/01/2000') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('01/01/2000','MM/DD/YYYY') from dual;

TO_DATE('
---------
01-JAN-00

SQL> alter session set nls_date_format='MM/DD/YYYY';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
02/04/2018

SQL> select to_date('01/01/2000') from dual;

TO_DATE('0
----------
01/01/2000

SQL>

Open in new window

bbaldwin

ASKER

Oracle updates fine in SQL Developer with this:

update Rig_Dates set Est_Rig_Off='25-Mar-18' Where XPrime='6913B5020BCECE4CB54370CC1F21A00A'

But NOT with this:

update Rig_Dates set Est_Rig_Off='03-25-18' Where XPrime='6913B5020BCECE4CB54370CC1F21A00A'

I get this error:
Error starting at line : 1 in command -
update Rig_Dates set Est_Rig_Off='03-25-18' Where XPrime='6913B5020BCECE4CB54370CC1F21A00A'
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:

slightwv (䄆 Netminder)

From above:

That is bad coding practice. ALWAYS to explicit data type conversion!!!! If you have a string and want a date, convert it. Do not rely on Oracle to do it for you.

Use TO_DATE as I did in my examples.

bbaldwin

ASKER

Ok, that examples above are using SQL Developer to test the soundness of the query. I am using your examples with parameters now. Still gets hung up with no error trapping apparently. I have waited for several minutes with not response. I did add Oracle exception trapping as well. It just gets hung up on the ocmd.ExecuteNonQuery() statement with no error.

Try
ocmd.ExecuteNonQuery()
Catch ex As OracleException
MsgBox(ex.Number)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

slightwv (䄆 Netminder)

even in SQL Developer use TO_DATE.

Try the update again in SQL Developer using TO_DATE and see if it hangs.

bbaldwin

ASKER

I was able to get with the Oracle DB manager and he found that I had an Oracle SQL Developer session that had the record locked up from Saturday. He released that and I was able to get the execute statement to return immediately. You would think I would get a timeout or do I need to set a timeout in my code?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbaldwin

ASKER

I was thinking of a time out error to let me know that the record is locked so that I can at least know that was the issue and go to the DBM to check who has the record locked and resolve the issue. Thank you Slightwv for the help!

slightwv (䄆 Netminder)

Error or warning? Resource plans will generate an error. I'm not sure of anything built-in that would generate a warning.

You might be able to write something that would look for locks before issuing DML but it would have a built-in race condition: In between looking for a lock and performing the DML, something to get a lock.