Pages

Wednesday, October 5, 2011

Calling Oracle Stored Procedure in JasperReports

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. 
Create a new blank 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

    2.  Also go to classpath tab and make sure you added classess12/14/ojdbc.jar in classpath  

    3.  Make sure that you have created oracle datasource connection available.
    4.  Open  report Query , select Query Language = “plsql”  

    5.  Write your plsql query as below make sure you have  procedure call withing  curly braces “{}“
    {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

      118 comments:

      1. Hi!
        Thank 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.

        ReplyDelete
      2. hi,

        I 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

        ReplyDelete
      3. Hi,

        Thank 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

        ReplyDelete
      4. Thank you Totonn,

        Field 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.

        ReplyDelete
      5. Hi,

        I 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

        ReplyDelete
      6. 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.

        Thank you in advance !

        {call PKG_QUERY.Prc_Test_Select_Users_By_FName($P{pin_firstname}$P{ORACLE_REF_CURSOR}) }

        ReplyDelete
      7. Thanks for comment.
        if 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

        ReplyDelete
      8. 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.

        ReplyDelete
      9. Hello,

        Very 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

        ReplyDelete
      10. 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.

        ReplyDelete
      11. Thanks. 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?
        Regards

        ReplyDelete
      12. I'm developing an application in Eclipse with Java. When I try to export a report using a stored procedure I get this error:

        net.sf.jasperreports.engine.JRRuntimeException: No query executer factory registered for the 'plsql' language.

        Can you help me with this, please?

        ReplyDelete
      13. 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.
        since 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.

        ReplyDelete
      14. 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.
        I 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.

        ReplyDelete
      15. I hope you are developing web based application.
        you 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.

        ReplyDelete
      16. I hope you are developing web based application.
        you 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.

        ReplyDelete
      17. 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..
        One 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.

        ReplyDelete
      18. 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.

        ReplyDelete
        Replies
        1. Rajendra,
          i 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

          Delete
        2. Hi Sorry for delay... actually I am on tour so could not access my emails on time.
          Please send me (desc adm_modules) also your jrxml of report my email id is below. rajendra.tambat@gmail.com

          Delete
        3. i have the same error, use IREPORT 4.6.0 and ORACLE 10g, please help me!

          create 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".

          Delete
        4. the language is plsql at the report and query :)

          Delete
        5. Hi, 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. :)

          Delete
        6. I changed the procedure and i used the function and shows the number of rows because the procedure shows nothing...
          this is the image:
          https://lh5.googleusercontent.com/-C42IG_oVmsA/UA7SjIwyOkI/AAAAAAAAAIc/VL9yS-q2KqY/s720/Error%2520ireport.png

          Delete
      19. Rajendra,

        Thanks 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

        ReplyDelete
      20. 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:

         java.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.

        ReplyDelete
        Replies
        1. 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.
          Hope 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.

          Delete
      21. Excellent Article! But, in my organization there are lot of stored functions that return cursor.

        CREATE 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?

        ReplyDelete
        Replies
        1. as per syntax what you tried is wrong way hope
          {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

          Delete
      22. 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.

        ReplyDelete
        Replies
        1. Thanks,
          the 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

          Delete
      23. 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.

        ReplyDelete
      24. hey 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
        for 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...

        ReplyDelete
      25. 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

        ReplyDelete
        Replies
        1. first 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....

          Delete
      26. It 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.

        ReplyDelete
      27. Hi man,
        I 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

        ReplyDelete
      28. Hi Sorry for late reply, since I was on Trip, well, it is possible but in a different way,
        there 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.

        ReplyDelete
      29. Hi man, thanks you helped me... :)

        I 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

        ReplyDelete
        Replies
        1. 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...
          I hope I understood your question correctly.
          check it out by exporting to html.

          Delete
      30. 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})}
        {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

        ReplyDelete
      31. 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.

        Let 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.

        ReplyDelete
      32. Hi,
        Can 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.

        ReplyDelete
      33. 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).

        ReplyDelete
        Replies
        1. Well to Answer is, you can add multiple datasrouce in your report. and each datasrouce with difff query or stored procedure.

          Delete
      34. how to call multiple stored procedures from ireport or one stored procedure and some sql query

        ReplyDelete
        Replies
        1. Well to Answer is, you can add multiple datasrouce in your report. and each datasrouce with difff query or stored procedure.

          Delete
        2. how to call datasrouce before the main query is called in report ?

          Delete
      35. Hi Rajendra,

        Great 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.

        ReplyDelete
      36. Hi Rajendra,

        June 12, 7:30
        I forgot to mention that I am using ireport 4.6.0

        Thanks again.

        ReplyDelete
        Replies
        1. IT do not expect semicolon in the code....
          normally 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

          Delete
      37. Hi Rajendra,

        I 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

        ReplyDelete
        Replies
        1. Hi Sunil,

          This 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.

          Delete
      38. 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...

        ReplyDelete
      39. Hola como estas me podrias ayudad con este mismo tema usando postgres por favor

        ReplyDelete
        Replies
        1. hola lo siento yo no entiendo español, traducido del google, así que gracias por su visita

          Delete
        2. Hello how are you?
          Can you help me with the same topic about JasperReports and stored procedures but with Postgres database

          Delete
        3. Hello how are you?
          Can you help me with the same topic about JasperReports and stored procedures but with Postgres database

          Delete
        4. Hi Axel,
          Sorry 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.

          Delete
      40. Hi Rajendra,

        I 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.

        ReplyDelete
        Replies
        1. Hi,
          "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?

          Delete
      41. This comment has been removed by the author.

        ReplyDelete
      42. Hi, 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.

        ReplyDelete
        Replies
        1. Hi Augusto,

          I 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.

          Delete
        2. Hi Rajendra,

          I 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.

          Delete
        3. Hi Rajendra Tambat,

          We 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.

          Delete
      43. This comment has been removed by the author.

        ReplyDelete
      44. I did follow your steps and when i click 'Read Fields',im getting this error
        Invalid 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.

        ReplyDelete
        Replies
        1. I feel problem could be from your stored procedure,
          Please check it once or call from simple java SQL prompt. If it works means some problem from jasper.
          Reg

          Delete
        2. I feel problem could be from your stored procedure,
          Please check it once or call from simple java SQL prompt. If it works means some problem from jasper.
          Reg

          Delete
        3. Thank You for your Reply Mr.Tambat.
          Im 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?

          Delete
        4. 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.
          so jasper will take care of closing it internally

          Delete
        5. Hi Mr.Tambat!
          Thank you so much for your continuous support.I just got some idea and after successful implementation i'll intimate you.

          Delete
      45. 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.

        Bt 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.

        ReplyDelete
        Replies
        1. 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.

          Delete
      46. Hi Rajesh,

        It 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

        ReplyDelete
        Replies
        1. Reports are working using soap web services the issue is rest web services.

          Delete
        2. Let me understand your problem in detail.
          1. 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.


          Delete
        3. Hi Rajendra,

          Thanks 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.

          Delete
        4. 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.

          Delete
        5. On future research we found anonymous pragma code is working fine, only issue is parameter not getting passed to report unit via rest API.

          Delete
        6. Hi,
          Sorry 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

          Delete
      47. 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?

        ReplyDelete
        Replies
        1. Vishnu, it is very simple to call function in the sql statement,
          so 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

          Delete
      48. FROM VAIBHAVB

        as 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.

        ReplyDelete
        Replies
        1. Hi Vaibhav Sorry but I could not understood your problem, do you still looking for answer? will you kindly rephrase it again then?

          Regards
          Raj

          Delete
      49. Hi! great tut but I have a problem with the query executors
        look 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

        ReplyDelete
        Replies
        1. This comment has been removed by the author.

          Delete
        2. I mistaken in seeing your images...
          but 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

          Delete
        3. HI Rajendra,

          I 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

          Delete
        4. Hi It clearly shows that your procedure is not properly called
          "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.

          Delete
        5. In Query window i have called : {call emplist_proc($P{ORACLE_REF_CURSOR} ) }
          Data type of this parameter($P{ORACLE_REF_CURSOR} ) is String. how to set this as Resultset. I am using IReport 4.1.1

          Delete
        6. For making the parameter Resultset. i have just copied "java.sql.ResultSet" and pasted into Parameter class dropdown. it worked. Thanks for your support.

          Delete
        7. HI Raj,

          One 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.

          Delete
        8. I will not say it will not work but its difficult
          you 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

          Delete
        9. This comment has been removed by the author.

          Delete
      50. Hola Rajendra, espero me pueda ayudar con el siguiente problema que me presenta en ireport, este es mi codigo :

        {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.

        ReplyDelete
        Replies
        1. Hola Saludos,
          qué 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

          Delete
      51. Hi Rajendra,
        I 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

        ReplyDelete
      52. Hello Raj,

        This 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

        ReplyDelete
        Replies
        1. Hi Mike,
          Thank 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

          Delete
      53. Hello Rajendra,

        Excellent article! I have one more question, is it possible to use anonymous PL SQL blocks as well for reports?

        Thanks,
        Puneet

        ReplyDelete
        Replies
        1. Hi Punit,

          Thanks, I am not sure... about this option someone need to try out

          Delete
        2. Hi Punit,

          Thanks, I am not sure... about this option someone need to try out

          Delete
      54. Hi Rajendra,
        I 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

        ReplyDelete
      55. 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.

        ReplyDelete
      56. Hi ,
        I 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

        ReplyDelete
      57. Hi ,
        I 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

        ReplyDelete
      58. Hi
        in all the reference documents it is there for i report , can anyone explain how to call procedure in tibco jasper menas jasper 6.3

        ReplyDelete
      59. 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.

        ReplyDelete
        Replies
        1. in 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.

          Delete
      60. Hi Sir,
        Do 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)?

        ReplyDelete
        Replies
        1. 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...
          for 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..

          Delete
      61. Hi, mr Rajendra..
        would 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.

        ReplyDelete

      62. The article is so informative. This is more helpful. Thanks for sharing
        Java Training in Chennai

        ReplyDelete