CODEWITHSUNDEEP
oracle-databaseplsql
Scouse_Bob
Scouse_Bob

Reputation: 546

DBMS_APPLICATION_INFO seems to work in procedure but not in a job

The below sample code illustrates an issue I am having with dbms_application_info. If I use it in the below procedure:

create or replace procedure test01 is
vsql varchar2(50);
begin
vsql := 'select sysdate from dual';
execute immediate vsql;
DBMS_APPLICATION_INFO.SET_MODULE('TEST','Starting...');
dbms_lock.sleep ( 10 );
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
end;
/

exec test01;

Then querying v$session reveals "Starting..." as I would hope!

However, it is necessary to run the related procedure in a job. If I do this, then I cannot see "Starting..."

declare 
JNAME varchar2(200) := to_char(sysdate, 'YYYYMMDDHHMiSS');
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST01_'||JNAME,
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN TEST.TEST01; END;',
    start_date      => NULL,
    repeat_interval => NULL,
    enabled         => TRUE);
END;
/

This code should be executable by anyone who potentially wishes to have a look and perhaps help me understand why this would be?

Thank you! --> Scouse.

Upvotes: 0

Views: 320

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

You must use the INTO clause in your execute immediate statement

create or replace procedure test01 is
vsql varchar2(50);
l_date DATE;
begin
vsql := 'select sysdate from dual';
execute immediate vsql into l_date;  --<<<< here
DBMS_APPLICATION_INFO.SET_MODULE('TEST','Starting...');
dbms_lock.sleep ( 20 );
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
end;
/

Using INTO clause both module and action are set.

As documented if you ommit the INTO clause no exception is raised but

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.

So I'd expect the application info should be set even without INTO clause, but from some reason it is not. Anyway using INTO it works fine.

I tested on 12.1

Upvotes: 1

Related Questions