Mais que simples stored procedures

Como utilizar pacotes utilitários em Oracle PL/SQL para criar programas profissionais

Stored procedures são pequenos programas ou procedimentos escritos em uma linguagem de banco de dados proprietária como PL/SQL para Oracle, Transact-SQL para o Microsoft SQL Server dentre outros, sendo armazenados e executados dentro do próprio banco de dados.

Boa parte dos bancos de dados possuem “recursos extras”, contudo, serão descritos neste artigo apenas alguns dos recursos disponíveis no banco de dados Oracle. Todos os exemplos foram testados com sucesso nas versões 9i e 10g.

PL/SQL significa Procedural Language / SQL, que amplia o SQL uma vez que adiciona estruturas de controle e repetição, variáveis e tipos, procedimentos e funções, resultando numa linguagem estruturada e poderosa. Basicamente, trata-se de um conjunto de comandos utilizados para manipulação dos dados das aplicações armazenados no banco de dados.

Ao contrário do que muitos podem pensar, stored procedures podem ser utilizadas não apenas para execução de comandos de DML e DDL. O objetivo deste artigo é demonstrar que é possível criar stored procedures para atividades que não estão relacionadas diretamente com a manipulação dos dados armazenados no banco de dados, como: manipulação de arquivos textos, envio de e-mail, envio e recebimento de arquivos via FTP, manipulação de documentos HTML provenientes de algum site, manipulação de dados em formato XML, ser cliente em uma conexão TCP-IP, utilizar aplicações Java, utilizar serviço de mensagens com JMS, serviço de diretório LDAP, comunicação com web services, entre outros.

Este primeiro artigo abordará apenas os recursos descritos na Tabela 1, e assume que o leitor já tenha conhecimentos de PL/SQL, os demais recursos ficam para um próximo artigo.

Para iniciar, um pré-requisito é dar direito de execução nos pacotes que utilizaremos, conforme Listagem 1.

Tabela 1. Descrição dos recursos em pacotes (Packages) no Oracle.

-- Logar no Oracle como SYS ou SYSTEM --

GRANT EXECUTE ON SYS.UTL_URL    TO <SEU USUARIO>;

GRANT EXECUTE ON SYS.UTL_INADDR TO <SEU USUARIO>;

GRANT EXECUTE ON SYS.UTL_TCP    TO <SEU USUARIO>;

GRANT EXECUTE ON SYS.UTL_HTTP   TO <SEU USUARIO>;

GRANT EXECUTE ON SYS.UTL_SMTP   TO <SEU USUARIO>;

GRANT EXECUTE ON SYS.UTL_FILE   TO <SEU USUARIO>;

Listagem 1. Concessão de privilégio de execução nos pacotes utilizados.

Pacotes de apoio

O pacote UTL_INADDR é utilizado em programas PL/SQL no suporte à identificação de endereços da internet. Conforme ilustrado na Figura 1, a função utl_inaddr.get_host_address retorna o endereço IP de um “host” válido, já a função utl_inaddr.get_host_name faz o contrário, retorna o nome do “host” para um endereço IP válido. Em ambos os casos, se o endereço passado como parâmetro não for válido, é gerado uma exceção unknown_host.

Figura 1. Resultado da execução do pacote utl_inaddr.

O pacote UTL_URL é utilizado em programas PL/SQL na formatação de endereços da internet ou URL definido na RFC 1738 (Nota 1). Existe um conjunto de caracteres válidos que compõem um endereço correto, por exemplo, não é permitido espaços em branco, entre outros.

Este pacote contém duas funções: utl_url.escape tem o objetivo de ajustar uma URL conforme Figura 2, resumindo, substituiu espaços em branco por “%20”. Já a função utl_url.unescape faz o processo inverso. Esta conversão é necessária pois alguns caracteres não podem ser utilizados na formação da URL pois tem funções específicas conforme descrito na RFC 1738 (Nota 1).

Figura 2. Resultado da execução do pacote utl_url.

Pacote UTL_TCP

Este pacote possibilita que programas PL/SQL atuem como clientes em uma comunicação com um servidor TCP/IP. Com o pacote UTL_TCP,  programas PL/SQL podem comunicar-se com serviços TCP/IP tanto para o envio como recebimento de dados. Por exemplo, o pacote UTL_HTTP utiliza o pacote UTL_TCP abrindo uma conexão TCP/IP na porta 80 onde o servidor web fica aguardando requisições, o mesmo ocorre com o pacote UTL_SMTP que abre uma conexão TCP/IP na porta 25 onde um servidor SMTP fica na escuta.

Desta forma, para utilizar este pacote basta que se conheça o protocolo utilizado no serviço desejado. Por exemplo, para se comunicar com um servidor FTP basta que se conheça a funcionalidade do protocolo definido na RFC 959 (Nota 1) e pronto. A partir daí poderíamos criar um novo pacote, por exemplo, My_Utl_Ftp que encapsularia as funcionalidades para envio e recebimento de arquivos.

É importante ressaltar que não é possível criar um servidor TCP/IP de dentro de um programa PL/SQL.

Na Listagem 2 vemos um exemplo de uma aplicação PL/SQL se conectando a um servidor TCP/IP para obtenção da data e hora. Este serviço é bem simples, não disponibiliza qualquer tipo de interação. Após a chamada na porta 13, o serviço devolve a data e hora e encerra a conexão.

Para abrir uma conexão cliente TCP/IP dentro de um programa PL/SQL é necessário definir uma referência para a conexão, isto é feito utilizando-se a procedure utl_tcp.connection. Para abrir a conexão, utilizar a procedure utl_tcp.open_conection passando como parâmetros o servidor (nome do host ou endereço IP) e a porta. É possível testar se a conexão está disponível com a procedure utl_tcp.available.

Após abrir a conexão e com o devido conhecimento do protocolo utilizado, basta utilizar os comandos para enviar e receber as informações.

Para receber dados do servidor:

·         utl_tcp.read_raw e utl_tcp.get_raw recebem dados em formato binário;

·         utl_tcp.get_text, utl_tcp.get_line, utl_tcp.read_text e utl_tcp.read_line recebem dados em formato texto.

Para enviar dados para o servidor:

·         utl_tcp.write_raw envia dados em formato binário;

·         utl_tcp.write_text e utl_tcp.write_line enviam dados em formato texto.

Para enviar uma quebra de linha ou nova linha, utilize o valor definido na variável utl_tcp.crlf, equivalente a chr(13).

Para fechar a conexão com o servidor, utilize as procedures utl_tcp.close_connection quando se quer fechar uma conexão específica ou utl_tcp.close_all_connections para fechar todas as conexões abertas.

--Programa PL/SQL para conexão em um servidor TCP/IP

Declare

  conn   sys.utl_tcp.connection;

Begin

  conn := sys.utl_tcp.open_connection('time-a.nist.gov', 13);

  Begin

    Loop

      sys.dbms_output.put_line(utl_tcp.get_line(conn, TRUE));

    End Loop;

  Exception

    When sys.utl_tcp.end_of_input Then

      Null;

  End;

  sys.utl_tcp.close_connection(conn);

Exception

  When Others Then

    sys.utl_tcp.close_connection(conn);

End;

--Retorno exibido no console

53688 05-11-14 16:23:50 00 0 0 934.0 UTC(NIST) *

Listagem 2. Código PL/SQL com exemplo do pacote utl_tcp.

Pacote UTL_HTTP

Este pacote possibilita escrever programas PL/SQL para comunicação com servidores web (HTTP Server) e contém também uma procedure para ser utilizada em consultas SQL. A comunicação ocorre por meio dos protocolos HTTP ou HTTPS. O pacote contém recursos para manipular sessão, requisição, resposta, cookies e pode trabalhar com servidor proxy. Resumidamente este pacote possibilita enviar uma requisição a um servidor web e receber uma resposta.

Na prática, para que este pacote seja útil é preciso que os dois parceiros na comunicação (quem solicita e quem responde) utilizem um padrão, de forma que a resposta possa ser manipulada. Neste sentido, o padrão XML tem se demonstrado excelente.

Na Listagem 3 temos dois exemplos de conexão ao site da revista SQL Magazine. No primeiro exemplo os primeiros 2000 bytes com os comandos html do site são exibidos por meio de uma consulta SQL, no segundo exemplo todo o conteúdo do site é exibido por meio de um programa PL/SQL.

A API está dividida em categorias e somente os principais recursos serão descritos.

Para obter o conteúdo de um site por meio de comandos SQL, a API disponibiliza duas procedures: utl_http.request e utl_http.request_piece. A primeira retorna os primeiros 2000 bytes e a segunda também retorna 2000 bytes só que em vários pedaços.

Para utilizar um servidor Proxy, utilize as procedures utl_http.set_proxy para definir o proxy que será usado durante toda a execução do procedimento e utl_http.get_proxy para obter as configurações do proxy utilizado.

Para iniciar uma requisição HTTP e estabelecer uma conexão com o servidor web, utilize a procedure utl_http.begin_request após estabelecer a conexão com o servidor. Para enviar informações e/ou solicitações, utilize as procedures utl_http.write_text e utl_http.write_line para enviar informações em formato texto e utl_http.write_raw para formato binário.

Para definir informações de autenticação, utilize a procedure utl_http.set_authentication e, informações do cabeçalho ou “header”, utilize a procedure utl_http.set_header.

Para obter informações ou resposta do servidor, faça uso inicialmente da função utl_http.get_response. Na seqüência, utilize as procedures utl_http.read_text e utl_http.read_line para receber o corpo da resposta em formato texto e utl_http.read_raw se o formato for binário.

Para trabalhar com as informações contidas no cabeçalho, utilize a procedure utl_http.get_header_by_name que retorna o valor para um nome de parâmetro informado. Uma outra opção seria utilizar a função utl_http.get_header_count que retorna a quantidade de parâmetros contidos no cabeçalho. No exemplo da Listagem 3, uma estrutura de repetição é utilizada para obter todos os parâmetros do cabeçalho com a procedure utl_http.get_header. Esta procedure devolve o nome e o valor do parâmetro de acordo com o índice do parâmetro informado.

Para obter informações de autenticação do “header”, utilize a procedure utl_http.get_authentication.

Para encerrar a comunicação, utilize a procedure utl_http.end_response, que serve para fechar tanto a comunicação de requisição (request) como de resposta (response).

--Função que retorna os comandos html de uma página web numa consulta SQL

select sys.utl_http.request('http://www.sqlmagazine.com.br') from dual

--Programa PL/SQL que exibe os comandos html de uma página web

Declare

  req   sys.utl_http.req;

  resp  sys.utl_http.resp;

  nome  VARCHAR2(256);

  valor VARCHAR2(1024);

Begin

  -- Abrindo a conexão e iniciando uma requisição

  req := sys.utl_http.begin_request('http://www.sqlmagazine.com.br');

  -- Preparando–se para obter as respostas

  resp := sys.utl_http.get_response(req);

  -- Pegando todas as informações contidas no cabeçalho

  For i In 1 .. sys.utl_http.get_header_count(resp) Loop

    sys.utl_http.get_header(resp, i, nome, valor);

    sys.dbms_output.put_line(nome || ': ' || valor);

  END LOOP;

  -- Pegando todo o código da página html utilizado na construção do site

  Loop

    sys.utl_http.read_line(resp, valor, True);

    sys.dbms_output.put_line(valor);

  End Loop;

  -- Encerrando a comunicação request/response

  sys.utl_http.end_response(resp);

Exception

  When Others Then

    sys.utl_http.end_response(resp);

End;

Listagem 3. Código PL/SQL com exemplo do pacote utl_http.

