how to use stored procedure in iReport

[ktrinad]

By: Dhananjoy Kumar Singh - dhananjoysingh

how to use stored procedure in iReport

2003-10-20 00:57

do anybody know how to use stored procedure in ireport.

my report reqiures a stored procedure to be called in stead of a simple query...

 

can anybody suggest , how to go abt this

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: how to use stored procedure in iReport

2003-10-20 13:25

Write a user defined function instead. You would return a table instead of a value. To call/use the function;

 

select * from dbo.functionname

 

I know this works in MS SQL2000. Don't know about other SQL flavors.

 

 

 

 

By: Giulio Toffoli - gt78

RE: how to use stored procedure in iReport

2003-10-21 04:22

 

Use a custom datasource and populate it by hand reading values from your store procedure.

 

Giulio

 

 

 

 

By: Dhananjoy Kumar Singh - dhananjoysingh

RE: how to use stored procedure in iReport

2003-10-21 01:22

i m using ORACLE 9i

 

I TRIED UR SUGGESTION

 

it is giving error

----"ORA-04044: procedure,function,package,or type is not allowed here"

 

do u know some other way MARK

 

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: how to use stored procedure in iReport

2003-10-21 09:30

Not familiar with Oracle. Surprised it can't do user defined functions. Might try an Oracle forum for a better answer and/or try Guilio suggestion.

 

 

 

 

By: Dhananjoy Kumar Singh - dhananjoysingh

RE: how to use stored procedure in iReport

2003-10-23 04:27

hi mark and Giulio

i think u suggested the right thing as to use function instead of Stored procedure(SP), so i might change the SP to function that might work but the

problems are

 

1) i have to pass some parameters/values in the SP/Function manually

that is also cusing some problem, as to how to pass values/parameter in SP/function manually

 

2) there are 3 queries in the SP that i m using. first one is using the parameters passed by me and returning 3 values

 

and then using those three values third query is giving me a set of values that i m interested in.

 

i m pasting the SP that i m using for desired result,

if u know SP in oracle,u will easily undersatnd what i want to do..

 

 

 

here goes the procedure(SP)

see if can help me.

 

 

 

CREATE OR REPLACE PROCEDURE SP_RPT_MSO_MONTHLY

(Dsr_Cursor IN OUT Refcur_Pkg.T_Refcur,

Broadcasterdesc IN VARCHAR2,

PrdStDt IN Date,

PrdEndDt IN Date )

AS

BEGIN

DECLARE

brdcstid NUMBER(10);

v_cycleno NUMBER(3);

v_periodid NUMBER(10);

BEGIN

SELECT broadcasterid into brdcstid

FROM SA_BROADCASTERMASTER

WHERE DESCRIPTION = Broadcasterdesc ;

select sa_billcycleperiod.cycleno,sa_billcycleperiod.periodid

into v_cycleno,v_periodid

from sa_billcycleperiod,

sa_billperiod

where sa_billcycleperiod.periodid = sa_billperiod.periodid

and sa_billperiod.startdate = to_date(to_char(PrdStDt,'DD-MON-YYYY'),'DD-MON-YYYY')

and sa_billperiod.enddate = to_date(to_char(PrdEndDt,'DD-MON-YYYY'),'DD-MON-YYYY');

 

OPEN dsr_cursor FOR

select a.ssn Ccode,

c.equipmentid STBNo,

a.firstname||' '||a.lastname||','||a.address1||' '||a.address2||','||f.cityname||','||e.statename Name_Address,

'Residential' Type_Of_Customer,

a.address3 SubOperCode,

decode(b.productlineid,19,'Analog',20,'Digital','Analog') Plan_Type,

'Monthly' Pay_Freq,

Substr(g.productname,1,20) Product,

d.startdate Plan_St_Date,

nvl(d.stopdate,(to_date('12/31/99','mm/dd/yy'))) Plan_Stop_Date ,InvAmtTab.DUEDATE DueDt,

Amt ,

v_cycleno Bill_CycleNo,

v_periodid Period_Id , Broadcasterdesc broadcasterdesc ,brdcstid brdcstid

from cc_account a, cc_accountservice b, cc_serviceequipment c, cc_serviceplanproduct d,

sa_states e, sa_city f,pk_product g,

