5 Replies Latest reply: Jun 27, 2014 1:42 AM by Jani Rautiainen-Oracle RSS

    ESS job parent request

    User12354256-Oracle

      Hi All,

       

      I have registered one pl/sql ess job.The pl/sql job internally has to submit another ess job.To submit ess job from pl/sql I am using the API "ESS_RUNTIME.SUBMIT_REQUEST".

       

      In total I have three ess jobs.Depends upon the state of the 2nd ess job,I have to call another ess job.3rd ess job has to wait untill second ess job complets.How can we make them wait.

       

      Thanks in advance.

       

      Regars,

      Venkat.

        • 1. Re: ESS job parent request
          Jani Rautiainen-Oracle

          Instead of dealing with managing state of the jobs have you considered Defining and Using Job Sets to coordinate the execution ?

          --

          Jani Rautiainen

          Fusion Applications Developer Relations

          https://blogs.oracle.com/fadevrel/

          • 2. Re: ESS job parent request
            User12354256-Oracle

            Hi Jani,

             

            The first ess job  that we created has to be integrated with web service. Because of this reason only we haven't considered Job Sets concept.

             

            It would be great help,if you share any wait request information.

             

            Regards,

            Venkat.

            • 3. Re: ESS job parent request
              Jani Rautiainen-Oracle

              I am not sure why the web service integration prevents from using Job Sets, I think the recommendation is to use Job Sets if possible, see the following documents in the support system:

               

              That said if you truly cannot use Job Sets then you could potentially consider Using Subrequests. If the approach described in Example 14-1 is not sufficient can you elaborate exactly what is needed for your use case ?

              --

              Jani Rautiainen

              Fusion Applications Developer Relations

              https://blogs.oracle.com/fadevrel/

              • 4. Re: ESS job parent request
                User12354256-Oracle

                Hi Jani,

                 

                Thanks for responding.

                 

                Actually I have already gone through notes that you mentioned above.I am using Sub-requests concept only to achieve this.The example code that mentioned,is not working out.The API "

                ess_runtime.get_reqprop_varchar(fnd_job.job_request_id, 'PAUSED_STATE')" has some problem.While executing this API,it is going into exception.And also "ess_job.PAUSED_STATE" is not available in the DB itself.

                 

                Could you please share any relevant code units if you have any thing.

                 

                Regards,

                Venkat

                • 5. Re: ESS job parent request
                  Jani Rautiainen-Oracle

                  AFAIK the sample provided should work, this may be a documentation issues as the "ess_job.PAUSED_STATE" does not exist in my Rel8 DB either instead there is "ess_runtime.PAUSED_STATE". I'll ask ESS team to comment, while waiting the Using Subrequests (14.5) document does mention:

                  For a SQL request, ess_runtime.submit_subrequest is used to submit the subrequest. The parent request must call ess_runtime.mark_paused when it is ready for the subrequest to run, commit the transaction and return successfully, without rasing an exception. The mark_paused method informs Oracle Enterprise Scheduler that, upon successful return from the parent request procedure, the parent request should be set to PAUSED and the subrequest allowed to execute. The mark_paused method supports an optional argument by which the paused state string can be specified.

                  It is important to note that subrequest will not be executed until the parent request has called mark_paused, commits, and returns normally, without raising an exception. If an exception is raised, Oracle Enterprise Scheduler will not set parent request to PAUSED state, but instead, it the parent state will be set to ERROR or WARNING depending on the SQL error code. Furthermore, the subrequests will be automatically CANCELLED and will not be executed.

                  Once the subrequest has finished, PL/SQL procedure for the parent request will be re-executed again as resumed request, similar to what occurs for a Java Executable.

                  Can you try in your parent code:

                  • Call ess_runtime.mark_paused
                  • commit the transaction
                  • return successfully

                  --

                  Jani Rautiainen

                  Fusion Applications Developer Relations

                  https://blogs.oracle.com/fadevrel/