ORA-31600: invalid input value INDEX PARTITION for parameter

运行备份数据库对象的存储过程报如下错误
SQL> exec proc_auto_backup;
 
begin proc_auto_backup; end;
 
ORA-31600: invalid input value INDEX PARTITION 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
ORA-06512: at "E_BACKUP.PROC_AUTO_BACKUP", line 134
ORA-06512: at line 2

我猜测是存储过程如下代码所调用的对象值是一个分开的字符串，而非一个连体字符
select dbms_lob.substr(dbms_metadata.get_ddl(v_object_type,v_tname,'E_CHANNEL')) into v_text from dual;

SQL> create table t_proc(a varchar2(100));
 
Table created
 
SQL> insert into t_proc values('str');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 

SQL> create or replace procedure proc_sep(v_in in varchar2)
  2  as
  3  v_cnt pls_integer;
  4  begin
  5  select strcount(1) into v_cnt from t_proc where a=v_in;
  6  dbms_output.put_line(v_cnt);
  7  end;
  8  /
 
Procedure created
 
SQL> set serverout on

--调用连体字符正常
SQL> exec proc_sep('str');
 
1
 
PL/SQL procedure successfully completed
 
SQL> truncate table t_proc;
 
Table truncated
 
SQL> select * from t_proc;
 
A
--------------------------------------------------------------------------------
 
SQL> insert into t_proc values('a b');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_proc;
 
A
--------------------------------------------------------------------------------
a b

--调用非连体字符亦正常
SQL> exec proc_sep('a b');
 
1
 
PL/SQL procedure successfully completed

SQL> create  materialized view mv_proc_sep
  2  refresh complete
  3  as
  4  select a
  5  from t_proc
  6  /
 
Materialized view created
 
SQL>
SQL> select object_name,object_type from user_objects where object_name='MV_PROC_SEP';
 
OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
MV_PROC_SEP                                                                      TABLE
MV_PROC_SEP                                                                      MATERIALIZED VIEW
 
SQL> select dbms_metadata.get_ddl('MATERIALIZED VIEW','MV_PROC_SEP','SCOTT') from dual;
 
select dbms_metadata.get_ddl('MATERIALIZED VIEW','MV_PROC_SEP','SCOTT') from dual
 
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

--查阅官方手册，对象类型参数的值为：MATERIALIZED_VIEW,非自user_objects查下的值
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_PROC_SEP','SCOTT') from dual;
 
DBMS_METADATA.GET_DDL('MATERIA
--------------------------------------------------------------------------------
 
  CREATE MATERIALIZED VIEW "SCOTT"."MV_PROC_SEP" ("A")
  ORGANIZATION HEAP PC

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754988/，如需转载，请注明出处，否则将追究法律责任。