Email From PL/SQL (9i, 10g)

Email From PL/SQL In Oracle9i

The UTL_SMTP package was introduced in Oracle8i and can be used to send emails from PL/SQL. In it's simplest form a single string or variable can be sent as the message body using:

DECLARE

  l_mailhost    VARCHAR2(64) := 'mail.mycompany.com';

  l_from        VARCHAR2(64) := 'me@mycompany.com';

  l_to          VARCHAR2(64) := 'you@mycompany.com';

  l_mail_conn   UTL_SMTP.connection;

BEGIN

  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);

  UTL_SMTP.helo(l_mail_conn, l_mailhost);

  UTL_SMTP.mail(l_mail_conn, l_from);

  UTL_SMTP.rcpt(l_mail_conn, l_to);

  UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));

  UTL_SMTP.quit(l_mail_conn);

END;

/

Multi-line messages can be written by expanding the UTL_SMTP.DATA command as follows:

DECLARE

  l_mailhost    VARCHAR2(64) := 'mail.mycompany.com';

  l_from        VARCHAR2(64) := 'me@mycompany.com';

  l_subject     VARCHAR2(64) := 'Test Mail';

  l_to          VARCHAR2(64) := 'you@mycompany.com';

  l_mail_conn   UTL_SMTP.connection;

BEGIN

  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);

  UTL_SMTP.helo(l_mail_conn, l_mailhost);

  UTL_SMTP.mail(l_mail_conn, l_from);

  UTL_SMTP.rcpt(l_mail_conn, l_to);

  UTL_SMTP.open_data(l_mail_conn);

 

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

 

  FOR i IN 1 .. 10 LOOP

    UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));

  END LOOP;

 

  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);

END;

/

The UTL_SMTP package requires Jserver which can be installed by running the following scripts as SYS:

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql

Extract the directory path from a full file path

Question:  I have a string that contains the full path to a file. How can I extract the directory path from the string in Oracle?

For example, if I have a full file path as follows:

    'c:\windows\temp\example.xls'

I want to retrieve the following:

    'c:\windows\temp'


Answer:  You can create a custom function in Oracle that will retrieve the directory path from the string value.

The following function called get_dir_path will extract the directory path. It will work with both Windows and UNIX system file paths.

    CREATE or REPLACE function get_dir_path
       (p_path IN VARCHAR2)
    RETURN varchar2

    IS
         v_dir VARCHAR2(1500);

    BEGIN

        -- Parse string for UNIX system
        IF instr(p_path,'/') > 0 THEN
            v_dir := substr(p_path,1,(instr(p_path,'/',-1,1)-1));

        -- Parse string for Windows system
        ELSIF instr(p_path,'\') > 0 THEN
            v_dir := substr(p_path,1,(instr(p_path,'\',-1,1)-1));

        -- If no slashes were found, return the original string
        ELSE
            v_dir := p_path;
        END IF;

        RETURN v_dir;

    END;


Once the above function has been created, you can reference this function in your SQL statement. For example,

    SELECT get_dir_path('c:\windows\temp\example.xls')
    FROM dual;

This SQL statement would return 'c:\windows\temp'.