Thiết kế website giá rẻ

Question

I have this code that makes a csv with the alters in the dba_audit_object:

CREATE OR REPLACE PROCEDURE generate_audit_csv AS
    v_output_dir VARCHAR2(4000) := 'UTL_FILE_DIR'; -- Utilizamos el nombre del directorio lógico
    v_output_file VARCHAR2(4000) := 'action_found.csv';
    v_file_handle UTL_FILE.FILE_TYPE;
    v_execution_date DATE := SYSDATE;

    -- Defined registries
    TYPE audit_details_type IS RECORD (
        os_username   VARCHAR2(128),
        username      VARCHAR2(128),
        terminal      VARCHAR2(128),
        owner         VARCHAR2(128),
        obj_name      VARCHAR2(128),
        action_name   VARCHAR2(128),
        timestamp     DATE,
        count_actions NUMBER
    );

    TYPE audit_details_table IS TABLE OF audit_details_type;
    audit_details_list audit_details_table := audit_details_table();

BEGIN

    -- Open file
    v_file_handle := UTL_FILE.FOPEN(v_output_dir, v_output_file, 'W');

    -- Iteration DBLINK one by one
    FOR rec_link IN (SELECT db_link FROM user_db_links) LOOP
        DECLARE
            v_db_link_name VARCHAR2(128) := rec_link.db_link;
            v_link_open BOOLEAN := FALSE;
        BEGIN
            -- Consulta para obtener los detalles del audit a través de nuestros DBLINKs
            EXECUTE IMMEDIATE '
                SELECT OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME, MAX(TIMESTAMP), COUNT(*)
                FROM DBA_AUDIT_OBJECT@'||v_db_link_name||'
                WHERE TIMESTAMP > SYSDATE - 5
                AND (
                    (ACTION_NAME IN (''SELECT'', ''UPDATE'', ''DELETE'', ''INSERT'')
                     AND UPPER(OWNER) NOT IN (
                         ''ADMRMAN'',''ANONYMOUS'',''APEX_030200'',''APEX_PUBLIC_USER'',''APPQOSSYS'',
                         ''CQADMIN'',''CQUSER'',''CSW_USR_ROLE'',''CTXSYS'',''DBA'',
                         ''DBSNMP'',''DELETE_CATALOG_ROLE'',''DIP'',''DMSYS'',''EXFSYS'',''EXP_FULL_DATABASE'',''FLOWS_030000'',''FLOWS_FILES'',
                         ''GATHER_SYSTEM_STATISTICS'',''IMP_FULL_DATABASE'',''MDDATA'',''MDSYS'',''MGMT_VIEW'',''OAS_PUBLIC'',''OAS_PUBLIC'',
                         ''ODM'',''ODM_MTR'',''OLAPSYS'',''OLAP_XS_ADMIN'',''OLAP_USER'',''OLAP_DBA'',''ORACLE_OCM'',''ORAEG'',''ORDDATA'',''ORDPLUGINS'',
                         ''ORDSYS'',''OUTLN'',''OWBSYS'',''OWBSYS_AUDIT'',''PERFSTAT'',''PUBLIC'',''QS'',''QS_ADM'',''QS_CB'',''QS_CBADM'',''QS_CS'',''QS_ES'',
                         ''QS_OS'',''QS_WS'',''RHQADMIN'',''RHQADMIN'',''RMAN'',''SCOTT'',''SI_INFORMTN_SCHEMA'',''SPATIAL_CSW_ADMIN'',''SPATIAL_CSW_ADMIN_USR'',
                         ''SPATIAL_WFS_ADMIN'',''SPATIAL_WFS_ADMIN_USR'',''SPOTLIGHT'',''SQLTXPLAIN'',''SYS'',''SYSMAN'',''SYSTEM'',''TOAD'',''TSMSYS'',''WFS_USR_ROLE'',
                         ''WK_TEST'',''WKPROXY'',''WKSYS'',''WMSYS'',''XDB'',''XDBADMIN'',''XS$NULL'',''APEX_040200''
                     )
                     AND UPPER(TERMINAL) NOT IN (
                         ''PGRVMQROPJPAP03'', ''PGRQROPJPAPP02'', ''PGRQROPJPAPP03'', ''PGRQROPJPAPP05'',
                         ''PGRQROPJPAPP06'', ''PGRQROPJPAPP07'', ''PGRQROPJPAPP08'', ''PGRQROPJPAPP09'',
                         ''PGRQROPJPAPP10'', ''PGRQROPJPAPP11'', ''PGRQROPJPAPP12'', ''PGRQROPJPAPP13'',
                         ''PGRQROPJPAPP14'', ''PGRVMQROPJPAP01'', ''PGRVMQROPJPAP02'', ''A103IKP002.FGR.ORG.MX'',
                         ''PGRQROPJPAPP18'', ''PGRQROPJPAPP16'', ''PGRQROPJPAPP17'', ''PGRQROPJPAPP19'',
                         ''A813IGR02'', ''A103IKP003.FGR.ORG.MX'', ''PGRVMQROPJPAP02''
                     ))
                    OR 
                    (ACTION_NAME IN (''ALTER USER'', ''ALTER FUNCTION'', ''ALTER PROCEDURE'', ''ALTER DATABASE'', ''ALTER VIEW''))
                )
                GROUP BY OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME'
            BULK COLLECT INTO audit_details_list;
            v_link_open := TRUE;

            -- Write headers
            IF audit_details_list.COUNT > 0 THEN
                UTL_FILE.PUT_LINE(v_file_handle, 'DBLINK,DATE/TIME,Total actions audited');
                UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || TO_CHAR(v_execution_date, 'DD-MON-YYYY HH24:MI:SS') || ',' || audit_details_list.COUNT);

                -- Escribir los detalles del audit en el archivo CSV
                UTL_FILE.PUT_LINE(v_file_handle, 'DBLINK,OS USERNAME,USERNAME,TERMINAL,OWNER,OBJ NAME,ACTION NAME,TIMESTAMP,COUNT ACTIONS');
                FOR i IN 1..audit_details_list.COUNT LOOP
                    UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || audit_details_list(i).os_username || ',' || audit_details_list(i).username || ',' || audit_details_list(i).terminal || ',' ||
                                                      audit_details_list(i).owner || ',' || audit_details_list(i).obj_name || ',' || audit_details_list(i).action_name || ',' ||
                                                      TO_CHAR(audit_details_list(i).timestamp, 'YYYY-MM-DD HH24:MI:SS') || ',' || audit_details_list(i).count_actions);
                END LOOP;
            ELSE
                UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || 'No audit data found.');
            END IF;

            COMMIT; s

            -- Close DBLINK
            IF v_link_open THEN
                DBMS_SESSION.CLOSE_DATABASE_LINK(v_db_link_name);
                v_link_open := FALSE;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                IF v_link_open THEN
                    EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK(' || v_db_link_name || '); END;';
                    v_link_open := FALSE;
                END IF;
                DBMS_OUTPUT.PUT_LINE('Error processing DBLINK ' || v_db_link_name || ': ' || SQLERRM);
        END;
    END LOOP;

    -- Close Output CSV file
    UTL_FILE.FCLOSE(v_file_handle);
    DBMS_OUTPUT.PUT_LINE('CSV file created: ' || v_output_file);
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file_handle) THEN
            UTL_FILE.FCLOSE(v_file_handle);
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END generate_audit_csv;
/
SHOW ERRORS;

