Database

hi there,

starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.

here is example of how to use UTL_MAIL to send emails from PL/SQL :

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'me@address.com',
            recipients => 'you@address.com',
               subject => 'Test Mail',
               message => 'Hello World',
             mime_type => 'text; charset=us-ascii');
END;

Note : for security purpose, UTL_MAIL is not enabled by default. so you should enable it by connecting to SYS user and executing the utlmail.sql and prvtmail.plb scripts in the $ORACLE_HOME/RDBMS/admin directory. In addition, you must configure an initialization parameter, SMTP_OUT_SERVER, to point to an outgoing SMTP server.
in addition, you must give EXECUTE permission to PUBLIC by run below script :

grant execute on UTL_MAIL to public

hope this helpful 🙂
share with others to stretch our experiences

This called correlated update you can find examples below

UPDATE TABLE(<SELECT STATEMENT>) <alias1>
SET <column_1> = (
  SELECT <column_1>
  FROM <table_2> <alias2>
  WHERE <alias2.table_name> = <alias1.table_name>);

in you sample :

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

alternative way you can do this

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

and now the final example is

UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);

The only way to achieve this is trying to declare that cursor in the package level then you can use it in any procedure or function in that package and the logic behind that is any cursor delcared in any function or procedure the scope for this cursor is only in that function below you can find sample example :

package body my_pkg is

   cursor test_cur is select * from table;

   procedure proc1 is 
   begin
      open test_cur;
      ...
      close test_cur;
   end proc1;

   procedure proc2 is 
   begin
      open test_cur;
      ...
      close test_cur;
   end proc2;
end;

Note: keep in mind in that example below if you open declared cursor in procedure 1 and you didn’t close it if you trying to open the same cursor in the procedure 2 you will get an exception raising