Postgresql oferece a possibilidade de mapear tabelas e vistas via FDWs (Foreign Data Wrappers). Isso, por si só, já é muito útil. Entretanto, melhor ainda seria poder invocar rotinas. Como o Postgresql permite escrever rotinas em Python, TCL, e Perl, a saída é óbvia.
create or replace
function call_remote_function(varchar, varchar[]) returns varchar as $$
use DBI;
use DBD::Oracle;
my $proc=shift;
$proc=~s/[^A-Za-z0-9_.]//g;
my $vars=shift;
my $num_vars=scalar(@{$vars});
my $placeholders=join(',',('?')x$num_vars);
my $host=$ENV{'REMOTE_HOST'};
my $schema=$ENV{'REMOTE_SCHEMA'};
my $sid=$ENV{'REMOTE_SID'};
my $pass=$ENV{'REMOTE_PASS'};
my $db = DBI->connect("dbi:Oracle:host=$host;sid=$sid;server=POOLED",
"$schema", "$pass", {ora_drcp=>1,ora_drcp_class=>'SHARED'})
|| die($DBI::errstr."\n");
$db->{AutoCommit}=0;
$db->{RaiseError}=1;
my $result;
my $cmd="BEGIN ?:=$proc($placeholders); END;";
my $stmt=$db->prepare($cmd);
my $i=1;
$stmt->bind_param_inout($i, \$result, {ora_type=>SQLT_CHR});
for my $var (@{$vars}) {
$stmt->bind_param(++$i, $var);
};
$stmt->execute();
$db->disconnect();
return $result;
$$ language plperlu;
Esta é uma versão que vale para qualquer número de parâmetros. Escrevi também uma versão para nenhum parâmetro, um parâmetro, e dois parâmetros.
Como essa função faz algumas coisas perigosas, ela só pode ser criada por uma conta administrativa. Além disso, a linguagem escolhida é a plperlu, quando rotinas normais podem ser declaradas com plperl.
Os parâmetros são inseridos como variáveis de ligação (bind variables), então não me preocupei muito com a segurança delas. O nome da rotina, entretanto é inserida diretamente no comando. Então, adicionei uma linha para limpar caracteres indesejados:
$proc=~s/[^A-Za-z0-9_.]//g;
Essa linha retira todos os caracteres que não forem alfabéticos, numéricos, traço-baixo, ou ponto.
Os dados de conexão são escondidos como variáveis de ambiente do usuário que roda o banco. Além disso, no lado do Oracle, defini um pool de conexões (usando DRCP - Database Resident Connection Pooling). O tempo de execução das chamadas variava entre 300ms e 500ms. Com o DRCP, o teto passou a ser 300ms e normalmente elas são invocadas em cerca de 100ms.
No banco Oracle, adicionei uma entrada ao tnsnames.ora:
DB_POOLED =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = POOLED)
(SID = DB)
)
)
E depois iniciei a pool com um comando:
execute dbms_connection_pool.start_pool();
Posso invocar uma função remota assim:
select call_remote_function('substr', array['postgresql', '2', '3']);
Mas prefiro criar funções locais para esconder a pilantragem:
CREATE OR REPLACE FUNCTION
oracle_substr(p_str varchar, p_start varchar, p_length varchar) RETURNS varchar AS $$
begin
return call_remote_function('substr', array[p_str, p_start, p_length]);
end;
$$ LANGUAGE plpgsql;
