Trouble using ODP PLSQLAssociativeArrary parameter

Topics: Development Team Discussion, User Discussion
May 4, 2010 at 8:19 PM

Hi,

If you are successfully using ODAP with parameters of CollectionType PLSQLAssociativeArray I would like to compare notes with you.

I am trying to pass in an associative array of ids which my stored procedure will use in a "WHERE IN" clause.  This is to avoid using dynamic SQL. 

All examples of find of using ODP for this purpose use stardard DotNet framework syntax (not the DAAB).  I am having trouble even getting that to work.  But even if I do get that to work I don't see an overload for db.AddParameter that will allow me to define a parameter with a CollectionType = OracleCollectionType.PLSQLAssociativeArray.

Here is the code I currently have in VB.net based on finding snippets of code from various similar (but not the same) scenarios:

Using con As New OracleConnection(NCCIConfig.GetConnectString(CONNECTIONSTRING))
                Using cmd As OracleCommand = con.CreateCommand()

                    con.Open()

                    cmd.CommandText = "arms.aro_largeloss_pkg.getLLClaimsTest2"
                    cmd.CommandType = CommandType.StoredProcedure

                   
                    Dim matchTypeIds As OracleParameter = New OracleParameter()
                    matchTypeIds.OracleDbType = OracleDbType.Int32
                    matchTypeIds.Value = New Integer() {0, 1, 2}
                    matchTypeIds.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                    matchTypeIds.Direction = ParameterDirection.Input
                    matchTypeIds.Size = 3
                    matchTypeIds.ParameterName = "matchTypeIds"
                    cmd.Parameters.Add(matchTypeIds)

                    Dim listCursor As OracleParameter = cmd.Parameters.Add("listCursor", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.InputOutput)

                    Dim rdr As OracleDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                    'check if it has any rows

                    Dim matchTypeDesc As String
                    If rdr.HasRows Then

                        'read the first row

                        rdr.Read()

                        'extract the details

                        matchTypeDesc = rdr("match_type_desc")

                    End If

                    'clear up the resources

                    rdr.Close()


                End Using
            End Using

 

If you are using ODP via the DAAB 4.1 to accomplish what I am trying to do then please post your .net code as well as your pl/sql code so that I can get this to work.  Someone out there must have been able to do this.

Thanks in advance,

Mark

 

Jun 17, 2010 at 7:44 AM

Mark,

I'm doing this extensively in some older code based on the 3.1 library but I am 100% confident that it'll work in 4.1 and 5.0.  In order to get around the fact that the MS Ora Provider stinks and needing to have Ent Lib use ODP.NET before some of the community built ODP.NET providers actually came out, I built a very simple class that inherited from the Generic Database, but performed an override on discoverparameters and also provided some public shared function/subs to handle building PLSQL Assoc Array assignments.  It works like a charm and I'm using it for exactly the same purpose you are trying to get working above.  I don't have the code here @ home tonight but I will pull it out of a project tomorrow and post a sample of what I'm doing.

I have not tried the relatively new ODP.NET provider for the DAAB that have been contributed here on CodePlex.  I have used it, just not w/ PLSQL Assoc Array...but again, I'm sure it can be done using similar logic to what I am doing.

Again, will try and post this in the next day or so...assuming you still haven't gotten it working.

-Chris

Jun 17, 2010 at 2:19 PM

Thanks Chris.  This is still an open issue for me so I would appreciate any help you could provide me and everyone else in the same situation.  I'll look forward to reviewing your next post.

-Mark

Aug 6, 2010 at 8:36 PM
Edited Aug 6, 2010 at 8:39 PM

Mark, sent you and email, and my apologies to anyone else that might have been expecting a response to this.  Either my account in not successfully sending me updates to threads, or I forgot to check the 'email' box when I posted last.  I'm guessing it's the latter :-)

So, here goes.  YMMV here, this goes back awhile for me.  I have an OLD class that I wrote prior to EntLibContrib that handled ODP.NET as a simple db class inherited from Generic and then did the provider mapping in the configuration.  I then wrote a couple of simple methods to handle building the parameter and associating it w/ the array data.  It then all passes right up to Oracle and you can use it in your package/proc as a 'table of' in a select or join...or simply loop it if you like.

