Skip to Main Content

Oracle Developer Tools for Visual Studio

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How do you do Bind variables in INSERT statement when using ODAC in VB.NET

user143756Jun 18 2009 — edited Jun 22 2009
Hi,

We are new to VS2008\VB.NET and we have written a program where the values of two string variables have gotten too large, we get error ORA-01704: string literal too long. We have created an XML string that probably exceeds the max character count in "tmpString" combined with all the other strings that are concatenated together. I need to modify the code below to replace the "tmpString" and "tranBody" string variables with bind variables so the insert command is not so long that it exceeeds Oracle's limit. Are there some examples somewhere on how to set up the insert and how to do the bind variable substitution?

Thanks in advance:

Bruce

' insert the record into the database
Dim tmpString As String
Dim tranBody As String
Dim appName As String
Dim wkString As String
Dim myTransaction As OracleTransaction

Try

myConnection.ConnectionString = objInetUtils.getConnectionString("ITRAN")
myConnection.Open()
myTransaction = myConnection.BeginTransaction

Dim sqlCommand As String
sqlCommand = "insert into my_table (transactionid, ip_address,orig_source,orig_date,chg_source,chg_date,application,notification_comments, notification_subject, transaction_body) " _
& "values(to_number('" _
& (Str(tmpInt) & "'),'" _
& objInetUtils.myIpAddress & "','" _
& appName & "'," _
& "to_date('" & DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") & "', 'mm/dd/yyyy hh24:mi:ss'),'" _
& appName & "'," _
& "to_date('" & DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") & "', 'mm/dd/yyyy hh24:mi:ss'),'" _
& appName & "','" _
& Replace(tmpString, "'", "''") & "','" _
& Replace(wkString, "'", "''") & "','" _
& Replace(tranBody, "'", "''") & "'" _
& ")")

Dim myDataCommand As New OracleCommand
myDataCommand.CommandType = CommandType.Text
myDataCommand.Connection = myConnection
myDataCommand.CommandText = sqlCommand
myDataCommand.ExecuteNonQuery()
myTransaction.Commit()
Catch ex As Exception
myTransaction.Rollback()
Throw ex
Finally
myConnection.Close()
myConnection.Dispose()
End Try

Edited by: user11218662 on Jun 18, 2009 2:31 PM

Edited by: user11218662 on Jun 18, 2009 2:31 PM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 20 2009
Added on Jun 18 2009
1 comment
4,356 views