今天写了一个存储过程，需要通过dblink把其他用户下面的表的数据增量copy到目标表中，结果访问其他系统的表的时候，在存储过程中一直有问题，在Stack Overflow中找到了答案。


In stored procedures, functions, and definers-rights packages, roles (such asSELECT_CATALOG_ROLE) are disabled. Therefore,such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession ofSELECT_CATALOG_ROLE), you must make the program invokers-rights.

To do this, you must addauthidto your procedure.

createproceduretestsauthidCURRENT_USERisl_fooclob;beginselectregexp_replace(dbms_metadata.get_ddl('USER','SCOTT',null)||dbms_metadata.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')||dbms_metadata.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')||dbms_metadata.GET_GRANTED_DDL('ROLE_GRANT','SCOTT'),'"'||chr(10),'";'||chr(10))intol_foofromdual;end;/SQL>EXECtestsPL/SQLproceduresuccessfullycompleted