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.
I created a database link between the two and have tweaked the get_ddl function of the dbms_metadata to look like this:
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 dual@ENTORA;
t_string is varchar2(30)
t_longstring is varchar2(32767)
and type t_ArrayLongString is table of t_longstring
I would really appreciate it if any one could help. Many thanks.
