i'm executing oracle procedure, has 3 output parameters , returns results in table type variable.
here limitations are, should not use odbc, msdaora providers call procedure. i'm planning using oracle oledb provider.
i'm able execute procedure successfully, when check (while dr.read()) not returning records. know per stored procedure results, should return 66 records.
i doubt vb.net code.... please suggest something.. in advance.
private sub getclients() dim cmd new oraclecommand("pkg_hobs.prc_hobs_get_clientid", fpp1_connection) cmd.commandtype = commandtype.storedprocedure dim p1 new oracleparameter(":obus_grp_id", oracledbtype.int32, parameterdirection.output) p1.collectiontype = oraclecollectiontype.plsqlassociativearray p1.size = 100 ' size of items in array in case cmd.parameters.add(p1) dim p2 new oracleparameter(":ostat_c", oracledbtype.int32, parameterdirection.output) p2.collectiontype = oraclecollectiontype.plsqlassociativearray p2.size = 100 ' size of items in array in case cmd.parameters.add(p2) dim p3 new oracleparameter(":ostat_msg_x", oracledbtype.varchar2, parameterdirection.output) p3.collectiontype = oraclecollectiontype.plsqlassociativearray p3.size = 100 ' size of items in array in case p3.arraybindsize = enumerable.repeat(500, 100).toarray cmd.parameters.add(p3) cmd.executenonquery() dim oranumbers1() oracledecimal = ctype(p1.value, oracledecimal()) dim myobus_grp_idvalues(oranumbers1.length - 1) integer integer = 0 oranumbers1.length - 1 myobus_grp_idvalues(i) = convert.toint32(oranumbers1(i).value) next dim oranumbers2() oracledecimal = ctype(p2.value, oracledecimal()) dim myostat_cvalues(oranumbers2.length - 1) integer integer = 0 oranumbers2.length - 1 myostat_cvalues(i) = convert.toint32(oranumbers2(i).value) next dim orastrings() oraclestring = ctype(p3.value, oraclestring()) dim myostat_msg_xvalues(orastrings.length - 1) string integer = 0 orastrings.length - 1 myostat_msg_xvalues(i) = orastrings(i).value next try messagebox.show(myobus_grp_idvalues.tostring) . . . . . package definition
type tnumber table of number index binary_integer; type tmsg_500 table of varchar2(500) index binary_integer; procedure prc_hobs_get_clientid ( obus_grp_id out tnumber, ostat_c out tnumber, ostat_msg_x out tmsg_500);
first of all, don't use oledb, period. microsoft tells use vendor-specific provider. use oracle's odp.net.
second, retrieve recordset oracle sp, need return refcursor.
edit: @ time know parameters tables. process need add p.collectiontype = oraclecollectiontype.plsqlassociativearray parameters
your code this:
declare obus_grp_id pkg_hobs.tnumber; -- numeric table value ostat_c pkg_hobs.tnumber; -- numeric table value ostat_msg_x pkg_hobs.tmsg_500; -- string table value begin pkg_hobs.prc_hobs_get_clientid(obus_grp_id, ostat_c, ostat_msg_x); end; i see executing anonymous block - don't need this complicates things you. need use vb.net execute package straight.
bottom line: current oracle code nothing output results .net. remove anonymous block , you're in business.
here code process type of procedure (read in comments)
dim cmd new oraclecommand("pkg_hobs.prc_hobs_get_clientid", conn) cmd.commandtype = commandtype.storedprocedure dim p1 new oracleparameter(":p1", oracledbtype.int64, parameterdirection.output) p1.collectiontype = oraclecollectiontype.plsqlassociativearray p1.size = 100 ' declare more expect ' line below not needed numeric types (date too???) ' p1.arraybindsize = new integer(99) {} cmd.parameters.add(p1) ' add parameter 2 here - same 1 dim p3 new oracleparameter(":p3", oracledbtype.varchar2, parameterdirection.output) p3.collectiontype = oraclecollectiontype.plsqlassociativearray p3.size = 100 ' declare more expect ' string data types need allocate space each element p3.arraybindsize = enumerable.repeat(500, 100).toarray() ' 100 elements of 500 - size of returning string ' don't know why have problems referencing system.linq if do... 'dim inta() integer = new integer(99) {} 'for integer = 0 inta.length -1 ' inta(i) = 500 'next cmd.parameters.add(p3) conn.open() cmd.executenonquery() ' ora number not compatible .net types. example integer ' between number(9) , (10). so, if number(10) type - long in ' return. therefore use "convert" ' also, return arrays, need process them arrays - note changes dim oranumbers() oracledecimal = ctype(p1.value, oracledecimal()) dim myp1values(oranumbers.length - 1) long integer = 0 oranumbers.length - 1 myp1values(i) = convert.toint64(oranumbers(i).value) next oranumbers = ctype(p2.value, oracledecimal()) dim myp2values(oranumbers.length - 1) long integer = 0 oranumbers.length - 1 myp2values(i) = convert.toint64(oranumbers(i).value) next dim orastrings() oraclestring= ctype(p3.value, oraclestring()) dim myp3values(orastrings.length - 1) string integer = 0 orastrings.length - 1 myp3values(i) = orastrings(i).value next and important part
the important part how fill declared type. lets take
type tnumber table of number index binary_integer; v_num tnumber; v_num(1) := 1234567890; v_num(2) := 2345678901; v_num(3) := 3456789012; this (above) work. fail:
v_num(0) := 1234567890; v_num(1) := 2345678901; v_num(2) := 3456789012; and finally, this, work 1 condition
v_num(2) := 1234567890; v_num(3) := 2345678901; v_num(4) := 3456789012; here 4 members in p1.value under index 0 have oracle null. so, need deal here (if have such condition)
' instead of myp2values(i) = convert.toint64(oranumbers(i).value) ' need first check if oranumbers(i).isnull . . . . so, principal thing here is, index of pl/sql table?! needs start larger 0, , preferably 1. , if have index skipped numbers, i.e. 2,4,6,8, spaces part of returning oracle array , there oracle null in them
Comments
Post a Comment