In my previous article I have very briefly explained how PLSQL has been supported in jasperreports. But it is not that easy when it comes to different databases. Here I will present how we can call stored procedure from jasperreports. For this sample I have used iReport 4.1.2 designer and Oracle database, it is supported in previous versions as well, may be with minor changes.
Well to begin with we need to create a procedure. That returns cursor. Here I am using a simple procedure with sample database available in oracle (HR).
Employee table
Procedure
Now follow steps to implement stored procedure in jasper report.
1. Add PLSQL executer to jasperreports.properties.
Select Menu Tools>>options>>query Executers
Find that below entry is added in the properties, if you are using latest version it will be already there for older version you need to add it separately. Please note that for this you need a jar jasperreports-extensions-3.5.3.jar which will be available in 4.1.2 version.
Language =plsql
Factory Class =com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory
Fields Provider Class =com.jaspersoft.ireport.designer.data.fieldsproviders.SQLFieldsProvider
3. Make sure that you have created oracle datasource connection available.
4. Open report Query , select Query Language = “plsql”
{call emplist_proc($P{ORACLE_REF_CURSOR} ) }
If you have your procedure accepting more input parameters you can have them passed as parameters.
6. If you notice that parameter “ORACLE_REF_CURSOR” is available in Available Parameter list we need to pass it as our OUT parameter for the stored procedure which will return cursor. Also note that if you are using old versions you can create your own parameter name “cursor” with datatype as “java.sql.ResultSet” and also make sure “use for prompt” is not selected.7. Read fields may not work in some cases but its ok. You can manually add them if do not work.
8. Place the fields and you are done !.
9. Click on preview and you will see that your report is generated.
10. lastly, if possible before closing, click on add to support me. Thanks
Hi!
ReplyDeleteThank you for very useful information and exact description, it works.
But step 7, I think this is a disadvantage of IReport. It is very inconvenient.
Regards, Stanislav.
hi,
ReplyDeleteI am using iRpeort 3.7.0 and id set all as per your above documents. but when i checked preview receive following error
net.sf.jasperreports.engine.JRException: OUT paramater used in non-stored procedure call : ORACLE_REF_CURSOR class java.sql.ResultSet
Please suggest
Hi,
ReplyDeleteThank you for the article.
But i'v got an error when read fields Error:SQL problems:Invalid column type:2000. I skip it, as you say.
And when i set the name of the column manually, i get an error - field not fount
Thank you Totonn,
ReplyDeleteField not found is a common error you get in queries, some times it happens that we give expressions in query for which alias is not given. For this I will suggest you try to run your stored procedure on sql prompt first make sure its working check the column names what it throws in output and try to set exactly same. I am sure that will resolve it.
Hi,
ReplyDeleteI had mentioned in my post that stored procedure call should be in curly braces i.e. as {call storedproc_name() }
so if it do not start with this then it will give following error
net.sf.jasperreports.engine.JRException: OUT paramater used in non-stored procedure call : ORACLE_REF_CURSOR class java.sql.ResultSet
Thank you very much ! This is a great instruction. It worked well for me with one Out parameter for resultset cursor(after I got around the quirks in I-Report). I'd like to add some IN parameters to the stored procedure and I can't seem to get it to work. This is the syntax I am using in I-Report...I am doing something wrong? I get a Fill Error.
ReplyDeleteThank you in advance !
{call PKG_QUERY.Prc_Test_Select_Users_By_FName($P{pin_firstname}$P{ORACLE_REF_CURSOR}) }
Thanks for comment.
ReplyDeleteif I am not wrong, you missed comma between two parameters that you are passing.
it should be
{call PKG_QUERY.Prc_Test_Select_Users_By_FName($P{pin_firstname}, $P{ORACLE_REF_CURSOR}) }
otherwise it should work fine
Thank you very much. I thought I had tried that, but apparently not with the correct order of the parameters. Thanks so much. This tech note really helped a lot.
ReplyDeleteHello,
ReplyDeleteVery nice guide.
I have a simple question. What do you do to see java.sql.ResultSet in the list of types of IReport 4.2. (I am not able to see it)
regards
Hi Thank you please note that in old version, I mentioned that you need to create your own parameter as name "cursor" and set its datatype as "java.sql.ResultSet" just like any other normal parameter.
ReplyDeleteThanks. My issue was because the report was setted in groovy and not java. Now the ORACLE_REF_CURSOR field is present. But when I'm making the test following your test, the result is field not found (in the report Query the fields aren't retrieved whereas the number of records is found (data not showing)). Any idea?
ReplyDeleteRegards
I'm developing an application in Eclipse with Java. When I try to export a report using a stored procedure I get this error:
ReplyDeletenet.sf.jasperreports.engine.JRRuntimeException: No query executer factory registered for the 'plsql' language.
Can you help me with this, please?
Hi. please see carefully that jasper will need a factory class to execute plsql query in my 1st step ("Add PLSQL executor to jasperreports.properties. ")I have mentioned to include factory class.
ReplyDeletesince you are doing this through program you need to add this property in jasperreports.properties and this properties file should be locatable in your class path.
its just a classpath problem I feel that you need to take care of.
Hi, I am developing a project in Jdeveloper using adf .when I try to export the report , I am getting the same exception net.sf.jasperreports.engine.JRRuntimeException: No query executer factory registered for the 'plsql' language.
ReplyDeleteI have added the jarjasperreports-extensions-3.5.3.jar in my project's lib folder .
please tell me how I can add jasperreports.properties file in my project and add the property ,so that it can be located in classpath while buiding an EAR.
Please reply immidiately . its urgent we need to EAR at client side.
I hope you are developing web based application.
ReplyDeleteyou just need to place jasperreports.properties file in your web-inf/classes or web-inf/lib folder for it identified by classpath. also note that default properties file will be available in jasperreports-x.x.x.jar
which you need to extract and add property in it to override.
I hope you are developing web based application.
ReplyDeleteyou just need to place jasperreports.properties file in your web-inf/classes or web-inf/lib folder for it identified by classpath. also note that default properties file will be available in jasperreports-x.x.x.jar
which you need to extract and add property in it to override.
Hi, I am using iReport 4.5. and Oracle. I want the sequence of parameter which has to be passed to stored procedure call. I can see the list of stored procedure and their in and out parameters but I don't know the sequence. plz tell me how to get that..
ReplyDeleteOne more thing the database is on server. after making the connection the property named as "Procedure callable" is unchecked.
Is it required to be checked. If yes then how can i achieve that.
Thanks in advance.
well I have created this post using ireport 4.1.2 and you are using 4.5 it seems that there is UI change. give me some time I will check and update you, since i need to download and check it.
ReplyDeleteRajendra,
Deletei have created one stored procedure that is shown in below
create or replace
procedure emp_list(emp_cursor OUT sys_refcursor) is
begin
open emp_cursor for
select name,group_id
from ADM_MODULES;
END;
i tried to call from ireport using this query
{call emp_list($P{ORACLE_REF_CURSOR} ) }
but every time i got same error that is "sql error invalid column type".
Can u pls help me ...hope u r reply....
Regards
Manu
Hi Sorry for delay... actually I am on tour so could not access my emails on time.
DeletePlease send me (desc adm_modules) also your jrxml of report my email id is below. rajendra.tambat@gmail.com
i have the same error, use IREPORT 4.6.0 and ORACLE 10g, please help me!
Deletecreate or replace PROCEDURE sp_lista_almacen(almcursor OUT sys_refcursor) IS
BEGIN
OPEN almcursor FOR
SELECT id_almacen, descripcion FROM ALMACEN;
END;
{call sp_lista_almacen($P{ORACLE_REF_CURSOR} ) }
Error: "sql error invalid column type".
the language is plsql at the report and query :)
DeleteHi, can you please tell me exacly where are you getting this error at execution of report or while reading field, as I have mentioned that while reading fields sometimes it gives error but still you can go ahead and put your fields mannually. also confirm me that your proc. returns same column names as you defined in your report. if possible send me log exception. on my mail id. I would glad to help you. :)
DeleteI changed the procedure and i used the function and shows the number of rows because the procedure shows nothing...
Deletethis is the image:
https://lh5.googleusercontent.com/-C42IG_oVmsA/UA7SjIwyOkI/AAAAAAAAAIc/VL9yS-q2KqY/s720/Error%2520ireport.png
Rajendra,
ReplyDeleteThanks a lot, I had the same problem "No query executer factory registered for the 'plsql' language"
I added a factory class to execute plsql (into jasperreports.properties)... and work perfectly
Thanks again
Hi Thank you for your note, it really help, I manage to create one but for the other one it keep gives me the following erro, please advise:
ReplyDeletejava.sql.SQLException: ORA-06550: line 1, column 27: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod not range rem => .. <> or != or ~= >= <= <> and or like between || indicator The symbol ")" was substituted for ";" to continue.
Thank you.
It seems that there may be some special char. in your data column, Please check so also send me your query string and col names that you are putting in your columns.
DeleteHope you are able to run your SP from sqlPrompt. Please do so
and verify data once.
From the error it seems that its a oracle exception nothing to do with report. so make sure your SP runs properly with entered IN Paramas.
Excellent Article! But, in my organization there are lot of stored functions that return cursor.
ReplyDeleteCREATE OR REPLACE PACKAGE BODY PKG_TEST_REPORTER
AS
FUNCTION EmpDept(
EMP_ID IN SCOTT.EMP.EMPNO%TYPE)
RETURN SYS_REFCURSOR
IS
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT
EMP.EMPNO AS "EMPLOYEE_ID",
EMP.ENAME AS "NAME",
DEPT.DNAME AS "DEPARTMENT"
FROM
SCOTT.EMP EMP, SCOTT.DEPT DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = EMP_ID;
RETURN CUR;
END EmpDept;
END PKG_TEST_REPORTER;
I tried using
{ $P{ORACLE_REF_CURSOR} = call pkg_test_reporter.empdept(7782)}
in reporter but does not work. Any ideas please?
as per syntax what you tried is wrong way hope
Delete{call pkg_test_reporter.empdept($P{deptno})} or
{call pkg_test_reporter.empdept(7782)} should work...
Please check once again. also check if you have given proper permission for proc./func to execute.
also let me know what error you get when you try to place above mentioned statement for calling.
regards
Wow, This is an excellent article. The issue i am facing is not related to the topic but i might have to take this approach to resolve it. My problem is i am using Java with RESTful API calls to run, schedule, fetch (download) reports. I am able to achieve all these. However, i overlooked the scheduling part where i need to give dynamic dates like startDate = startOfMonth and endDate = endOfMonth. Can you tell me what would be the easy way of doing this. Can i create a Input Control in JASPERSERVER and have it take the value of startOfMonth and endOfMonth each time the schedule report runs. If so, can you tell me how i can create these input controls.
ReplyDeleteThanks,
Deletethe solution to this is write your own custom pojo class keep it in classpath so that it can be available to jasper compiler. in that pojo class wire a function getStartDateOfCurMonth()... something like that which will return start date of current month and then you can set to a variable or use as expression in your report where you want to use date
Thanks Rajendra, that suggestion worked to me. I have a scriplet(a java class) which takes in string parameters and computes the custom dates to my startDate and endDate parameters.
ReplyDeletehey i have followed all steps except step no 1...as i am using ireport 4.5.1...so i think its not needed in new version...but after doing step 5 when i pressed on "Refresh Preview Data" i am getting such Error:net.sf.jasperreports.engine.JRException:Unknown Column name:DUMMY
ReplyDeletefor your information i am writing this query in new dataset not in default report query...i added new dataset and then writing this query...whats wrong in this??pls help me...i have my project work of final year....and i am stuck with this error...so pls help me as soon as possible...
Can you please verify your sql store procedure by executing what columns that it gives and verify same with column names that you have defined in jasper reports. also make sure java data types should also be compatible with sql datatypes
ReplyDeletefirst of all i dont know how to define column name in report...as i have always used automatically read fields....it gives me fields from the query i have given to dataset...and second thing is i am using oralce 10g express edition...and i am not using my different procedure....i am getting this error while executing your example which u have given above....thnx for suggestion....
DeleteIt is very simple in your report designer on right hand side where you see Parameters in tree view right click on it it will populate property pane below type the name of parameter and dattype. and you are done.
ReplyDeleteHi man,
ReplyDeleteI want to ask you very important question please.
Can i write in the query section to a sql procedure furthermore to execute a select clause?
for example:
{call my_func($P{parame})}
select * from my table
is this possible?
thank you very much,
omri
Hi Sorry for late reply, since I was on Trip, well, it is possible but in a different way,
ReplyDeletethere is something called dataset which you need to create, as you can see a default dataset in which you will write a plsql query you can addtionally can add more dataset and in that you can place your query and execute them all.
Hi man, thanks you helped me... :)
ReplyDeleteI have one more question,
how can i do column lines to seperate between the data in a report.
In other words how can i put all the select result in the report pdf output in a table format...
1|2|3|4|
5|4|4|3|
thanks man,
omri
Hi Sam, It is simpler while design your report draw lines or use rectangle drawing tool to draw border, when you generate/export your report in html/excel/pdf jasper automatically take care of putting them in columner format...
DeleteI hope I understood your question correctly.
check it out by exporting to html.
Hi Raj, its really useful discussion going on. i have two questions. 1. how do we call multiple stored procedure calls in a single report. for example : {call test_zasper_tool($P{ORACLE_REF_CURSOR})}
ReplyDelete{call test_jasper_1($P{pi_param_one},$P{cursor1})} and 2. how do we call an stored procedure which is having multiple ref cursors. Thanks Raj
This article has been very helpful, as I have read many others that say there is no way to call a stored proc from within JasperReports.
ReplyDeleteLet me share my results so far. The parameters section of my stored proc looks like this:
CREATE OR REPLACE PROCEDURE CC_REPORT.PROC_RPT_CC_STDCOMMSUMMARY (
resultCursor IN OUT RPT_CURSOR_PACKAGE.Rpt_Curr
, project_id IN VARCHAR2
, start_date IN VARCHAR2
, end_date IN VARCHAR2
, start_time IN VARCHAR2
, end_time IN VARCHAR2
, time_range IN VARCHAR2
, time_slice IN VARCHAR2
, segments IN VARCHAR2
, standard_comments IN VARCHAR2
, selection_expert IN VARCHAR2
, site_code IN VARCHAR2
)
as . . . . .
Here is my stored proc call:
{call CC_REPORT.PROC_RPT_CC_STDCOMMSUMMARY($P{ORACLE_REF_CURSOR}, $P{PROJECT_ID}, $P{START_DATE}, $P{END_DATE}, $P{START_TIME}, $P{END_TIME}, $P{TIME_RANGE}, $P{TIME_SLICE}, $P{SEGMENTS}, $P{STANDARD_COMMENTS}, $P{SELECTION_EXPERT}, $P{SITE_CODE})}
When I click "Refresh Preview Data", the result is "Ready (0 records read)".
The proc itself is a current production proc, so I know that it works correctly and returns data. Can anyone think why the call is not returning any data?
Thank you.
Hi,
ReplyDeleteCan you trace all your in parameters are going properly?
if not can you modify your proc to test by defaulting your in parameters hard coded with values for test.
I hope as you mentioned it is not throwing any error on jasper side. I pretty sure you will get results.
I am also interested to know how to call multiple stored procedures in a single report, as well as the syntax to call a function (instead of a procedure).
ReplyDeleteWell to Answer is, you can add multiple datasrouce in your report. and each datasrouce with difff query or stored procedure.
Deletehow to call multiple stored procedures from ireport or one stored procedure and some sql query
ReplyDeleteWell to Answer is, you can add multiple datasrouce in your report. and each datasrouce with difff query or stored procedure.
Deletehow to call datasrouce before the main query is called in report ?
DeleteHi Rajendra,
ReplyDeleteGreat article... Thanks.
I am experiencing a problem in iReports. I am reporting directly from an oracle 11g database and not embedding code in a java application. The driver that I am using is ojdbc6.jar
Once connected to the database, I go to the 'Report query' screen, select 'plsql' from the query language dropdown box. Now, if I enter any plsql statement ending in a semicolon, I will receive the error: "Error:SQL problems:ORA-00911:invalid character"
Now, when I remove the semicolon, the statement executes as a sql statement and returns correct results. My question is why is it not executing plsql statements when I specify plsql as a query language?
However, if I run the sample database hsqldb, select plsql query language and enter a plsql statement, it returns correct results and no error message.
If you can help, I would greatly appreciate it!
Thank You.
Hi Rajendra,
ReplyDeleteJune 12, 7:30
I forgot to mention that I am using ireport 4.6.0
Thanks again.
IT do not expect semicolon in the code....
Deletenormally we do not use it except on sql prompt.
in your third para you mentioned it worked as sql statement... means what... did you change lagnuage from plsql to sql ?
also note that hsqldb is inmemory database provided and bundled with it... so it works fine with its provided jars.
I would suggest to use another jars provided by oracle like classes12/14.jar
and test once your code..
still you face problem please email me with your jrxml and queries in detail my email id is rajendra.tambat@gmail.com
Hi Rajendra,
ReplyDeleteI have created a report in Ireport by using a stored procedure.
Now we are having many data sources( many databases within a single server each representing to one client). The report is working fine when connecting to all the data sources one at a time. But the data source string needs to be dynamic, depending on the client that respective data string needs to be selected and the report should run with that data.
Please can you help me in resolving this issue.
Thank You,
sunil Kumar
Hi Sunil,
DeleteThis is really a application design issue.
Well let me know do u want to call it from application or iReport? since you mentioned you tested it for all datasources from ireport.
so please confirm following
* want to use multiple datasource from ireport or application?
* type of application standalone/webbased
* application server? if any?
* how have you configured it currently multiple datasources for your application to multiple client.
Regards,
Rajendra.
hi....how can we pass o/p of one query as input to other query in reports where we have 3 parameters out of which 2 are same for both queries and third is same parameter with different value........please reply...
ReplyDeleteHola como estas me podrias ayudad con este mismo tema usando postgres por favor
ReplyDeletehola lo siento yo no entiendo español, traducido del google, así que gracias por su visita
DeleteHello how are you?
DeleteCan you help me with the same topic about JasperReports and stored procedures but with Postgres database
Hello how are you?
DeleteCan you help me with the same topic about JasperReports and stored procedures but with Postgres database
Hi Axel,
DeleteSorry for late reply,
I think only change could be the appropriate plsqlfactory is needed I hope other things are same, as I did not have any experiance on Postgress... I am not sure the syntax of calling procedure is same or not. I will try to find out and update you if possible.
Hi Rajendra,
ReplyDeleteI am new to jasper reports and was trying with sql queries all these days.Your blog helped me a lot.
And i am facing one issue(No idea whether its a valid issue or not).I created the parameter "ORACLE_REF_CURSOR" as i didnt see it in the parameter list and while trying to change its parameter class as "java.sql.ResultSet" i didnt find the respective class in the corresponding dropdown. I am setting the report language as Java only. Kindly guide me in where i am going wrong.
Hi,
Delete"java.sql.ResultSet" is a standard interface api provided from java so should be made available by default. well did you check your classpath entries? also let me know which version of jasper are you using?
This comment has been removed by the author.
ReplyDeleteHi, I'm from Brazil and use iReports some time. My current version is 4.7.0. 'm migrating my reports "forms / reports" for jasper reports. I'm having trouble with some reporting functions pl / sql because I can not apply these functions on iReports. I followed your tutorial but I could not create the parameter "ORACLE_REF_CURSOR" with the property "java.sql.ResultSet". I've added my classplath the ojdbc6.jar but still can not create or import parameter. I need your help, if I'm using the wrong jar, please tell me or give me the link. My real goal is to apply functions pl / sql variables like I do in the "forms reports," but the lack of options I could create these functions in datasets. Thank you and I await your response.
ReplyDeleteHi Augusto,
DeleteI am not sure that is the correct jar that you use for oracle normally it is ojdbc14.jar which you may find in "oracle\product\10.2.0\server\jdbc\lib" or respective path. also note that in step 4 you can see on right side button "new parameter" there you can click on button
type parameter name ORACLE_REF_CURSOR and value expression type "java.sql.ResultSet". this way you can add new parameter there is another way to add is in your ireport designer main window you see report inspecter where you see list of parameters right click on same and click add Parameter it will open same dialog box as explain above.
hope this will help
in case of further difficulties please feel free to ask.
Hi Rajendra,
DeleteI am also getting the above error even after adding the ORACLE_REF_CURSOR as mentioned in your article. I have done from step 1 to 7 as per your article but end up in getting the Invalid column error. I am using IReport 4.2.1 and have added ojdbc14.jar too.
Hi Rajendra Tambat,
DeleteWe are using IReport 4.2.1 version and we are trying to call an SP with four params (3 input param and 1 output refcursor type param) in i report with the syntax as mentioned in your article as {call DB_SCHEMA_NAME.PKG_NAME.PROC_NAME($P{a1},$P{a2},$P{a3},$P{ORACLE_REF_CURSOR})}.
We have followed all the steps from 1 to 7 as per your article as well as added ojdbc14.jar, but end up in getting the Error:SQL problems:Invalid column type when we tried to read fields. Also, if we add the field manually and tried to preview the records we are not getting any records, though data is present in DB and we are getting it when running through SP. Please advice.
This comment has been removed by the author.
ReplyDeleteExcellent article
ReplyDeleteI did follow your steps and when i click 'Read Fields',im getting this error
ReplyDeleteInvalid Column type and when clicking Preview im getting this error,Caused by: java.sql.SQLException: Cursor is closed.I can't understand what may be the reason.Pls help me to overcome this issue.
I feel problem could be from your stored procedure,
DeletePlease check it once or call from simple java SQL prompt. If it works means some problem from jasper.
Reg
I feel problem could be from your stored procedure,
DeletePlease check it once or call from simple java SQL prompt. If it works means some problem from jasper.
Reg
Thank You for your Reply Mr.Tambat.
DeleteIm having a small doubt.In your Procedure Query,you have opened a cursor 'emp_cursor'that is not closed anywhere .Then when it'll get closed?The only strange thing i did is in my procedure query i did close my cursor.Is that the reason for the error?
Yes, since it is sys_ref cursor of oracle need not to be closed it has to get closed by calling code like plsql, or java resultset.
Deleteso jasper will take care of closing it internally
Hi Mr.Tambat!
DeleteThank you so much for your continuous support.I just got some idea and after successful implementation i'll intimate you.
And also i need one more help from you.I tried calling Function/SP thru this link http://brianburridge.com/2006/06/04/how-to-call-stored-procedures-from-jasper-reports/ also.
ReplyDeleteBt here im getting this error"My Function_Name":Invalid Identifier.
How can we know whether we have proper permission to execute functions/procedures in the version Jasper iReport designer 4.7.0.Pls do help me.
rights of executing sp/function depends upon the connection that you created, there you already provided user and pwd who should have rights given in oralce to execute.
DeleteNice Article..
ReplyDeleteHi Rajesh,
ReplyDeleteIt is very good article thanks for sharing it to community.
We are facing an issue while migrating from jasper server 4.0 to 5.0.
1) we faced close_wait issue with sockets, while scheduling the reports via SOAP web services. The Close_wait are making the weblogic server to hang. We overcome this issue by using rest web services.
2) With Rest web services reports are getting generated for one application, but reports are failing in a different application.
we added the following in applicationContext.xml
plsql
PLSQL
and net.sf.jasperreports.query.executer.factory.plsql=com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory in jasperreports.properties
The report with following error
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for :
at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:143)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
... 18 more
Caused by: java.sql.SQLException: ORA-01403: no data found
Any help is really appreciated
Reports are working using soap web services the issue is rest web services.
DeleteLet me understand your problem in detail.
Delete1. you are migrating jasper from 4 to 5
..> does it mean it was working earlier fine with jasper 4.x with soap web service? and with more than 1 application calling this same report was working fine?
2. explain in detail about close_wait. means where is it coming at weblogic threads or jasper etc.
3. when you changed your soap web service to rest it is working fine with 1 application. but same report called with another application fails... is that correct?
4. which version of weblogic are you using?
as per the error that you shown I understand following.
1. it is sql exception so problem is with oracle plsql and not jasper reports, they are running fine.
2. cause could be that proper parameters are not getting passed on 2nd time so query returning empty recordset/cursor.
3. are you cashing/storing varibles/parameters/jasperobject in session or some other way..
hope my hints will lead you to solve the problem.
good luck.
Hi Rajendra,
DeleteThanks for the reply, really appreciate it. The reports are working Fine in 4.0, we want to migrate to jasper server 5.0. All our reports are rendered via stored procedure which return ref cursor, we schedule the reports using SOAP web services and render the output via the URL.
For migration we have installed jasper server 5.0 in QA, run the regression, we faced close_wait issue, every time we schedule a report 2-3 sockets are getting created in CLOSE_WAIT state, they are not getting closed, after running aroung 50 reports web logic server is getting hanged.
Since we are not able to fix the close_wait issue, we tried to use rest web services instead. We are able schedule the report, but we running into below 2 issues
1)parameter which we are passing to reportunit is not getting propagated to stored procedure, parameter is going as null to stored procedure.
2)There is anonymous pragma code which commits the data into a dubug table.the data is not getting added into debug table via anonymous pragma, data is going into debug if we added insert statement in maion procedure and commit it.
The Weblogic versions we are using are 10.3 for 1 application and 12c for 2 application. The issue i said is because of parameter not getting propagated to stored procedure.
DeleteOn future research we found anonymous pragma code is working fine, only issue is parameter not getting passed to report unit via rest API.
DeleteHi,
DeleteSorry for delay in reply. well, looking at the previous approach of soap web service. I feel there is a thread handling/memory leak issue with reports it could seems that with every call, the open cursors with database is not closed. or threads keep connections alive. if possible check for heap dumps with connection objects that are lying with. may be it is not properly closed by jasper server or if programatically handled by you. by default weblogic keep 200 open cursors with connection pool and it looks logical with your test with 40/50 test it left 3/4 connection open with every call. check again how it is handled either you provide it or he creates a connection. (if he creates then )may be it is a bug with japser server 5 you need to digg there as well but chances are less to find there.
these are just hints.
about rest problem as i said earlier that parameters are going null you need to check within your rest application and it has nothing to do with jasper.
as a test keeping web service aside. just create direct hit with jasper server calling reports 50 times so to make sure that it do not leave connections open at end.
regards,
Rajendra
I am using IREPORT 4.7 tool.I have PL/SQL function( SOS_NUMBER_TO_WORD_JR_F(10)) for Convert amount to words(integer to string).It has one INTEGER parameter and return type is STRING. Now How will i call this function in text field or variable in Ireport?
ReplyDeleteVishnu, it is very simple to call function in the sql statement,
Deleteso please google on it you will find many example, use it in your existing query. like
select col1,... SOS_NUMBER_TO_WORD_JR_F(col2) as amtinword,...
form....
and your mapped field object will be populated automatically.
Regards
FROM VAIBHAVB
ReplyDeleteas per details i done setting and its mostly working but setp no.7 is happing with me.
have no fleids paramters than i am using directly
$P{ORACLE_REF_CURSOR} as paramter its show correct no of row like 30 recods but all are null.
Hi Vaibhav Sorry but I could not understood your problem, do you still looking for answer? will you kindly rephrase it again then?
DeleteRegards
Raj
Hi! great tut but I have a problem with the query executors
ReplyDeletelook at the pics please
http://img189.imageshack.us/img189/4411/rep1x.png
http://img109.imageshack.us/img109/8253/rep2.png
http://img267.imageshack.us/img267/4735/rep3u.png
This comment has been removed by the author.
DeleteI mistaken in seeing your images...
Deletebut not sure if your ireport is picking up classes from jar at the place where you have shown in first image
please see step 2 image of my tut. to show where all jars are located and refered by ireport. it is ireport\modules\ext\*.jar
hope this helps
HI Rajendra,
DeleteI have followed all your step above to create a jasper report by procedure call, but still i am facing exception. Kindly help.
Error filling print... Error executing SQL statement for : report3
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report3 at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:143) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:727) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:625) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1238) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:869) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:813) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247) at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:878) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLIST_PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Hi It clearly shows that your procedure is not properly called
Delete"wrong number or types of arguments in call to 'EMPLIST_PROC' " check with the data types of the fields you created or parameters are passed in correct order.
In Query window i have called : {call emplist_proc($P{ORACLE_REF_CURSOR} ) }
DeleteData type of this parameter($P{ORACLE_REF_CURSOR} ) is String. how to set this as Resultset. I am using IReport 4.1.1
For making the parameter Resultset. i have just copied "java.sql.ResultSet" and pasted into Parameter class dropdown. it worked. Thanks for your support.
DeleteHI Raj,
DeleteOne more clarification, i need to call the generated jasper file from java class and the procedure call will be passed a parameter to query window. i tried with the below code, but it did not worked.
excample:
String p_query="{call emplist_proc($P{ORACLE_REF_CURSOR} ) }";
HashMap map = new HashMap();
map.put("p_query", p_query);
and add this $P{p_query} as a parameter to the query window.
kindly help.
I will not say it will not work but its difficult
Deleteyou can only pass parameters for query input as long as your query name is fixe. if you need to pass whole query name as dynamic then from compiled report you need to extract query object and reset the query string again. little more programming... search more you can do it. currently i do not have time to provide you example quickly
This comment has been removed by the author.
DeleteHola Rajendra, espero me pueda ayudar con el siguiente problema que me presenta en ireport, este es mi codigo :
ReplyDelete{call SIAPPNSP_LISTA_PRUEBA($P{ORACLE_REF_CURSOR})}
es simple, ya que solo como parametro pide un cursor, pero cuando le doy clic en read file me sale como erro "TIPO DE COLUMNA NO VALIDO".
aun asi ejecute mi reporte para ver el error en especifico y me lanza lo siguiente:
Error filling print... OUT paramater used in non-stored procedure call : ORACLE_REF_CURSOR class java.sql.ResultSet
No se si tendras alguna idea de esto.
Espero me puedas ayudar.
Gracias.
Saludos cordiales.
Hola Saludos,
Deletequé versión de jasperreports está usando?
por favor consulte este enlace puede ayudarte.
No estoy seguro de su error
http://stackoverflow.com/questions/20773742/error-outparamaterusedinnon-storedprocedurecalloracle-ref-cursorclass
Hi Rajendra,
ReplyDeleteI am new to Jaspersoft Studio.getting error while calling stored procedure Wrong number or type of argument in call.
How do i use stored procedure in Jaspersoft studio. Can you please send me some information.
Thanks,
Ram
Hello Raj,
ReplyDeleteThis helped me a lot. I just have one question, J-reports seems great for tabular reports, but how can you get a cross tab or master-Detail report to work. Do you have any doc's or example I can check out.
Thanks,
Mike
Hi Mike,
DeleteThank you for feedback, and sorry for late reply hope you have already checked my blog for other entries.
i do have one sample on this as well
http://rajendratambat.blogspot.co.at/2010/10/creating-crosstab-report-with-jasper.html
Hello Rajendra,
ReplyDeleteExcellent article! I have one more question, is it possible to use anonymous PL SQL blocks as well for reports?
Thanks,
Puneet
Hi Punit,
DeleteThanks, I am not sure... about this option someone need to try out
Hi Punit,
DeleteThanks, I am not sure... about this option someone need to try out
Hi Rajendra,
ReplyDeleteI am using pervasive database & calling a stored procedure. I can preview the output of the report in iReport or Studio. No issues here.
Eg : call usSchedule() [No parameters needed to pass from SP as I do not need in my requirement]
The problem is when I published it to server, it is throwing me the error.
I've configured the query executor to jaser server(6.0.1 version) with out any issue.
Here is the log .. Could you have look into it & provide me your inputs ?
I don't understand whether it is a server level issue or DB level issue...
LOG : Posted in this thread.
http://community.jaspersoft.com/questions/851496/javasqlsqlexception-lnapervasiveodbc-engine-interfacedata-record
Hi Rajendra, I am almost a wits end trying to resolve a No query executer factory registered for the 'plsql' language. The code runs fine on our Windows server, but I get this error when I run the same code on our Linux server. There is a jasperreports.properties file that includes net.sf.jasperreports.query.executer.factory.plsql=com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory and I have added the extensions-3.5.3 JAR file. Any ideas would be greatly appreciated.
ReplyDeleteHi ,
ReplyDeleteI have requirement to call procedure with 1 in parameter and employee object as OUT . how can I use the procedure in jasper designer to generate pdf report. Please share the inputs.
Regards
Krishna
Hi ,
ReplyDeleteI have requirement to call procedure with 1 in parameter and employee object as OUT . how can I use the procedure in jasper designer to generate pdf report. Please share the inputs.
Regards
Krishna
Hi
ReplyDeletein all the reference documents it is there for i report , can anyone explain how to call procedure in tibco jasper menas jasper 6.3
I'm trying to call to OUT parameters from stored procedure.I have called one OUT parameter by sql.ResultSet cursor but how to call another OUT parameter.
ReplyDeletepls reply soon
Deletein jasper report it is expected to return only one out parameter as cursor so api do not provide any functionality to access another out parameters if defined.
DeleteHi Sir,
ReplyDeleteDo you know any convinient way to run Oracle stored procedures bfore and after running the report itself(query/proc which will return data for report)?
Jasper report do not provide any way to call post processing db queries/Procedure. if you are unable to fire procedure from report then you may call procedure externallay from java program pass fetched resultset to report and after report execution again call another procedure...
Deletefor e.g.
in java program
...
.. get db connection
... execute store proce and get resultset
.... get compiled jasper report
... fire jasperreport with resultset
... execute another store procedure
.. close connection..
Hi, mr Rajendra..
ReplyDeletewould you please give a sample jrxml that using cursor as output parameter?
since ORACLE_REF_CURSOR is not accessible from report query window.
i add parameter manually, and i didn't see any java.sql.ResultSet data type for parameter, and i just add it by copying on this blog,
and lastly, how did you convert the cursor to the column name?
that you place into the report itself?
many thanks,
have a great day.
ReplyDeleteThe article is so informative. This is more helpful. Thanks for sharing
Java Training in Chennai
Thanks and that i have a swell provide: How Much House Renovation for home renovation
ReplyDelete