PROCEDURE to send email

This Procedure have two input parameters for email body.


CREATE OR REPLACE PROCEDURE "SEND_EMAIL"(
    p_message_body IN VARCHAR2,
    p_recipient1   IN VARCHAR2 DEFAULT 'saman@test.com',
    p_recipient2   IN VARCHAR2 DEFAULT 'kamal@test.com',
    p_sender       IN VARCHAR2 DEFAULT 'oracle_database@test.com',
    p_subject      IN VARCHAR2 DEFAULT 'PRODUCTION DATABASE EMAIL',
    p_cc           IN VARCHAR2 DEFAULT 'nimal@test.com',
    p_mailport     IN NUMBER DEFAULT 25,
    p_mailhost     IN VARCHAR2 DEFAULT 'localhost' )
IS
  l_mail_conn UTL_SMTP.connection;
  crlf      CHAR(2) := CHR(13)||CHR(10);
  l_message VARCHAR2(32767);
BEGIN
  l_message   := 'Date: '||TO_CHAR(sysdate,'dd Mon yy hh24:mi:ss')||crlf;
  l_message   := l_message||'From: '||p_sender||crlf;
  l_message   := l_message||'To: '||p_recipient1||';'||p_recipient2||';'||crlf;
  l_message   := l_message||'CC: '||p_cc||crlf;
  l_message   := l_message||'Subject: '||p_subject||crlf;
  l_message   := l_message||crlf||p_message_body;
  l_mail_conn := utl_smtp.open_connection(p_mailhost, p_mailport);
  utl_smtp.helo(l_mail_conn, p_mailhost);
  utl_smtp.mail(l_mail_conn, p_sender);
  utl_smtp.rcpt(l_mail_conn, p_recipient1);
  utl_smtp.rcpt(l_mail_conn, p_recipient2);
  utl_smtp.rcpt(l_mail_conn, p_recipient3);
  utl_smtp.rcpt(l_mail_conn, p_recipient4);
  utl_smtp.rcpt(l_mail_conn, p_cc);
  utl_smtp.data(l_mail_conn, l_message);
  utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
  raise_application_error( -20000,'Failed to send mail due to the following error: ' ||crlf|| sqlerrm);
END;

Rename Datafile Name / Change Datafile path ( Oracle)

Create Tablespace as EXAMPLE_DATA01

CREATE TABLESPACE EXAMPLE_DATA01 DATAFILE '/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 20m;

--Check datafiles current path
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/undotbs01.dbf
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf


ALTER TABLESPACE EXAMPLE_DATA01 OFFLINE NORMAL;

--From File system change copy datafile to new path
mv /u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf /u01/app/oracle/oradata/newdata/EXAMPLE_DATA01_01.dbf

ALTER TABLESPACE EXAMPLE_DATA01 RENAME DATAFILE '/u01/app/oracle/oradata/EXAMPLE_DATA01_01.dbf' TO '/u01/app/oracle/oradata/newdata/EXAMPLE_DATA01_01.dbf';

ALTER TABLESPACE EXAMPLE_DATA01 online;

How to Update One table from Another tables data which have join

We want to update First table TICKET_ID from second table TICKET_ID where TICKET_NAME = 'NEW',
we should have to update first table ( ID ) matching data to second table (U_ID).

CREATE TABLE TABLE1
(ID,F_NAME,L_NAME,TICKET_ID);

CREATE TABLE TABLE2
(T_ID,TICKET_ID,U_ID,TICKET_NAME);


SET SERVEROUTPUT ON
DECLARE
  CURSOR C1
  IS
    SELECT A.ID,
      A.TICKET_ID
    FROM TABLE2 A,
      WHERE A.TICKET_NAME = 'NEW';
COUNT_UP NUMBER;
BEGIN
  BEGIN
   COUNT_UP := 0;
    FOR C2 IN C1
    LOOP
      UPDATE TABLE1
      SET TICKET_ID   = C2.TICKET_ID
      WHERE ID = C2.U_ID;
      COUNT_UP := COUNT_UP + 1;
    END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  DBMS_OUTPUT.PUT_LINE('Number of Record Update : '||COUNT_UP);
END;