How to use Oracle Apex session in scheduled job

Question

I'm starting a plsql procedure from my Oracle Apex application using dbms_scheduler.create_job. The job starts immediately.

The procedure fails because it uses Oracle Apex collections and it won't run outside the Oracle Apex application context.

There are some code examples on the internet showing how to create an Oracle Apex application context but I was wondering if it's possible to use the existing context. For example by passing the session ID to the job and reusing it there.

If this is possible, how can it be done and will this be different for Oracle Apex 4.02 and 5.02?

Answer 1

Create a type, pass it to a procedure used in your job. Create job with parameters.

Including example with table type including Blob and filename:

CREATE OR REPLACE TYPE blob_files AS OBJECT(
              attachment BLOB,
              filename varchar2(255),
              mime_type varchar2(255)
             )

create or replace type blobs as table of blob_files;

declare
  attachments BLOBS;
  i NUMBER;
begin
  select blob_files(invoice_blob,invoice_filename, mime_type) 
  BULK COLLECT INTO attachments
  from receive.inv_invoice_blobs
  where invoice_id = 223730;

  i := attachments.FIRST;
  WHILE i IS NOT NULL LOOP
    htp.p(dbms_lob.getlength(attachments(i).attachment) || ' ' || attachments(i).filename);
    i := attachments.NEXT(i);
  END LOOP;
end;