ROWTOCOL ( linhas para colunas )

SQL> CREATE OR REPLACE FUNCTION rowtocol (
  2     p_slct IN VARCHAR2,
  3     p_dlmtr IN VARCHAR2 DEFAULT ',' )
  4  RETURN VARCHAR2
  5     AUTHID CURRENT_USER AS
  6     TYPE c_refcur IS REF CURSOR;
  7     lc_str VARCHAR2(4000);
  8     lc_colval VARCHAR2(4000);
  9     c_dummy c_refcur;
 10     l number;
 11  BEGIN
 12     OPEN c_dummy FOR p_slct;
 13     LOOP
 14       FETCH c_dummy INTO lc_colval;
 15       EXIT WHEN c_dummy%NOTFOUND;
 16       lc_str := lc_str || p_dlmtr || lc_colval;
 17     END LOOP;
 18     CLOSE c_dummy;
 19     RETURN SUBSTR(lc_str,2);
 20  END;
 21  /

Function created.

SQL> SELECT ROWTOCOL('SELECT ENAME FROM EMP') FROM DUAL;

ROWTOCOL('SELECTENAMEFROMEMP')
------------------------------------------------------------------
SMITE,ALLEN,WARD,JONES,MARTIN,BLAKE,SCOTT,TURNER,ADAMS,JAMES,FORD

SQL>

grava arquivo

PROCEDURE GRAVA_ARQUIVO (
                             vPath        IN Varchar2:='/utl_file/log'
                           , vNomeArquivo IN Varchar2:= null
                           , vConteudo    IN Clob:='comteudo'      
             )
is
      arquivo_saida     UTL_FILE.FILE_TYPE;
      vsqlerrm      Varchar2(1024):=null ;
      vNomeArquivo2 Varchar2(255):='';
Begin
     DBMS_APPLICATION_INFO.set_client_info('ADM UTIL GRAVA_ARQUIVO');
    
     iValor := iValor + 1;
    
     vNomeArquivo2 := vNomeArquivo;
     If (vNomeArquivo2 is null) Then 
     vNomeArquivo2 := TO_CHAR(sysdate,'YYYYMMDD_hh24_mi_ss')||'_'||to_char(iValor)||'.html';
     End If;                    
      
     arquivo_saida := Utl_File.FOpen(vPath, vNomeArquivo2, 'w');
     Utl_File.Put (arquivo_saida, vConteudo );
     Utl_File.FClose(arquivo_saida);
     
EXCEPTION
      WHEN UTL_FILE.INVALID_OPERATION THEN
               Dbms_Output.Put_Line('Operação inválida no arquivo.'||Sqlerrm);
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.WRITE_ERROR THEN
               Dbms_Output.Put_Line('Erro de gravação no arquivo.'||Sqlerrm);
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.INVALID_PATH THEN
               Dbms_Output.Put_Line('Diretório inválido.'||Sqlerrm);
               UTL_File.Fclose(arquivo_saida);
      WHEN UTL_FILE.INVALID_MODE THEN
               Dbms_Output.Put_Line('Modo de acesso inválido.'||Sqlerrm);
               UTL_File.Fclose(arquivo_saida);
      WHEN Others THEN
               Dbms_Output.Put_Line('Problemas na geração do arquivo.'||Sqlerrm);
               UTL_File.Fclose(arquivo_saida);
end GRAVA_ARQUIVO;

Send mail

PROCEDURE SEND_MAILc ( mail_from IN VARCHAR2
                                      , mail_to IN VARCHAR2
                                      , mail_subject IN VARCHAR2
                                      , mail_body IN clob
                                      , mail_smtp IN VARCHAR2
                                      , mail_user IN VARCHAR2
                                      , mail_user_pwd IN VARCHAR2)
is
    conn utl_smtp.connection;
    vMail_Current Varchar2(512);
    vMail         Varchar2(512);
    mail_date varchar2(20);
    mail_body2 clob;

