How to copy all tables in Oracle to a SQL Server database?

During migration of applications, you will often maintain a long-term copy of your application data as a copy in a data lake or traditional database. The following query copies data from an Oracle RDBMS database to SQL Server:

declare
  --
  -- Alias of source data container.
  -- In this case Oracle RDBMS.
  --
  l_source_alias varchar2 := 'SOMEORACLE';
  --
  -- Alias of target data container.
  -- In this case SQL Server 2019 instance.
  --
  l_target_alias varchar2 := 'sql2019';
begin
  for r 
  in 
  ( select 'create or replace table bak_'
           || lower(ste.name)
           || '@'
           || l_target_alias
           || ' as select * from '
           || ste.schema
           || '.' 
           || ste.name 
           || '@'
           || ste.provider_data_container_alias
           stmt
    from   SYSTEMTABLES@DataDictionary ste
    where  ste.provider_data_container_alias = l_source_alias
    --
    -- Skip SYS, SYSTEM, etc. on Oracle.
    --
    and    ste.schema = 'SOMESCHEMA'
    order
    by     ste.name
  )
  loop
    execute immediate r.stmt;
  end loop;
end;

End result of running this anonymous Invantive PSQL procedure is that for each table X in SQL Server a table is created on Oracle with name bak_X.

1 Like

dont forget to add and ste.type = 'table' otherwise you’ll get the views as well. There is no point to copy data from the views, only the statement of the view makes sense.

also @guido.leenders suggested today this new nice implementation that would continue on error and place all errors at the end of the loop instead breaking the script.

as following:

set invantive-sql-correct-invalid-date@source_container true

declare
  --
  -- Alias of source data container.
  -- In this case Oracle RDBMS.
  --
  l_source_alias varchar2 := 'source_container';
  --
  -- Alias of target data container.
  -- In this case SQL Server 2019 instance.
  --
  l_target_alias varchar2 := 'dest_container';
  --
  -- Cumulative errors.
  --
  l_error_txt       varchar2;
  l_error_cnt       pls_integer;
begin
  l_error_txt := '';
  l_error_cnt := 0;
  --
  for r
  in
  ( select 'create or replace table naarden_'
           || lower(ste.name)
           || '@'
           || l_target_alias
           || ' as select * from '
           || ste.schema
           || '.'
           || ste.name
           || '@'
           || ste.provider_data_container_alias
           stmt
    from   SYSTEMTABLES@DataDictionary ste
    where  ste.provider_data_container_alias = l_source_alias
    and    ste.schema = 'MARINA'
    and    ste.type = 'table'
    order
    by     ste.name
  )
  loop
    begin
      execute immediate r.stmt;
    exception
      when others
      then
        l_error_txt := l_error_txt 
                       || chr(13) || chr(10) 
                       || 'Statement ' 
                       || r.stmt 
                       || ':' 
                       || sqlerrm
                       ;
        l_error_cnt := l_error_cnt + 1;
    end;
  end loop;
  --
  if l_error_cnt > 0
  then
    raise_application_error(-20163, l_error_txt);
  end if;
end;

Fix on correcting dates should be bundled with BETA release 20.1.326 on https://releasenotes.invantive.com/BETA/invantive-data-hub.html and https://releasenotes.invantive.com/BETA/invantive-query-tool.html.

this reply from @guido.leenders belongs to this thread

After a few resolution of issues with Oracle connector, this script has now been able to pull out 434 tables from 3 Oracle Databases back in SQLServer with a total running time of 30 minutes. I’ll count the number of records later…I’ll share it with you !
thanks Invantive :heart:

Great to hear! And together with Invantive SQL reading skills your Dutch probably has improved sufficiently to order a table of beer at a Dutch marina :wink: