vhp1990 Posted June 19, 2015 Share Posted June 19, 2015 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 Link to comment Share on other sites More sharing options...
davidiazcasta Posted June 22, 2015 Share Posted June 22, 2015 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}) } [/code]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[{CALLschema.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"/>[/code] Now, you can use any ORACLE procedures. Link to comment Share on other sites More sharing options...
rajesh.sirsikar Posted June 23, 2015 Share Posted June 23, 2015 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 Link to comment Share on other sites More sharing options...
vhp1990 Posted June 24, 2015 Author Share Posted June 24, 2015 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now