Copying metadata over a database link in Oracle 10g

Question

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.

Answer 1

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(...)

Answer 2

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.

An example is

select demo_fn@db_link('TABLE','TABLE_NAME') FROM DUAL;

Answer 3

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;