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.
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 @gls 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;
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
