Database link Postgres X Oracle

–######################################################################
–# Autor : Rodrigo Mufalani (rodrigo@mufalani.com.br)
–# Data : Mai 6, 2009
–# De : Mufalani
–# Para :
–# Motivo: Database link do postgres para o Oracle
–######################################################################
–# Obs :
–######################################################################

— 1) ELIMINAÇÃO DO TIPO CASO ELE EXISTA

DROP TYPE TP_USUARIO CASCADE;

— 2) PRECISAMOS PARA ISSO DE UM TIPO PARA QUE POSSAMOS EXECUTAR A CONSULTA NO ORACLE

CREATE TYPE TP_USUARIO AS (
“NO_USUARIO” VARCHAR(70),
“NO_USUARIO_FONETIZADO” VARCHAR(70),
“NO_MAE” VARCHAR(70),
“NO_MAE_FONETIZADO” VARCHAR(70),
“NO_PAI” VARCHAR(70),
“NO_PAI_FONETIZADO” VARCHAR(70)
);

— 3) CRIACAO DA FUNÇÃO NO POSTGRES

CREATE OR REPLACE FUNCTION FC_CONSULTA_ORACLE(text)
RETURNS SETOF TP_USUARIO AS
$BODY$
use DBI;

# usa o modulo DBI
my $consulta = shift;

# Definicao da variavel de entrada da funcao
return unless (defined $consulta);

# Abre a conexao com o banco de dados Oracle
my $db_ora = DBI->connect(“dbi:Oracle:teste”,
“meuteste”,
“**************”) # Senha fica exposta por isso criaremos usuarios especificos
# para conexao com o Oracle
or die “Nao obtive acesso ao banco: ” . DBI->errstr;

# Variavel que recebe a consulta para execucao
my $sel = $db_ora->prepare($consulta)
or die “Nao pude preparar a consulta: ” . $db_ora->errstr;

# Execucao da query que foi passada na chamada da funcao
$sel->execute() or die “Nao pude executar a consulta: ” . $sel->errstr;

# Loop com a variavel $row para retornar as varias linhas da query
while (my $row = $sel->fetchrow_hashref)
{
return_next($row);
}

# Disconexao do Oracle
$db_ora->disconnect;

return;
$BODY$
LANGUAGE ‘plperlu’ VOLATILE
COST 100
ROWS 1000;

— 4) TORNANDO O POSTGRES O DONO DA FUNÇÃO
ALTER FUNCTION FC_CONSULTA_ORACLE(text) OWNER TO postgres;

— 5) EXEMPLO DE CONSULTA USANDO A FUNÇÃO NO ORACLE

select a.”NO_USUARIO”
, a.”NO_USUARIO_FONETIZADO”
, a.”NO_MAE”
, a.”NO_PAI”
from fc_consulta_oracle(‘select
NO_USUARIO
, NO_USUARIO_FONETIZADO
, NO_MAE
, NO_PAI
from
tm_usuario
where
rownum < 10’) a; /* Para essa consulta funcionar, precisa do módulo perl DBI instalado, existe na comunidade um database link também usando o DBI/DBD, porém, ele faz sempre acesso full por isso eu criei essa função em perl dentro do postgres para realizar esse acesso “indexado”. Quando eu tive que colocar essa rotina em funcionamento, a tabela tinha mais de 100Gb em dados, imaginem quão desastroso seria um fullscan a todo acesso vindo do postgres… Se você tem mais facilidade com Java, pode usar a mesma tecnologia e escrever uma função em Java fazendo acesso no Oracle via JDBC. */