This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 3, 2010 3:42 AM by 511905 RSS

CALL A REPORT FROM ORACLE 10G FORMS

511905 Newbie
Currently Being Moderated
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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    try starting a server ..

    rwserver server=rep1
  • 8. Re: CALL A REPORT FROM ORACLE 10G FORMS
    511905 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    start from your windows ...

    rwserver server=rep1
  • 10. Re: CALL A REPORT FROM ORACLE 10G FORMS
    511905 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points