I have a stored procedure with a CLOB as an input.
I'm looking to do a refactor, so my Oracle library is not directly referenced. When I create a parameter from the object, I'm getting back an IDbDataParameter object.
Passing a string to it without defining the type seems to work fine (setting the name and value).
However, the text gets cut off at 32767, the maximum length for Oracle VARCHAR.
So, my question is, how do I set the OracleDbType to type Clob, without having to expose the assembly or use reflection. I tried using a DbType of binary and it failed. I'd rather not load the library of use reflection if there is a better way.
Here's some sample code:
''' <summary>
''' Creates a Parameter
''' </summary>
''' <param name="name"></param>
''' <param name="value"></param>
''' <returns></returns>
Public Function CreateParameter(ByVal name As String, ByVal value As String) As IDbDataParameter
Dim parameter As IDbDataParameter = m_Db.CreateParameter()
parameter.ParameterName = name
parameter.Value = value
Return parameter
End Function
And from the caller. the p_notes parameter is what I want to be a CLOB.
oDAL = New DAL.DataAccessLayer(m_BSL)
Dim parameters As New List(Of IDbDataParameter)
parameters.Add(oDAL.CreateParameter("p_no", compNo))
parameters.Add(oDAL.CreateParameter("p_sys", m_BSL.CurrentSystem.Description.ToString))
parameters.Add(oDAL.CreateParameter("p_notes", notes))
parameters.Add(oDAL.CreateParameter("p_chuser", m_BSL.CurrentUser.ToString))
oDAL.Exec_SP("P_UPDATE_MASTSTAT_NOTES", parameters.ToArray())
So is there a way to set DbType in such a way that it will assign OracleDbType to CLOB?