5 Replies Latest reply: Jun 19, 2009 10:20 AM by pjturley RSS

    APEX_PLSQL_JOB.SUBMIT_PROCESS not working

    Martin Giffy D'Souza
      Hi,

      I'm trying to run APEX_PLSQL_JOB.SUBMIT_PROCESS (see: [http://download.oracle.com/docs/cd/E14373_01/apirefs.32/e13369/apex_plsql_job.htm#insertedID3|http://download.oracle.com/docs/cd/E14373_01/apirefs.32/e13369/apex_plsql_job.htm#insertedID3]

      I have on an On Load before Header page process:
      DECLARE
        v_num                         PLS_INTEGER;
      BEGIN
        IF apex_plsql_job.jobs_are_enabled THEN
          HTP.p ('Jobs are enabled on this database instance.');
        ELSE
          HTP.p ('Jobs are NOT enabled on this database instance.');
        END IF;
      
        v_num                      := apex_plsql_job.submit_process (p_sql                        => 'BEGIN null; END;');  -- I kept the sql code to a minimum for demonstration purposes
        HTP.br;
        HTP.p (v_num);
      END; 
      The output is:

      Jobs are enabled on this database instance.
      +10083+

      It seems to work (no errors) but it is not registering in the APEX_PLSQL_JOBS view. When I do have "working" code in the p_sql parameter I know it isn't getting executed.
      SELECT *
      FROM   apex_plsql_jobs
      Any ideas on this? I'm using APEX 3.1.2 on Oracle 10.2.0.4.0

      Thank you,

      Martin

      -----
      [http://apex-smb.blogspot.com/]
        • 1. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS not working
          pjturley
          Hi Martin,

          The APEX_PLSQL_JOB.SUBMIT_PROCESS function populates table WWV_FLOW_JOBS in the FLOWS_030nnn schema. The view apex_plsql_jobs references that table so can you see any data in the WWV_FLOW_JOBS table? The privileges to this table are restricted so you will need to log in as sys to see it.

          Also, if you run the PL/SQL block you have in the On Load before Header process in the SQL Workshop and then run SELECT * FROM apex_plsql_jobs does it return any records.

          Regards

          Paul
          • 2. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS not working
            Martin Giffy D'Souza
            Hi Paul,

            It's not in the WWV_FLOW_JOBS table.

            Martin

            -----
            [http://apex-smb.blogspot.com/]
            • 3. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS not working
              Z?
              Hey

              apex_plsql_jobs relies on
              SELECT nv( 'FLOW_SECURITY_GROUP_ID' ) sgid
                                           FROM DUAL
              So you need to run it within your apex session. I created a new page with your On-load process and an unconditional report with a source of
              SELECT *
              FROM apex_plsql_jobs
              Everything shows up fine here for me (not if queried through SQLplus though as security group isn't set-up).

              Cheers

              Ben
              http://www.munkyben.wordpress.com
              Don't forget to mark replies helpful or correct ;)
              • 4. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS not working
                Martin Giffy D'Souza
                Hi Paul,

                I made a mistake, it's in the WWV_FLOW_JOBS table... I'll have to look at the view to see why it isn't showing up (I think the next post has something on this).

                Thank you,

                Martin

                -----
                [http://apex-smb.blogspot.com/]
                • 5. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS not working
                  pjturley
                  Hi Martin,

                  If it is in the WWV_FLOW_JOBS table then the job has been submitted successfully. Is the system_status column set to 'COMPLETE'?

                  You only need to set the security group if you are running the code outside of APEX.

                  Have a look at my example on apex.oracle.com

                  Workspace : EDIDEV
                  Username : USER
                  Password : jadohi


                  Run App Flow Jobs Example - 33462


                  Enter a value in Enter Val Text field and press Submit

                  The process will insert a record in the flow jobs table, you may need to press refresh to see the job complete and for the value to appear in the PT_TEMP_TABLE

                  Regards

                  Paul