ORA-39083 And ORA-00942 Reported When Importing PROCOBJ Objects From a Full Export Dump (Doc ID 2216347.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

DataPump Export was taken with:

> expdp system/<password> full=y directory=data_pump_dir dumpfile=DEV_SOAINFRA.dmp logfile=DEV_SOAINFRA.log

The following errors are reported during the DataPump import job when using a dump file from a full export:

> impdp system/******** dumpfile=DEV_SOAINFRA.dmp schemas=DEV_SOAINFRA directory=test logfile=test3.log

Import: Release 12.1.0.2.0 - Production on Tue Jun 14 15:49:06 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** dumpfile=DEV_SOAINFRA.dmp schemas=DEV_SOAINFRA directory=test logfile=test3.log
....
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
ORA-39083: Object type PROCOBJ:"DEV_SOAINFRA"."CONTROL_MIGRATION_MAIN_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
....


This is a callout issue, related to some incorrect argument. Failing SQL is:

BEGIN
dbms_scheduler.create_program('"CONTROL_MIGRATION_MAIN_PRG"','STORED_PROCEDURE
',
'control_migration.control_migration_main',14, FALSE,NULL);
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='DEV_SOAINFRA' and
a.program_name='CONTROL_MIGRATION_MAIN_PRG';
BEGIN
FOR arg in prog_args LOOP
IF arg.int_arg_type IS NULL THEN
dbms_scheduler.define_anydata_argument('"CONTROL_MIGRATION_MAIN_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('"CONTROL_MIGRATION_MAIN_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;

ORA-00942 is raised because SYSTEM.SCHEDULER_PROGRAM_ARGS_TMP doesn't exit.

 

Changes

n/a

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms