SQL Plus script to disable/enable all triggers an Oracle database

Below is an SQL Plus script to disable/enable all triggers for the current user in an Oracle database.  This is useful when importing data in an oracle database and you do not want triggers firing.  Substitute "DISABLE" below with "ENABLE" to enable back triggers after data import.

begin
  for c in (select * from user_triggers a where a.trigger_name not like '%$xd' )
  loop
      execute immediate('alter trigger '||c.trigger_name||' DISABLE');
  end loop;
end;

Scripts to disable/enable all constraints in an Oracle database

Use the scripts below to enable/disable constraints for an oracle user.  You can create them as procedures in your oracle schema, or simply execute them in sqlplus without the create procedure parts.

---

create or replace procedure sp_ddl_cons_disable as
begin
for c in (select 'ALTER TABLE '|| a.table_name ||' DISABLE constraint ' ||  a.constraint_name as command
  from user_constraints a
 where a.constraint_type in ('R'))
 loop
       ---dbms_output.put_line(c.command);
       EXECUTE IMMEDIATE (c.command);
 end loop;

end;

---

create or replace procedure sp_ddl_cons_enable as
begin
for c in (select 'ALTER TABLE '|| a.table_name ||' ENABLE constraint ' ||  a.constraint_name as command
  from user_constraints a
 where a.constraint_type in ('R'))
 loop
       ---dbms_output.put_line(c.command);
       EXECUTE IMMEDIATE (c.command);
 end loop;

end;

Sending UTF-8 encoded HTML Email using UTL_SMTP

The PL/SQL procedure below can send html email using the UTL_SMTP Oracle built in package. It handles utf-8 strings in the subject, To/From Name fields and in the message itself.
It was tested on an Oracle 11g R2 database with UTF-8 NLS_CHARACTERSET set and an Exchange smtp server.

CREATE OR REPLACE PROCEDURE send_html_email(p_sender_name VARCHAR2, -- name of person sending email
                                            p_sender_mail VARCHAR2, -- email of person sending email
                                            p_recipient   VARCHAR2, -- recipient of contact email
                                            p_subject     VARCHAR2, -- subject of email
                                            p_message     VARCHAR2 -- message of email
                                            ) is

  conn UTL_SMTP.CONNECTION;

  v_smtp_hostname varchar(200) := 'smtp_server';
  v_smtp_uname    VARCHAR2(200) := 'smtp_server_uname';
  v_smtp_passwd   VARCHAR2(200) := 'smtp_server_password';
  v_sender        VARCHAR2(200) := null;
 
  CHAR_SET constant varchar(200) := 'Content-Type: text/html;charset=UTF-8' ||UTL_TCP.CRLF;
  MIME_VERSION constant varchar(200) := 'MIME-version: 1.0' || UTL_TCP.CRLF;
  CONT_ENCODING constant varchar(200) := 'Content-Transfer-Encoding: quoted-printable ' ||UTL_TCP.CRLF;

BEGIN

  if p_message is null then
    return;
  end if;

  conn := utl_smtp.open_connection(v_smtp_hostname, 25);

  if v_smtp_uname is not null then
    UTL_SMTP.ehlo(conn, v_smtp_hostname);
  
    UTL_SMTP.command(conn, 'AUTH LOGIN');
    UTL_SMTP.command(conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_uname))));
    UTL_SMTP.command(conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_passwd))));
  
  else
    UTL_SMTP.helo(conn, v_smtp_hostname);
  
  end if;

  UTL_SMTP.Helo(conn, v_smtp_hostname);
  UTL_SMTP.Mail(conn, p_sender_mail);
  UTL_SMTP.rcpt(conn, p_recipient);
  UTL_SMTP.OPEN_DATA(conn);

  UTL_SMTP.WRITE_DATA(conn, MIME_VERSION);
  UTL_SMTP.WRITE_DATA(conn, CHAR_SET);
  UTL_SMTP.WRITE_DATA(conn, CONT_ENCODING);

  UTL_SMTP.WRITE_DATA(conn,
                      'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss') ||
                      ' -0800 (GMT)' || UTL_TCP.CRLF);
  UTL_SMTP.write_raw_data(conn,
                          utl_raw.cast_to_raw('Subject:' || p_subject));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'Subject' field

  if p_sender_name is null then
    v_sender := p_sender_mail;
  else
    v_sender := '"' || p_sender_name || '" <' || p_sender_mail || '>';
  end if;
  UTL_SMTP.write_raw_data(conn, utl_raw.cast_to_raw('From:' || v_sender));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF); -- this crlf is for 'From' field
  UTL_SMTP.WRITE_DATA(conn, 'To: ' || p_recipient || UTL_TCP.CRLF);
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF);
  UTL_SMTP.WRITE_RAW_DATA(conn,
                          UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(p_message)));
  UTL_SMTP.WRITE_DATA(conn, UTL_TCP.CRLF);
  UTL_SMTP.CLOSE_DATA(conn);
  UTL_SMTP.QUIT(conn);
END;