Have you ever tried to get the DDL for Materialized Views in Oracle using dbms_metadta.get_ddl and got the error ORA-06512?
Here is a little demo the explains the error and then provides a work around.
SQL> create materialized view mv
2 as
3 select d.dname, sum(sal) sal_per_dept, avg(sal) avg_per_dept, count(*) count_per_dept
4 from emp e join dept d on (e.deptno= d.deptno)
5 group by d.dname;
Materialized view created.
SQL> select dbms_metadata.get_ddl(object_type, object_name) as mv_ddl
2 from user_objects where object_type = 'MATERIALIZED VIEW';
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 3773
ORA-06512: at "SYS.DBMS_METADATA", line 3828
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
no rows selected
The error is caused by the space in the object name MATERIALIZED VIEW. Replace the space with "_" and the object_type will become MATERIALIZED_VIEW and the function will work!
Similarly, DB LINK should be DB_LINK
PACKAGE SPECIFICATION should be PACKAGE_SPEC
and PACKAGE BODY should be PACKAGE_BODY
The object_type parameter will not accept space in the object_type name.
SQL> set long 50000
SQL> col mv_ddl format a100 word_wrap
SQL> set pagesize 100
SQL>
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl(replace(object_type,' ','_'), object_name) as mv_ddl
2 from user_objects where object_type = 'MATERIALIZED VIEW';
MV_DDL
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SCOTT"."MV" ("DNAME", "SAL_PER_DEPT",
"AVG_PER_DEPT", "COUNT_PER_DEPT")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select d.dname, sum(sal) sal_per_dept, avg(sal) avg_per_dept, count(*)
count_per_dept
from emp e join dept d on (e.deptno= d.deptno)
group by d.dname;
If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting
