vb.net - OracleCommand StoredProcedure returning "null" instead of DBNull -

i have stored procedure in situations returns null. when tostring() null returns "null" string instead of empty string, if don't use .tostring() method causes error.

public function returnvalue(byval lngid long) string     dim adocmd new oraclecommand     dim strreturn string = ""      adocmd          .commandtext = "bus_test.returnvalue"         .commandtype = commandtype.storedprocedure          .parameters.add("return", oracledbtype.char, 256, strreturn, parameterdirection.returnvalue)         .parameters.add("lngid", oracledbtype.double, lngid, parameterdirection.input)     end      objsalutil.execfunc(ocewbconnection, adocmd, "returnvalue")      return adocmd.parameters(0).value.tostring  end function 

the above code returns "null"

public function returnvalue(byval lngid long) string     dim adocmd new oraclecommand     dim strreturn string = ""      adocmd          .commandtext = "bus_test.returnvalue"         .commandtype = commandtype.storedprocedure          .parameters.add("return", oracledbtype.char, 256, strreturn, parameterdirection.returnvalue)         .parameters.add("lngid", oracledbtype.double, lngid, parameterdirection.input)     end      objsalutil.execfunc(ocewbconnection, adocmd, "returnvalue")      return adocmd.parameters(0).value  end function 

the above code causes invalidcastexception: conversion type 'oraclestring' type 'string' not valid.

public function returnvalue(byval lngid long) string     dim adocmd new oraclecommand     dim strreturn string = ""      adocmd          .commandtext = "bus_test.returnvalue"         .commandtype = commandtype.storedprocedure          .parameters.add("return", oracledbtype.char, 256, strreturn, parameterdirection.returnvalue)         .parameters.add("lngid", oracledbtype.double, lngid, parameterdirection.input)     end      objsalutil.execfunc(ocewbconnection, adocmd, "returnvalue")      return strreturn  end function 

and lastly, above code returns proper empty string, in cases, regardless of whether should have been returned or not.

i've used stored procedures lot in past, they've returned value in cases. i'm not quite sure how handle null. i'd prefer not check string containing "null" first, little hacky, , i'd know future i'm doing wrong.

i think, you're using odp.net. in case, instead of this

return adocmd.parameters(0).value 

you this

dim orastr oraclestring = ctype(adocmd.parameters(0).value, oraclestring) return orastr.value ' return .net string 

what happening is, adocmd.parameters(0).value returns oracle null. if cast return value oracle type, can access .net-typed value. when use adocmd.parameters(0).value.tostring() oracle implementation of tostring, returns word null.

this remember when use odp.net oracle parameters, stored procedure or parametrized query return values - doesn't matter, here

dim sql string = "insert table ...) returning fld1 :1" 

interesting observation: in sqlclient illegal parameters(0).value = nothing. wants dbnull.value. in odp.net not problem. takes - nothing or/and dbnull.value

when use oraclereader, through idatareader - not problem. reader("fld1") return .net type.

dim r idatareader = cmd.executereader... dim integer = reader("fld1") 

where need careful in matching oracle db type .net. problem is, don't match. if declare table field number(9) return .net integer number(9) not large enough fit integer.maxvalue. if declare table field number(10), reader("fld1") return long. so, .net integer falls somewhere between number(9) , number(10). need use convert

 dim integer = convert.toint32(reader("fld1"))