this works perfectly, now I am trying to send the csv via email.

this is my approach, it doesn’t work:

PROCEDURE generar_y_enviar_csv(
    v_from       IN VARCHAR2,
    v_to         IN VARCHAR2,
    v_asunto     IN VARCHAR2,
    v_firma      IN VARCHAR2,
    v_cc         IN VARCHAR2 DEFAULT '',
    v_filename    IN VARCHAR2 DEFAULT 'action_found.csv',
    v_directorio  IN VARCHAR2 DEFAULT 'UTL_FILE_DIR'
    ) IS
        vg_contenido CLOB := EMPTY_CLOB();
        file_handle  UTL_FILE.FILE_TYPE;
        buffer       VARCHAR2(32767);
        amount       INTEGER;
    BEGIN
        -- Abrir el archivo
        file_handle := UTL_FILE.FOPEN(v_directorio, v_filename, 'R');

        -- Leer el contenido del archivo en el cuerpo del correo
        LOOP
            UTL_FILE.GET_LINE(file_handle, buffer);
            amount := LENGTH(buffer);
            IF amount > 0 THEN
                IF vg_contenido IS NULL THEN
                    vg_contenido := buffer;
                ELSE
                    vg_contenido := vg_contenido || CHR(10) || buffer;
                END IF;
            END IF;
        END LOOP;

        -- Cerrar el archivo
        UTL_FILE.FCLOSE(file_handle);

        -- Enviar el correo electrónico con el archivo adjunto y el contenido en el cuerpo
        PG_ENVIO_MAIL.entrega(
            vg_from      => v_from,
            vg_to    => v_to,
            vg_asunto  => v_asunto,
            vg_cuerpo => vg_contenido,
            vg_cc      => v_cc,
            vg_filename => v_filename,
            vg_firma   => v_firma
        );
    EXCEPTION
        WHEN OTHERS THEN
            IF UTL_FILE.IS_OPEN(file_handle) THEN
                UTL_FILE.FCLOSE(file_handle);
            END IF;
            DBMS_OUTPUT.PUT_LINE('Error al procesar el archivo: ' || SQLERRM);
    END generar_y_enviar_csv;

