Here is the How To of scripting them instead

Oracle Data Pump can import simple programs without arguments, but it fails when handling Scheduler-Programs that contain arguments. This has been a persistent issue with no official resolution.
Since this was a critical challenge in a recent project of mine, I’d like to share my solution.

In rare cases, the scripts are available and can be reused, but when you need to dig through Change Requests, JIRA tickets or the Document Management System (DMS) because the developer has already retired, you’re often left with no choice but to reengineer the solution.

At first, I started with a schema-export and import which generated a log file with 15,000 lines and hundreds of error messages. I was puzzled by this message:
There were more of them, however I would like to use this single Scheduler-Program as example.

Here’s an example of an error message I encountered:
19-OCT-24 10:55:09.222: ORA-39083: Object type PROCOBJ:"MMTK"."MAIL_MMTK_PRG" failed to create with error:
ORA-06550: line 8, column 265:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 21:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 5:
PLS-00364: loop index variable 'ARG' use is invalid
ORA-06550: line 11, column 2:
PL/SQL: Statement ignored

Failing sql is:
BEGIN 
dbms_scheduler.create_program('"MAIL_MMTK_PRG"','STORED_PROCEDURE',
'PKG_JOBMMTK.prc_wmail'
,18, FALSE,
'Send Mail'
);
DECLARE 
CURSOR prog_args IS SELECT replace(a.argument_name, '''', '''''') arg_name,  a.argument_position position, a.argument_type type_name, a.metadata_attribute int_arg_type, a.default_anydata_value value, decode(a.out_argument,'FALSE',0,'TRUE',1) out_flag  FROM SYSTEM.SCHEDULER_PROGRAM_ARGS_TMP a  where a.owner='MMTK' and  a.program_name='MAIL_MMTK_PRG';
 BEGIN
FOR arg in prog_args LOOP
 IF arg.int_arg_type IS NULL THEN
 dbms_scheduler.define_anydata_argument('"MAIL_MMTK_PRG"' , arg.position,  CASE WHEN arg.arg_name IS NULL THEN NULL ELSE 
     '"'||arg.arg_name||'"' END, arg.type_name, arg.value, arg.out_flag=1 );
 ELSE
 dbms_scheduler.define_metadata_argument('"MAIL_MMTK_PRG"' ,arg.int_arg_type, arg.position, CASE WHEN arg.arg_name IS NULL THEN NULL ELSE 
     '"'||arg.arg_name||'"' END );
 END IF;
 END LOOP;
 END;
dbms_scheduler.enable('"MAIL_MMTK_PRG"');COMMIT; END;

The first hint (ORA-00942) suggests a missing table or privilege, but which one?
Maybe the SYSTEM.SCHEDULER_PROGRAM_ARGS_TMP in the DECLARE-block? It does not exist. Sounds like a temporary view during import.
The second hint about variable ‘ARG’ is mysterious – though the loop variable appears syntactically correct, it still triggers an error.
Furthermore, in another Scheduler-Program, I found the same error.
Although the Scheduler-Programs worked fine in the existing environment, I could not import them. Missing tables or permissions as the reason for the errors could be excluded after some work.
Somewhere on the internet, I found the information, that this is an unsolved problem. A request for enhancement is pending …

Now, how can we script these Scheduler-Programs?
dbms-metadata? Good idea! Let’s see what it delivers:

To extract the definition of the Scheduler-Program, I used the following command:
select dbms_metadata.get_ddl('PROCOBJ','MAIL_MMTK_PRG','MMTK') from dual;

BEGIN
  dbms_scheduler.create_program('MAIL_MMTK_PRG','STORED_PROCEDURE',
  'PKG_JOBMMTK.prc_wmail', 18, FALSE, 'Send Mail'
);
COMMIT;
END;
/

Nice 😐
It retrieves the Scheduler-Program itself, but not its arguments ☹
This is just the “failing sql” we have seen in the error message above.

Using USER_SCHEDULER_PROGRAM_ARGS allows us to reconstruct missing arguments
select argument_position, argument_name, argument_type, default_value 
 from USER_SCHEDULER_PROGRAM_ARGS 
 where  program_name = 'MAIL_MMTK_PRG' order by   argument_position;

ARGUMENT_POSITION ARGUMENT_NAME      ARGUMENT_TYPE  DEFAULT_VALUE
----------------- ------------------ -------------- --------------------
                1 P_HOST             VARCHAR2       mailhost
                2 P_DOMAIN           VARCHAR2       mycompany.com
                3 P_PORTNUM          NUMBER         25
                4 P_FROM             VARCHAR2
                5 P_TO               VARCHAR2
                6 P_CC               VARCHAR2
                7 P_BCC              VARCHAR2
                8 P_REPLYTO          VARCHAR2
                9 P_SUBJECT          VARCHAR2
               10 P_MSGTXT           VARCHAR2
               11 P_CONTENTS         VARCHAR2
               12 P_ATTACH           VARCHAR2
               13 P_URL              VARCHAR2
               14 P_TRACEMODE        VARCHAR2       N
               15 P_MIMETYPE         VARCHAR2       text/plain
               16 P_DISPOSITION      VARCHAR2       attachment
               17 P_CHARSET          VARCHAR2       iso-8859-1
               18 P_TRANSFERTENCODE  VARCHAR2

I was not aware that Emails need to have 18 arguments, but ok
Now I started puzzling pieces. The syntax for an argument is:

dbms_scheduler.define_program_argument(
 program_name      => 'MAIL_MMTK_PRG',
 argument_position => 1,
 argument_name     => 'P_HOST',
 argument_type     => 'VARCHAR2',
 default_value     => 'mailhost'  );

copy this block 18 times and insert the values from listing above.
Yes, I could have done some clever dynamic-SQL …

At the end, the entire code block looks like that:
Before, you need to create the scheduler-program as seen above – of course …

begin
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 1,
    argument_name     => 'P_HOST',
    argument_type     => 'VARCHAR2',
    default_value     => 'mailhost'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 2,
    argument_name     => 'P_DOMAIN',
    argument_type     => 'VARCHAR2',
    default_value     => 'mycompany.com'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 3,
    argument_name     => 'P_PORTNUM',
    argument_type     => 'NUMBER',
    default_value     => 25  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 4,
    argument_name     => 'P_FROM',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 5,
    argument_name     => 'P_TO',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 6,
    argument_name     => 'P_CC',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 7,
    argument_name     => 'P_BCC',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 8,
    argument_name     => 'P_REPLYTO',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 9,
    argument_name     => 'P_SUBJECT',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 10,
    argument_name     => 'P_MSGTXT',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 11,
    argument_name     => 'P_CONTENTS',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 12,
    argument_name     => 'P_ATTACH',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 13,
    argument_name     => 'P_URL',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 14,
    argument_name     => 'P_TRACEMODE',
    argument_type     => 'VARCHAR2',
    default_value     => 'N'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 15,
    argument_name     => 'P_MIMETYPE',
    argument_type     => 'VARCHAR2',
    default_value     => 'text/plain'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 16,
    argument_name     => 'P_DISPOSITION',
    argument_type     => 'VARCHAR2',
    default_value     => 'attachment'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 17,
    argument_name     => 'P_CHARSET',
    argument_type     => 'VARCHAR2',
    default_value     => 'iso-8859-1'  );
  dbms_scheduler.define_program_argument(
    program_name      => 'MAIL_MMTK_PRG',
    argument_position => 18,
    argument_name     => 'P_TRANSFERTENCODE',
    argument_type     => 'VARCHAR2',
    default_value     => NULL  );
commit;
end;
/

exec DBMS_SCHEDULER.ENABLE(name=>'MAIL_MMTK_PRG');

I hope this guide helps others facing the same issue. If you’ve encountered this problem before or have alternative solutions, share your thoughts in the comments!

Post Views: 443
Thumbnail [60x60]
by
Dieter Henig