Conectando Postgresql a Oracle com Perl

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;