5 Replies Latest reply: Mar 9, 2013 3:39 PM by rp0428 RSS

    How to scheduled the procedure with passing the parameters from db table

    ChakravarthyDBA
      Hi

      I have two procedures which need to run one by one based time scheduled in table and I need to pass the parameters from database table for these procedures which is received from asp.net application then I need to update the procedure status to the same table as 'Schedule','Running','Error' when I got Error I need to store the error message to the table.

      Please help me on this
        • 2. Re: How to scheduled the procedure with passing the parameters from db table
          damorgan
          No version number and there is so little information in your inquiry I have no idea how to help you.

          It is easy to run scheduled jobs based on values in tables. But without a complete description of how you plan on using it I have no idea what to suggest. For example what behaviour do you expect if, while the job is running, someone updates a row in the table. Your description needs to be complete on all business rule issues related to this project.

          As SB suggested ... please read the FAQ.
          • 3. Re: How to scheduled the procedure with passing the parameters from db table
            ChakravarthyDBA
            Oracle version 11 G R2 and OS is Windows 2008 R2

            I find a solution how to run a job and passing parameters.
            now I want to know how to get the procedure status Started,Running, Successes,Error

            If any Error, need to update Error message and I need to update the status to my table where it is scheduled to run in table.
            • 4. Re: How to scheduled the procedure with passing the parameters from db table
              sb92075
              ChakravarthyDBA wrote:
              Oracle version 11 G R2 and OS is Windows 2008 R2

              I find a solution how to run a job and passing parameters.
              now I want to know how to get the procedure status Started,Running, Successes,Error

              If any Error, need to update Error message and I need to update the status to my table where it is scheduled to run in table.
              pick one of the below to query

              DBA_SCHEDULER_CHAINS
              DBA_SCHEDULER_CHAIN_RULES
              DBA_SCHEDULER_CHAIN_STEPS
              DBA_SCHEDULER_CREDENTIALS
              DBA_SCHEDULER_DB_DESTS
              DBA_SCHEDULER_DESTS
              DBA_SCHEDULER_EXTERNAL_DESTS
              DBA_SCHEDULER_FILE_WATCHERS
              DBA_SCHEDULER_GLOBAL_ATTRIBUTE
              DBA_SCHEDULER_GROUPS
              DBA_SCHEDULER_GROUP_MEMBERS
              DBA_SCHEDULER_JOBS
              DBA_SCHEDULER_JOB_ARGS
              DBA_SCHEDULER_JOB_CLASSES
              DBA_SCHEDULER_JOB_DESTS
              DBA_SCHEDULER_JOB_LOG
              DBA_SCHEDULER_JOB_ROLES
              DBA_SCHEDULER_JOB_RUN_DETAILS
              DBA_SCHEDULER_NOTIFICATIONS
              DBA_SCHEDULER_PROGRAMS
              DBA_SCHEDULER_PROGRAM_ARGS
              DBA_SCHEDULER_REMOTE_DATABASES
              DBA_SCHEDULER_REMOTE_JOBSTATE
              DBA_SCHEDULER_RUNNING_CHAINS
              DBA_SCHEDULER_RUNNING_JOBS
              DBA_SCHEDULER_SCHEDULES
              DBA_SCHEDULER_WINDOWS
              DBA_SCHEDULER_WINDOW_DETAILS
              DBA_SCHEDULER_WINDOW_GROUPS
              DBA_SCHEDULER_WINDOW_LOG
              DBA_SCHEDULER_WINGROUP_MEMBERS
              • 5. Re: How to scheduled the procedure with passing the parameters from db table
                rp0428
                >
                Oracle version 11 G R2 and OS is Windows 2008 R2

                I find a solution how to run a job and passing parameters.
                now I want to know how to get the procedure status Started,Running, Successes,Error

                If any Error, need to update Error message and I need to update the status to my table where it is scheduled to run in table.
                >
                You either need to add coded to your procedures to log their status to a LOG table or create new wrapper proceders.

                1. the 'job' would call the wrapper procedure.
                2. the wrapper procedure would check the status table to make sure the procedure is not already being executed (e.g. STATUS=IDLE).
                3. the wrapper procedure would update the procedure status to RUNNING and then execute your current procedure that now does the actual work.
                4. when the actual procedure completes the wrapper procedure would update the STATUS table with SUCCES or ERROR as appropriate.

                Typically you would actually store the parameters the procedure needs in a table and NOT pass the parameters to the job. The parameters passed to the job would only tell it basic things like which procedure to run.

                That makes it easier to run wrapper procedures in parallel without running the same process multiple times concurrently.