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?
LVL 2
bbaldwinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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) Commented:
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

bbaldwinAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>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

bbaldwinAuthor Commented:
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) Commented:
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.
bbaldwinAuthor Commented:
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) Commented:
even in SQL Developer use TO_DATE.

Try the update again in SQL Developer using TO_DATE and see if it hangs.
bbaldwinAuthor Commented:
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?
slightwv (䄆 Netminder) Commented:
Told you it was likely an uncommited transaction.

>>You would think I would get a timeout

Why?  A transaction is a transaction.  How would you feel if your bank had a timeout on your deposit insert statement?

>>do I need to set a timeout in my code?

You shouldn't need anything in your code unless you need to account for bad developers/dbas that might lock rows/tables for expended periods of time.  If you feel you need to, then by all means add it.

All that said:  You can tell Oracle to timeout idle sessions using resource management on the database side and sqlnet.ora on the client side.  I'll let you work with your DBA on those if you feel you need them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbaldwinAuthor Commented:
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) Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.