0

When I have tried to run the following SQL statements I'm getting the error:

 set long 90000
set heading 999
set lines 100
select 
   dbms_metadata.GET_DDL(u.object_type,u.object_name,'MY_SCHEMA')
from  
   dba_objects u
where  
   owner = 'MY_SCHEMA'
   AND OBJECT_TYPE='TABLE';

Error:

ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE in function GET_DDL Funktion GET_DDL ORA-06512: in "SYS.DBMS_METADATA", Line 6069 ORA-06512: in "SYS.DBMS_METADATA", Line 8666 ORA-06512: in Line 1 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.

Can you please give some advice how I can retrieve the select output.

Steffen Moritz
  • 7,277
  • 11
  • 36
  • 55
SocketM
  • 564
  • 1
  • 19
  • 34
  • also, why not just query user_tables - dbms_metadata is an expensive pkg to call, no need to give it an expensive query to feed it as well – thatjeffsmith Mar 30 '18 at 11:57

1 Answers1

1

https://docs.oracle.com/database/121/ARPLS/d_metada.htm#BGBIEDIA

List of possible object type parameters. And not all elements from the list are equal to dba_objects.object_type. You have to translate it to appropriate value.
In your case problem is chain object. Generally for any scheduler object, object type is PROCOBJ. But i don't know if it will work for chains.

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

Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17