Hello,
I have a stored procedure my_sp which takes in two parameters, first an integer and then a date. That is,
EXEC my_sp 265522,'6-10-15'
I have been trying to design a report based on the result set from this procedure in Jaspersoft Studio 5.6. The report does not generate or atleast takes way too long if I use the two parameters though the output data consists of only 25 rows. I am using this query,
exec my_sp $P{param1} , $P{param2}
and feel the probelm is with the syntax only. Also the same query works perfect when I had tried it in Sql Server Management Studio and in jaspersoft studio, stored procedures with a single parameter work like magic. Kindly help me out on this
1 Answer:
Posted on June 22, 2015 at 7:53am
Hi everyone!
You can call the procedure when you adding a dataset with PL/SQL text code.
{ CALL schema.NAME_SP($P{param1} , $P{param2} ,$P{ORACLE_REF_CURSOR}) }
the param Oracle_ref_cursor contains the params of your response call, and you must create the fields. the field name must be the same procedure to set your params return like that:
<queryString language="plsql"> <![CDATA[{CALL schema.NAME_SP($P{param1} , $P{param2} ,$P{ORACLE_REF_CURSOR})]]> </queryString> <field name="response1" class="java.lang.String"/> <field name="response2" class="java.lang.String"/>
Now, you can use any ORACLE procedures.
Hi vhp1990,
Your syntax for runningSP with multiple parameter is correct. But the issue can be at the format of teh parameter field created.
Can you confirm you have created param1 as Integer and Param2 as DateTime?
Thanks,
Rajesh S
Hello Rajesh. Yes the problem was with the parameter. Since I was trying to format the parameter before passing it, it was throwing up an error. Thanks a lot.