We recently migrated our Oracle database from 10 to 12 s (12.1.0.1.0). Having examined the problem with some queries, we cheated to clean up the database and delete all unnecessary objects.
So I wrote a query that looks for a DDL database to display specific text that uses a specific view or function.
SELECT
object_name, object_type, DBMS_METADATA.GET_DDL(object_type, object_name) as ddl_txt
FROM user_objects
WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER')
AND UPPER( DBMS_METADATA.GET_DDL(object_type, object_name) ) LIKE upper('%myFunction%')
This results in the following exception:
ORA-31600: invalid input value TYPE BODY for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5746
ORA-06512: at "SYS.DBMS_METADATA", line 8333
ORA-06512: at 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.
The exception is due to the fact that we have Body Type objects in our database and they do not provide ddl c DBMS_METADATA.GET_DDL(). Running the query below gives the same exception as in the original query.
select dbms_metadata.get_ddl('TYPE BODY', 'myBodyStringType') from dual
, , , , :
select
lst.*,
DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt
from (
SELECT
object_name, object_type
FROM user_objects
WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER')
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')
, , . , .
, Oracle DBMS_METADATA.GET_DLL() , . Oracle - ?
, ORDER BY , . Oracle ORDER BY?
select
lst.*,
DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name) as ddl_txt
from (
SELECT
object_name, object_type
FROM user_objects
WHERE object_type IN ( 'FUNCTION', 'VIEW', 'PROCEDURE', 'TRIGGER')
ORDER BY ROWNUM ASC
) lst
where upper(DBMS_METADATA.GET_DDL(lst.object_type, lst.object_name)) like upper('%myFunction%')
, ? - , - Oracle 10g.
( , , - !).