Pacote UTL_SMTP

Este pacote possibilita o envio de e-mail de dentro de programas PL/SQL por meio do protocolo SMTP definido pela RFC 821 (Nota 1). O protocolo SMTP só possibilita o envio de mensagens eletrônicas, sendo assim, não é possível o recebimento de e-mail de dentro de programas PL/SQL.

É possível enviar mensagens em formato texto, HTML ou outro formato desde que especificado o tipo MIME (Nota 1). As mensagens podem ser enviadas também com ou sem anexos.

Como se pode notar, a utilidade deste pacote é infinita; torna as aplicações mais amigáveis, com uma comunicação mais ativa, com mensagens de alerta e confirmação, além de um excelente canal de comunicação com os clientes.

Na Listagem 4 temos um pequeno exemplo de um programa PL/SQL para envio de um e-mail simples.

Declare

  conn sys.utl_smtp.connection;

Begin

  --Abre a conexão com o servidor SMTP

  conn := sys.utl_smtp.open_connection('servidor', 25);

  sys.utl_smtp.helo(conn, 'servidor');

  sys.utl_smtp.mail(conn, 'remetente@dominio.com.br');

  sys.utl_smtp.rcpt(conn, 'destinatario@dominio.com.br');

  sys.utl_smtp.open_data(conn);

  --Prepara mensagem do e-mail

  sys.utl_smtp.write_data(conn, 'From' || ': ' ||

                 '"Nome do remetente" < remetente@dominio.com.br>' || sys.utl_tcp.CRLF);

  sys.utl_smtp.write_data(conn, 'To' || ': ' ||

                 '"Nome do destinatário" <DESTINATARIO@DOMINIO.COM.BR>' || sys.utl_tcp.CRLF); 

  sys.utl_smtp.write_data(conn, 'Subject' || ': ' || 'ASSUNTO TESTE' || sys.utl_tcp.CRLF); 

  sys.utl_smtp.write_data(conn, sys.utl_tcp.CRLF || 'CORPO DO E-MAIL!');

  sys.utl_smtp.write_data(conn, sys.utl_tcp.CRLF || 'TEXTO LINHA 2');

  sys.utl_smtp.write_data(conn, sys.utl_tcp.CRLF || 'TEXTO LINHA 3');

  --Fecha a sessão e envia o e-mail

  sys.utl_smtp.close_data(conn);

  --Fecha a conexão com o servidor SMTP

  sys.utl_smtp.quit(conn);

Exception

  When Others Then

    sys.dbms_output.put_line('Falha no envio do e-mail - ' || SQLERRM);

    sys.utl_smtp.quit(conn);

End;

Listagem 4. Código PL/SQL com exemplo do pacote utl_smtp.

Para começar é preciso obter uma conexão com o servidor SMTP, isto é feito definindo uma variável do tipo utl_smtp.connection e chamando a procedure utl_smtp.open_connection. Esta referência à conexão será utilizada em praticamente todos os procedimentos do pacote UTL_SMTP.

Ao abrir a conexão é preciso informar o servidor (nome do host ou endereço IP) e a porta (só é preciso informar se o valor for diferente de 25 que é o padrão).

Na seqüência, o comando utl_smtp.helo identifica o domínio do remetente e utl_smtp.mail inicia a transação com o servidor. Já utl_smtp.rcpt especifica o remetente da mensagem.

O comando utl_smtp.open_data abre uma sessão para o envio de grande volume de dados com utl_smtp.write_data, seguido de utl_smtp.close_data que fecha a sessão e envia o e-mail. Já o comando utl_smtp.quit encerra a conexão aberta com o servidor SMTP.

Opcionalmente:

·         para enviar comandos específicos para o servidor SMTP pode-se utilizar utl_smtp.command;

·         para validar o endereço de e-mail do destinatário utilize utl_smtp.vrfy e;

·         para abortar a transação de e-mail corrente utilize utl_smtp.rset.

