/* @C:\WEB_411\SQL\END_CONV\ORACLE\0000_GERVIEO_SQLWEBTUNNING.SQL */

DROP TABLE SQLWEBTUNNING
/

CREATE OR REPLACE VIEW SQLWEBTUNNING (NRSID,
                                      DSREMOTEADDR,
                                      DSREMOTEHOST,
                                      DSREMOTEAPPL,
                                      DSSERVERRDM,
                                      DSSERVERDLL,
                                      DTSTARTDATE,
                                      NRAUDSID,
                                      DSREMOTEOPER,
                                      DSSERVMETHOD)
AS (SELECT 0,
           RPAD(' ',15),
           RPAD(' ',15),
           RPAD(' ',10),
           RPAD(' ',20),
           RPAD(' ',15),
           SYSDATE,
           0,
           RPAD(' ',12),
           RPAD(' ',30)
    FROM PARAMGERAL)
/


CREATE OR REPLACE TRIGGER TRG_INS_SQLWEBTUNNING
INSTEAD OF INSERT ON SQLWEBTUNNING
DECLARE
  IP_ADDR   VARCHAR2(4);
  DT_START  VARCHAR2(14);
  ST_START  VARCHAR2(7);
  DS_APPL   VARCHAR2(5);
BEGIN
  IP_ADDR := CHR(TO_NUMBER(SUBSTR(:NEW.DSREMOTEADDR,1,INSTR(:NEW.DSREMOTEADDR,'.')-1)));
  IP_ADDR := IP_ADDR||CHR(TO_NUMBER(SUBSTR(:NEW.DSREMOTEADDR,INSTR(:NEW.DSREMOTEADDR,'.')+1,INSTR(:NEW.DSREMOTEADDR,'.',1,2)-INSTR(:NEW.DSREMOTEADDR,'.')-1)));
  IP_ADDR := IP_ADDR||CHR(TO_NUMBER(SUBSTR(:NEW.DSREMOTEADDR,INSTR(:NEW.DSREMOTEADDR,'.',1,2)+1,INSTR(:NEW.DSREMOTEADDR,'.',1,3)-INSTR(:NEW.DSREMOTEADDR,'.',1,2)-1)));
  IP_ADDR := IP_ADDR||CHR(TO_NUMBER(SUBSTR(:NEW.DSREMOTEADDR,INSTR(:NEW.DSREMOTEADDR,'.',1,3)+1,15-INSTR(:NEW.DSREMOTEADDR,'.',1,3))));

  DT_START := TO_CHAR(:NEW.DTSTARTDATE,'YYYYMMDDHH24MISS');
  ST_START := CHR(TO_NUMBER(SUBSTR(DT_START,1,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,3,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,5,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,7,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,9,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,11,2)));
  ST_START := ST_START||CHR(TO_NUMBER(SUBSTR(DT_START,13,2)));

  DS_APPL := SUBSTR(SUBSTR(:NEW.DSREMOTEAPPL,1,INSTR(:NEW.DSREMOTEAPPL,'.')-1),1,5);

  DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>SUBSTR(:NEW.DSSERVERDLL, 1, 47), ACTION_NAME=>SUBSTR(:NEW.DSSERVMETHOD, 1, 31));
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SUBSTR(RPAD(:NEW.DSREMOTEOPER,12)||RPAD(:NEW.DSSERVERRDM, 20)||RPAD(:NEW.DSREMOTEHOST, 15)||RPAD(DS_APPL, 5)||RPAD(IP_ADDR,4)||RPAD(ST_START,7), 1, 63));
END;
/

CREATE OR REPLACE TRIGGER TRG_DEL_SQLWEBTUNNING
INSTEAD OF DELETE ON SQLWEBTUNNING
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE (MODULE_NAME=>'', ACTION_NAME=>'');
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('');
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SQLWEBTUNNING','5.01.007'); END;
/