( select Ccode, product, DUEDATE, sum(InvAmt) Amt

from ( select a.chargeid,a.chargename,a.invoicedate invdt,a.chargeamount InvAmt,

substr(b.productname,1,20) product,d.ssn Ccode,e.duedt DUEDATE ,

e.billcycleno

from ap_invoicedetails a, pk_productrc c, pk_product b,cc_account d,ap_invoice e

where a.chargeid = c.recurringchargeid

and c.productid = b.productid

and b.field3 = to_char(brdcstid) --6 -- 4

and a.invoiceid = e.invoiceid

and e.accountno = d.accountno

and e.billcycleno = v_cycleno

and a.invoicedate between PrdStDt - 1 and PrdEndDt + 1 )

group by Ccode,product,DUEDATE ) InvAmtTab

where a.accountno = b.accountno

and b.serviceno = c.serviceno

and b.serviceno = d.serviceno

and a.stateid = e.stateid

and a.cityid = f.cityid

and d.productid = g.productid

and ((d.startdate <= PrdStDt ) and nvl(d.stopdate, to_date ('12/31/99','mm/dd/yy')) >= PrdEndDt)

and g.field3 = to_char(brdcstid)

and a.ssn = InvAmtTab.Ccode

and g.productname=InvAmtTab.product

order by Ccode;

END;

END SP_RPT_MSO_MONTHLY;

/

 

 

 

 

 

By: Dhananjoy Kumar Singh - dhananjoysingh

RE: how to use stored procedure in iReport

2003-10-29 00:22

hi

Giulio

can u please elabotate how to implement ur suggestion.

 

i.e,...

 

Use a custom datasource and populate it by hand reading values from your store procedure.

 

i want to try this

 

Dhananjoy

[jcvlad]

Hello,

I know this question has been sent a long time ago lol but I can give an answer that will be useful for all generations !!!

In the QueryDesigner of iReports, choose "PLSQL" as Query Language. Then write your call to procedure/function like this :

SELECT My_Function_Name($P{My_Parametre_1}, ..., $P{My_Parametre_N})
or
CALL My_Procedure_Name($P{My_Parametre_1}, ..., $P{My_Parametre_N})

I've already tried it and it's OK. Hopefully it will help someone...

Post Edited by jcvlad at 01/25/2011 15:47

[wqiao]

Hello,

I'm new here, really need some help.
Could you please let me know what your function returns?

My function returns a sys_ref_cursor. Will it work?
In the Report query window, I entered "select myfunction(...) from dual", after I click the button "Read Fields", it gives me one field of type OracleResultSet.
How do I get the fields/columns in the result set so I can use in the report design?

B.T.W. The syntax "call myfunction(...)" doesn't work for me.

Thank you.

[tytok]

if you have a stored procedure  in your sql ( like sqlserver)  and the stored procedure  name is " SP_DMTOVERTIME_STAT "

so you can select sql and use the  sql command  "exec  stored prosedure name" like this exec SP_DMTOVERTIME_STAT '2010-11-05','2011-03-05'  in iReport.

 

please try it,

 

 

 

 

 

[wqiao]

Thank you very much for your reply.
Since the one I'm testing is a function, exec syntax doesn't work.

If I change the function to a stored procedure, with output parameter of a cursor. Then how do I use the cursor result set? Is there a way to do it in iReport like in Actuate e.ReportDesigner. Acutate automatically maps each column in a result set to a different field.

For example, myStoredProc has an input parameter "myid", an output cursor parameter "myrecord". How do I use it in iReport Designer?

[jasperian]

Hello,

This did not work for me.Can u tel me only the change of Query language and call to procedure with parameters is enough or should i do anything prior to this.

Thanks in advance :)

[wqiao]

What I did was: I created all the fields first. And then go to Report Query, select "plsql". Use below as query:

{call my_stored_proc($P{parameter1},$P{ORACLE_REF_CURSOR})}

If you have a lot of fields in the output like I did, you can use a trick to create the fields. Choose "SQL" first in Report Query window, and wirte a dummy select statment with all the fields you need from the tables (the same as the columns in the stored proc output cursor.) Then click "Read Fields". That will create all the fields you need. And then you change query language to "plsql", and call the stored proc as above.