nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Generating report dynamically

Ronak

Hi Sir,

I got a big problem. Sending you a procedure first and elaborating the problem.

CREATE OR REPLACE FUNCTION getRepData( pvReportName Varchar2, rcList OUT XS.RC ) return varchar2 is
lvString Varchar2(1000);
iPointer Integer;
Begin

If pvReportName = 'TITLE' Then
Open rcList FOR
SELECT Key Col1,
nt.Name Col2,
'' Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM t_title a, Table(a.NAME) nt;
Elsif pvReportName = 'SALUTATION' Then
Open rcList FOR
SELECT ID Col1,
Key Col2,
nt.Name Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM T_SALUTATION a, Table(a.NAME) nt;
Elsif pvReportName = 'ENTPTYPE' Then
Open rcList FOR
SELECT ID Col1,
Key Col2,
nt.Name Col3,
Inactive Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM t_entptype a, Table(a.NAME) nt;
Elsif pvReportName = 'USER' Then
Open rcList FOR
SELECT ID Col1,
Login_Name Col2,
First_Name Col3,
Last_Name Col4,
Position Col5,
Dept Col6,
Room Col7,
Empl_no Col8,
Email Col8,
Phone Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM T_USER;
Elsif pvReportName = 'CLIENT' Then
Open rcList FOR
SELECT Key Col1,
Name Col2,
Nuts_Code Col3,
Reg_Code Col4,
Email Col5,
URL col6,
'' Col7,
'' Col8,
'' Col8,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM T_CLIENT;
Else
Open rcList FOR
SELECT '' Col1,
'' Col2,
'' Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM DUAL;
End If;
Exception
When Others Then
return Null;
End;

Now the scenario is that I have to take only one table with 20 columns.
I wants to display the report as per the parameter value.
In the procedure/function one of the parameter is pvreportname. So if the pvreportname = 'TITLE' then the specific query of it is to be fire as shown in function.

i.e if Pvreportname = 'TITLE' the report to be generated for the query--

SELECT Key Col1,
nt.Name Col2,
'' Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM t_title a, Table(a.NAME) nt;

Similarly for others like salutation and so on.
I don't know how to do
Help me in this. I am using version 3.7.

Thanks

Find more posts tagged with

Comments

Hans_vd

Hi Ronak, 
 
First of all, what is XS.RC? Is that a self defined record structure inside an package? You should be using an output parameter of type SYS_REFCURSOR. 
 
Second: did you test the stored procedure with parameter 'SALUTATION'? That query seems wrong to me. What are you trying to do in "Table(a.NAME)"? I'm aware of the Table function in Oracle, but it cannot be used like this. 
 
At last: the part 
<pre class='_prettyXprint _lang-auto _linenums:0'>Exception
When Others Then
return Null;</pre>
will "hide" any possible errors that happen in the procedure. I mean, if a query that I wrote raises an error, I would very much like to know what I've done wrong. 
So you are trying to build this report, based on a stored procedure, but if it raises an error you will never know it. Maybe an ORA error is causing the report not to work as you expect. But again, you'd never get to know it. My advice: remove those three lines from the procedure. 
 
 
As for how to create a dataset based on a stored procedure: read all about it at <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/'>http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/</a> 
 
Hope this helps 
 
Hans

Hans_vd

Also very important: you cannot use a Oracle function, you must create a procedure

Ronak

Hi sir,

Thanks for the reply. Can you just create the sample-demo example with that requirements. Currently I am not getting anything right. I have nothing in my mind with this.

Please create a simple example so I can understand it easily or mention steps of it.

Thanks,
Ronak

Ronak

HELLO,

Fine, Now I have one procedure which is as follows.

CREATE OR REPLACE PROCEDURE getRepGlobalProperties( pvReportName Varchar2, pnCols OUT Number ) AS
BEGIN

If pvReportName = 'TITLE' Then
pnCols := 2;
Elsif pvReportName = 'SALUTATION' Then
pnCols := 3;
Elsif pvReportName = 'ENTPTYPE' Then
pnCols := 4;
Elsif pvReportName = 'USER' Then
pnCols := 10;
Elsif pvReportName = 'CLIENT' Then
pnCols := 6;
Else
pnCols := 0;
End If;
Exception
WHEN OTHERS THEN
pnCols := 0;
END getRepGlobalProperties;
/

Scenario is : I agaoin have 20 columns.
In procedure I have two parameters. The first parameter is pvReportName. If pvReportName = 'TITLE' then the two coluns to be diaplayes as described as PNCOLS = 2 in procedure.

Similarly if pvReportName = 'SALUTATION' THEN out of 20 columns only 3 columns to be displayed as PNCOLS = 3 given in procedure.
Similarly for others...
How to do this.
I am using birt 3.7.
If possible then provide sample example of it.

Thanks,
Ronak

Hans_vd

Wow, now your switching to something completely different. 
 
 
As for your original question - let's get that one working first - this is how the procedure should look: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
CREATE OR REPLACE PROCEDURE getRepData( pvReportName IN Varchar2, rcList OUT SYS_REFCURSOR )
IS
lvString Varchar2(1000);
iPointer Integer;
Begin

If pvReportName = 'TITLE' Then
Open rcList FOR
SELECT Key Col1,
Name Col2,
'' Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM t_title;
Elsif pvReportName = 'SALUTATION' Then
SELECT ...
...
Else
Open rcList FOR
SELECT '' Col1,
'' Col2,
'' Col3,
'' Col4,
'' Col5,
'' Col6,
'' Col7,
'' Col8,
'' Col9,
'' Col10,
'' Col11,
'' Col12,
'' Col13,
'' Col14,
'' Col15,
'' Col16,
'' Col17,
'' Col18,
'' Col19,
'' Col20
FROM DUAL;
End If;
END;
</pre>
 
 
And please drop the EXCEPTION WHEN OTHERS part. It hides possible ORA errors and you will need to know about them.

Ronak

Hii Sir,

Now I dropped that exception. Now what shall I do
Thanks

Hans_vd

Did you also change the output parameter type to SYS_REFCURSOR?

And did you follow the link that I mentioned in one of my earlier comments? Everything to get this working is explained there step by step

Ronak

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="79585" data-time="1309857884" data-date="05 July 2011 - 02:24 AM">
Did you also change the output parameter type to SYS_REFCURSOR? 
 
And did you follow the link that I mentioned in one of my earlier comments? Everything to get this working is explained there step by step </blockquote>Hi sir, 
 
Now I did that change. What shall I do then?

Hans_vd

Now goto http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/

There is a subtitle "BIRT data set" -> start reading from there