Discussions
Categories
Choose a Product
THRUST SERVICES
CORE APPS
CE PRODUCTS
...
Quick Links
POPULAR
HELPFUL TIPS
Groups
My Links
FOR SIGNED IN MEMBERS:
Back to website
Home
Designing Analytics Reports
Data Set - set Oracle sql array parameter
Migrateduser
Hello,<br />
<br />
In my data set, I am trying to set an SQL query parameter of type SQL array.<br />
<br />
I have a custom type defined in the database(Oracle) and a function that receives and returns a parameter of that type:<br />
<br />
<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>
<br />
The query that I use in my data set is something like:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>select * from table(my_function(?))</pre>
<br />
<br />
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:<br />
<br />
<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 :<br />
<br />
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 )<br />
SEVERE: Cannot set the object value (oracle.sql.ARRAY@7ab97e44) to parameter 1.<br />
java.lang.UnsupportedOperationException: IQuery.setObject( int, Object )<br />
<br />
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.<br />
<br />
Birt version - 3.7.1, java - 1.6 and Oracle - 10g.<br />
<br />
Any ideeas on how I can make this work?<br />
<br />
Regards,<br />
Emil
Find more posts tagged with
Comments
There are no comments yet