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.