Sending Email using Oracle Database Script

Need to execute the two scripts utlmail.sql and prvtmail.mlb from $ORACLE_HOME/rdbms/admin

Set the parameter SMTP_OUT_SERVER with smtp server IP 

Create below procedure
create or replace
PROCEDURE SENDEMAIL
(
  P_EMail IN VARCHAR2 
) AS
BEGIN
  execute immediate 'ALTER SESSION SET smtp_out_server = ''101.291.227.65''';
  UTL_MAIL.SEND(SENDER => 'dhanumjaya.mvs@thomsonreuters.com',
            recipients =>  P_EMail ,--'dhanumjaya.mvs@thomsonreuters.com',
               SUBJECT => 'Test Mail',
               message => 'Oracle Email',
             mime_type => 'text; charset=us-ascii');
END SENDEMAIL;

Execute the above procedure by providing the email
SQL> EXEC SENDEMAIL('abc@xyz.com');

How to print data from multiple rows as a single field

Using LISTAGG:
 
SELECT deptno,listagg(ename,',') within group (order by deptno) employees
from emp group by deptno;

Using COLLECT:

SELECT DEPTNO, TO_STRING(CAST( COLLECT(ENAME) AS VARCHAR2_NTT)) AS EMPLOYEES  FROM EMP GROUP BY DEPTNO