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.

score 2 · Answer 1 · edited Sep 26 '12 at 06:58

2

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;

edited Sep 26 '12 at 06:58

Mihai Iorga

39,330
16
106
107

answered Sep 07 '12 at 15:31

a1ds

21
2

score 2 · Accepted Answer · edited Oct 29 '09 at 10:44

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;

score 1 · Answer 3 · edited Dec 12 '11 at 15:54

1

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;

edited Dec 12 '11 at 15:54

Peter O.

32,158
14
82
96

answered May 13 '10 at 06:31

Jaroslav

11
1

score -1 · Answer 4 · answered Oct 28 '09 at 10:05

-1

I hope I get the syntax right, but I think it should be v_clob := dbms_metadata@ENTORA.get_ddl(...) instead of v_clob := dbms_metadata.get_ddl(...)

answered Oct 28 '09 at 10:05

Erich Kitzmueller

36,381
5
80
102

Thanks AmmoQ but nope, it gave an error that dbms_metadata@ENTORA.get_ddl(...); gave an error ORA-04054 : database link ENTORA.GET_DDL does not exist and dbms_metadata.get_ddl@ENTORA(...); gave this error: ORA-06553: PLS -564 lob arguments are not permitted in calls to remote server. Both of them didn't compile in the function, I ran them against the dual table. – Tunde Oct 28 '09 at 15:15
dbms_metadata.get_ddl@ENTORA is the right syntax, but... well it's simply not that easy, obviously. – Erich Kitzmueller Oct 28 '09 at 18:46
Yep, I guess. I have been able to work around it by tweaking the DBMS_METADATA.GET_DDL a bit. The main setback is that Oracle 10g doesnt support transfer of CLOB datatypes across database links. I'm not sure it has been ammended in 11g... Here's a new dbms_metadata.get_ddl that I created. It also removes the schema names and double quotes. And yes, your syntax does work. The function i used can be seen below. Thanks for your help. – Tunde Oct 29 '09 at 10:40

Copying metadata over a database link in Oracle 10g

4 Answers4