END PG_GENERA_Y_ENVIA_CSV;
/

I have a package which I used, that sends mail using UTL_SMTP:

CREATE OR REPLACE PACKAGE UEITICDSCC_FRL_SS.PG_ENVIO_MAIL AS
   PROCEDURE send_header
           (conn IN OUT NOCOPY utl_smtp.connection, 
            name IN VARCHAR2, payload IN VARCHAR2);
   PROCEDURE send_subject (conn IN OUT NOCOPY utl_smtp.connection, 
                           name IN VARCHAR2, payload IN VARCHAR2);
   PROCEDURE mailit(
          vg_from in Varchar,
          vg_to IN VARCHAR2,
          vg_asunto IN VARCHAR2,
          vg_msg IN VARCHAR2,
          vg_cc IN varchar2 default '',
          vg_filename IN VARCHAR2, 
          vg_contenido IN CLOB);
   FUNCTION ConvierteAcentos (cadena1 IN VARCHAR2,tip IN NUMBER) RETURN VARCHAR2; 
   PROCEDURE entrega(vg_from   IN VARCHAR2,
                   vg_to     IN VARCHAR2,
                   vg_asunto IN VARCHAR2,
                   vg_cuerpo   IN CLOB,
                   vg_firma  IN VARCHAR2,
                   vg_cc   IN varchar2 default '',
                   vg_filename    IN varchar2 default '',
                   vg_contenido  IN CLOB default '');          
