chaitanya.koli Posted October 28, 2016 Share Posted October 28, 2016 Hi,how can I pass list parameter in store procedure in jrxml?Here is my oracle SP query sample-create or replace PROCEDURE PROC_SCM_MIS_TEMP1 ( p_from_date in date, p_to_date in date,dely_gb_param in dely_gb_tbl,state_param in state_tbl, SCM_CUR OUT SYS_REFCURSOR) as begin insert into TSCM_MIS_TEMP select (select NVL(MAX(TSCM_MIS_TEMP.PID),0)+1 from TSCM_MIS_TEMP) as PID, a.DOCKET_NO as AWB_NO, a.OUT_CLOSE_DATE as INV_ORDER_DATE, TCODE_NAME('L021', a.WB_GB) ORDER_TYPE, b.ORDER_NO, TCODE_NAME('J004', a.wb_proc) Status, ORDER_D_SEQ, ORDER_G_SEQ, a.DELY_GB, null DR_COMPLETION from twaybillm a LEFT JOIN twaybilld b on a.wb_I_no=b.wb_I_no LEFT JOIN twarehouse c on a.WH_CODE=c.wh_code where a.dely_gb<>'53' and a.OUT_CLOSE_DATE>=p_from_date and a.OUT_CLOSE_DATE<p_to_dateand a.dely_gb in (select * from table(dely_gb_param))and f.CITY_NAME in (select * from table(state_param)); When I am executing above SP in oracle tool its work finevar c refcursor;execute proc_scm_mis_temp1(to_date('01-10-2016 00:00:00','dd-mm-yyyy hh24:mi:ss'),to_date('01-10-2016 23:59:59','dd-mm-yyyy hh24:mi:ss'),new dely_gb_tbl(18,40),new state_tbl('MAHARASHTRA'),:c)PRINT c; Its works fine even in jasper Report when I am using last 2 parameter as a String.{call proc_scm_mis_temp1(TO_CHAR($P{IO_DATE_FROM}, 'DD-MON-YY'),TO_CHAR($P{IO_DATE_TO}, 'DD-MON-YY'),new dely_gb_tbl($P!{DELAY_GB_PARAM}),$P!{STATE_PARAM},$P{ORACLE_REF_CURSOR})} But I need $P!{DELAY_GB_PARAM} and $P!{STATE_PARAM} in a list/Collection type instead of String.As I need to use further in jasper server. Thanks. Link to comment Share on other sites More sharing options...
chaitanya.koli Posted November 3, 2016 Author Share Posted November 3, 2016 Hi,Not yet got any solution.Can I use above parameter in jasper server? As I need list parameter instead of type string parameter. Or is there is any other way that I can change store procedure parameter so that I can use list parameter?Thanks. Link to comment Share on other sites More sharing options...
chaitanya.koli Posted November 14, 2016 Author Share Posted November 14, 2016 Hi All,I get the solution, Actually In jasper Server i mismatch Input paramete of jasper server and iReport.After keeping same parameter name ,Its working fine for me. 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