I'm sure folks much brigher than me will find a more elegant and/or full-featured solution here, and I'm sure this can probably be simplified w/ the use of the EntLibContrib ODP provider and totally eliminate the class below and possibly simplify the parameter building.  This is more to just show how I pulled it off under Ent Lib 2 and 3 back in the day...

First, the simple ODP database class that inherits from Generic...

<DatabaseAssembler(GetType(OracleOdpDatabaseAssembler))> _

Public Class OracleOdpDatabase    

Inherits GenericDatabase
    Public Sub New(ByVal connectionString As String, ByVal dbProviderFactory As DbProviderFactory)
        MyBase.New(connectionString, dbProviderFactory)
    End Sub
    Protected Overrides Sub DeriveParameters(ByVal discoveryCommand As DbCommand)
        OracleCommandBuilder.DeriveParameters(CType(discoveryCommand, OracleCommand))
    End Sub
End Class
Public Class OracleOdpDatabaseAssembler

Implements IDatabaseAssembler
    Public Function Assemble(ByVal name As String, ByVal connectionStringSettings As ConnectionStringSettings, ByVal configurationSource As IConfigurationSource) As Database Implements IDatabaseAssembler.Assemble
        Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
        Return New OracleOdpDatabase(connectionStringSettings.ConnectionString, providerFactory)
    End Function
End Class

Next, the function to build the parameter.  The DbParameter has already been created and passed in here, datatypes etc have already been set.  This simply changes the parameter to allow the array of values to be mapped into it.

 

    Public Shared Sub AssignArrayListToParameter(ByVal ValueArrayList As ArrayList, ByVal Param As DbParameter, ByVal BindSize As Integer)
        Dim rowCount As Integer = ValueArrayList.Count
        Param.CollectionType = OracleCollectionType.PLSQLAssociativeArray
        Param.Value = ValueArrayList.ToArray
        If rowCount > 0 Then
            Param.Size = rowCount
        End If
        If Param.OracleDbType = OracleDbType.Varchar2 _
        Or Param.OracleDbType = OracleDbType.Char Then
            Dim arrayBindSize(rowCount - 1) As Integer
            For i As Integer = 0 To rowCount - 1
                arrayBindSize(i) = BindSize
            Next
            Param.ArrayBindSize = arrayBindSize
        End If
    End Sub
And finally, my call to it is pretty straightforward.  The params objects I'm passing here are DbParameters, not Oracle.  I cast them inside AssignArrayListToParameter to deal w/ them as Oracle parameter objects.  The cmd here was created w/ a round trip to the database and discover parameters (notice the overload in the db class above that provides discovery for ODP since Generic does not support that).  I prefer param discovery and don't have an issue w/ the round trip, some do, either way should work.
Dim db As Database = DatabaseFactory.CreateDatabase(connectionName)
Dim cmd As DbCommand = db.GetStoredProcCommand("mypkg.myproc")            
db.DiscoverParameters(cmd)
AssignArrayListToParameter(alParam1, cmd.Parameters("p_param1"), 80) 'maps to a varchar2 field0
AssignArrayListToParameter(alParam2, cmd.Parameters("p_param2"), 2)   'maps to a varchar2 field           
AssignArrayListToParameter(alParam3, cmd.Parameters("p_param3"), Nothing)   'maps to a numeric field
db.ExecuteNonQuery(cmd)

 

Enjoy!

Looking forward to anyone who has done this or wants to take it to the next level w/ EntLibContrib (if they haven't already).  I haven't done any searching on assoc array stuff w/ the ODP.Net provider in EntLibContrib.

-Chris

Aug 9, 2010 at 2:14 PM

Thanks for getting back to me but I think I’m all set. I figured out that all I needed to do was to get a handle to the parameter object to be able to set the CollectionType. So I opened the source for EntLibContrib and added a new method called GetParameter.

Thanks,

Mark

From: cchylton [mailto:notifications@codeplex.com]
Sent: Sunday, August 08, 2010 8:28 PM
To: Mark Jirtian
Subject: Re: Trouble using ODP PLSQLAssociativeArrary parameter [entlibcontrib:211563]

From: cchylton

Mark, sent you and email, and my apologies to anyone else that might have been expecting a response to this. Either my account in not successfully sending me updates to threads, or I forgot to check the 'email' box when I posted last. I'm guessing it's the latter :-)