END PG_ENVIO_MAIL;
/
show errors;

 
CREATE OR REPLACE PACKAGE BODY UEITICDSCC_FRL_SS.PG_ENVIO_MAIL IS


     PROCEDURE send_header
           (conn IN OUT NOCOPY utl_smtp.connection,
            name IN VARCHAR2, payload IN VARCHAR2) IS
     BEGIN
         utl_smtp.write_data(
         conn,
         name || ': ' ||
         utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(payload))||
         ''||
         utl_tcp.crlf);
     END send_header;
     
     PROCEDURE send_subject
           (conn IN OUT NOCOPY utl_smtp.connection,
           name IN VARCHAR2, payload IN VARCHAR2) IS
     BEGIN
          utl_smtp.write_data(
          conn,
          --name || ': =?ISO-8859-1?B?' ||
          --payload ||'?='||utl_tcp.crlf);
          -- lineas comentada por VHO el 30/03/12, la sustituye la siguiente
          name || ':' || utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(payload)) || ' ' ||
          utl_tcp.crlf);

     END send_subject;
     
     PROCEDURE mailit(
          vg_from in Varchar,
          vg_to IN VARCHAR2,
          vg_asunto IN VARCHAR2,
          vg_msg IN VARCHAR2,
          vg_cc IN varchar2 default '',
          vg_filename  IN varchar2, 
          vg_contenido IN CLOB)
     AS
         conn utl_smtp.connection;
         vl_mailhost varchar2(1000) := 'webmail.pgr.gob.mx';
         vl_port number := 25;
         vl_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
         vl_step        PLS_INTEGER  := 24573;
         vl_clob CLOB := vg_contenido;
         v_len INTEGER;
         v_index INTEGER;
         v_chunk VARCHAR2(32000);

     BEGIN

    
            conn := utl_smtp.open_connection(vl_mailhost, vl_port);
            

         utl_smtp.helo(conn, vl_mailhost);
         utl_smtp.mail(conn, vg_from);
         utl_smtp.rcpt(conn, vg_to);
         IF vg_cc is not null THEN
            utl_smtp.rcpt(conn, vg_cc);
         END IF;
         utl_smtp.open_data(conn);
         send_header(conn, 'To', vg_to);
         send_header(conn, 'From', vg_from);
         send_subject(conn, 'Subject', vg_asunto);
         IF vg_cc is not null THEN
            send_header(conn, 'To', vg_cc);
         END IF;


           utl_smtp.write_data(conn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
           -- utl_smtp.write_data(conn, 'Content-Type: text/html; charset=UTF-8' || utl_tcp.CRLF);
           -- utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || utl_tcp.CRLF);       
           utl_smtp.write_data(conn, 'Content-Type: multipart/mixed; boundary="' || vl_boundary || '"' || utl_tcp.CRLF || utl_tcp.CRLF);


         IF vg_msg IS NOT NULL THEN
        utl_smtp.write_data(conn, '--' || vl_boundary || utl_tcp.CRLF);
        utl_smtp.write_data(conn, 'Content-Type: text/html; charset=UTF-8' || utl_tcp.CRLF || utl_tcp.CRLF);
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(vg_msg));
        utl_smtp.write_data(conn, utl_tcp.CRLF || utl_tcp.CRLF);
     END IF;

     IF vg_filename IS NOT NULL THEN
        utl_smtp.write_data(conn, '--' || vl_boundary || utl_tcp.crlf);
        utl_smtp.write_data(conn, 'Content-Type: text/plain;  charset="UTF-8" name="' || vg_filename || '"' || utl_tcp.CRLF);
        utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || utl_tcp.CRLF);
        utl_smtp.write_data(conn, 'Content-Disposition: attachment; filename="' || vg_filename || '"' || utl_tcp.CRLF || utl_tcp.CRLF);

        IF vg_contenido  IS NOT NULL THEN
              vl_clob := ConvierteAcentos(vg_contenido,2); 
          FOR i IN 0 .. TRUNC((DBMS_LOB.getlength( vl_clob) - 1 )/vl_step) LOOP
            utl_smtp.write_data(conn, DBMS_LOB.substr( vl_clob, vl_step, i * vl_step + 1));
          END LOOP;
        END IF; 
        utl_smtp.write_data(conn, utl_tcp.CRLF || utl_tcp.CRLF);
     END IF;

   IF vg_filename IS NOT NULL AND vg_contenido IS NOT NULL THEN
      utl_smtp.write_data(conn, '--' || vl_boundary || UTL_TCP.CRLF);
      utl_smtp.write_data(conn, 'Content-Type: text/plain; charset="UTF-8" name="' || vg_filename || '"' || UTL_TCP.CRLF);
      utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.CRLF);
      utl_smtp.write_data(conn, 'Content-Disposition: attachment; filename="' || vg_filename || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF);

      v_len := DBMS_LOB.getlength(vl_clob);

      WHILE v_index <= v_len LOOP
          v_chunk := DBMS_LOB.SUBSTR(vl_clob, 32000, v_index);
          utl_smtp.write_data(conn, v_chunk);
          v_index := v_index + 32000;
      END LOOP;

      utl_smtp.write_data(conn, UTL_TCP.CRLF || UTL_TCP.CRLF);
  END IF;    

       utl_smtp.write_data(conn, '--' || vl_boundary || '--' || utl_tcp.crlf);
        -- utl_smtp.write_data(conn, utl_tcp.crlf); 
        -- utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(vg_msg));
        -- utl_smtp.write_data(conn, utl_tcp.CRLF || 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 others then null;
        END;
        raise_application_error(-20000,'Failed to send mail due to the following error:'||sqlerrm);
      WHEN OTHERS THEN
        raise_application_error(-20000,'Failed to send mail due to the following error:'||sqlerrm);
     END mailit;

   -----------------
  FUNCTION ConvierteAcentos (cadena1 IN VARCHAR2, tip IN NUMBER) RETURN VARCHAR2 IS
   cadena    VARCHAR2(2000);
   i            number;
   liValorAscii number;
   letra        varchar2(50);
   resultado    varchar2(4000);
   ant          varchar2(50);
   post         varchar2(50);
   paso         varchar2(50);


   BEGIN
   resultado:='';
   cadena:=cadena1;
        
        FOR i IN 1 .. LENGTH(cadena) LOOP
          letra := SUBSTR(cadena, i, 1);
          liValorAscii := ASCII(SUBSTR(cadena, i, 1));
                   ant := ASCII(SUBSTR(cadena, i-1, 1));
                  post := ASCII(SUBSTR(cadena, i+1, 1));
               CASE 
                  WHEN liValorAscii in (181,193,230) then  IF(tip=1) THEN letra:=''||chr(38)||'Aacute;'; ELSE letra:=''||chr(38)||'A'; END IF;
                  WHEN liValorAscii in (160,225,255) then  IF(tip=1) THEN letra:=''||chr(38)||'aacute;'; ELSE letra:=''||chr(38)||'a'; END IF;
                  WHEN liValorAscii in (144)     then IF(tip=1) THEN letra:=''||chr(38)||'Eacute;'; ELSE letra:=''||chr(38)||'E'; END IF; 
                  WHEN liValorAscii in (130,254) then IF(tip=1) THEN letra:=''||chr(38)||'eacute;'; ELSE letra:=''||chr(38)||'e'; END IF; 
                  WHEN liValorAscii in (214,153) then IF(tip=1) THEN letra:=''||chr(38)||'Iacute;'; ELSE letra:=''||chr(38)||'I'; END IF;
                  WHEN liValorAscii in (161,173) then IF(tip=1) THEN letra:=''||chr(38)||'iacute;'; ELSE letra:=''||chr(38)||'i'; END IF;
                  WHEN liValorAscii in (224,133) then IF(tip=1) THEN letra:=''||chr(38)||'Oacute;'; ELSE letra:=''||chr(38)||'O'; END IF;
                  WHEN liValorAscii in (162,189) then IF(tip=1) THEN letra:=''||chr(38)||'oacute;'; ELSE letra:=''||chr(38)||'o'; END IF; 
                  WHEN liValorAscii in (233,130) then IF(tip=1) THEN letra:=''||chr(38)||'Uacute;'; ELSE letra:=''||chr(38)||'U'; END IF; 
                  WHEN liValorAscii in (163,156) then IF(tip=1) THEN letra:=''||chr(38)||'uacute;'; ELSE letra:=''||chr(38)||'u'; END IF; 
                  WHEN liValorAscii in (165,190) then IF(tip=1) THEN letra:=''||chr(38)||'Ntilde;'; ELSE letra:=''||chr(38)||'�'; END IF; 
                  WHEN liValorAscii in (164,207) then IF(tip=1) THEN letra:=''||chr(38)||'ntilde;'; ELSE letra:=''||chr(38)||'�'; END IF; 
                  WHEN liValorAscii in (233,154) then letra:=''||chr(38)||'Uuml;';
                  WHEN liValorAscii in (129)     then letra:=''||chr(38)||'Uuml;';
                  ELSE liValorAscii:=liValorAscii;
               END CASE;

          IF (liValorAscii not in (117,85)) THEN
            IF (ant=103) THEN
               IF (post=105 OR post=101) THEN letra:=''||chr(38)||'uuml;';
               END IF;
            IF (ant=71 OR ant=254) THEN
               IF (post=69 OR post=73) THEN letra:=''||chr(38)||'Uuml;';
               end if;
            END IF;
            END IF;
          end if;

          resultado:=resultado||letra;
        END LOOP;
    RETURN resultado;
   END;
   -----------------
 PROCEDURE entrega(vg_from   IN VARCHAR2,
                   vg_to     IN VARCHAR2,
                   vg_asunto IN VARCHAR2,
                   vg_cuerpo IN CLOB,
                   vg_firma  IN VARCHAR2,
                   vg_cc     IN varchar2 default '',
                   vg_filename    IN varchar2 default '',
                   vg_contenido  IN CLOB default '' 
                   ) AS
  BEGIN
      DECLARE
       unicodedb       number;
       vl_subject         varchar2(2000);
       final_hex_train varchar2(2000);
       vl_html         CLOB;
       vl_firma        VARCHAR2(2000);
       vl_cuerpo       VARCHAR2(2000);
       vl_contenido    CLOB;

    BEGIN
          -- determine if connected to Unicode DB (return 1)
          -- or not (return 0)
          select
            decode(value,
              -- Unicode Charactersets
              'AL24UTFFSS',1,
              'UTFE',1,
              'UTF8',1,
              'AL32UTF8',1,
              'AL16UTF16',1,
              -- NON-Unicode characterset
             0 )
          into
            unicodedb
          from
            nls_database_parameters
          where
            upper(parameter)='NLS_CHARACTERSET';

       

          vl_cuerpo:= ConvierteAcentos(vg_cuerpo,1); 
          vl_cuerpo := replace(vl_cuerpo, '+', '<br>');

     
          vl_firma := ConvierteAcentos(vg_firma,1);
          vl_firma := replace(vl_firma, '+' , '<br>');          

          
          vl_html := '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"/>';
          vl_html := vl_html || '</head><body><i> ' || vl_cuerpo || '<i><br><i><i><br><i><i><br><i>Atentamente <i><br><i>' || vl_firma || '<i><br></body></html>';


          vl_subject := ConvierteAcentos(vg_asunto,2);

          mailit(vg_from,vg_to,vl_subject,vl_html,vg_cc,vg_filename,vg_contenido);
     END;
   END entrega;

END PG_ENVIO_MAIL;
/
show errors

What am I doing wrong?

Do I need to modify the package?

or Do I need another approach?

Thiết kế website giá rẻ

Danh mục

Need to send csv stored in UTL_FILE directory using UTL_SMTP