Pacote UTL_FILE

Este pacote possibilita que programas escritos em PL/SQL leiam e escrevam dados em arquivos armazenados no servidor.

Em versões do Oracle anterior à 9i, por motivo de segurança, os arquivos e diretórios acessíveis tinham  que ser informados no parâmetro UTL_FILE_DIR localizado no arquivo de inicialização do banco de dados INIT.ORA. Esta exigência tinha uma desvantagem, o diretório só estaria disponível para manipulação quando o banco de dados fosse reiniciado.

A partir da versão 9i é possível criar uma referência e permissão (diretório virtual) para manipulação dos arquivos contidos em um determinado diretório sem a necessidade de reiniciar o banco. Para tanto, deve-se utilizar o comando “create e grant directory” conforme Listagem 5.

É importante mencionar que as permissões se aplicam apenas ao diretório e aos arquivos contidos no mesmo, não se aplicando a subdiretórios. Para isto, deve-se criar um novo diretório virtual para cada subdiretório.

-- Logar no Oracle como SYS ou SYSTEM ou um usuário que tenha permissão/grant de CREATE DIRECTORY

--Criar referência ao diretório

CREATE OR REPLACE DIRECTORY <INFORMAR DIRETÓRIO LÓGICO>AS <INFORMAR DIRETÓRIO FÍSICO NO DISCO>;

  --Exemplo

  create directory dir as ‘c:\temp’;

--Dar permissão

GRANT <INFORMAR TIPO: READ ou WRITE>ON DIRECTORY <INFORMAR DIRETÓRIO LÓGICO>TO <SEU USUARIO>;

  --Exemplo

  GRANT READ ON dir TO usuario;

  GRANT WRITE ON dir TO usuario;

Listagem 5. Criação de diretório virtual e permissão para manipulação.

Outro fator a ser considerado é a segurança do sistema operacional com as permissões nos arquivos e diretórios envolvidos.

Resumindo, para manipular um arquivo com PL/SQL é preciso: (1) criar uma referência para um diretório no disco rígido com o comando “create directory” e (2) que o usuário que iniciou o banco de dados tenha permissões adequadas (leitura/escrita) tanto para o diretório como para o arquivo a ser manipulado.

Na Listagem 6 temos um exemplo de um programa PL/SQL que lê um arquivo “arqDE” formato texto e grava todo o seu conteúdo em outro arquivo “arqPARA”.

A utilização é bem simples: substituir os valores das variáveis “arqDE” e “arqPARA” para: nome do arquivo de entrada e nome do arquivo de saída respectivamente.

É possível abrir o arquivo para leitura e escrita. Isto é feito com a função utl_file.fopen, que espera receber o diretório e nome do arquivo além do modo de abertura, para leitura utilizar “R” de “read”, para escrita utilizar “W” de “write” e para atualização “A” de “append”. Neste último modo de abertura, os novos dados inseridos serão acrescentados no final do arquivo.

Para ler uma linha do arquivo de origem, utilize a procedure utl_file.get_line. Se não houver mais linhas no arquivo, uma exceção No_Data_Found é gerada.

Para escrever uma linha de dados no arquivo, utilize a procedure utl_file.put. Neste caso, será necessário utilizar a procedure utl_file.new_line para acrescentar uma quebra de linha no arquivo ou então utilizar a procedure utl_file.put_line que acrescenta a linha e a quebra de linha automaticamente.

Para gravar no disco as informações acrescentadas no arquivo, utilize a procedure utl_file.fflush, caso contrário, os dados só serão salvos quando o arquivo for fechado.

Para fechar o arquivo, utilize a procudure utl_file.fclose e informe qual arquivo deseja fechar ou  utl_file.fclose_all para fechar todos os arquivos abertos. Para verificar se um arquivo está aberto, utilize a função utl_file.is_open.

Por fim, para copiar, renomear ou remover um arquivo fisicamente no disco rígido, utilize as procedures utl_file.fcopy, utl_file.frename e utl_file.fremove respectivamente.

