OracleClient array parameter

[ohad weiss]

Hi all,

I have a procedure in my Oracle DB that one of its inputs is array:
procedure pivot(inMaxCols in number default NULL,
inMaxColsQuery in varchar2 default NULL,
inQuery in varchar2,
inAnchor in array,
inPivot in array,
inData in array,
inDataQuery in varchar2,
outRefCursor in out refcursor) is

I need to send these input parameters from my vb.net application.

This is what I do in my vb.net code:
Function GetRefCursor() As DataSet
Try

Dim instance As OracleClient.OracleParameterCollection
Dim array As Array
Dim index As Integer
Dim cnnConn As New
OracleClient.OracleConnection(ORAstrConnection)
cnnConn.Open()
Dim cmdDynQuery As OracleClient.OracleCommand = New _
OracleClient.OracleCommand
Dim st0 As String = "0"
Dim st1 As String = "select max(count(*)) from sim_results where
sim_no=27 " _
& "and month='01'group by activity_id"
Dim st2 As String = "select product_id, activity_id,
total_quantity, " _
& "total_price_nis from sim_results where sim_no=27 and
month='01' order by " _
& "product_id"
Dim st3() As String = {"pivot_pkg.array('product_id')"}
Dim st4() As String = {"pivot_pkg.array('activity_id')"}
Dim st5() As String = {"pivot_pkg.array('total_quantity')"}
Dim st6 As String = "select distinct activity_id, " _
& "replace(activity_id, '-','_') activity_id_header from
sim_results where " _
& "sim_no=27 order by activity_id"
cmdDynQuery.Connection = cnnConn
cmdDynQuery.CommandText = "pivot_pkg.pivot"
cmdDynQuery.CommandType = CommandType.StoredProcedure
cmdDynQuery.Parameters.Add("inmaxcols",
OracleClient.OracleType.Number).Value = st0
cmdDynQuery.Parameters.Add("inMaxColsQuery",
OracleClient.OracleType.VarChar, _
2000).Value = st1
cmdDynQuery.Parameters.Add("inQuery",
OracleClient.OracleType.VarChar, _
2000).Value = st2
cmdDynQuery.Parameters().Add("inAnchor",
OracleClient.OracleType.VarChar, _
2000).Value = st3
cmdDynQuery.Parameters().Add("inPivot",
OracleClient.OracleType.VarChar, _
2000).Value = st4
cmdDynQuery.Parameters().Add("inData",
OracleClient.OracleType.VarChar, _
2000).Value = st5
cmdDynQuery.Parameters.Add("inDataQuery",
OracleClient.OracleType.VarChar, _
2000).Value = st6
cmdDynQuery.Parameters.Add("outRefCursor", _
OracleClient.OracleType.Cursor).Direction =
ParameterDirection.Output
Dim drDynQuery As OracleClient.OracleDataReader
drDynQuery = cmdDynQuery.ExecuteReader
cnnConn.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try
End Function

My problem is that I can't send to parameters "inAnchor", "inPivot",
"inData" varchar type, and I need to send an array.

How can I do that?



Thanks for your help

 

[Cor Ligthert[MVP]]

Ohad,

Not get the idea that somebody send you away here (that is never done in
dotnet newsgroups), but for Oracle is a much better newsgoup

microsoft.public.dotnet.framework.adonet.

Most Net newsgroups are based on SQL Server, but that newsgroup as well
Oracle.

Cor

 

[Paul Clement]

¤ Hi all,
¤
¤ I have a procedure in my Oracle DB that one of its inputs is array:
¤ procedure pivot(inMaxCols in number default NULL,
¤ inMaxColsQuery in varchar2 default NULL,
¤ inQuery in varchar2,
¤ inAnchor in array,
¤ inPivot in array,
¤ inData in array,
¤ inDataQuery in varchar2,
¤ outRefCursor in out refcursor) is
¤
¤ I need to send these input parameters from my vb.net application.

You would need to use ODP.NET in order to pass an array to an Oracle stored procedure

http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html


Paul
~~~~
Microsoft MVP (Visual Basic)