passing list parameter in store proc jrxml

0

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_date
and 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 fine
var 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.
     
chaitanya.koli's picture
Joined: Oct 3 2016 - 6:51am
Last seen: 3 years 4 months ago

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.

chaitanya.koli - 3 years 4 months ago

1 Answer:

0

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.

chaitanya.koli's picture
Joined: Oct 3 2016 - 6:51am
Last seen: 3 years 4 months ago
Feedback
randomness