sexta-feira, 17 de dezembro de 2010

Criação de objetos para enviar e-mail

table SQ_VERIF_TABLESPACE

create table SQ_VERIF_TABLESPACE
(
  ID       CHAR(2),
  CONTEUDO VARCHAR2(50)
)
tablespace DAD5
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 32K
    next 32K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

 procedure sq_sendmail

create or replace procedure sq_sendmail (

      from_name     varchar2                     :='sergio.queiroz@inteligtelecom.com.br'

      ,to_name       varchar2                    :='sergio.queiroz@inteligtelecom.com.br'

      ,subject       varchar2                    :='Mensagem Oracle'

      ,message       varchar2                    :='Envio automatico de e-mail'

      ,max_size      number   default 9999999999

      ,filename1  in varchar2                    :='/erro.txt'

      ,filename2  in varchar2                    :='/u41/spool/email/enviados/P182303.txt'

      ,filename3  in varchar2                    :='/u41/spool/email/enviados/P182552.txt'

      ,debug         number   default 0 )

 

is

 

  -- Colocar os e-mails separados por ponto-vírgula

 

  v_smtp_server                          varchar2(30)     :='EXCHQT.INTELIG23';

  v_smtp_server_port                     number           := 25;

  v_directory_name                       varchar2(100);

  v_file_name                            varchar2(100);

  v_line                                 varchar2(1000);

  crlf                                   varchar2(2)      := chr(13) ||

chr(10);

  mesg                                   varchar2(32767);

 

  conn                                   UTL_SMTP.CONNECTION;

 

  type varchar2_table  is table of       varchar2(200) index by binary_integer;

  file_array                             varchar2_table;

 

  i                                      binary_integer;

  v_file_handle                          utl_file.file_type;

  v_slash_pos                            number;

  mesg_len                               number;

  mesg_too_long                          exception;

  invalid_path                           exception;

  mesg_length_exceeded                   boolean           := false;

 

  x number;

  vemail varchar2(200) := null;

  chars char(1);

 

begin

 

   -- Carregando os arquivos dentro do ARRAY

   -- ---------------------------------------

   file_array(1) := filename1;

   file_array(2) := filename2;

   file_array(3) := filename3;

 

   -- Abrindo Conexão SMTP e HTTP

   -- ----------------------------

   conn  := utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );

 

   -- Comunicando SMTP

   -- ------------------

   utl_smtp.helo( conn, v_smtp_server );

 

         -- Autenticando Usuário e Senha

         -- -----------------------------

         --utl_smtp.command (conn, 'AUTH LOGIN');

         --utl_smtp.command (conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('sergio.queiroz'))));

         --utl_smtp.command (conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('sergio0101'))));

 

   utl_smtp.mail( conn, from_name );

--   utl_smtp.rcpt( conn, to_name );

 

    -- Separa os destinatários

    -- -----------------------

    x := 1;

    for x in 1..length(to_name) loop

        chars :=  substr(to_name,x,1);

        if chars <> ';' then

           vemail := vemail || chars;

        else

           utl_smtp.rcpt( conn, vemail );

           vemail := '';

        end if;

    end loop;

    utl_smtp.rcpt( conn, vemail );

 

   -- Abre a conexão

   -- --------------

 

   utl_smtp.open_data ( conn );

 

   -- Criando Cabeça do E-mail

   -- -----------------------------------

   mesg:= 'Date: '    || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )                     || crlf ||

          'From: '    || from_name                                                      || crlf ||

          'Subject: ' || subject                                                        || crlf ||

          'To: '      || to_name                                                        || crlf ||

          'Mime-Version: 1.0'                                                           || crlf ||

          'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"'            || crlf ||

          ''                                                                            || crlf ||

          'This is a Mime message, which your current mail reader may not'              || crlf ||

          'understand. Parts of the message will appear as text. If the remainder'      || crlf ||

          'appears as random characters in the message body, instead of as'             || crlf ||

          'attachments, then you''ll have to extract these parts and decode them'       || crlf ||

          'manually.'                                                                   || crlf ||

          ''                                                                            || crlf ||

          '--DMW.Boundary.605592468'                                                    || crlf ||

          'Content-Type:              text/plain; name="message.txt"; charset=US-ASCII' || crlf ||

          'Content-Disposition:       inline;     filename="message.txt"'               || crlf ||

          'Content-Transfer-Encoding: 7bit'                                             || crlf ||

          ''                                                                            || crlf ||

          message                                                                       || crlf ;

 

   mesg_len := length(mesg);

 

   if mesg_len > max_size then

      mesg_length_exceeded := true;

   end if;

 

   utl_smtp.write_data ( conn, mesg );

 

   -- Anexando Arquivos

   -- ------------------

   for i in  1..3 loop

 

       -- Sair se ultrapassar o tamanho de mensagem

       -- -----------------------------------------

       exit when mesg_length_exceeded;

 

       if file_array(i) is not null then

 

          begin

 

             -- Localiza a '/' ou '\' no caminho

             -- ---------------------------------

             v_slash_pos := instr(file_array(i), '/', -1 );

 

             if v_slash_pos = 0 then

                v_slash_pos := instr(file_array(i), '\', -1 ); -- Valor Retornado = 3

 

             end if;

 

             -- Separa o arquivo do diretório

             -- ------------------------------

             v_directory_name := substr(file_array(i), 1, v_slash_pos - 1);

             v_file_name      := substr(file_array(i), v_slash_pos + 1 );

 

             -- Abrir Arquivo

             -- --------------

--             v_file_handle := utl_file.fopen(v_directory_name,v_file_name,'r');

             v_file_handle := utl_file.fopen('c:/',v_file_name,'r');

 

             -- Gera a linha MIME boundary

             -- --------------------------

             mesg := crlf || '--DMW.Boundary.605592468' || crlf ||

             'Content-Type:              application/octet-stream; name=    "v_file_name"'  || crlf ||

             'Content-Disposition:       attachment;               filename="v_file_name"'  || crlf ||

             'Content-Transfer-Encoding: 7bit'                                              || crlf ||

             crlf ;

 

             mesg_len := mesg_len + length(mesg);

             utl_smtp.write_data ( conn, mesg );

 

             -- Anexa o conteúdo do arquivo ao corpo da mensagem

             -- ------------------------------------------------

 

             loop

 

                 utl_file.get_line(v_file_handle, v_line);

 

                 if mesg_len + length(v_line) > max_size then

 

                    mesg := '*** truncado ***' || crlf;

 

                    utl_smtp.write_data ( conn, mesg );

 

                    mesg_length_exceeded := true;

 

                    raise mesg_too_long;

 

                 end if;

 

                 mesg := v_line || crlf;

 

                 utl_smtp.write_data ( conn, mesg );

 

                 mesg_len := mesg_len + length(mesg);

 

             end loop;

 

          exception

 

             when utl_file.invalid_path then

                 if debug > 0 then

                    dbms_output.put_line('Erro anexando arquivo ! '|| file_array(i));

                 end if;

 

             -- Todas EXCEPTIONS ignoradas

             when others then null;

 

          end;

 

          mesg := crlf;

 

          utl_smtp.write_data ( conn, mesg );

 

          -- Fecha Arquivo

          -- --------------

          utl_file.fclose(v_file_handle);

 

        end if;

 

   end loop;

 

   -- Fechando a Cabeça do E-mail

   -- ----------------------------

   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;

   utl_smtp.write_data ( conn, mesg );

 

   -- Fechando conexão SMTP

   -- -----------------------

   utl_smtp.close_data( conn );

   utl_smtp..quit( conn );

 

end;

PROCEDURE SQ_VERIFICAR_TABLESPACE

CREATE OR REPLACE PROCEDURE SQ_VERIFICAR_TABLESPACE IS

DECLARE

    Cursor c_space is (SELECT TABLESPACE_NAME, SUM(BYTES)

                FROM DBA_FREE_SPACE

               WHERE TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM USER_TABLES

                                         UNION

                                         SELECT DISTINCT TABLESPACE_NAME FROM USER_INDEXES

                                          WHERE TABLE_NAME IN (SELECT TABLE_NAME

                                                                 FROM USER_TABLES))

              GROUP BY TABLESPACE_NAME);

 