Declare

  linha VarChar2(1024);           -- Cada linha de dados

  arqIN sys.utl_file.file_type;   -- Handle para o arquivo de entrada

  arqOUT sys.utl_file.file_type;  -- Handle para o arquivo de saída

  arqDE VarChar2(200);            -- Nome do arquivo Origem

  arqPARA VarChar2(200);          -- Nome do arquivo Destino

Begin

  arqDE := 'nome do arquivo e extensão'; 

  arqPARA := 'nome do arquivo e extensão'; 

  --Abrindo os arquivo de entrada e saída

  Begin

    --dir = diretório criado com comando create directory

    arqIN  := sys.utl_file.fopen('dir', arqDE, 'R');

    arqOUT := sys.utl_file.fopen('dir', arqPARA, 'W');

  Exception

    When Others Then

      raise_application_error(-20001,'Erro ao abrir arquivos: ' || sqlerrm);

  End;

  --Ler linha a linha do arquivo de entrada

  Loop

    Begin

      --Pegando o valor da linha corrente do arquivo de entrada

      sys.utl_file.get_line(arqIN, linha);

    Exception

      When No_Data_Found Then

        Exit;

    End;

    --Gravando a linha no arquivo de saída

    sys.utl_file.put_line(arqOUT, linha);

  End Loop;

  --Fechando os arquivos abertos

  sys.utl_file.fclose(arqIN);

  sys.utl_file.fclose(arqOUT);

Exception

  --Se der algum erro e ainda tiver com os arquivos abertos os mesmos são fechados

  When Others Then

    If sys.utl_file.is_open(ArqIN) then

      sys.utl_file.fClose(ArqIN);

    End If;

    If sys.utl_file.is_open(ArqIN) then

      sys.utl_file.fClose(ArqOUT);

    End If;

    Raise_Application_Error(-20005, SQLERRM);

End;

Listagem 6. Código PL/SQL com exemplo do pacote utl_ftp.

Nota 1. Padrões RFC

RFC é um acrônimo para o inglês “Request for Comments” que quer dizer “Requerimento para Comentários”. Trata-se de uma série de documentos (editados desde os anos 70) que abordam, de forma técnica, aspectos relacionados à internet como protocolos e serviços.

Os padrões são mantidos pela IETF (Internet Engineering Task Force) que é uma comunidade internacional preocupada com a evolução e funcionamento da internet.

Abaixo os padrões citados neste artigo:

·   RFC 1738 – URL

Universal Resource Locator é o endereço que identifica um recurso disponível na internet e tem a seguinte estrutura: protocolo://maquina/caminho/arquivo. O protocolo pode ser por exemplo HTTP.

·   RFC 959 – FTP

File Transfer Protocol, com a finalidade de transferir arquivos entre dois computadores.

·   RFC 821 – SMTP

Simple Mail Transport Protocol, com a finalidade de enviar as mensagens eletrônicas para um destinatário em um servidor específico.

·   RFC 1341 - MIME

Multipurpose Internet Mail Extensions, é parte importante do processo de envio e recebimento de mensagens eletrônicas, pois tais mensagens são basicamente em formato texto, cabendo ao MIME a interpretação e garantia de anexos em outros formatos.

Conclusões

Como foi demonstrado, stored procedures no Oracle podem ser utilizadas para execução de inúmeras tarefas e atividades que não estão diretamente relacionadas com a manipulação dos dados armazenados no banco de dados. Inclui-se nestas atividades a comunicação com outros softwares e serviços disponibilizados em outros servidores por meio de protocolos padrões de comunicação e integração.

Apesar de simples, devido à enorme quantidade de recursos e detalhes, estes exemplos demonstram um universo de possibilidades para o desenvolvimento de aplicações mais interativas, distribuídas e robustas.

Referências

IETF: http://www.ietf.org

Lista de RFC: http://www.ietf.org/rfc.html