BEGIN
     DBMS_APPLICATION_INFO.set_client_info('ADM UTIL SEND_MAILc');


    mail_body2 := mail_body;
   
    conn := utl_smtp.open_connection(mail_smtp,25);
    utl_smtp.helo(conn,mail_smtp );
    SELECT TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )  INTO mail_date FROM dual;


    /*** Autenticação do SMTP ***/
    IF LENGTH((trim(MAIL_USER))) > 0 THEN
       utl_smtp.command(conn, 'AUTH LOGIN');
       utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((MAIL_USER)))));
       utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((MAIL_USER_PWD)))));
    END IF;
    utl_smtp.mail(conn,mail_from);

    vMail:= mail_to;
    while INSTR(vMail,';') > 0
    loop
       vMail_Current := substr(vMail,1,INSTR(vMail,';')-1);
       utl_smtp.rcpt(conn,vMail_Current);
       vMail := substr(vMail,INSTR(vMail,';')+1);
    end loop;
    utl_smtp.rcpt(conn,vMail);

    utl_smtp.open_data (conn);
    utl_smtp.write_data(conn,'From: '    ||mail_from   ||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'To: '      ||mail_to     ||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Subject: ' ||mail_subject||' Data: '||mail_date||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Content-Type:              Text/Html;'||utl_tcp.CRLF);
    utl_smtp.write_data(conn,utl_tcp.CRLF   );
       
    declare       
          vStart number := 1;
          vLength number := 3999; -- What ever size to split the CLOB into     
    begin     
          -- If the Body of the message is too large break up inserting into segments
          if length(mail_body2) > vLength then
          -- Build message in segments
            loop
       
              if vStart + vLength <= length(mail_body2) + 1 then
              utl_smtp.write_data(conn , substr(mail_body2, vStart, vLength));
              end if;
     
              vStart := vStart + vLength;
              exit when vStart + vLength > length(mail_body2);
         
            end loop;
       
            utl_smtp.write_data(conn , substr(mail_body2, vStart, length(mail_body2) - vStart + 1));
         
          else
            utl_smtp.write_data(conn , mail_body2);
          end if;     
    end;   
   
    utl_smtp.write_data(conn,utl_tcp.CRLF);
    utl_smtp.close_data(conn);
    utl_smtp.quit      (conn);


EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(conn);

    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The quit call will raise an
              -- exception that we can ignore.

    END;
    raise_application_error(-20000,'Falha ao enviar o e-mail: ' || sqlerrm);

END SEND_MAILc;

kill

--kill
SELECT 'alter system kill session '''||to_char(t.SID)||','||to_char(t.SERIAL#)||''''||';' ||'/*'||t.USERNAME
||
  case
  when T.SECONDS_IN_WAIT > 96400 then
       ' Dias:'|| trunc( T.SECONDS_IN_WAIT /60/60/24)  
  when T.SECONDS_IN_WAIT > 3600 then
       ' Horas:'|| trunc( T.SECONDS_IN_WAIT /60/60)
  when T.SECONDS_IN_WAIT > 60 then
       ' Minutos:'|| trunc( T.SECONDS_IN_WAIT /60)
  else
       ' Seg:'||  T.SECONDS_IN_WAIT
  end
||' '||t.OSUSER
||' '||t.MACHINE
||' '||t.TERMINAL
||' '||t.USERNAME
||' '||t.PROGRAM
||' '||t.MODULE
||' '||t.CLIENT_INFO
||'*/' cmd
FROM v$session t
WHERE --t.USERNAME = 'PEDRO' AND
      T.STATUS = 'INACTIVE'
AND   T.SECONDS_IN_WAIT > 900
and t.USERNAME is not null
order by T.SECONDS_IN_WAIT desc;

uso do dbms_application_info

CREATE OR REPLACE Package pkg_teste
Is
     
       PROCEDURE pteste;

End pkg_teste;

CREATE OR REPLACE Package Body pkg_teste
Is
       Procedure pteste
       is
         vfano integer:= to_number(to_char(sysdate,'yyyy'));
         vi varchar2(90):= to_char( sysdate, 'dd/mm/yyyy hh24:mI:ss' );
         ic pls_integer;
       begin
      
           dbms_application_info.set_module( module_name => 'pkg_teste',action_name => 'pteste ini>'||vi);        
           -- dbms_application_info.set_action(action_name => '');
           -- dbms_application_info.set_client_info(client_info => '');  
           -- count(*)|| ' de '|| count(*)over() per                                             
           
           -- codigo ...

             
            vi := vi ||' fim> '||  to_char( sysdate, 'dd/mm/yyyy hh24:mI:ss' );
            dbms_application_info.set_action(action_name => 'fim'|| vi );
             
       Exception 
            When Others Then     
                 begin
                     rollback;
                     Raise_Application_Error (SQLCODE,'ERRO Others '||SQLERRM);
                 end;
       end pteste;

End pkg_teste;