Skip to content

Why Data Pump Fails to Import Scheduler-Programs

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:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
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!

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

1 thought on “Why Data Pump Fails to Import Scheduler-Programs”

Leave a Comment

Your email address will not be published. Required fields are marked *