So, here goes. YMMV here, this goes back awhile for me. I have an OLD class that I wrote prior to EntLibContrib that handled ODP.NET as a simple db class inherited from Generic and then did the provider mapping in the configuration. I then wrote a couple of simple methods to handle building the parameter and associating it w/ the array data. It then all passes right up to Oracle and you can use it in your package/proc as a 'table of' in a select or join...or simply loop it if you like.

I'm sure folks much brigher than me will find a more elegant and/or full-featured solution here, and I'm sure this can probably be simplified w/ the use of the EntLibContrib ODP provider and totally eliminate the class below and possibly simplify the parameter building. This is more to just show how I pulled it off under Ent Lib 2 and 3 back in the day...

First, the simple ODP database class that inherits from Generic...

<DatabaseAssembler(GetType(OracleOdpDatabaseAssembler))> _

Public Class OracleOdpDatabase

Inherits GenericDatabase
Public Sub New(ByVal connectionString As String, ByVal dbProviderFactory As DbProviderFactory)
MyBase.New(connectionString, dbProviderFactory)
End Sub
Protected Overrides Sub DeriveParameters(ByVal discoveryCommand As DbCommand)
OracleCommandBuilder.DeriveParameters(CType(discoveryCommand, OracleCommand))
End Sub
End Class
Public Class OracleOdpDatabaseAssembler

Implements IDatabaseAssembler
Public Function Assemble(ByVal name As String, ByVal connectionStringSettings As ConnectionStringSettings, ByVal configurationSource As IConfigurationSource) As Database Implements IDatabaseAssembler.Assemble
Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName)
Return New OracleOdpDatabase(connectionStringSettings.ConnectionString, providerFactory)
End Function
End Class

Next, the function to build the parameter. The DbParameter has already been created and passed in here, datatypes etc have already been set. This simply changes the parameter to allow the array of values to be mapped into it.

Public Shared Sub AssignArrayListToParameter(ByVal ValueArrayList As ArrayList, ByVal Param As DbParameter, ByVal BindSize As Integer)

Dim rowCount As Integer = ValueArrayList.Count

Param.CollectionType = OracleCollectionType.PLSQLAssociativeArray

Param.Value = ValueArrayList.ToArray

If rowCount > 0 Then

Param.Size = rowCount

End If

If Param.OracleDbType = OracleDbType.Varchar2 _

Or Param.OracleDbType = OracleDbType.Char Then

Dim arrayBindSize(rowCount - 1) As Integer

For i As Integer = 0 To rowCount - 1

arrayBindSize(i) = BindSize

Next

Param.ArrayBindSize = arrayBindSize

End If

End Sub

And finally, my call to it is pretty straightforward. The params objects I'm passing here are DbParameters, not Oracle. I cast them inside AssignArrayListToParameter to deal w/ them as Oracle parameter objects. The cmd here was created w/ a round trip to the database and discover parameters (notice the overload in the db class above that provides discovery for ODP since Generic does not support that). I prefer param discovery and don't have an issue w/ the round trip, some do, either way should work.

Dim db As Database = DatabaseFactory.CreateDatabase(connectionName)

Dim cmd As DbCommand = db.GetStoredProcCommand("mypkg.myproc")

db.DiscoverParameters(cmd)

AssignArrayListToParameter(alParam1, cmd.Parameters("p_param1"), 80) 'maps to a varchar2 field0

AssignArrayListToParameter(alParam2, cmd.Parameters("p_param2"), 2) 'maps to a varchar2 field

AssignArrayListToParameter(alParam3, cmd.Parameters("p_param3"), Nothing) 'maps to a numeric field

db.ExecuteNonQuery(cmd)

Enjoy!

Looking forward to anyone who has done this or wants to take it to the next level w/ EntLibContrib (if they haven't already). I haven't done any searching on assoc array stuff w/ the ODP.Net provider in EntLibContrib.

-Chris

Read the full discussion online.

To add a post to this discussion, reply to this email (entlibcontrib@discussions.codeplex.com)

To start a new discussion for this project, email entlibcontrib@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com



The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. This message may be an attorney-client communication and/or work product and as such is privileged and confidential. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.