Re: Error ORA-06502

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