E-Mail from Oracle Procedures...

UTL_SMTP is the package that can be utilized to send email from Oracle Procedures. Its nothing new as its been there since 8i!!!
Here is the simple code and an example how to use it:


CREATE OR REPLACE PROCEDURE send_mail
(p_to        IN VARCHAR2,
 p_from      IN VARCHAR2,
 p_message   IN VARCHAR2,
 p_subject   in VARCHAR2,
 p_smtp_host IN VARCHAR2,
 p_smtp_port IN NUMBER DEFAULT 25)
IS
  t_mail_conn   UTL_SMTP.connection;
BEGIN

  t_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(t_mail_conn, p_smtp_host);
  UTL_SMTP.mail(t_mail_conn, p_from);
  UTL_SMTP.rcpt(t_mail_conn, p_to);

  UTL_SMTP.open_data(t_mail_conn);
 
  UTL_SMTP.write_data(t_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(t_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(t_mail_conn);

  UTL_SMTP.quit(t_mail_conn);
 
END san_send_mail;
/

Example of using the above procedure to send an email:


BEGIN
  send_mail(p_to    => 'to.me@me.com',
            p_from      =>  'to.me@me.com',
            p_message   => 'This is a test message from send_mail procedure... ',
            p_subject   => 'Test MSG - for Email Notifications...',
            p_smtp_host => 'Your.SMTP.SERVER.Address.'); --check SMTP_OUT_SERVER parameter for this value
END;
/

Oracle BLOB to File Extract...

If you are using TOAD, then you don't need to look any further to export BLOB column data into a file.
Just write a simple select statement (select blob_column, reg_col as file_name from blob_table;) in Editor and execute the statement. Right Click in the Data Grid Once the records are fetched and chose option "Export Blobs" and follow the instructions you are good to go.

Well, depending on the resources available on your PC, TOAD might throw an error with Low Memory or other issues when the exporting BLOB entries are huge or number of records to fetch are more.

Here is the simple proc that can be utilized instead and store all these files on the database server and get'em later.
Make sure that you have write access to Oracle Directory that you are going to use.


CREATE OR REPLACE procedure blob2file
is
 t_blob   blob;
 vstart  number := 1;
 bytelen number := 32000;
 t_blob_len     number;
 my_vr   raw(32000);
 x       number;
 t_file_name varchar2(200);
 l_output utl_file.file_type;
begin

  for i in (
  select reg_col file_name,
         dbms_lob.getlength(blob_column) blob_len,
         blob_column blob_val
    from blob_table
  ) loop

  begin
      -- save blob length
      x := i.blob_len;
     
      -- define output directory
      l_output := utl_file.fopen('ORACLE_DIRECTORY_NAME_HERE', i.file_name, 'WB', 32760);

      -- if small enough for a single write
      if i.blob_len < 32760 then
        utl_file.put_raw(l_output,i.blob_val);
        utl_file.fflush(l_output);
      else -- write in pieces
        vstart := 1;
        while vstart < i.blob_len
        loop
          dbms_lob.read(i.blob_val,bytelen,vstart,my_vr);

          utl_file.put_raw(l_output,my_vr);
          utl_file.fflush(l_output);

          -- set the start position for the next cut
          vstart := vstart + bytelen;

          -- set the end position if less than 32000 bytes
          x := x - bytelen;
          if x < 32000 then
            bytelen := x;
          end if;
        end loop;
      end if;
      utl_file.fclose(l_output);
 
  end;
 
  end loop;

end blob2file;
/

Oracle Invisible Index...

Oracle 11g introduces a new feature for indexes, invisible indexes, that is useful in 
several different situations.  


An invisible index is an index that is maintained by the database but 
ignored by the optimizer unless explicitly specified.  


The invisible index is an alternative to dropping or making an index unusable.  


This feature is also functional when certain modules of an application 
require a specific index without affecting the rest of the application. 


One use of the invisible index feature is to test the removal of 
an index before dropping it. Prior to 11g, this was typically achieved by making 
an index unusable during a set period of time. During this observation period, 
the DBA would monitor the database performance to determine whether or not to drop the index.  
If performance was negatively affected, the index would need to be rebuilt before it could be used again.


Beginning with Oracle 11g, we have an option of making the index invisible 
as opposed to unusable during this observation period. If performance degradation is observed, 
the index can be made visible again without having to rebuild the index.  
This can minimize the period of performance degradation, while also preventing an 
expensive operation to rebuild or recreate an index.


To make an index invisible:


ALTER INDEX index_name INVISIBLE;


To make an index visible:


ALTER INDEX index_name VISIBLE;


Data Dictionary:
Query the column "VISIBILITY" from the data dictionary view dba_indexes 
to check the current status of an index. 


Another potential use for invisible indexes is in situations where specific applications 
require an index temporarily.  
An index can be created as invisible to allow specific SQL statements to use the index 
while leaving the rest of the database unaffected. 
Creating a visible index for this same purpose would cause the optimizer to consider 
the new index for all execution plans on that object. 


Create an invisible index on order_lines.attribute7:


create index claim_dtl_inv1 
    on claim_details(col2) 
INVISIBLE;


This one query can be modified to explicitly use the invisible index with a hint:


select /*+ INDEX (claim_details claim_dtl_inv1) */ 
       claim_no#, claim_amt 
  from claim_details
 where col2 = 'some_value_here';


If the application code cannot be modified, it is possible to instruct the optimizer to 
include invisible indexes at the session level:


alter session set optimizer_use_invisible_indexes = true;


NOTE: Keep in mind that rebuilding an invisible index will make it visible. 


Init Parameter:
By Default optimizer_use_invisible_indexesis set to FALSE and thus all INVISIBLE indexes are ignored by COB. 
then the CBO will see the index. By default, the parameter is set to FALSE so that the CBO ignores 
the invisible index when using execution plans. 


Invisible indexes are an attractive feature for the process of dropping an index. 
They are also useful when a specific application needs the benefit of a temporary 
index without impacting the database on a wider scale. Since the database must still 
maintain an invisible index for all DML operations, invisible indexes should not be 
used unless necessary. Though they should be removed once their purpose has been served, 
invisible indexes offer substantial advantages for short-term solutions.  


The beauty of using the invisible index feature for Oracle 11g is that it allows the developer 
and DBA staff to test performance for the index without the risk of dropping an index which can be 
time consuming and a potential risk to the production environment.