---------------------------------------

-- Criado por: Sérgio Queiroz

-- Empresa   : Atos Origin

-- Data      : 27/03/09

---------------------------------------

 

reg_tablespace varchar2(50);

reg_tamanho    varchar2(30);

reg_minimo     number := 52428800; --50Mb

msg            varchar2(30000);

crlf           varchar2(2) := chr(13) || chr(10);

 

/*

A TABELA SQ_VERIF_TABLESPACE DEVE SER ALIMENTADA CONFORME ABAIXO:

 

ID | CONTEUDO

--------------

1  | NOME DO CONSULTOR

2  | E-MAIL DO CONSULTOR

3  | TELEFONES DO CONSULTOR

4  | EMPRESA DO CONSULTOR

5  | E-MAIL QUE ENVIARÁ AS MENSAGEM

6  | E-MAILS QUE RECEBERÃO AS MENSAGENS (SEPARAR POR ";" SEM ESPAÇOS)

7  | ASSUNTO DO E-MAIL

8  | QUANTINDADE MÍNIMA DE Mb PARA O ENVIO DA MENSAGEM

9  | NOME DA TABLESPACES DE DADOS OU ÍNDICES QUE SERÁ VERIFICADA (UM PARA CADA ID)

9  | ...

9  | ...

10 | MENSAGEM INCIAL (ATÉ 50 BYTES POR ID, SOMENTE PARA OS IDs 10 A 15)

11 | ...

12 | ...

13 | ...

14 | ...

15 | ...

*/     

 

vconsnome   sq_verif_tablespace.conteudo%type;

vconsmail   sq_verif_tablespace.conteudo%type;

vconstel    sq_verif_tablespace.conteudo%type;

vconsemp    sq_verif_tablespace.conteudo%type;

 

vmailfrom   sq_verif_tablespace.conteudo%type;

vmailto     sq_verif_tablespace.conteudo%type;

vmailsubj   sq_verif_tablespace.conteudo%type;

 

vmensagem   varchar2(400);

vmsg        varchar2(50);

 

vqtdeminima sq_verif_tablespace.conteudo%type

vind        number;

vconteudo   sq_verif_tablespace.conteudo%type;

 

