Executing a stored procedure using javascript or java event handler

Hi All,

I have below requirement:

1) Have stored procedure testrun(in number) and it will insert into temp table
2) Design the report to show the data in temp table
3) Create the data set like select * from temp table

Question:

How can I call stored procedure before the execution of data set? I am currently using "beforeOpen" method to customize the select query but NOT finding the way to execute the stored procedure.

Here select and stoted procedure execution should be seperate; its the requirement.

Could you please help on this?

Thanks in advance,

Regards,
Athul Shetty

Find more posts tagged with

Comments

Hans_vd

<blockquote class='ipsBlockquote' >Here select and stoted procedure execution should be seperate; its the requirement.</blockquote>
What is the reason behind this? It would be much easier if you could just use a stored procedure with a refcursor as a dataset. 
 
Are you putting the data into a real temp table (scope is session)? In that case this might not even be possible. 
 
But I really want to know why there is a requirement to separate the stored procedure execution and the select?

Shetty

Hi,

We are making use of existing code which are written in oracle reports. Its not session table, its normal table only; I just mentioned as tmp table. Each time this table will be get cleared whenever we call stored procedure (procedure_name.clear()).

In detail, whenever oracle form get loaded stored procedure will get called and while rendering the oracle reports select query will be executed against the table which is populated by stored procedure. This is written in oracle reports.

Now I am thinking to use the same code for BIRT reports. So I created the data set using "select ..." query and added the table into BIRT report. Now I want to run the 1 or 2 stored procedure calls (passing arguments) before the execution of above created data set.

Please let me know if you need more info...

It would be great helpful if you guide me on this.

Thanks in advance,

Regards,
Athul Shetty

Hans_vd

Okay, I see.

Is it possible to create a new stored procedure with refcursor parameter that does nothing but call the existing stored procedures that you already have?

Shetty

Hi,

Thanks for the reply.

I think I was wrong in the previous post...sorry for that; I didn?t review the existing code correctly.
Here are the more details, so that you can suggest me correctly.

We have package which is used to do the below things:
1) cleanup procedure to close the cursor
2) set_parameters procedure to take the arguments which in turn generate the query and create the cursor
3) create the view1 from iterator --> which reads the cursor I hope which is still open
4) create the view from view1

How it got called in oracle reports:
1) In oracle form (AfterPForm trigger), we are calling cleanup and set_parameters procedures
2) In oracle form (main block), calling select * from view to show the data.
Above are the steps used to generate the oracle reports.

I think I can get the help of DBA to create the stored procedure with refcursor parameter but I want help on how to get call from BIRT and how to use the cursor in BIRT?

Is the new stored procedure should return rows from view as refcursor, so that cursor will be used to generate the reports? Please explain me how exactly it works.

Please suggest on this...

Regards,
Athul Shetty

Hans_vd

First, this is how the stored procedure should be in the database (you might want to add it to the package you already have, so that all code for this report is nicely kept together):

PROCEDURE new_db_proc (p_number IN NUMBER, p_refcur OUT SYS_REFCURSOR)
IS
BEGIN
your_package.cleanup;
your_package.set_parameters;
OPEN p_refcur FOR SELECT * FROM view;
END;

