| Error setting parameter to procedures with output parameter [message #1175294] |
Thu, 07 November 2013 17:02  |
 andy Moses
Messages: 26
Registered: November 2013 |
Junior Member |
|
|
Hi,
I have a question but could not find the answer after searching on the internet. Really appreciate if anyone has some quick idea.
My question is that I have a Oracle procedure with two parameters in procedure used by a birt report design file.
One parameter is input, the other is output which is the resultset returned by the procedure (using output parameter is the only way Oracle procedure to return the resultset).
In the java code where the report engine runs, it seems there were errors (listed below) when I set the parameters for the procedures and ran the task. I tried to set the parater individually using setParameterValue(), and setParameterValues(hashMap), both gave me same errors.
It seems somehow birt IRunAndRenderTask didn't recognize the output parameter for the procedure.
When I did preview in Birt report designer, and input an paramter, I was able to view the returned result on report successfully. The error happened in integration java code.
code:
IRunAndRenderTask task = engine.createRunAndRenderTask(runnable);
//1st try set parameter individually
task.setParameterValue("EMPLYEE_ID", "1918");
task.setParameterValue("PRC", ""); // There's same error no matter I set the second parameter or not
task.run();
// 2nd try try, below code gave the same error when I used a hashmap
HashMap parms = new HashMap();
parms.put("EMPLYEE_ID","1918");
task.setParameterValues(parms);
//Error displayed
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_EMPLOYEE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.executeQuery(CallStatement.java:458)
at org.eclipse.birt.report.data.oda.jdbc.CallStatement.execute(CallStatement.java:605)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaAdvancedQuery.doExecute(OdaAdvancedQuery.java:123)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaAdvancedQuery.execute(OdaAdvancedQuery.java:82)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:570)
... 55 more
CREATE OR REPLACE PROCEDURE GET_EMPLOYEE
(
EM_ID IN VARCHAR2,
prc out sys_refcursor
) AS
BEGIN
open prc for
select *
from COMPANY
where EMPLOYEE_ID = to_number(EM_ID);
END GET_EMPLOYEE;
Environment:
birt-runtime-4_3_1
[Updated on: Fri, 08 November 2013 13:18] Report message to a moderator |
|
|
|
| Re: Error setting parameter to procedures with output parameter [message #1176643 is a reply to message #1176531] |
Fri, 08 November 2013 13:12  |
andy Moses
Messages: 26
Registered: November 2013 |
Junior Member |
|
|
Thanks, donino.
I am using Oracle. Birt support sql query with procedure, but the only way oracle to return a resultset is through a output parameter. Birt does not support function (which could return a resultset) to be used for query type of "procedure".
It sounds like this is a limitation of Birt, since it listed procedure listed as a query type, but the engine API does not support setting parameter for a main database like Oracle. I hope Birt team could consider to handle this case if birt does not support this scenario.
The reason the plain sql query was not used because I saw other strange error or behavior when I put the sql with paramter in the query. The sql has multiple extractvalue function statements which extract value from xml type column. The report designer had no response when I tried to click "Ok" button so there's sth wrong when this sql is used in Birt. The same sql had no problem is executed against oracle directly. And iff there are only a couple of those statement, the Ok button worked. So I made the conclusion that Birt can not handle that sql for some reason.
[Updated on: Fri, 08 November 2013 13:26] Report message to a moderator |
|
|
|
| Re: Error setting parameter to procedures with output parameter [message #1176766 is a reply to message #1176740] |
Fri, 08 November 2013 14:41 |
andy Moses
Messages: 26
Registered: November 2013 |
Junior Member |
|
|
Thanks, donino. The birt report with procedure did worked from the beginning in the report designer GUI.
The problem I was describing was in the integration of it with Java code.
The java code use engine API to run the report design file and try to set parameter, but error occurred complaining the number of parameters passed not right. Maybe the engine API does not know how to handle the situation of setting parameter to procedure with output parameter.
[Updated on: Fri, 08 November 2013 14:41] Report message to a moderator |
|
|
| Re: Error setting parameter to procedures with output parameter [message #1176854 is a reply to message #1176766] |
Fri, 08 November 2013 15:51 |
donino donino
Messages: 183
Registered: July 2011 |
Senior Member |
|
|
ooops, ok i see. Firstly we can be sure this line is not necessary:
task.setParameterValue("PRC", "");
Of course i have not seen the .rptdesign, but i guess PRC is a dataset parameter, not a report parameter so it is not relevant to use setParameterValue here.
Furthermore, are you 100% sure this error is really due to the output parameter? as you set the employee_id parameter, it should be declared as a String in the report design. It's easy to make a mistake with different places where we have to declare the datatype (in report parameter, dataset parameter, stored procedure, database schema...). You should do a test without any parameter, except the output param to be sure.
|
|
|
|
|
|
| Re: Error setting parameter to procedures with output parameter [message #1181465 is a reply to message #1181438] |
Mon, 11 November 2013 16:20 |
andy Moses
Messages: 26
Registered: November 2013 |
Junior Member |
|
|
donino, I will consider to upload my rpt design file later when I get chance.
About function, yes, I tried.
First, Birt does not allow me to run oracle functions which return resultset like calling a procedure for data set with type of store procedure.
So I have to create data set of normal query type. Even I created a function which returns a sys_refcursor (which is a pointer to a compiled sql), the oracle can only run the function inside a select statement querying from a dummy table "dual" like below:
select getresult(?) from dual
The returned resulset of above sql has only one column, instead of multiple columns defined in the select statement in the function. I didn't find out how birt or sql parse out the resultset returned by the function., since I got an "Cannot get the result set metadata" error org.eclipse.birt.data.engine.odaconsumer.OdaDataException.
Then what I did is to define the function to return only value which is a comma delimited string. Then in Birt, I split and parse out the values from the result and create multiple calculated columns.
This is not straightforward, but could be an sub-optimal option for me.
[Updated on: Mon, 11 November 2013 16:29] Report message to a moderator |
|
|
|
Powered by
FUDForum. Page generated in 0.03911 seconds
] 
Search
Help
Register
Login
Home
