在 Oracle 10g 中通过数据库链接复制元数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1636154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying metadata over a database link in Oracle 10g
提问by Tunde
Thanks in advance for your help experts.
在此先感谢您的帮助专家。
I want to be able to copy over database objects from database A into database B with a procedure created on database B.
我希望能够使用在数据库 B 上创建的过程将数据库对象从数据库 A 复制到数据库 B。
I created a database link between the two and have tweaked the get_ddl function of the dbms_metadata to look like this:
我在两者之间创建了一个数据库链接，并将 dbms_metadata 的 get_ddl 函数调整为如下所示：
create or replace function GetDDL
(
p_name in MetaDataPkg.t_string
p_type in MetaDataPkg.t_string
)
return MetaDataPkg.t_longstring
is
-- clob
v_clob clob;
-- array of long strings
c_SYSPrefix constant char(4) := 'SYS_';
c_doublequote constant char(1) := '"';
v_longstrings metadatapkg.t_arraylongstring;
v_schema metadatapkg.t_string;
v_fullength pls_integer := 0;
v_offset pls_integer := 0;
v_length pls_integer := 0;
begin
SELECT DISTINCT OWNER
INTO v_schema
FROM all_objects@ENTORA
where object_name = upper(p_name);
-- get DDL
v_clob := dbms_metadata.get_ddl(p_type, upper(p_name), upper(v_schema));
-- get CLOB length
v_fullength := dbms_lob.GetLength(v_clob);
for nIndex in 1..ceil(v_fullength / 32767)
loop
v_offset := v_length + 1;
v_length := least(v_fullength - (nIndex - 1) * 32767, 32767);
dbms_lob.read(v_clob, v_length, v_offset, v_longstrings(nIndex));
-- Remove table's owner from DDL string:
v_longstrings(nIndex) := replace(
v_longstrings(nIndex),
c_doublequote || user || c_doublequote || '.',
''
);
-- Remove the following from DDL string:
-- 1) "new line" characters (chr(10))
-- 2) leading and trailing spaces
v_longstrings(nIndex) :=
ltrim(rtrim(replace(v_longstrings(nIndex), chr(10), '')));
end loop;
-- close CLOB
if (dbms_lob.isOpen(v_clob) > 0)
then
dbms_lob.close(v_clob);
end if;
return v_longstrings(1);
end GetDDL;
so as to remove the schema prefix that usually comes with metadata. I get a null value whenever I run this function over the database link with the following queries.
以便删除通常带有元数据的模式前缀。每当我通过以下查询在数据库链接上运行此函数时，我都会得到一个空值。
select getddl( 'TABLE', 'TABLE1') from user_tables@ENTORA where table_name = 'TABLE1';
select getddl('TABLE', 'TABLE1') from user_tables@ENTORA where table_name = 'TABLE1';
select getddl( 'TABLE', 'TABLE1') from dual@ENTORA;
select getddl('TABLE', 'TABLE1') from dual@ENTORA;
t_string is varchar2(30) t_longstring is varchar2(32767) and type t_ArrayLongString is table of t_longstring
t_string 是 varchar2(30) t_longstring 是 varchar2(32767) 类型 t_ArrayLongString 是 t_longstring 的表
I would really appreciate it if any one could help. Many thanks.
如果有人可以提供帮助，我将不胜感激。非常感谢。
采纳答案by Tunde
CREATE OR REPLACE function DEMO_FN
(object_type varchar2, table_name varchar2) return varchar2
is
v_longstrings varchar2(32223);
c_doublequote constant char(1) := '"';
begin
v_longstrings := dbms_metadata.get_ddl(object_type,table_name);
-- Remove double quotes from DDL string:
v_longstrings := replace(v_longstrings, c_doublequote || user || c_doublequote || '.','');
-- Remove the following from DDL string:
-- 1) "new line" characters (chr(10))
-- 2) leading and trailing spaces
v_longstrings := ltrim(rtrim(replace(v_longstrings, chr(10), '')));
return v_longstrings;
end;
/
Please note that the signed in schema must have the SELECT_CATALOG_ROLE to execute this function.
请注意，登录模式必须具有 SELECT_CATALOG_ROLE 才能执行此功能。
An example is
一个例子是
select demo_fn@db_link('TABLE','TABLE_NAME') FROM DUAL;
select demo_fn@db_link('TABLE','TABLE_NAME') FROM DUAL;
回答by Jaroslav
this works fine
这工作正常
declare
v_ddl clob;
V_DDL_PART varchar2(4000);
begin
for I in 0 .. 50 loop
select DBMS_LOB.SUBSTR@db_link(DBMS_METADATA.GET_DDL@db_link('TABLE','MYTABLE'),(4000 * I + 1)) into V_DDL_PART from dual@db_link;
V_DDL := V_DDL || V_DDL_PART;
end loop;
end;
回答by a1ds
This work fine, small bug fixed:
这个工作正常，修复了小错误：
declare
v_ddl clob;
V_DDL_PART varchar2(4000);
begin
for I in 0 .. 10 loop
select DBMS_LOB.SUBSTR@LINK(
DBMS_METADATA.GET_DDL@LINK('TABLE','NAME','SCHEMA'),
4000,(4000 * I + 1)) into V_DDL_PART from dual@LINK;
V_DDL := V_DDL || V_DDL_PART;
end loop;
dbms_output.put_line(v_ddl);
end;
回答by Erich Kitzmueller
I hope I get the syntax right, but I think it should be v_clob := [email protected]_ddl(...)instead of v_clob := dbms_metadata.get_ddl(...)
我希望我的语法正确，但我认为它应该v_clob := [email protected]_ddl(...)代替v_clob := dbms_metadata.get_ddl(...)