Second,
In BIRT designer you create a dataset, and where you would normally enter the query, you put this (Don't forget to add the owner, it will not work without it. I think that is a jdbc issue):
{call OWNER.YOUR_PACKAGE.NEW_DB_PROC(?,?)}

Now you have to create 2 dataset parameters. A first one for the numeric input parameter that you already have, a second one will be an ouput parameter with no datatype and that doesn't need to be linked to a report parameter.

This dataset will act just the same as any other dataset based on a query, except for that you will not see any data in the preview data grid (though you will see the column names in the header)

Success!

Shetty

Hi,

Thanks for the information.

I will work on as you suggested and let you know my progress.

Regards,
Athul Shetty

Shetty

Hi,

I loaded the stored procedure birt_report_test_1.birt_test_1() which takes one IN parameter and one OUT parameter and able to run in SQL command prompt (means able to get the output).

Now I tried to call the stored procedure in BIRT data set like below...

{call ni50.birt_report_test_1.birt_test_1(?,100)}

Here ? for output parameter and 100 is hard coded for IN parameter.

But when I try to preview getting the below error:

NOTE: Similar error got when I tried with REF CURSOR stored procedure.

Error:
Cannot get result set metadata.
SQL statement does not return ResultSet object.
SQL error: Missing IN or OUT parameter at index:: 1

Could you please help on this?

Regards,
Athul Shetty

Hans_vd

Hi Athul

I didn't mention in any of my previous posts, but when you created the dataset, did you select "SQL Stored Procedure Query" when asked for the Data Set Type?

Seems like it is not possible to change the Data Set Type, so you will have to create a new one, not just change the query text into the procedure call.

Hope this helps
Hans

Shetty

Hi,

Sorry, I forgot to select type..Thanks a lot for the quick help.

I have tested with simple and REF cursor stored procedure in data set, everything is working fine.

Thanks again for your help...

Regards,
Athul Shetty

Hans_vd

Thanks to you too.

You inspired me to write a few words on the subject on our company blog: http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/

Regards,
Hans

Shetty

Hi Hans,

I gone through your blog...very nicely you posted the information.

Regards,
Athul Shetty

Shetty

Hi Hans,

Could you please help on below items? instead of new thread I am using same thread...I hope this is ok.

1) How to do dynamic LIST box?

I have first LIST box which is populated with simple query like below

select OCN,OC_NAME from ni_operating_company (Here OC_NAME is display text and OCN is the value passed to second LIST box query)

Whenever I select first LIST box, the value (OCN) will be passed to 2nd data set query will be like below

select UTILIZATION_RULES_PK,UTILIZATION_NAME from NI_UTILIZATION_RULES where OCN=? (Here UTILIZATION_NAME is the display text and UTILIZATION_RULES_PK is the value passed to final data set query)

2nd LIST is working fine means it shows the UTILIZATION_NAME if any.

But irrespective of results from 2nd query, I need to add below entries

"Default Rule" as display text
"0" as value

In details, if we assume NI_UTILIZATION_RULES table has 2 rows like

Rule Name Rule PK
ABC 1
CBA 2

Now my 2nd LIST should show like

Default Rule
ABC
CBA

If user select ?Default Rule?, value should be 0 OR if select ABC value should be 1, etc.

How to do this one in BIRT? (We can do in SQL query using "UNION" clause" but don?t want to use)

2) What?s the best way to do the validation for BIRT parameters?

It would be helpful if you suggest on above items...

Thanks in advance,

Regards,
Athul Shetty

Hans_vd

Shetty,

1) I can't come up with a better solution than a "union all" in SQL. Actually, I can't come up with any onther solution. What is it that you don't like about the union?

2) There is the validate event on parameters, but I haven't used it that much. Do you have a specific problem that you want to solve?

Regards
Hans

Shetty

Hi Hans, 
 
Thanks for the reply. 
 
1) Due to strange requirement, like default value "Default Rule" should appear as first selection; then remaining values should come in sorting order...after using SQL UNION we could solve the problem but sorting issue didn?t solve. That's why I was thinking to do in BIRT. I thought it?s not a major issue that?s why I left it. 
 
2) As you said I used validate event for parameter, code is like below: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( params["ptest"].value > 10 )
false;
else
true;
</pre>
 
As I read somewhere, we have to return boolean values. 
 
Not getting answer for the below items: 
 
1) Now BIRT showing as exception when the value is greater than 10. How to show custom message like "Entered value is greater than 10"? 
 
2) May be NOT related but wanted to ask... parameter dialog shows "{}" before parameter name where user enter the value...how to remove or hide the "{}"? 
 
 
It would be helpful if you suggest on above items. 
 
Thanks in advance, 
 
Regards, 
Athul Shetty

Shetty

Hi, 
 
Could you please help on below query? 
 
I used validate event for parameter, code is like below: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( params["ptest"].value > 10 )
false;
else
true;
</pre>
 
As I read somewhere, we have to return boolean values. 
 
BIRT showing as exception when the value is greater than 10. How to show custom message like "Entered value is greater than 10"? 
 
It would be helpful if you suggest on above items. 
 
Thanks in advance, 
 
Regards, 
Athul Shetty