ODP.NET Orcale Function returing VARCHAR2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

I am unable figure out the following problem.I am using VB.NET with ODP.NET
9.2.0.4 to access ORACLE 8i Database Server. While I am trying to call an
Oracle function which returns a VARCHAR2 at the time of ExecuteNonQuery it
generates the OracleExecption( "ORA-06502: PL/SQL: numeric or value error:
character string buffer too small ORA-06512: at line 1" ).

But No problem occurs while function returns Number.

Thanks in Advance

Arnab
------------------------------------
Codes
-------------------------------------------

Oracle Function:
-----------------------------------------------------------------------------------------

FUNCTION Get_DATA1 (p_val IN VARCHAR2)
RETURN VARCHAR2 IS

v_val VARCHAR2(10);
BEGIN
v_val:=p_val;
RETURN v_val;
END Get_DATA1;


VB.NET Function That accessing the Above function:
-----------------------------------------------------------


Public Function GetData1() As String

Dim strConnection As String
Dim objConnection As OracleConnection
Dim objCommand As OracleCommand
Dim objParameterReturn As New OracleParameter

Dim strReturnValue As String = ""
Dim strValue As String="HELLO"

Try
strConnection = "My Oracle connection String"
objConnection = New OracleConnection(strConnection)
objCommand = New OracleCommand("Get_DATA1", objConnection)
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Parameters.Add("p_val", OracleDbType.Varchar2, 10,
strValue, ParameterDirection.Input)

objParameterReturn = objCommand.Parameters.Add("retval",
OracleDbType.Varchar2, 10, Nothing,
ParameterDirection.ReturnValue)

objCommand.Connection.Open()

'Upto connection open is working fine
'but ExecuteNonQuery casues the OracleException

objCommand.ExecuteNonQuery()
strReturnValue = CType(objParameterReturn.Value(), String)

Catch ex As OracleException
strReturnValue="Oracle Error"
Catch ex As Exception
strReturnValue="Other Error"
Finally
''Release All DB objects
ReleaseDB(objCommand, objConnection)
End Try

Return strReturnValue
End Function
 
Back
Top