************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Don't force the
moderators to do your work. Many readers disregard
postings without the code tags.
**************************************************

Hi,

I am getting "ORA-01722: invalid number" error when I execure my Update statement with IN clause.

Here is my procedure:

Public Function UpdateExportDtl(ByVal ORACon As OracleConnection, ByVal ORATra As OracleTransaction, ByVal lstrHeaderId As String) As Boolean





            Dim OraCmd As OracleCommand





            UpdateExportDtl = False





            OraCmd = New OracleCommand(Me.UPDATE_EXPORT_DTL) --This has my update statement





            OraCmd.CommandType = CommandType.Text





            With OraCmd.Parameters


                .Add(New OracleParameter(EXPORT_DATE_PAR, OracleType.VarChar, 10)).Value = System.DateTime.Now.ToString("yyyyMMdd")


                .Add(New OracleParameter(EXPORT_BY_PAR, OracleType.VarChar, 30)).Value = HttpContext.Current.User.Identity.Name


                .Add(New OracleParameter(LAST_UPDATE_USER_PAR, OracleType.VarChar, 30)).Value = HttpContext.Current.User.Identity.Name


                .Add(New OracleParameter(HEADER_ID_PAR, OracleType.VarChar, 1000)).Value = lstrHeaderId


            End With





            With OraCmd


                .Connection = ORACon


                .Transaction = ORATra


                .ExecuteNonQuery()


            End With





            UpdateExportDtl = True





            Return UpdateExportDtl





        End Function

Here is my Update Statement:

        Private Const UPDATE_EXPORT_DTL As String = "UPDATE  EXPORT_DBA.TB_BROKER_PMT_SMRY SET " _


           & "EXPORT_DATE = :PAR_EXPORT_DATE, EXPORT_BY = :PAR_EXPORT_BY, " _


           & "LAST_UPDATE_USER = :PAR_LAST_UPDATE_USER " _


           & " WHERE HEADER_ID IN :PAR_HEADER_ID"

:PAR_EXPORT_DATE, etc are properly mapped as follows:

Public Const EXPORT_DATE_PAR As String = "PAR_EXPORT_DATE"


Public Const EXPORT_BY_PAR As String = "PAR_EXPORT_BY"


Public Const LAST_UPDATE_USER_PAR As String = "PAR_LAST_UPDATE_USER"


Public Const HEADER_ID_PAR As String = "PAR_HEADER_ID"

and I am passing :PAR_HEADER_ID value and that is as follows:

"(218,217,221,219,221,220,219,218,217,220)"


I event tried to put single quotes for all above values, but same error.

Can anyone help?

Thanx
cm

The code is specifying a varchar parameter where Oracle wants to compare against a number. It's tough to pass a parameter to a stored procedure to use with the IN clause, i.e. there is a difference between

select * from t where x in ( 1, 2, 3 )

and

select * from t where x in "( 1, 2, 3 )"

in that the latter will never work. Perhaps this article can give you some pointers.