13 Replies Latest reply: Feb 21, 2013 4:14 AM by HamidHelal RSS

    how to run report with parameters from oracle forms 10g

    salute-Salem
      db and dev 10g rel2 , xp
      hi all ,
      i can run report from forms builder with this code :
      DECLARE
       
      v_repid REPORT_OBJECT;
      v_rep VARCHAR2(100);
      v_rep_status VARCHAR2(100);
      v_param VARCHAR2(200) := NULL;
      v_valor VARCHAR2(200);
      v_url VARCHAR2(2000);
      v_repserver varchar2(20) := 'myserver';
       
      v_report varchar2(100) := 'D:\REPORT_NAME.REP';
      v_PARAMETRO varchar2(100) := '';
       
      BEGIN
       
      v_repid := FIND_REPORT_OBJECT('MYREPORT'); -- report is an element from object navigator report
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
      SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
      v_rep := RUN_REPORT_OBJECT(v_repid);
      v_rep_status := REPORT_OBJECT_STATUS(v_rep);
      WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
      v_rep_status := REPORT_OBJECT_STATUS(v_rep);
      END LOOP;
      IF v_rep_status = 'FINISHED' THEN
      message(v_rep);
      message(v_rep);
      WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
      SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
      END IF;
       
      END;
      but i want to run a report with parameter , that is my problem .

      what i am thinking of is to create a parameter list "tmp" and add a parameter to it "p_ename" which holds the value of a text item ":t_ename" with which i want to run my report , then to add the parameter list parameter to the
      run_report_object(myreport,"parameter list") ;
      then
      adds a user parameter with the name "p_ename" to my report in reports builder then test it , but i did that , and nothing happens , it runs the report with the whole data , not just with the text item value's data ?

      how to resolve this?
      thanks
        • 1. Re: how to run report with parameters from oracle forms 10g
          Christian Erlinger
          Did you take a look at the documentation; e.g. here: http://docs.oracle.com/cd/E15523_01/doc.1111/e10394/repgrph.htm#BHBBEECA
          where exactly the thing you want is done in the sample?

          cheers
          • 2. Re: how to run report with parameters from oracle forms 10g
            Michael Ferrante-Oracle
            Also, be aware that we have updated the Forms/Reports Integration paper. Although it has been named with references to 11g, the concept for version 10 is the same. The paper can be found here:

            http://www.oracle.com/technetwork/developer-tools/forms/documentation/techlisting11gr1-086315.html
            • 3. Re: how to run report with parameters from oracle forms 10g
              HamidHelal
              SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
              for example
              declare
              QT char(1):='''';
              begin
              .........
              SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
              ........     
              Hope this helps

              Hamid

              Edited by: HamidHelal on Feb 19, 2013 7:48 AM
              • 4. Re: how to run report with parameters from oracle forms 10g
                Amatu Allah Neveen Ebrahim
                Hi
                pls have a look at the white paper which offers a working way to accomplish calling a Report which uses a Reports parameter form. 

                http://www.oracle.com/technetwork/developer-tools/forms/frmrepparamform-128021.pdf

                Amatu Allah
                • 5. Re: how to run report with parameters from oracle forms 10g
                  salute-Salem
                  hamidhelal
                  for example
                  declare
                  QT char(1):='''';
                  begin
                  .........
                  SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' >VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
                  ........
                  what is voucher_type and :txt_cid_id ? if you could modify my code please ??? , i am using scott/tiger schema .
                  amatuallah
                  http://www.oracle.com/technetwork/developer-tools/forms/frmrepparamform-128021.pdf
                  this link provide the way to run the report without parameter , and this is what i posted from the beginning .

                  thanks
                  • 6. Re: how to run report with parameters from oracle forms 10g
                    Christian Erlinger
                    what is voucher_type and :txt_cid_id ? if you could modify my code please ??? , i am using scott/tiger schema .
                    The sample in the documentation I gave you a link is using the scott/tiger schema.

                    you have to pass the parameter list in the form
                    'parameter_name_in_report=value'
                    where you certainly have to enclose alphanumeric values with single quotes which you'd have to escape.

                    cheers
                    • 7. Re: how to run report with parameters from oracle forms 10g
                      Amatu Allah Neveen Ebrahim
                      sorry
                       ============================================
                      
                      Begin
                      
                      PARAM_ID := GET_PARAMETER_LIST(PARAM_NAME);
                      
                      IF NOT ID_NULL(PARAM_ID) THEN
                      DESTROY_PARAMETER_LIST('REPORT_PARAMETER');
                      END IF;
                      
                      
                      PARAM_ID := CREATE_PARAMETER_LIST(PARAM_NAME);
                      ADD_PARAMETER(PARAM_ID,'PARAMFORM',TEXT_PARAMETER,'NO'); 
                      
                      ADD_PARAMETER(PARAM_ID,'COPIES',TEXT_PARAMETER,:COPIES);
                      ADD_PARAMETER(PARAM_ID,'P_DISPLAY',TEXT_PARAMETER,:DESTYPE);
                      ADD_PARAMETER(PARAM_ID,'DESFORMAT',TEXT_PARAMETER,'pdf'); 
                      
                      
                      ADD_PARAMETER(PARAM_ID,'P_DATA_TYPE',TEXT_PARAMETER,:PERIOD_MONTH);
                      ....
                      ....
                      u need to specify which parameter u Will pass cryeate them first in report with its corresponding datatype in forms u Will need after creating parameter list to add them one by one with the same name &datatype specified in report ..

                      in forms they r just text item non db in the form's button pl/sql code is added as u did the piece of code u r missing is above customize it the way u want, forms input is

                      occurred by user the
                      • 8. Re: how to run report with parameters from oracle forms 10g
                        salute-Salem
                        Amatu Allah      sorry
                        i am so sorry amatu allah , but i feel so confused this way, i do not know what is :copies , :destype and :period_month , i think :destype is a property of my report , i setting it programmatically or in the property palette ,and there is a conflict in my head .

                        please , i am using scott/tiger schema , with a text item and a button .............. , like i said before at the first post and mentioned their names too . and mentioned the parameter list too .
                        - i modified my code now to this
                        DECLARE
                         
                        v_repid REPORT_OBJECT;
                        v_rep VARCHAR2(100);
                        v_rep_status VARCHAR2(100);
                        v_param VARCHAR2(200) := NULL;
                        v_valor VARCHAR2(200);
                        v_url VARCHAR2(2000);
                        v_repserver varchar2(20) := 'step';
                         
                        v_report varchar2(100) := 'E:\MYREPORT.JSP';
                        v_PARAMETRO varchar2(100) := '';
                         pl paramlist ; 
                        BEGIN
                         pl := get_parameter_list('tmp') ; 
                         if not id_null(pl) 
                              then
                              destroy_parameter_list('tmp') ; 
                              else  
                              pl := create_parameter_list('tmp') ; 
                              end if ; 
                              add_parameter('tmp','p_ename',text_parameter,:t_ename) ; 
                              
                        v_repid := FIND_REPORT_OBJECT('REPORT9'); -- report is an element from object navigator report
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
                        SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
                        v_rep := RUN_REPORT_OBJECT(v_repid,'tmp');
                        v_rep_status := REPORT_OBJECT_STATUS(v_rep);
                        WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
                        v_rep_status := REPORT_OBJECT_STATUS(v_rep);
                        END LOOP;
                        IF v_rep_status = 'FINISHED' THEN
                        message(v_rep);
                        message(v_rep);
                        WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
                        SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
                        END IF;
                         
                        END;
                        then created the bind parameter ":p_ename" on the report like "select ename from emp where ename = :p_ename"
                        and modified its data type to char .
                        but when i press the button it show the whole data of the "ename" column not just the name i put into the :t_ename text item
                        ?

                        Edited by: semsem on Feb 20, 2013 3:03 AM
                        • 9. Re: how to run report with parameters from oracle forms 10g
                          salute-Salem
                          i've seen your example chris , and it's very good and simple but i can not test it ., i modified it to
                          DECLARE
                           v_report_id              Report_Object;
                           vc_report_job_id                  VARCHAR2(100);  /* unique id for each Report      request */
                           vc_rep_status                    VARCHAR2(100);           /* status of the Report job */
                                    
                           BEGIN
                                  
                                  v_report_id:= FIND_REPORT_OBJECT('report7');
                                  SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_COMM_MODE, SYNCHRONOUS);
                                  SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESTYPE,CACHE);
                           
                           
                                  SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_DESFORMAT, 'PDF');
                                  
                                  SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_SERVER, 'stepdev');
                                  SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
                                  
                                  vc_report_job_id:=RUN_REPORT_OBJECT(v_report_id);
                           
                           
                           
                                  vc_rep_status := REPORT_OBJECT_STATUS(vc_report_job_id);
                                  IF vc_rep_status='FINISHED' THEN
                           
                           web.show_document ('http://stepdev:8889/reports/rwservlet/getjobid='||
                           vc_report_job_id ||'?server='|| 'stepdev','_blank');
                                  ELSE
                                  message ('Report failed with error message '||vc_rep_status);
                                  END IF;
                          END;
                          it ends with "unable to run report" message , and 'report failed with error message terminated with error'
                          ??
                          ?

                          if any one could help with this example
                          • 10. Re: how to run report with parameters from oracle forms 10g
                            Michael Ferrante-Oracle
                            An unable to run report error means that there is a problem when RRO is called. It can be either on the Forms or Reports side. The easiest/fastest way to figure out which side the problem is occurring it to expose the currently recieved jobs sent to the Rep server. If your job was recieved but was unable to complete you will see a red X icon in the table. Clicking on will provide you with the details. For example, unable to connect to the db, invalid parameter/value, etc.

                            However, I have these questions:
                            <blockquote>1. What is the name of the host where Reports installation lives?
                            2. What is the name of the Reports server? This is not and should not be the same as the host name.</blockquote>
                            You should not have the host and rep server name the same
                            <blockquote><li>SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_SERVER, +'stepdev'+ );
                            <li>http:// stepdev* :8889/reports/rwservlet .... </blockquote>
                            As for looking at the recent jobs, do this:

                            http://stepdev:8889/reports/rwservlet/showjobs?server=<ENTER THE REPORTS SERVER NAME AND NOT THE HOST NAME>
                            • 11. Re: how to run report with parameters from oracle forms 10g
                              HamidHelal
                              SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'p_deptno='||:dept.deptno||'paramform=no');
                              modify the above line with bellow one
                              SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no p_deptno='||QT||:dept.deptno||QT); --- remember to add QT at your declaration
                              QT char(1):=''''; -- only at declaration section if you want to add single quote(')
                              and place it just place of
                              SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
                              at your first posted code

                              Hope this works.. :)

                              Hamid
                              • 12. Re: how to run report with parameters from oracle forms 10g
                                salute-Salem
                                thank to you all ,
                                special thanks for HamidHelal ,
                                but what is the need of "QT char(1):='''';"
                                i've tried it without QT and it worked even when i changed the parameter to "ename" -- a character column not a number like deptno ?

                                and if you could tell me how to work if i need to specify two or three parameters not one ?


                                thanks a lot
                                • 13. Re: how to run report with parameters from oracle forms 10g
                                  HamidHelal
                                  semsem wrote:
                                  thank to you all ,
                                  special thanks for HamidHelal ,
                                  but what is the need of "QT char(1):='''';"
                                  Hi semsem,
                                  instead of using single quote i use QT, it's easier to me if i forget to add single quote
                                  QT is just replace of '''' /'
                                  i've tried it without QT and it worked even when i changed the parameter to "ename" -- a character column not a number like deptno ?
                                  For number datatype, you didn't need of single quote or QT but for character and date you must need it. but in both data type you can use.


                                  >
                                  and if you could tell me how to work if i need to specify two or three parameters not one ?
                                  for multiple parameter my first example is right. Again i paste it
                                  SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no UNIT='||QT||:TXT_CID_ID||QT||' VOUCHER_TYPE='||QT||:TXT_VOUCHER_TYPE||QT);
                                  --UNIT AND VOUCHER_TYPE is parameter name. and can add more like this way.
                                  Hope this helps..

                                  Hamid

                                  Edited by: HamidHelal on Feb 21, 2013 4:14 PM