| Error setting parameter to procedures with output parameter [message #1175294] |
Thu, 07 November 2013 12:02  |
Eclipse User |
|
|
|
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 08:18] by Moderator |
|
|
|
|
|
|
| Re: Error setting parameter to procedures with output parameter [message #1176854 is a reply to message #1176766] |
Fri, 08 November 2013 10:51  |
| Eclipse User |
|
|
|
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 11:20 |
| Eclipse User |
|
|
|
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 11:29] by Moderator |
|
|
| Re: Error setting parameter to procedures with output parameter [message #1231493 is a reply to message #1181465] |
Tue, 14 January 2014 15:30 |
| Eclipse User |
|
|
|
donino, finally I found out you were right. it's my fault that caused the procedure call not working.
The parameter name for birt "Report Parameters", and parameter name for Data Set's "parameters" confused me.
The java code need to use "Report Parameters"'s parameter name to pass parameter value, not the name defined in "parameters" in Data Set for procedure parameter name.
This issue was found soon after my last message above. Now I am just update this post in case other new birt users had similar confusion.
|
|
|
Powered by
FUDForum. Page generated in 0.08492 seconds
Home