Passing and working with Arrays? 1

[ThatRickGuy]

Hi Guys,
I have an GUI with some highly specific search settings. The users can specifically select which elements they want to see (for instance, show me all of the data related to Elements 1, 2, 5 and 15.) The elements they are selecting are dynamic and change based on which set of data they are looking at. I can get the keys for those elements and pass them into a package, but I'd really prefer to pass them in as an array.

I could create a load of parameters and just pass them in individually and default the unused ones, but I'm looking at 40+ possible elements in some cases (and the search's complexity builds as there are 5 different sets of elements they can select in this way).

I could also create a string that contains a delimited list of values, but this seems like an overly complex way to get an array of IDs.

If I could take an array as a parameter, and cram it into a temp table, I think that would work well as I could just join my query onto that temp table. But my lack of Oracle knowledge is giving me a hard time on this one.

I poked around on Tom's for a while, but everything he talks about there seems to focus on Java and binding, which doesn't help me much as I'm using VB.Net for my front end and DAL.

Any help would be appreciated. Thanks!
-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Thargy]

Rick,

I haven't done VB for years, but I have recently come across a stored procedure with no less than 35 parameters. Needless to say, this was a nightmare to handle.

I'd suggest that you define an Oracle type, containing appropriate data types for each of the 40 parameters. Then in VB create such a type, and pass that to the stored procedure. Individually coding 35 parameters is too cumbersome for words.

Also, if you do need to change parameters, then a simple alteration of the type definition will do the trick, instead of hacking code. I'd suggest you consult with some VB gurus about passing types, but I don't believe that it's too hard.

If you like, I could ask the guys at work tomorrow.

Regards

Tharg

Grinding away at things Oracular

 

[ThatRickGuy]

All of the parameters should be of the same type. The problem is the scaling...

The user can select as many Modules, Forms, Groups, Assessments, etc... as there are available. In all of these cases, I have the PK for each entity (Module_ID, Form_ID, etc...) But the user has the ability to select 10+ modules, 40+ forms, 5+ groups, and 20+ assessments. Everyone one of those parameters would need to be passed to the database as integers. Coupled on to that is also the ability to filter by dates, CallerID (a meaningful numeric value) range, and a number of other filterable fields.

To make it even more grandios, the filter has to be applied on 5 different levels of a tree view, where the data is pulled back only for the selected node.

So it's not so much the data type that is an issue, as much as having 5 different packages that each have a procedure with ~100 parameters and huge where clauses.

Right now, I could write a proc like:

 usp_TableName_Select(p_Results OUT Cur, p_Module_ID1, p_Module_ID2, p_Module_ID3...)

OPEN p_Results FOR
SELECT ...
FROM ...
WHERE Module_ID = p_Module_ID1
   OR Module_ID = p_Module_ID2
   OR Module_ID = p_Module_ID3
...

which isn't that bad to write. Most just a lot of copy and paste. And passing the parameters isn't that bad, I can handle it all through loops on the code side.

But it just seems like there has to be a better way than to set up the procs with some insane number of parameters hoping that the users never have an option to select more than the arbitrary numbers I pick out now.

Also, I'm not sure if Oracle short circuits the logical operators. For instance, in an OR operator, if the first evaluation is true, there is no need to check the 2nd evaluation. And in a case like this, where there are going to be 100 or more evaluations, shorting the OR's would be pretty important. But that would still leave the worst possible case of having to check every single evaluation before determining that there is no match.

Thus my desire to cram all the values into a temp table. Unfortunately, I'm having a heck of a time wrapping my brain around how to do so.

Any thoughts?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jimirvine]

You were right to look at Tom's site. You just missed what you actually needed tho'. Varying In List was the required search term:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

 

[Thargy]

Rick,

this sounds like a "Query by Form" situation - is that the case? Normally, I'd suggest a standard routine which loops round every control on the form, gets its settings and then passes these as parameters to the database. However, your discussion (correctly IMHO) precludes this approach.

Therefore, why not create some global temporary tables (GTT's), one for each class of parameter, e.g. rick_module, rick_form, rick_group, rick_assessment etc. GTT's automatically disappear when the session ends - so no housekeeping is needed, Oracle will do that for you.

Mod your routine to loop round the form, get all of the modules say, and then do an insert of all the values into rick_module. Repeat for each parameter type.

Write a PL/SQL routine which will inspect all of the values in the tables (regardless of how many there happen to be), and use them to build a query with all the necessary predicates. Then run the query and return the ref_cursor result.

I think this would be an inherent win, because your original post said

I can get the keys for those elements and pass them into a package

which implies that you're querying the db based on each parameter, to find the corresponding primary key. This must be at least one round trip to the db.

By putting the data straight into the db, you can let it find the keys (if necessary) and just handle them internally. This must surely be quicker than extraction, transition across a network to the client, and then reinsertion as parameters.

Is this the sort of thing you had in mind, or am I wandering from your requirements?

Regards

Tharg

Grinding away at things Oracular

 

[ThatRickGuy]

Thanks Jim, that's probably what I'll wind up doing. Tom's has a lot of great info, but searching for Array info just kept bringing me back to Java and Array Binding (which would be horrendous for this!)

Tharg, close. I have a tree view, that tree view has five levels. Each level is retrieved for only the selected node when that node is selected. That means that I have to apply the filter to each dataset returned for each tree view selection. The filter is comprised of 10+ fields, some of which are multiple boolean selects. While populating the interface I retain a copy of the data object for each filter option. This gives me the ability to provide in-depth information in tool tips, and to get the ID of any selectable element.

So 1 query to populate each filter segment, and 1 query to populate each tree view selection.

All of the parameter compilation is done via loops in the GUI layer, since I can loop through the checked items collections of the various controls I'm working with, and each of those items is tied to a data object. So for as much as is going on, the volume of calls to the database is pretty light.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.