Data Set - set Oracle sql array parameter

Hello, 
 
In my data set, I am trying to set an SQL query parameter of type SQL array. 
 
I have a custom type defined in the database(Oracle) and a function that receives and returns a parameter of that type: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
create or replace type my_custom_type as table of varchar2(32767);

create or replace function my_function(p_type in my_custom_type) return my_custom_type
as
...
end my_function;</pre>
 
The query that I use in my data set is something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select * from table(my_function(?))</pre>
 
 
I've created the parameter that I want to pass to the query as Java Object type, and for the default value I have the following code: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.util.logging);

try {

var conn = DBConnectionUtils.getConnection(); //database connection

var arrayDescriptor = oracle.sql.ArrayDescriptor.createDescriptor("MY_CUSTOM_TYPE", conn);

var idsArray = new java.util.Arrays.asList("0,1,2").toArray();

var arrayToPass = new oracle.sql.ARRAY(arrayDescriptor, conn, idsArray);

arrayToPass;

} catch(e) {

Logger.getAnonymousLogger().info(e);

}
</pre>
The error that I am getting in the console is : 
 
2013-06-04 12:48:41,472 ERROR [STDERR] Jun 4, 2013 12:48:41 PM org.eclipse.birt.data.engine.odaconsumer.PreparedStatement doSetObject( int, Object ) 
SEVERE: Cannot set the object value (oracle.sql.ARRAY@7ab97e44) to parameter 1. 
java.lang.UnsupportedOperationException: IQuery.setObject( int, Object ) 
 
The java code for passing an sql array to a PreparedStatement parameter via JDBC works fine. Also, if I use the query with another type of in parameter, varchar2 for example, the reports works fine, the only problem is when I'm trying to set an SQL array. 
 
Birt version - 3.7.1, java - 1.6 and Oracle - 10g. 
 
Any ideeas on how I can make this work? 
 
Regards, 
Emil

Find more posts tagged with

Comments

mwilliams

I would guess this is because the object parameter type doesn't exist. If you were to write your function query with a fixed set of values, how would it look? Have you tried passing the same as a string value or building your query in the beforeOpen script of your dataSet rather than using dataSet parameters?

emil_m

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="117343" data-time="1370411277" data-date="04 June 2013 - 10:47 PM">
I would guess this is because the object parameter type doesn't exist. If you were to write your function query with a fixed set of values, how would it look? Have you tried passing the same as a string value or building your query in the beforeOpen script of your dataSet rather than using dataSet parameters? </blockquote>
 
Hello Michael and thank you for your answer, 
 
The number of values I need to send to the database function varies so I cannot pass a fixed set. 
 
Passing as a string was causing the problem in the first place because I was sending a varchar2 parameter larger than 4000 bytes and Oracle throws an error on this so I've tried to split this string into 4000 bytes chunks in an array. 
 
The solution I am trying to implement now is involving the build of the query in the beforeOpen but I was trying to avoid this because I have to apply the solution to a large number of already existing reports. 
 
Regards, 
Emil

mwilliams

Yeah. I knew it wasn't going to work as static. I was only asking this because of use in the beforeOpen script.

Actuate BIRT has a Java Object type dataSet parameter which might help this situation, if you were using it. Otherwise, it unfortunately looks like the script way will be the way you'll have to go. You wouldn't have to build the entire query in the beforeOpen, just the object part. You could put a dummy object value in the query in the dataSet editor and then replace this dummy value in your script. That might make converting all of the reports easier than building the entire query over again.