Skip to content

Instantly share code, notes, and snippets.

@gajoseph
Created May 22, 2018 18:56
Show Gist options
  • Save gajoseph/bc27c515fbd22b327d331ec36ae43633 to your computer and use it in GitHub Desktop.
Save gajoseph/bc27c515fbd22b327d331ec36ae43633 to your computer and use it in GitHub Desktop.
oracle compare tables in 2 schema
CREATE OR REPLACE FUNCTION SYS.sf_compTablesInschemas
(
pvchTablename IN VARCHAR2,
pvchSchema1 in varchar2,
pvchSchema2 in varchar2
)
return varchar2
IS
asd int;
begin
select Count(*) into asd
fROM (
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION , data_SCALE, nullable
from DBA_TAB_COLS
where table_name = pvchTablename
and owner = pvchSchema1
minus
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION , data_SCALE, nullable
from DBA_TAB_COLS
where table_name = pvchTablename
and owner =pvchSchema2
)
;
if asd > 0 then
return 'false';
else
return 'true';
end if ;
end ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment