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