CODEWITHSUNDEEP
oracle-databaseoracle11gexportddldbms-scheduler
ttirtawi
ttirtawi

Reputation: 45

ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE

I was trying to exported the complete scheduler chain created on Oracle 11g (including steps, rules, etc), when I got this error :

ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE in function GET_DDL 
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344 
ORA-06512: at line 1

Message meaning

31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

Failed query was :

select dbms_metadata.get_ddl('CHAIN','RUN_SERVICE_IP') from dual;

Are we able to export (get the DDL) for a chain ? What is the alternative to export entire chain set up?

Upvotes: 2

Views: 3509

Answers (1)

Andrew Spencer
Andrew Spencer

Reputation: 16474

The following is true on Oracle 12c, I haven't been able to test an earlier version.

You need to specify 'PROCOBJ' as the OBJECT_TYPE, thus:

select dbms_metadata.get_ddl('PROCOBJ','RUN_SERVICE_IP') from dual;

This is valid for programs, jobs, and chains. Job definitions will include attributes. Chain definitions will include chain steps, but bizarrely not chain rules. These you can only obtain from the views *_SCHEDULER_CHAIN_RULES e.g.

select 'dbms_scheduler.define_chain_rule(
  chain_name => ''' || chain_name || ''',
  condition => ''' || condition || ''',
  action => ''' || action || ''',
  rule_name => ''' || rule_name || ''',
  comments => ''' || comments || '''
);'
from user_scheduler_chain_rules;

Upvotes: 2

Related Questions