Tag: ‘oracle’
Manipulando BLOB – MySQL vs. Oracle
A missão de um programador em manipular arquivos e armazená-los no banco de dados já não é uma tarefa desafiadora a tempos, visto que é extremamente fácil encontrar exemplos internet a fora, aqui vou fazer mais um, em que o foco é demonstrar a diferença de se realizar o mesmo procedimento utilizando o MySQL e Oracle.
1. Oracle
Primeiramente vamos fazer o processo para gravar um arquivo qualquer no banco de dados.
Criação da tabela que será utilizada para armazenar os arquivos.
CREATE TABLE ARQUIVOS( ID NUMBER(4) NOT NULL, TIPO VARCHAR2(20) NULL, ARQ BLOB DEFAULT EMPTY_BLOB() NOT NULL, CONSTRAINT PKARQ PRIMARY KEY(ID) )
Perceba que o campo ARQ que irá guardar o binário, tem como valor default a propriedade EMPTY_BLOB() – Retorna um LOB vazio, para ser usado como um indicador para INSERT ou UPDATE.
No geral, o processo para inserir um campo LOB (BLOB ou CLOB) no Oracle é um pouco burocrático, feito em duas etapas. Primeiro deve-se inserir o registro setando o campo BLOB como EMPTY_BLOB, no em seguida finalizamos com um SELECT FOR UPDATE no campo “LOB” especifico para que o byte[] seja gravado.
// Inserindo o empty_blob // String sql = "INSERT INTO arquivos(id, tipo, arq) VALUES(1, ?, EMPTY_BLOB())"; conn = ConnOracle.getInstance().getConn(); pstmt1 = conn.prepareStatement(sql); pstmt1.setString(1, "TEXT"); pstmt1.execute();
Este passo irá fazer com que um arquivo seja armazenado no banco.
// Faz o select como for update
// Inserir o binário
//
String ins = "SELECT arq FROM arquivos WHERE id = 1 FOR UPDATE";
pstmt2 = conn.prepareStatement(ins);
rset = pstmt2.executeQuery(ins);
while( rset.next() ){
Blob blob = rset.getBlob(1);
// filePath eh a String com o caminho do arquivo a ser gravado
File file = new File( filePath );
byte[] bbuf = new byte[1024];
InputStream bin = new FileInputStream( file );
// Realiza o cast especifico para o driver Oracle
OutputStream bout = ((BLOB) blob).getBinaryOutputStream();
int bytesRead = 0;
while( (bytesRead = bin.read(bbuf))!=-1 ){
bout.write(bbuf, 0, bytesRead);
}//
if( bout!=null ) bout.close();
}// end while
conn.commit();
2. MySQL
A estrutura da tabela muda pouco, o mais importante é que o campo para armazenar o arquivo é do tipo LONGBLOB, veja:
CREATE TABLE ARQUIVOS( ID INT(4) NOT NULL, TIPO VARCHAR(20) NULL, ARQ LONGBLOB NOT NULL, PRIMARY KEY(ID) );
Gravar um campo LONGBLOB no MySQL, é tão fácil quando gravar um int ou uma String, e eu não estou falando no sentido figurado.
File file = new File( filePath ); byte[] bytes = getBytesFromFile( file );</pre> // Inserindo o longblob // String sql = "INSERT INTO arquivos(id, tipo, arq) VALUES(1, ?, ?)"; conn = ConnMysql.getInstance().getConn(); pstmt = conn.prepareStatement(sql); pstmt.setString(1, "TEXT"); pstmt.setBytes(2, bytes); pstmt.execute();
O método getBytesFromFile() é uma função genérica para converter o File em byte[], e somente este último é necessário para gravar, fácil assim.
Não posso deixar de indicar este artigo completo e bem detalhado, escrito por Giovane Kuhn do JavaFree.
Espero que estes simples códigos sejam proveitosos, abaixo estão as duas classes utilizadas para este post.
Enviando e-mail com PL/SQL para múltiplos endereços
A situação mais comum é o envio de e-mail para diversos destinatários, portanto, complementando o código já postado aqui sobre o envio de e-mails utilizando PLSQL.
Uma lista de destinatários formatada normalmente assim:
Luciano <luciano@lucianosilva.com>, Grupos <grupos@lucianosilva.com>, Futebol <futebol@cfb.com.br>
Este é o nosso problema, o Oracle não irá conseguir trabalhar com a String formatada dessa maneira, e irá lançar o erro:
ORA-29279: SMTP permanent error: 501 Bad address syntax
Enfim, para conseguir solucionar o problema do envio de e-mail para multiplos destinatários, utilize a função abaixo, ela irá devolver apenas o endereço de e-mail.
FUNCTION FORMAT_ADDRESS(ADDR_LIST IN OUT VARCHAR2) RETURN VARCHAR2 IS --- ADDR VARCHAR2(256); I PLS_INTEGER; --- FUNCTION LOOKUP_UNQUOTED_CHAR(STR IN VARCHAR2, CHRS IN VARCHAR2) RETURN PLS_INTEGER AS C VARCHAR2(5); I PLS_INTEGER; LEN PLS_INTEGER; INSIDE_QUOTE BOOLEAN; --- BEGIN INSIDE_QUOTE := FALSE; I := 1; LEN := LENGTH(STR); WHILE (I <= LEN) LOOP C := SUBSTR(STR, I, 1); IF (INSIDE_QUOTE) THEN IF (C = '"') THEN INSIDE_QUOTE := FALSE; ELSIF (C = '\') THEN I := I + 1; -- Skip the quote character END IF; GOTO NEXT_CHAR; END IF; IF (C = '"') THEN INSIDE_QUOTE := TRUE; GOTO NEXT_CHAR; END IF; IF (INSTR(CHRS, C) >= 1) THEN RETURN I; END IF; <<NEXT_CHAR>> I := I + 1; END LOOP; RETURN 0; END; BEGIN ADDR_LIST := LTRIM(ADDR_LIST); I := LOOKUP_UNQUOTED_CHAR(ADDR_LIST, ',;'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR_LIST, 1, I - 1); ADDR_LIST := SUBSTR(ADDR_LIST, I + 1); ELSE ADDR := ADDR_LIST; ADDR_LIST := ''; END IF; I := LOOKUP_UNQUOTED_CHAR(ADDR, '<'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR, I + 1); I := INSTR(ADDR, '>'); IF (I >= 1) THEN ADDR := SUBSTR(ADDR, 1, I - 1); END IF; END IF; RETURN ADDR; END FORMAT_ADDRESS;
A mudança do código anterior não é tão drástica, já que o parâmetro é entrada/saída, você pode fazer um looping na lista de endereços para adicionar um-a-um.
UTL_SMTP.MAIL(MAIL_CONN, V_FROM ); WHILE( V_LIST_ADDRESS IS NOT NULL )LOOP UTL_SMTP.RCPT(MAIL_CONN, FORMAT_ADDRESS(V_LIST_ADDRESS) ); END LOOP;
Veja que é uma modificação simples, porém, ganha-se muito quando é necessário deixar o programa o mais abrangente possível
Enjoy!
Enviando e-mail com PL/SQL
Em continuidade a nossa série “as mil-e-uma utilidades do seu banco de dados Oracle”, agora você poderá descobrir como é fácil enviar e-mails a partir do PL/SQL e saber o quanto isto é produtivo.
Vamos lá, para o envio de e-mails é necessário que o pacote UTL_MAIL esteja instalado.
Conectando como sysdba na instância ORCL.
C:\>set oracle_sid=orcl C:\>sqlplus sys/oracle as sysdba
Instalando o pacote UTL_MAIL. Isto não é nenhuma novidade, mas a variável de ambiente %ORACLE_HOME% indica o caminho a qual o banco de dados foi instalado, neste caso, foi utilizada a versão 10.2.0 – C:\oracle\product\10.2.0\db_1. Os sub-diretórios, contém os pacotes necessários para o UTL_MAIL funcionar.
SQL> set serveroutput on SQL> @%ORACLE_HOME%/rdbms/admin/utlmail.sql SQL> @%ORACLE_HOME%/rdbms/admin/prvtmail.plb SQL> show errors;
Pronto, isto é o suficiente para o nosso próximo passo, a criação da procedure de envio de e-mails.
Crie uma package para agrupar todas as funcionalidades que podem ser comuns a diversos projetos, e reutilize o código quando necessário.
create or replace package PKG_BLOG_UTIL is -- Author : LUCIANO -- Created : 05/07/2009 PROCEDURE enviar_email_auth(p_USUARIO IN VARCHAR2, p_SENHA IN VARCHAR2, p_TO IN VARCHAR2, p_COPIA IN VARCHAR2, p_SUBJECT IN VARCHAR2, p_MESSAGE IN VARCHAR2); end PKG_BLOG_UTIL;
Veja agora a versão 1.0 desta implementação:
CREATE OR REPLACE PACKAGE BODY PKG_BLOG_UTIL IS PROCEDURE ENVIAR_EMAIL_AUTH(P_HOST IN VARCHAR2, P_USUARIO IN VARCHAR2, P_SENHA IN VARCHAR2, P_TO IN VARCHAR2, P_COPIA IN VARCHAR2, P_SUBJECT IN VARCHAR2, P_MESSAGE IN VARCHAR2) IS MAIL_CONN UTL_SMTP.CONNECTION; V_HEADER VARCHAR2(4000); CRLF VARCHAR2(2) := CHR(13) || CHR(10); -- quebra de linha BEGIN -- Abre a conexão MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(P_HOST, 25); UTL_SMTP.HELO(MAIL_CONN, P_HOST); -- Faz a autenticação para envio de mensagem UTL_SMTP.COMMAND(MAIL_CONN, 'AUTH LOGIN'); UTL_SMTP.COMMAND(MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USUARIO)))); UTL_SMTP.COMMAND(MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_SENHA)))); -- Prepara o cabeçalho de V_HEADER := 'Date:' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') || CRLF || 'From:' || p_USUARIO || CRLF || 'Subject:' || p_SUBJECT || CRLF || 'To:' || p_TO || CRLF || 'Cc:' || p_COPIA || CRLF || CRLF || CRLF || p_MESSAGE; -- UTL_SMTP.MAIL(MAIL_CONN, '<' || P_USUARIO || '>'); UTL_SMTP.RCPT(MAIL_CONN, '<' || P_TO || '>'); UTL_SMTP.DATA(MAIL_CONN, V_HEADER); -- Fecha a conexão UTL_SMTP.QUIT(MAIL_CONN); END ENVIAR_EMAIL_AUTH; END PKG_BLOG_UTIL;
Algumas coisas podem ser melhoradas como, separar o corpo da mensagem do cabeçalho e utilizar o UTL_TCP.CRLF como separador de linhas no lugar da variável CRLF, veja como o código fica mais organizado:
/* APENAS UM TRECHO DO CÓDIGO */ UTL_SMTP.MAIL(MAIL_CONN, '<' || P_USUARIO || '>'); UTL_SMTP.RCPT(MAIL_CONN, '<' || P_TO || '>'); UTL_SMTP.open_data(MAIL_CONN); -- Prepara o cabeçalho UTL_SMTP.write_data(MAIL_CONN, 'From' || ': ' || p_USUARIO || UTL_TCP.CRLF); UTL_SMTP.write_data(MAIL_CONN, 'To' || ': ' || p_TO || UTL_TCP.CRLF); UTL_SMTP.write_data(MAIL_CONN, 'Cc' || ': ' || p_COPIA || UTL_TCP.CRLF); UTL_SMTP.write_data(MAIL_CONN, 'Subject' || ': ' || P_SUBJECT || UTL_TCP.CRLF); -- Escreve a mensagem UTL_SMTP.write_data(MAIL_CONN, UTL_TCP.CRLF || p_message); UTL_SMTP.close_data(MAIL_CONN);
Eu preferi adotar este segundo código como o mais recomendável, mesmo assim, alguns detalhes podem ser adaptados a sua realidade, por exemplo, criar uma constante para o Mail Host, e indicando que o envio de e-mails sempre será a partir daquele servidor ou fazer a sobrecarga da procedure – a reutilização é uma benção.
Veja que a assinatura da procedure abaixo é um overload do que foi mostrado acima, isto é um conceito de Orientação-a-Objetos suportado pelo PLSQL, permite que um método seja escrito com diversas assinaturas diferentes, e reaproveitando o código de maneira eficaz.
PROCEDURE ENVIAR_EMAIL_AUTH(P_USUARIO IN VARCHAR2, P_SENHA IN VARCHAR2, P_TO IN VARCHAR2, P_COPIA IN VARCHAR2, P_SUBJECT IN VARCHAR2, P_MESSAGE IN VARCHAR2) IS V_MAILHOST CONSTANT VARCHAR2(100) := 'pop.xxxx.com.br'; BEGIN enviar_email_auth(P_HOST => v_mailhost, P_USUARIO => P_USUARIO, P_SENHA => P_SENHA, P_TO => P_TO, P_COPIA => P_COPIA, P_SUBJECT => P_SUBJECT, P_MESSAGE => P_MESSAGE); END ENVIAR_EMAIL_AUTH;
A package completa, está disponível aqui para download.
Não envie spam!
Chamando o bash a partir do PL/SQL com Java Stored Procedure
Se você sabe utilizar o recursos do Java Stored Procedure em seu banco de dados, significa que você é uma pessoa abençoada.
A não tão popular JSP compartilha alguns dos recursos da plataforma Java com a linguagem estrutural PL/SQL, eu disse alguns, pois existem limitações. Muitos desenvolvedores se beneficiam do pacote java.io.* quando falamos de Java Stored Procedure, visto que os recursos são mais amplos, quando comparado ao famoso UTL_FILE.
Vamos realizar passo-a-passo utilizando o banco de dados Oracle 10g em ambiente windows, para a criação de uma Procedure, que irá chamar um programa Java (Java Stored Procedure), cujo este permitirá executar comandos do prompt (os mais antigos leiam Bash).
É provavel que você encontre no Google diversas outras maneiras de criar a Java Stored Procedures, porém, o proposito aqui é mais amplo, ajudando a criar todo o ambiente, vamos lá!
Primeiramente, o usuário de banco, owner do objeto JSP, deve ter alguns permissões para manipular os arquivos no sistema operacional. Neste exemplo no nosso schema será blog.
Informe o usuário (schema), tipo de acesso e, opcionalmente o diretório em que será permitida a manipulação do arquivo.
EXEC DBMS_JAVA.grant_permission('BLOG', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
EXEC Dbms_Java.Grant_Permission('BLOG', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('BLOG', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
Feito isto, os objetos de banco são simples, crie o programa abaixo com o usuário blog.
create or replace and compile java source named Bash as
package com.lucianosilva.oracle;
import java.io.*;
import java.lang.*;
public class Bash {
public static void command(String command) throws Exception {
final Process process = Runtime.getRuntime().exec(command);
process.waitFor();
process.destroy();
}
public static String commandReturn(String command) throws Exception {
final Process process = Runtime.getRuntime().exec(command);
process.waitFor();
String retCode = Integer.toString( process.exitValue() );
if ( retCode.equals("0") ){
retCode = retCode + " Executado com sucesso.";
}else{
retCode = retCode + " Erro durante a execucao.";
}
process.destroy();
return retCode;
}
public static void executeAsynchronousCommand(String command) throws Exception {
Runtime.getRuntime().exec( command );
}
}
O método commandReturn não retorna o resultado do comando bash e sim uma mensagem informando se o comando foi bem executado ou não.
Agora vamos testar, ainda como blog, faça:
CREATE OR REPLACE FUNCTION EXECUTAR_COMAND_BASH(P_COMMAND IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'com.lucianosilva.oracle.Bash.commandReturn(java.lang.String) return java.lang.String';
Isto é tudo! Um exemplo muito prático, sem dúvidas, já que dependendo da solução adotada a integração de procedures PLSQL com o crontab é muito comum e isto pode ajudar.
Sayonara
Apresentando o BPEL com o Adapter File
A solução da Oracle para o conceito de arquitetura de serviços SOA, conhecida como Oracle SOA Suite, disponibiliza um pacote com diversos recursos para organização, orquestração, segurança e integração. Para este último posso destacar o Business Process Execution Language, BPEL, que integrado ao JDeveloper é uma excelente ferramenta de trabalho, fácil, simples e com alguns recursos (adapters) pré-compilados que auxiliam.
O BPEL é uma linguagem que descrita em XML, que básicamente é utilizada para desenhar processo de negócio seguindo como base o BPM, destaca-se pela simplicidade da criação de um processo e pela amigável interface, proporcionando uma sensação agradável no momento do design. O utilizador de BPEL não necessita de conhecimentos em linguagem de programação, porém, o programador poderá obter mais recursos da ferramenta.
Para apresentar o BPEL escolhi algo um pouco mais além do famoso “Hello Word”, vamos trabalhar em um cenário de integração de softwares, aonde o legado irá disponibilizar um arquivo TXT no padrão CVS posicional diáriamente, o processo deverá interpretar o conteúdo deste arquivo e gravar no banco.
Pré-Requisitos
JDeveloper 10.1.3.4 (com suporte a SOA).
SOA Suite 10.1.3.1.0 (Com Windows Vista, talvez vc tenha problemas, baixe o patch).
MySQL.
Parte 1 – Criando o Projeto
Crie um projeto do tipo BPEL Project em File -> New -> Projects -> BPEL Process Project (certifique-se de que está filtrando por todas as tecnologias). Em seguida, dê um nome ao projeto e na opção template opte por Empty BPEL Process.
Parte 2 – Usando o Adapter File
O Adapter File é um recurso do BPEL para manipulação de arquivos, oferece três opções Leitura, Escrita e Sincronização (Read, Writer e Synchronous Read), além dele existe o Adapter FTP (autoexplicativo), porém iremos falar sobre ele depois. Continuando com nosso projeto, vamos utiliza a opção Read para o arquivo texto que estará disponível no diretório c:\interface_dir, haverá um pooling a cada 1 minuto, obviamente que isto é apenas para testes você deve adequar o intervalo de tempo de acordo com sua necessidade, veja a seguir o passo-a-passo do Adapter File.
Veja aqui os screenshot do passo-a-passo para configurar o File Adapter.
Até este passo fizemos apenas configurações básicas da funcionalidade do Adapter, porém, agora é necessário definir um o mapeamento das colunas posicional (fixo), utililzando um Schema XSD, veja a seguir como é simples:
Screenshots para criar o Schema XSD.
Parte 3 – Criando o Adapter Database
Crie um banco de dados no MySQL ou em qualquer outro SGDB, em seguida crie a tabela pais que irá receber os dados contidos no arquivo texto. Antes de utilizar o Adapter Database certifique-se que sua conexão com o banco está feita pelo JDeveloper, ela será necessária para os passos a seguir, portando vá até a aba Connection, se não existir crie-a.
Com a tabela criada e a conexão feita, agora vamos utilizar o componente Database Adapter que será o responsável por executar instruções SQL. Os passos aqui são tão simples quanto a criação da conexão, porém, é importante destacar que no Passo 4 escolha a opção Execute Custom SQL, e no quinto e último passo coloque a instrução de inserção da tabela pais, não se preocupe com o XSD ele será criado automáticamente, veja como fica:
Passo 4 – While e Variáveis
Para cada campo do arquivo TXT, mapeado pelo Schema XSD, devemos criar uma variável que irá armazenar o valor dos campos, então vá até a aba Structure e crie as variáveis.
Depois de todas as variáveis criadas precisamos associa-las aos campos do XSD e inicializa-las, mas para isto devo esclarecer uma coisa, sabemos que o arquivo texto poderá ter uma ou mais linhas de registro, portanto é necessária uma instrução de repetição. Utilizaremos o componente WHILE, a partir de uma variável contadora (índice).
O While cria um novo escopo aonde devemos arrastar os componentes de assinatura das variáveis e chamada da procedure, ou seja, para cada registro iremos fazer a inicialização da variáveis e a chamada da procedure para gravar no banco de dados, finalizando o nosso processo BPEL.
O processo é simples mas o dominio do ambiente BPEL é excencial, saber criar as variáveis, inicializá-las, passagem de valores, etc. Abaixo está disponível o fonte do projeto criado no JDeveloper.
Clique aqui para baixar o fonte.