-- -------------------------------------------------------
-- Roqet Workstation_ip
-- collects IP address and sends email if changed
-- Workstation_ip sql script copyright 2005 roqet - http://www.roqet.org.
-- Workstation_ip can be distributed and modified under the terms of the
-- GNU General Public License: http://www.gnu.org/copyleft/gpl.html
--   ~~~
--   'l'
--    -
-- -------------------------------------------------------

PROMPT
PROMPT Roqet Workstation_ip;
PROMPT collects IP address and sends email if changed
PROMPT

PROMPT WARNING:
PROMPT This script will create a table, stored procedure and schedule
PROMPT a task; The table and stored procedure will be deleted if they
PROMPT already exist.
PROMPT This script requires that the ROQET environment scripts have
PROMPT been run, or it should be modified to use your own tablespaces
PROMPT etc.
PROMPT
PROMPT Hit Enter to Continue, ctrl-c + exit to Abort
PAUSE

SET VERIFY OFF
SET PAGESIZE 800
SET LINESIZE 120
SET FEEDBACK ON


PROMPT Drop and Create table IP_ADDRESS

DROP TABLE IP_ADDRESS
/

CREATE TABLE IP_ADDRESS (
IP VARCHAR(40)
)
TABLESPACE ROQDATA
/

COMMENT ON TABLE IP_ADDRESS IS 'contains latest ip address'
/

GRANT SELECT ON IP_ADDRESS TO RPD_R_READ
/

GRANT DELETE, INSERT, SELECT, UPDATE ON IP_ADDRESS TO RPD_R_WRITE
/

--
-- Insert blank record into table
--
INSERT INTO IP_ADDRESS (IP) VALUES('blank');
COMMIT;

--
-- Collect Information
--
PROMPT Collect Information:
ACCEPT smtp CHAR PROMPT 'Enter SMTP: '
ACCEPT wsname CHAR PROMPT 'Enter Workstation Name: '
ACCEPT email CHAR PROMPT 'Enter from email address: '
ACCEPT email2 CHAR PROMPT 'Enter to email address: '

PROMPT Creating stored procedure ROQ_P_WSPING
--
-- ROQ_P_WSPING procedure
---
CREATE OR REPLACE PROCEDURE ROQ_P_WSPING
  (
  workstationname IN VARCHAR2
  ) IS

  crlf VARCHAR2(2):= UTL_TCP.CRLF;
  connection utl_smtp.connection;
  mailhost VARCHAR2(30) := '&smtp';
  emailaddr VARCHAR2(40) := '&email';
  emailaddr2 VARCHAR2(40) := '&email2';
  hostaddress  VARCHAR2(20);
  oldip VARCHAR2(20);
  header VARCHAR2(1000);

BEGIN
  --
  -- Collect the IP Address
  --
  hostaddress := UTL_INADDR.GET_HOST_ADDRESS(workstationname);

  --
  -- check if the ip address has changed
  --
  SELECT NVL(IP,'nada') INTO oldip
  FROM IP_ADDRESS;

  IF hostaddress != oldip THEN
    BEGIN
    --
    -- Update table with new IP Address
    --
    DELETE FROM IP_ADDRESS;
    COMMIT;
    INSERT INTO IP_ADDRESS (IP) VALUES(hostaddress);
    COMMIT;

    --
    -- Start the connection.
    --
    connection := utl_smtp.open_connection(mailhost,25);

    header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
    'From: '||emailaddr||''||crlf||
    'Subject: '||'Workstation Ping'||crlf||
    'To: '||emailaddr2||crlf;

    --
    -- Handshake with the SMTP server
    --
    utl_smtp.helo(connection, mailhost);
    utl_smtp.mail(connection, emailaddr);
    utl_smtp.rcpt(connection, emailaddr2);
    utl_smtp.open_data(connection);
    --
    -- Write the header
    --
    utl_smtp.write_data(connection, header);
    --
    -- The crlf is required to distinguish that what comes next is not
    -- simply part of the header..
    --
    utl_smtp.write_data(connection, crlf ||
    'Workstation (' || workstationname || ') IP Address is: ' || hostaddress);
    utl_smtp.close_data(connection);
    utl_smtp.quit(connection);

    EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
        dbms_output.put_line(' Invalid Operation in SMTP transaction.');
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
        dbms_output.put_line(' Temporary problems with sending email - try again later.');
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
        dbms_output.put_line(' Errors in code for SMTP transaction.');
    END;
  END IF;
END;
/
SHOW ERRORS

PROMPT Submit hourly job
--
-- Submit hourly job
--
DECLARE
nJobNo NUMBER(3);
BEGIN
DBMS_JOB.SUBMIT
(nJobNo,
'BEGIN ROQ_P_WSPING(''&wsname''); END;',
TRUNC((SYSDATE)+6/24),
'TRUNC(SYSDATE+1/24,''HH'')' ,
TRUE
);
COMMIT;
END;
/

SET VERIFY ON