begin

     -- Busca informações para envio do e-mail

     -- --------------------------------------

     select conteudo into vconsnome from sq_verif_tablespace where id = '1';

     select conteudo into vconsmail from sq_verif_tablespace where id = '2';

     select conteudo into vconstel from sq_verif_tablespace where id = '3';

     select conteudo into vconsemp from sq_verif_tablespace where id = '4';

     select conteudo into vmailfrom from sq_verif_tablespace where id = '5';

     select conteudo into vmailto from sq_verif_tablespace where id = '6';

     select conteudo into vmailsubj from sq_verif_tablespace where id = '7';

     select conteudo into vqtdeminima from sq_verif_tablespace where id = '8';

     --

 

     -- Busca a mensagem incial que vai aparecer no topo do e-mail

     -- ----------------------------------------------------------

     vmensagem :=  crlf || 'TABLESPACES COM MENOS DE ' || vqtdeminima || ' Mb' || crlf || crlf ;

     vind := 0;

     for vind in 10..15 loop

         select conteudo into vmsg from sq_verif_tablespace where id = vind;

         if vmsg is not null then

            vmensagem := vmensagem || trim(vmsg) || crlf;

         end if;

     end loop;

     --

 

     -- Monta parte superior da tabela

     -- ------------------------------

     msg := crlf || vmensagem || crlf || crlf;

     msg := msg || '+------------------------------------------+---------------+' || crlf ;

     msg := msg || '| TABLESPACE_NAME                          |  FREE SPACE   |' || crlf ;

     msg := msg || '+------------------------------------------+---------------+' || crlf ;

     msg := msg || '|                                          |               |' || crlf ;

     --

    

     -- Transforma o quantidade mínima em Mb

     -- ------------------------------------

     vqtdeminima := to_char(to_number(vqtdeminima) * 1024 * 1024);

     --

    

     open c_space;

    

     loop

         fetch c_space into reg_tablespace, reg_tamanho;

         exit when c_space%notfound;

                                                                                                                                                                & nbsp;                                                                                                                                                                                               & nbsp;                                                                                                                                                                                                  

         vconteudo := null;

         begin

             -- Verifica as tablespaces existentes e monta a linha da tabela

             -- ------------------------------------------------------------

             select conteudo into vconteudo from sq_verif_tablespace where id = 9 and conteudo = reg_tablespace;

          

             if vconteudo is not null then

                 if reg_tamanho <= to_number(vqtdeminima) then        

                    msg := msg || '| ' || rpad(reg_tablespace, 41,' ') || '| ' || lpad(to_char(to_number(reg_tamanho)/1024/1024,'999G999G999G999','nls_numeric_characters='',.''') || ' Mb',11,' ') || '   |' || crlf;

                 end if;

             end if;

             exception

                 when no_data_found then

                      null;

             --        

         end;

     end loop;

 

     -- Monta parte inferior da tabela

     -- ------------------------------

     msg := msg || '|                                          |               |' || crlf ;

     msg := msg || '+------------------------------------------+---------------+' || crlf || crlf ;

     msg := msg || '------------------------------------------------------------' || crlf ;

     msg := msg || ' CONSULTOR: ' || vconsnome || crlf ;

     msg := msg || ' E-MAIL   : ' || vconsmail || crlf ;

     msg := msg || ' TELEFONE : ' || vconstel  || crlf ;

     msg := msg || ' EMPRESA  : ' || vconsemp  || crlf ;

     msg := msg || '------------------------------------------------------------' || crlf ;

     --

    

     -- Envia o e-mail

     -- --------------

     sq_sendmail (vmailfrom,

                  vmailto,

                  vmailsubj,

                  msg);

     --

                    

     close c_space;

end;


Exemplo insert SQ_VERIF_TABLESPACE

prompt PL/SQL Developer import file

prompt Created on sexta-feira, 27 de março de 2009 by sergio.queiroz

set feedback off

set define off

prompt Loading SQ_VERIF_TABLESPACE...

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('1 ', 'SÉRGIO QUEIROZ');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('2 ', 'sergio.queiroz@atosorigin.com');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('3 ', '13 8111-8329 / 11 2183-2629');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('4 ', 'ATOS ORIGIN');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('5 ', 'sergio.queiroz@inteligtelecom.com.br');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('6 ', 'sergio.queiroz@inteligtelecom.com.br');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('7 ', 'ALERTA AUTOMÁTICO: Tablespaces com pouco espaço');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('8 ', '100');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'USERS');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('10', 'FAVOR VERIFICAR AS TABLESPACES ABAIXO');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('11', '*************************************');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('12', null);

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('13', null);

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('14', null);

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('15', null);

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'DAD1');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'DAD2');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'DAD3');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'DAD4');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'DAD5');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX1');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX2');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX3');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX4');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX5_S160K');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'IDX6_S5');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_CIAP_DADOS');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_CIAP_INDICES');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_FIS_ARQMAG');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_FIS_ARQMAG_IDX');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_IN86_DADOS');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_IN86_DADOS_CARGA');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_IN86_INDEX');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_IN86_INDEX_CARGA');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_SYN_ALL');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_SYN_ALL_IDX');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_SYN_PRCGER_IMP');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_SYN_PRCGER_IMP_IDX');

insert into SQ_VERIF_TABLESPACE (ID, CONTEUDO)

values ('9 ', 'TS_TEMP');

commit;

prompt 39 records loaded

set feedback on

set define on

prompt Done.


Nenhum comentário :

Postar um comentário

Related Posts Plugin for WordPress, Blogger...