Re: Error ORA-06502

"sybrandb" <sybrandb_at_gmail.com> schreef in bericht news:1163755182.975359.51010_at_h54g2000cwb.googlegroups.com...
>
>
> On Nov 17, 10:07 am, "Nits" <nitinv..._at_gmail.com> wrote:
>> Hello,
>>
>> Why the following Stored Procedure shows me Error as follows -
>>
>> "ORA-06502: PL/SQL: numeric or value error: character to number
>> conversion error
>> ORA-06512: at line 1"
>>
>> I am calling this stored procedure from VB 2005
>>
>> PROCEDURE sp_dbservdate (p_dbservdate OUT CHAR)
>> IS
>> dbservdate1 CHAR (20);
>> BEGIN
>> dbservdate1 := '';
>> SELECT TO_CHAR (SYSDATE, 'YYYY/MM/DD HH:MM:SS')
>> INTO dbservdate1
>> FROM DUAL;
>>
>> p_dbservdate := dbservdate;
>>
>> END sp_dbservdate;
>>
>> The calling function of VB 2005 as follows
>>
>> Public Function DBServDate() As String
>> '
>> DBServDate = ""
>> Try
>> Dim dteServDate As String = ""
>> Dim cmDataInit As New OleDbCommand
>> cmDataInit.Parameters.Clear()
>> '
>> cmDataInit.Parameters.Add("P_DBSERVDATE", DbType.String,
>> 20).Direction = ParameterDirection.Output
>> '
>> cmDataInit.CommandType = CommandType.StoredProcedure
>> cmDataInit.CommandText = "DB_GLOBAL.SP_DBSERVDATE"
>> '
>> ConnectData() '
>> function to open cnData connection object
>> cmDataInit.Connection = cnData
>> cmDataInit.ExecuteNonQuery() ' at this line
>> the error shows
>> DisconnectData()
>> '
>> DBServDate =
>> CType(cmDataInit.Parameters("P_DBSERVDATE").Value, String)
>> '
>> Catch eEx As System.Data.OleDb.OleDbException
>> blnHasError = True
>> strErrorMsg = eEx.Message
>> '
>> Catch ex1 As Exception
>> '
>> blnHasError = True
>> strErrorMsg = ex1.Message
>> End Try
>> End Function
>>
>> Thanks
>>
>> Nitin.
>
> Apart from the 6502 error, the format mask in the call to to_char is
> incorrect.
> It is HH:MI:SS instead of HH:MM:SS
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

And then wonder why minutes is always 11 ... not the first, and definitely not the last one to make this error.... ;-)

Shakespeare Received on Fri Nov 17 2006 - 04:00:52 CST