1 2 Previous Next 19 Replies Latest reply: Mar 3, 2010 5:42 AM by 511905 RSS

    CALL A REPORT FROM ORACLE 10G FORMS

    511905
      HI,

      I HAVE STARTED TO WORK WITH ORACLE DATABASE 11G AND DEVELOPER SUIT 10G.
      I HAVE A REPORT THAT IT WORKS CORRECTLY WHEN I RUN IT FROM REPORT DEVELOPER.
      BUT WHEN I WANT TO CALL IT FROM A FORM, IT DOESN'T WORK.
      IN ORACLE DEVELOPER 6I, I USE "RUN_PRODUCT" COMMAND AND IT WORKS WELL ,BUT IN
      DEVELOPER SUIT 10G I HAVE TO USE "RUN_REPORT_OBJECT" AND I DON'T KNOW HOW.
      WHEN I COMPILE MY CODES THERE IS NO ERROR ,BUT WHEN I RUN IT, IT SHOWS ME SOME ERRORS .
      THIS IS MY CODES FROM ORACLE HELP :

      DECLARE  
      repid REPORT_OBJECT; 
      v_rep VARCHAR2(100); 
      rep_status VARCHAR2(20);
      BEGIN
      repid := FIND_REPORT_OBJECT('D:\TESTPROJ\REPORT1.JSP'); 
      v_rep := RUN_REPORT_OBJECT(repid);
      END;

      IT SHOWS ME THIS ERROR : FRM-41219 .CANNOT FIND REPORT. INVALID ID.
      AND THEN : FRM-40738. ARGUMENT 1 TO BUILTIN RUN_REPORT_OBJECT CANNOT BE NULL;


      PLEASE HELP ME TO SOLVE THIS PROBLEM

      THANKFUL
      MAHSA
        • 1. Re: CALL A REPORT FROM ORACLE 10G FORMS
          Manu.
          Mahsa,

          Welcome to the Forum :)

          To run a report from 10g, you need to add an item under the Reports section in the Object Navigator View of form. Then write the following code to call the report.
          PROCEDURE PRINT_REP_WEB IS
               RO_Report_ID          REPORT_OBJECT;
               Str_Report_Server_Job VARCHAR2(100);
               Str_Job_ID            VARCHAR2(100);
               Str_URL               VARCHAR2(100);
               PL_ID                 PARAMLIST ;
          BEGIN
               PL_ID := GET_PARAMETER_LIST('TEMPDATA');
               IF NOT ID_NULL(PL_ID) THEN
                    DESTROY_PARAMETER_LIST(PL_ID);
               END IF;
                PL_ID := CREATE_PARAMETER_LIST('TEMPDATA');
               RO_Report_ID := FIND_REPORT_OBJECT('REPORT_OBJ');
               ADD_PARAMETER(PL_ID, '<user_parameter_name>',     TEXT_PARAMETER, '<parameter_value>');
          
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_FILENAME, '<report_file_path>');
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_COMM_MODE, SYNCHRONOUS);
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_EXECUTION_MODE, BATCH);
               
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_DESTYPE, FILE);
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_DESFORMAT, 'PDF');
               SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_SERVER, '<report_server_name>');
               
               Str_Report_Server_Job := RUN_REPORT_OBJECT(RO_Report_ID, PL_ID);
               Str_Job_ID := SUBSTR(Str_Report_Server_Job, LENGTH('<report_server_name>') + 2, LENGTH(Str_Report_Server_Job));
               Str_URL       := '/reports/rwservlet/getjobid' || Str_Job_ID || '?server=<report_server_name>';
               WEB.SHOW_DOCUMENT(Str_URL, '_SELF');
               DESTROY_PARAMETER_LIST(PL_ID);
          END;
          Regards,


          Manu.



          If my response or the response of another was helpful, please mark it accordingly

          Edited by: Manu. on Mar 2, 2010 11:48 AM
          • 2. Re: CALL A REPORT FROM ORACLE 10G FORMS
            511905
            Thank you for the reply,
            I have found some samplse like what you said, But I don't know what is my Report-Server-Name.
            I searched a lot to understand how I must get my Server-Name, But I didn't find anything.
            Could you please tell me how can I find my Report-Server-Name ?

            Thankful,
            Mahsa
            • 3. Re: CALL A REPORT FROM ORACLE 10G FORMS
              Manu.
              Mahsa,

              The Report server means the Oracle server name from which the reports are running. If you are using Oracle Application Server, check in the folder *<application_server_home>\reports\server*, and if you are using Local OC4J Instance, then check in the folder *<dev_suite_home>\reports\server*. There you can find a file starting like rep_. This is your report server name.


              Regards,


              Manu.




              If my response or the response of another was helpful, please mark it accordingly
              • 4. Re: CALL A REPORT FROM ORACLE 10G FORMS
                511905
                Thanks, But that folder in <dev_suite_home>\reports\serve is empty.
                I'm using Local OC4J Instance.

                Is there any problem in my installation?
                If there is a problem, Why my report is working correctly in Report builder ?
                • 5. Re: CALL A REPORT FROM ORACLE 10G FORMS
                  Manu.
                  Mahsa,

                  If you are using local OC4J Instance, then the report server name is Rep_<your_computer_name> And the file will be generated at the first run of the report from form.


                  Regards,


                  Manu.



                  If my response or the response of another was helpful, please mark it accordingly
                  • 6. Re: CALL A REPORT FROM ORACLE 10G FORMS
                    511905
                    Manu,

                    I added my report to Form-Navigator and I changed it's name to REP1.
                    I added all you codes to When-Button-Press event like this :

                    DECLARE
                         RO_Report_ID REPORT_OBJECT;
                         Str_Report_Server_Job VARCHAR2(100);
                         Str_Job_ID VARCHAR2(100);
                         Str_URL VARCHAR2(100);
                         PL_ID PARAMLIST ;
                    BEGIN
                         PL_ID := GET_PARAMETER_LIST('TEMPDATA');
                         IF NOT ID_NULL(PL_ID) THEN
                              DESTROY_PARAMETER_LIST(PL_ID);
                         END IF;
                         PL_ID := CREATE_PARAMETER_LIST('TEMPDATA');
                         RO_Report_ID := FIND_REPORT_OBJECT('REP1'); -- This is my report's name in Form-Navigator. Is it correct to put it here ?
                         ADD_PARAMETER(PL_ID, 'P_SELECT_CITY',     TEXT_PARAMETER, :CITY); --These are my parameter in REPORT and it's Value in my FORM*

                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_FILENAME, 'D:\projects\PROJECT_OF_1388\TestOraDev10g\TEST_REP1');
                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_COMM_MODE, SYNCHRONOUS);
                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_EXECUTION_MODE, BATCH);
                         
                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_DESTYPE, FILE);
                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_DESFORMAT, 'PDF');
                         SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_SERVER, 'Rep_faraji'); -- as you said, I put my computer name after REP_   here
                         
                         Str_Report_Server_Job := RUN_REPORT_OBJECT(RO_Report_ID, PL_ID);
                         Str_Job_ID := SUBSTR(Str_Report_Server_Job, LENGTH('Rep_faraji') + 2, LENGTH(Str_Report_Server_Job)); -- as you said, I put my computer name after REP_   here
                         Str_URL      := '/reports/rwservlet/getjobid' || Str_Job_ID || '?server=Rep_faraji'; -- as you said, I put my computer name after REP_   here
                         WEB.SHOW_DOCUMENT(Str_URL, '_SELF');
                         DESTROY_PARAMETER_LIST(PL_ID);
                    END;



                    But it doesn't work again, And shows this error :
                    FRM-41213. unable to connect to the report server Rep_faraji

                    Edited by: user508902 on Mar 2, 2010 2:18 AM

                    Edited by: user508902 on Mar 2, 2010 2:20 AM
                    • 7. Re: CALL A REPORT FROM ORACLE 10G FORMS
                      527733
                      try starting a server ..

                      rwserver server=rep1
                      • 8. Re: CALL A REPORT FROM ORACLE 10G FORMS
                        511905
                        Sorry can you explain more ?
                        What do you mean with "try start a server ..."

                        How can I do that?
                        • 9. Re: CALL A REPORT FROM ORACLE 10G FORMS
                          527733
                          start from your windows ...

                          rwserver server=rep1
                          • 10. Re: CALL A REPORT FROM ORACLE 10G FORMS
                            511905
                            Manu,

                            Your codes worked and opened a new page http://faraji:8889/reports/rwservlet/getjobid9?server=Rep_faraji
                            But it has a error : REP-52251: Cannot get output of job ID 9 you requested on Tue Mar 02 15:16:44 IRST 2010.<P>REP-51026: No output for job 9
                            It increases JobID every time I run it .

                            This error occurs due to the last lines that was about job


                            Could you please describe these lines :
                            Str_Report_Server_Job := RUN_REPORT_OBJECT(RO_Report_ID, PL_ID);
                                 Str_Job_ID := SUBSTR(Str_Report_Server_Job, LENGTH('<report_server_name>') + 2, LENGTH(Str_Report_Server_Job));
                                 Str_URL      := '/reports/rwservlet/getjobid' || Str_Job_ID || '?server=<report_server_name>';
                                 WEB.SHOW_DOCUMENT(Str_URL, '_SELF');
                            • 11. Re: CALL A REPORT FROM ORACLE 10G FORMS
                              Manu.
                              Mahsa,

                              RUN_REPORT_OBJECT built-in used to run a report. when this line is executed, the report starts generating the output.

                              Try changing the line
                              SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_FILENAME, 'D:\projects\PROJECT_OF_1388\TestOraDev10g\TEST_REP1'); 
                              to
                              SET_REPORT_OBJECT_PROPERTY(RO_Report_ID, REPORT_FILENAME, 'D:\projects\PROJECT_OF_1388\TestOraDev10g\TEST_REP1.rdf'); 
                              Regards,


                              Manu.
                              • 12. Re: CALL A REPORT FROM ORACLE 10G FORMS
                                511905
                                Manu,

                                I changed it, But same error occurred .

                                REP-52251: Cannot get output of job ID 21 you requested on Tue Mar 02 15:58:22 IRST 2010.<P>REP-51026: No output for job 21





                                I don't know what should I do, It's making me crazy.


                                Grateful,
                                Mahsa
                                • 13. Re: CALL A REPORT FROM ORACLE 10G FORMS
                                  Manu.
                                  Mahsa,

                                  In the addressbar of the explorer, in whihc the error is coming will be something like this.
                                  http://localhost:8889/reports/rwservlet/getjobid374?server=Rep_faraji
                                  In that change the getjobid to showjobid And check what is written in there.



                                  Regards,


                                  Manu.
                                  • 14. Re: CALL A REPORT FROM ORACLE 10G FORMS
                                    511905
                                    Manu,

                                    Error changed like this :

                                    Job Status

                                    Security Mode Non-secure
                                    Queue on server Rep_faraji, on Tue Mar 02 16:17:14 IRST 2010 To kill a current (enqueued or scheduled) job, click on the status icon for the specific job. Then click on Cancel Job button in next page.To get a cached output of a successfully finished past job, click on the Job Name hyperlink of that job (if available).

                                    Past Jobs

                                    Job ID , Job Type , Job Name , Job Status , Job Owner , Output Type , Output Name , Server Name , Queued At , Started At , Finished At
                                    24 , report , D:\projects\PROJECT_OF_1388\TestOraDev10g\TEST_REP2.rdf , * , ANONYMOUS , File , undefined , rep_faraji , Mar 2, 2010 4:17:14 , Mar 2, 2010 4:17:14 PM , Mar 2, 2010 4:17:14 PM



                                    When i click on it , It shows me this : Terminated with error: <br>REP-501: Unable to connect to the specified database.

                                    Grateful,
                                    Mahsa
                                    1 2 Previous Next