Sunday, June 19, 2011

Email in PL/SQL Oracle 10G

The UTL_MAIL package was introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP. In order to use Oracle UTL_MAIL package you need to set a new init.ora parameter "SMTP_OUT_SERVER", set to your outgoing mailserver.

rajesh@ORA10GR2> alter system set smtp_out_server = 'mailinbound.domain.com';

System altered.

Elapsed: 00:00:00.14

rajesh@ORA10GR2> alter system set utl_file_dir = 'FILE_DIR' scope=spfile;

System altered.

Elapsed: 00:00:00.14
rajesh@ORA10GR2>

rajesh@ORA10GR2> connect sys/***** as sysdba
Connected.
sys@ORA10GR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10GR2> startup;
ORACLE instance started.
Database mounted.
Database opened.
sys@ORA10GR2>
sys@ORA10GR2>
sys@ORA10GR2> show parameter utl_file_dir;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      FILE_DIR
sys@ORA10GR2>

You must set UTL_FILE_DIR to a directory, where the attachment files exists

sys@ORA10GR2>
sys@ORA10GR2> show parameter smtp_out_server;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
smtp_out_server                      string      mailinbound.domain.com
sys@ORA10GR2> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlmail.sql;

Package created.

Elapsed: 00:00:00.70

Synonym created.

Elapsed: 00:00:00.10
sys@ORA10GR2>
sys@ORA10GR2> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtmail.plb;

Package body created.

Elapsed: 00:00:00.45
No errors.
sys@ORA10GR2>
sys@ORA10GR2> grant execute on utl_mail to rajesh,scott;

Grant succeeded.

Elapsed: 00:00:00.04
sys@ORA10GR2>
sys@ORA10GR2> connect scott/tiger
Connected.
scott@ORA10GR2>
scott@ORA10GR2> begin
  2     utl_mail.SEND_ATTACH_RAW(
  3     sender=>'rajeshwaran_jeyabal@domain.com',
  4     recipients=>'rajeshwaran_jeyabal@domain.com',
  5     subject=>'Hello World',
  6     message=>'Hello World',
  7     attachment =>utl_raw.cast_to_raw('PDF_Contents'),
  8     att_filename=>'TEST_PDF.pdf');
  9
 10     utl_mail.SEND_ATTACH_VARCHAR2(
 11     sender=>'rajeshwaran_jeyabal@domain.com',
 12     recipients=>'rajeshwaran_jeyabal@domain.com',
 13     subject=>'Hello World',
 14     message=>'Hello World',
 15     attachment =>'Text_Contents',
 16     att_filename=>'log.txt');
 17  end;
 18  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.64
scott@ORA10GR2>

Oracle 10g has added over fifty new PL/SQL packages and enhanced many of the existing packages, thus expanding the Oracle DBA's toolkit once again !
Posted by at
Labels:

7 comments:

  1. Ramesh TogaraAugust 1, 2011 at 7:40 AM

    Can you please give me the clarification on the below
    "utl_file_dir = 'FILE_DIR' "
    here we need to specify the path right?

    ReplyDelete
  2. RAJESHAugust 2, 2011 at 8:46 PM

    @Ramesh:

    You can do it either way.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams226.htm#CHDJJDJH


    rajesh@ORA10GR2> show parameter utl_file_dir;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string FILE_DIR
    rajesh@ORA10GR2>
    rajesh@ORA10GR2> SELECT directory_name, directory_path
    2 FROM dba_directories
    3 WHERE directory_name ='FILE_DIR'
    4 /

    DIRECTORY_NAME DIRECTORY_PATH
    -------------------- --------------------
    FILE_DIR C:\TRASH

    Elapsed: 00:00:00.03
    rajesh@ORA10GR2>
    rajesh@ORA10GR2>
    rajesh@ORA10GR2>
    rajesh@ORA10GR2> alter system set utl_file_dir = 'C:\TRASH' scope=spfile;

    System altered.

    Elapsed: 00:00:00.14
    rajesh@ORA10GR2>
    rajesh@ORA10GR2> connect sys/****** as sysdba
    Connected.
    sys@ORA10GR2>
    sys@ORA10GR2> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    sys@ORA10GR2>
    sys@ORA10GR2> startup;
    ORACLE instance started.

    Total System Global Area 612368384 bytes
    Fixed Size 1292036 bytes
    Variable Size 192940284 bytes
    Database Buffers 411041792 bytes
    Redo Buffers 7094272 bytes
    Database mounted.
    Database opened.
    sys@ORA10GR2>
    sys@ORA10GR2> connect rajesh/*****
    Connected.
    rajesh@ORA10GR2>
    rajesh@ORA10GR2> show parameter utl_file_dir;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string C:\TRASH
    rajesh@ORA10GR2>
    rajesh@ORA10GR2> begin
    2 utl_mail.SEND_ATTACH_VARCHAR2(
    3 sender=>'rajeshwaran_jeyabal@domain.com',
    4 recipients=>'rajeshwaran_jeyabal@domain.com',
    5 subject=>'Hello World',
    6 message=>'Hello World',
    7 attachment =>'Text_Contents',
    8 att_filename=>'log.txt');
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:01.20
    rajesh@ORA10GR2>

    ReplyDelete
  3. rajaSeptember 14, 2011 at 5:08 AM

    hi rajesh

    can you pls brief me with an example of inline view
    in a single query i saw 10 from clauses actually on what basis and when we need to write such kind of queries if u would like to send the answer pls send to my mail id rajkumarguntu25@gmail.com

    ReplyDelete
  4. RAJESHSeptember 14, 2011 at 7:21 AM

    @raja:

    Post your queries along with Explain plan & statistics. lets see what needs to be done further.

    ReplyDelete
  5. rajaSeptember 25, 2011 at 12:17 PM

    hi rajesh


    rajkumar .guntu asked you about inline view query execution process here i am posting the query


    "SELECT rownum srno
    ,decode(rn,1,bm) AS "Manager Name"
    ,AID
    ,fls AS "FLS Name"
    ,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31
    ,month

    FROM (SELECT bm
    ,ROW_NUMBER () OVER (PARTITION BY bm ORDER BY fls,month) RN
    ,fls
    ,month
    ,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31
    ,AID

    FROM (SELECT DISTINCT func_get_name(tmr.agent_id) AS fls
    ,decode(fun_report_to(tmr.agent_id),'00008320','self',func_get_name(fun_report_to(tmr.agent_id)))bm
    ,fun_report_to(tmr.agent_id),tmr.agent_id,tmr.month,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31,tmr.agent_id as AID
    FROM ams_attd_huddle1 tmr,ams_agent_mASter aam
    WHERE tmr.agent_id=aam.agent_id AND AMS_PKG_HUDDLE_TEAM_DUMMY.FUNC_GET_DESIG(tmr.agent_id)= aam.designation
    AND tmr.agent_id IN ((select AGENT_ID from ams_agent_master
    where reporting_to in(select agent_id from ams_agent_master where reporting_to='00008320')
    and active='A'
    and designation in ('AZ','BD','BDM','SD','SDM','TB','RS','SM','RA','RJ','AX','MU','SQ','BO')
    )
    union all
    (SELECT AGENT_ID
    FROM ams_agent_master
    WHERE reporting_to = '00008320'
    and Active='A'
    and designation in('AZ','BD','BDM','SD','SDM','TB')
    ))
    and tmr.month in('04','05')
    )
    )
    order by srno"

    ReplyDelete
  6. RAJESHSeptember 25, 2011 at 1:12 PM

    @Raja:

    I am not sure about your data models and their relations.

    SELECT AGENT_ID
    FROM ams_agent_master
    WHERE reporting_to IN
    (SELECT agent_id
    FROM ams_agent_master
    WHERE reporting_to='00008320'
    )
    AND active ='A'
    AND designation IN ('AZ','BD','BDM','SD','SDM','TB','RS','SM','RA','RJ','AX','MU','SQ','BO')
    )

    1) How big is the table ams_agent_master? But looking at the above query. you have a kind of Hierarchial relation in your queries, try using CONNECT BY feature in oracle, which will reduce two pass to table ams_agent_master

    ReplyDelete
  7. UnknownAugust 26, 2013 at 1:10 AM

    Hi, Nice description about Email in PL/SQL.Thanks, its really helped me......

    -Aparna
    Theosoft

    ReplyDelete

Subscribe to: Post Comments (Atom)