Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Data Set - set Oracle SQL array parameter
Data Set - set Oracle SQL array parameter [message #1061855] Tue, 04 June 2013 11:40 Go to next message
Emil Moise is currently offline Emil MoiseFriend
Messages: 1
Registered: June 2013
Junior Member
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:

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;




The query that I use in my data set is something like:

select * from table(my_function(?))




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:

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);
  
}


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
Re: Data Set - set Oracle SQL array parameter [message #1062493 is a reply to message #1061855] Sat, 08 June 2013 05:29 Go to previous message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

I'm linking this post over to BIRT Exchange where we've already discussed this some:

BIRT Exchange Forums - Oracle SQL Array Parameter


Michael

Developer Evangelist, Silanis
Previous Topic:Dynamic Master page
Next Topic:BIRT ppt report with Office 2010
Goto Forum:
  


Current Time: Fri Mar 29 10:46:42 GMT 2024

Powered by FUDForum. Page generated in 0.02745 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top