How can a PL/SQL procedure tell if it is being run from a concurrent program?

Question

I want to write a procedure that logs output to the Oracle concurrent manager log when run from a concurrent program, but writes to dbms_output when run "standalone".

Is there a way from PL/SQL to check whether my code is being run from a concurrent request? The best way I've been able to find is

select * from fnd_concurrent_requests
where oracle_session_id = userenv('SESSIONID');

but that's pretty slow. Is there a function or table I can query that gives me the information more efficiently?

Not sure if it would work, but have you tried the FND_GLOBAL package? e.g. `FND_GLOBAL.CONC_PROGRAM_ID` — kfinity, Nov 15 '21 at 16:33
Thanks! I wasn't aware of that (or `FND_GLOBAL.CONC_REQUEST_ID` which appears similar). These seem like a really good way of checking, as they appear to return -1 if you're not in a concurrent program. — Paul Moore, Nov 16 '21 at 10:32

score 4 · Accepted Answer · answered Nov 16 '21 at 10:51

You can best use fnd_global.conc_request_id like we do in our blitz report code:

procedure write_log(p_text in varchar2, p_log_level in number default 1) is
begin
  if fnd_global.conc_request_id>0 then
    fnd_file.put_line(fnd_file.log,p_text);
  else
    fnd_log.string(p_log_level,'XXEN',p_text); --or your dbms_output.put_line() call
  end if;
end write_log;

MT0 · Answer 2 · 2021-11-15T16:21:15.517

Add a boolean flag argument to the procedure that you can use to tell it where you want to log to when you call the procedure and then pass different flags from your two different (concurrent/non-concurrent) programs:

CREATE PROCEDURE my_proc(
  i_value1                 IN NUMBER,
  i_use_concurrent_logging IN BOOLEAN DEFAULT FALSE
)
IS
  -- Helper function so you only check the flag in one place.
  PROCEDURE log(value IN VARCHAR2)
  IS
  BEGIN
    IF i_use_concurrent_logging THEN
      -- put your concurrent logging code here.
      NULL;
    ELSE
      DBMS_OUTPUT.PUT_LINE(value);
    END IF;
  END;
BEGIN
  -- Do stuff.
  
  log('Stuff done');

  -- Do other stuff

  log('Other Stuff done');
END;
/

If you want to use your check once in the procedure then you could use:

CREATE OR REPLACE PROCEDURE my_proc(
  i_value1                 IN NUMBER
)
IS
  v_use_concurrent_logging BOOLEAN := FALSE;
  
  PROCEDURE log(value IN VARCHAR2)
  IS
  BEGIN
    IF v_use_concurrent_logging THEN
      -- put your concurrent logging code here.
      NULL;
    ELSE
      DBMS_OUTPUT.PUT_LINE(value);
    END IF;
  END;

BEGIN
  DECLARE
    v_exists INT;
  BEGIN
    SELECT 1
    INTO   v_exists
    FROM   fnd_concurrent_requests
    WHERE  oracle_session_id = userenv('SESSIONID')
    AND    ROWNUM = 1;
    
    v_use_concurrent_logging := TRUE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_use_concurrent_logging := FALSE;
  END;

  -- Do stuff.
  
  log('Stuff done');

  -- Do other stuff

  log('Other Stuff done');
END;
/

db<>fiddle here

Thanks. That's an option, but I'm writing a series of functions in the same package, all called from a main procedure, and I was hoping to be able to test the procedures individually. Having to add the parameter everywhere would be a nuisance. I guess I could have a package global that I set once (in main) which defaults to "not in a concurrent job". That would work. It does seem surprising to me that there's no way to detect this directly, though :-( — Paul Moore, Nov 15 '21 at 16:26

How can a PL/SQL procedure tell if it is being run from a concurrent program?

2 Answers2