2 Replies Latest reply: Nov 16, 2012 12:31 AM by JasperA RSS

    APEX_PLSQL_JOB.SUBMIT_PROCESS with no results

    JasperA
      Hi,

      When I run APEX_PLSQL_JOB.SUBMIT_PROCESS I can see the process ran in the view APEX_PLSQL_JOBS from the sql workshop, but nothing has happened.

      For instance: when I submit the following, no results show up in the table.

      Any ideas as to how I can check what's going wrong?

      DECLARE
      t_job_id NUMBER;
      t_sql VARCHAR2(4000);
      --
      BEGIN
      t_sql := 'BEGIN INSERT INTO EXAMPLE ( COL1, COL2, COL3, COL4) '||
      ' VALUES (1, 763707, 20, 30 ); END; ';
      --
      t_job_id := APEX_PLSQL_JOB.SUBMIT_PROCESS (p_sql => t_sql);
      apex_debug_message.log_message('Job started: '||t_job_id);
      COMMIT;
      END;

      I am using APEX version 4.1.1.00.23 on Oracle 11g
        • 1. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS with no results
          Prabodh
          Hi,
          When I run APEX_PLSQL_JOB.SUBMIT_PROCESS I can see the process ran in the view APEX_PLSQL_JOBS from the sql workshop, but nothing has happened.
          For instance: when I submit the following, no results show up in the table.

          Any ideas as to how I can check what's going wrong?
          >
          This may be because the job has an error. Check DBA_ERRORS View for errors.

          Always post your code snippets in a pair of {code} tags as explained in the FAQ.
          >
          DECLARE
          t_job_id NUMBER;
          t_sql VARCHAR2(4000);
          --
          BEGIN
          t_sql := 'BEGIN INSERT INTO EXAMPLE ( COL1, COL2, COL3, COL4) '||
          ' VALUES (1, 763707, 20, 30 ); END; ';
          --
          t_job_id := APEX_PLSQL_JOB.SUBMIT_PROCESS (p_sql => t_sql);
          apex_debug_message.log_message('Job started: '||t_job_id);
          COMMIT;
          END;
          I am using APEX version 4.1.1.00.23 on Oracle 11g>

          In the above code, are all the columns of type Number? VARCHAR2 columns will need to be in quotes, right?

          Also check the Debug report of the page after running it in Debug mode. Maybe you will get some clues there too.

          Cheers,
          • 2. Re: APEX_PLSQL_JOB.SUBMIT_PROCESS with no results
            JasperA
            The "problem" here is that if something goes wrong inside that process, there's no place where the error-message turns up. The only way to know is that the result is not as expected.