If this is your first visit, be sure to
check out the
FAQ
by clicking the
link above. You may have to
register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
When you are executing SQl that includes an IN clause, is it possible to specify the values range in an OracleParameter (or SQLParameter) in your code or do you have to artificially create a new parameter for ewach member of the IN subclase?
e.g. Suppose the SQL reads like:
Code:
SELECT * FROM Currencies
WHERE Currency_Code IN ('EUR', 'USD')
can you do something in .NET like:
Code:
SELECT * FROM Currencies
WHERE Currency_Code IN ( :currencies )
And create an OracleParameter that has both values in?
Unfortunately extra parameters are needed, unless you insert the values into a table instead (which will generally be much more effort!).
The good news is that you can add parameters in a loop, so it doesn't take much extra code.
"Oracle Collections can be represented as an array of .NET Types. For example, an Oracle Collection type of NUMBER can be mapped to an int[]." --
http://download.oracle.com/docs/html...1/featUDTs.htm
You need to create a collection type that is executable and can be referenced by public
CREATE OR REPLACE
TYPE VARCHAR2_4K_COLLTYP
AS TABLE OF VARCHAR2(4000)
Above is equivalent to a "string array", such as in
SELECT * FROM TABLE(varchar2_4k_colltyp('USD','EUR'))
Up to you how you will pass parameters to the TABLE() function. You can create a stored procedure (overloaded in a package) to accept various number of parameters and returns varchar2_4k_colltyp but you need to overload everytime you want to support more parameters, e.g.
Code:
package.proc_to_table(:p1) returns varchar2_4k_colltyp
package.proc_to_table(:p1, :p2) returns varchar2_4k_colltyp
package.proc_to_table(:p1, :p2, :p3) returns varchar2_4k_colltyp
Another way is to leverage fact that oracle collections can be represented as an array of Net types, but I don't have a sample. Pass Net array to oracle procedure which returns a SYS_REFCURSOR if you need to return a resultset. Inside the procedure, SELECT * FROM TABLE(p_passed_net_array AS varchar2_4k_colltyp) is assigned to a cursor, hence SYS_REFCURSOR return type of procedure.
Creating simple collection types for NUMBER, VARCHAR2, and DATE doesn't take much effort and they can be reused elsewhere, e.g. bulk DML operations, Java/NET stored procedures (I created a Java stored proc that retrieves list of files from path passed to proc, this is used by scheduled DB jobs that process files; read, write, rename/move, zip all via DB).
Check out documentation on NET stored procedures for Oracle for sample on passing collection type and to confirm data type mappings.
Advertiser Disclosure:
Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
