1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ip address and hostname of pc

Discussion in 'SQL PL/SQL' started by rezacolomb, Nov 24, 2016.

  1. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Hello,
    I would like to know how to return an IP Adress and Hostname of a PC connected on server with Oracle data base. I tried to use
    Code (SQL):
    SYS_CONTEXT
    and
    Code (SQL):
    ult_inaddr
    but these retun successively a server IP and hostname when I need PC IP and Hostname. These functions are useful when users are created in v$session but my users are created in personalized table. I would like to use these information in a Trigger.
    Someone can help me please!
    Thank u.
    Here is my Trigger :

    Code (SQL):
    CREATE OR REPLACE TRIGGER KAZOZA.CUACCTYY_REC_AUDIT_TRIG
    BEFORE DELETE OR UPDATE
    ON KAZOZA.CUACCTYY_REC  REFERENCING NEW AS NEW OLD AS OLD   --(KAZOZA c'est le Schema)
    FOR EACH ROW
    DECLARE
        TYPE aud$ IS RECORD (tab$ VARCHAR2(30), col$  VARCHAR2(30),  col_old VARCHAR2(500), col_new  VARCHAR2(500));
        TYPE REC_AUD  IS TABLE OF aud$ INDEX BY BINARY_INTEGER;
        rec$   REC_AUD;
        actionFlag      VARCHAR2(1);
    BEGIN

       IF UPDATING THEN
          actionFlag:='M';
       ELSIF DELETING THEN
          actionFlag:='S';
       END IF;
    rec$(1).tab$:= 'CUACCTYY_REC';
    rec$(1).col$:= 'CUA_ID';
    rec$(1).col_old:= TO_CHAR(:OLD.CUA_ID);
    rec$(1).col_new:= TO_CHAR(:NEW.CUA_ID);
    rec$(2).tab$:= 'CUACCTYY_REC';
    rec$(2).col$:= 'CUA_ACC_INT';
    rec$(2).col_old:= TO_CHAR(:OLD.CUA_ACC_INT);
    rec$(2).col_new:= TO_CHAR(:NEW.CUA_ACC_INT);
    rec$(3).tab$:= 'CUACCTYY_REC';
    rec$(3).col$:= 'CUA_ACC_CUST';
    rec$(3).col_old:= TO_CHAR(:OLD.CUA_ACC_CUST);
    rec$(3).col_new:= TO_CHAR(:NEW.CUA_ACC_CUST);
    rec$(4).tab$:= 'CUACCTYY_REC';
    rec$(4).col$:= 'CUA_MATURITY_DATE';
    rec$(4).col_old:= TO_CHAR(:OLD.CUA_MATURITY_DATE);
    rec$(4).col_new:= TO_CHAR(:NEW.CUA_MATURITY_DATE);
    rec$(5).tab$:= 'CUACCTYY_REC';
    rec$(5).col$:= 'CUA_TYPE';
    rec$(5).col_old:= TO_CHAR(:OLD.CUA_TYPE);
    rec$(5).col_new:= TO_CHAR(:NEW.CUA_TYPE);
    rec$(6).tab$:= 'CUACCTYY_REC';
    rec$(6).col$:= 'CUA_RUBRIQUE';
    rec$(6).col_old:= TO_CHAR(:OLD.CUA_RUBRIQUE);
    rec$(6).col_new:= TO_CHAR(:NEW.CUA_RUBRIQUE);
    rec$(7).tab$:= 'CUACCTYY_REC';
    rec$(7).col$:= 'CUA_CAT_DEP';
    rec$(7).col_old:= TO_CHAR(:OLD.CUA_CAT_DEP);
    rec$(7).col_new:= TO_CHAR(:NEW.CUA_CAT_DEP);
    rec$(8).tab$:= 'CUACCTYY_REC';
    rec$(8).col$:= 'CUA_STATUS';
    rec$(8).col_old:= TO_CHAR(:OLD.CUA_STATUS);
    rec$(8).col_new:= TO_CHAR(:NEW.CUA_STATUS);
    rec$(9).tab$:= 'CUACCTYY_REC';
    rec$(9).col$:= 'CUA_MOTIF';
    rec$(9).col_old:= TO_CHAR(:OLD.CUA_MOTIF);
    rec$(9).col_new:= TO_CHAR(:NEW.CUA_MOTIF);
    rec$(10).tab$:= 'CUACCTYY_REC';
    rec$(10).col$:= 'CUA_EXT_FREQ';
    rec$(10).col_old:= TO_CHAR(:OLD.CUA_EXT_FREQ);
    rec$(10).col_new:= TO_CHAR(:NEW.CUA_EXT_FREQ);
    rec$(11).tab$:= 'CUACCTYY_REC';
    rec$(11).col$:= 'CUA_EXT_NUM';
    rec$(11).col_old:= TO_CHAR(:OLD.CUA_EXT_NUM);
    rec$(11).col_new:= TO_CHAR(:NEW.CUA_EXT_NUM);
    rec$(12).tab$:= 'CUACCTYY_REC';
    rec$(12).col$:= 'CUA_EXT_NO';
    rec$(12).col_old:= TO_CHAR(:OLD.CUA_EXT_NO);
    rec$(12).col_new:= TO_CHAR(:NEW.CUA_EXT_NO);
    rec$(13).tab$:= 'CUACCTYY_REC';
    rec$(13).col$:= 'CUA_STMT_FREQ';
    rec$(13).col_old:= TO_CHAR(:OLD.CUA_STMT_FREQ);
    rec$(13).col_new:= TO_CHAR(:NEW.CUA_STMT_FREQ);
    rec$(14).tab$:= 'CUACCTYY_REC';
    rec$(14).col$:= 'CUA_OPEN_DATE';
    rec$(14).col_old:= TO_CHAR(:OLD.CUA_OPEN_DATE);
    rec$(14).col_new:= TO_CHAR(:NEW.CUA_OPEN_DATE);
    rec$(15).tab$:= 'CUACCTYY_REC';
    rec$(15).col$:= 'CUA_LTR_DATE';
    rec$(15).col_old:= TO_CHAR(:OLD.CUA_LTR_DATE);
    rec$(15).col_new:= TO_CHAR(:NEW.CUA_LTR_DATE);
    rec$(16).tab$:= 'CUACCTYY_REC';
    rec$(16).col$:= 'CUA_LEXT_DATE';
    rec$(16).col_old:= TO_CHAR(:OLD.CUA_LEXT_DATE);
    rec$(16).col_new:= TO_CHAR(:NEW.CUA_LEXT_DATE);
    rec$(17).tab$:= 'CUACCTYY_REC';
    rec$(17).col$:= 'CUA_LSTMT_DATE';
    rec$(17).col_old:= TO_CHAR(:OLD.CUA_LSTMT_DATE);
    rec$(17).col_new:= TO_CHAR(:NEW.CUA_LSTMT_DATE);
    rec$(18).tab$:= 'CUACCTYY_REC';
    rec$(18).col$:= 'CUA_LINT_DATE';
    rec$(18).col_old:= TO_CHAR(:OLD.CUA_LINT_DATE);
    rec$(18).col_new:= TO_CHAR(:NEW.CUA_LINT_DATE);
    rec$(19).tab$:= 'CUACCTYY_REC';
    rec$(19).col$:= 'CUA_LOW_VALUE_DATE';
    rec$(19).col_old:= TO_CHAR(:OLD.CUA_LOW_VALUE_DATE);
    rec$(19).col_new:= TO_CHAR(:NEW.CUA_LOW_VALUE_DATE);
    rec$(20).tab$:= 'CUACCTYY_REC';
    rec$(20).col$:= 'CUA_RECOND';
    rec$(20).col_old:= TO_CHAR(:OLD.CUA_RECOND);
    rec$(20).col_new:= TO_CHAR(:NEW.CUA_RECOND);
    rec$(21).tab$:= 'CUACCTYY_REC';
    rec$(21).col$:= 'CUA_CHEQUIER';
    rec$(21).col_old:= TO_CHAR(:OLD.CUA_CHEQUIER);
    rec$(21).col_new:= TO_CHAR(:NEW.CUA_CHEQUIER);
    rec$(22).tab$:= 'CUACCTYY_REC';
    rec$(22).col$:= 'CUA_ORDER';
    rec$(22).col_old:= TO_CHAR(:OLD.CUA_ORDER);
    rec$(22).col_new:= TO_CHAR(:NEW.CUA_ORDER);
    rec$(23).tab$:= 'CUACCTYY_REC';
    rec$(23).col$:= 'CUA_RECEIPT';
    rec$(23).col_old:= TO_CHAR(:OLD.CUA_RECEIPT);
    rec$(23).col_new:= TO_CHAR(:NEW.CUA_RECEIPT);
    rec$(24).tab$:= 'CUACCTYY_REC';
    rec$(24).col$:= 'CUA_OFF_CODE';
    rec$(24).col_old:= TO_CHAR(:OLD.CUA_OFF_CODE);
    rec$(24).col_new:= TO_CHAR(:NEW.CUA_OFF_CODE);
    rec$(25).tab$:= 'CUACCTYY_REC';
    rec$(25).col$:= 'CUA_TAXE_CODE';
    rec$(25).col_old:= TO_CHAR(:OLD.CUA_TAXE_CODE);
    rec$(25).col_new:= TO_CHAR(:NEW.CUA_TAXE_CODE);
    rec$(26).tab$:= 'CUACCTYY_REC';
    rec$(26).col$:= 'CUA_INT_PAID';
    rec$(26).col_old:= TO_CHAR(:OLD.CUA_INT_PAID);
    rec$(26).col_new:= TO_CHAR(:NEW.CUA_INT_PAID);
    rec$(27).tab$:= 'CUACCTYY_REC';
    rec$(27).col$:= 'CUA_INDISP';
    rec$(27).col_old:= TO_CHAR(:OLD.CUA_INDISP);
    rec$(27).col_new:= TO_CHAR(:NEW.CUA_INDISP);
    rec$(28).tab$:= 'CUACCTYY_REC';
    rec$(28).col$:= 'CUA_DEBTOR';
    rec$(28).col_old:= TO_CHAR(:OLD.CUA_DEBTOR);
    rec$(28).col_new:= TO_CHAR(:NEW.CUA_DEBTOR);
    rec$(29).tab$:= 'CUACCTYY_REC';
    rec$(29).col$:= 'CUA_EMPL';
    rec$(29).col_old:= TO_CHAR(:OLD.CUA_EMPL);
    rec$(29).col_new:= TO_CHAR(:NEW.CUA_EMPL);
    rec$(30).tab$:= 'CUACCTYY_REC';
    rec$(30).col$:= 'CUA_MAIL';
    rec$(30).col_old:= TO_CHAR(:OLD.CUA_MAIL);
    rec$(30).col_new:= TO_CHAR(:NEW.CUA_MAIL);
    rec$(31).tab$:= 'CUACCTYY_REC';
    rec$(31).col$:= 'CUA_MVT';
    rec$(31).col_old:= TO_CHAR(:OLD.CUA_MVT);
    rec$(31).col_new:= TO_CHAR(:NEW.CUA_MVT);
    rec$(32).tab$:= 'CUACCTYY_REC';
    rec$(32).col$:= 'CUA_PLA_CRE';
    rec$(32).col_old:= TO_CHAR(:OLD.CUA_PLA_CRE);
    rec$(32).col_new:= TO_CHAR(:NEW.CUA_PLA_CRE);
    rec$(33).tab$:= 'CUACCTYY_REC';
    rec$(33).col$:= 'CUA_DAILY_PLA_CRE';
    rec$(33).col_old:= TO_CHAR(:OLD.CUA_DAILY_PLA_CRE);
    rec$(33).col_new:= TO_CHAR(:NEW.CUA_DAILY_PLA_CRE);
    rec$(34).tab$:= 'CUACCTYY_REC';
    rec$(34).col$:= 'CUA_PLAFOND_CRE';
    rec$(34).col_old:= TO_CHAR(:OLD.CUA_PLAFOND_CRE);
    rec$(34).col_new:= TO_CHAR(:NEW.CUA_PLAFOND_CRE);
    rec$(35).tab$:= 'CUACCTYY_REC';
    rec$(35).col$:= 'CUA_INT_ACC_INT';
    rec$(35).col_old:= TO_CHAR(:OLD.CUA_INT_ACC_INT);
    rec$(35).col_new:= TO_CHAR(:NEW.CUA_INT_ACC_INT);
    rec$(36).tab$:= 'CUACCTYY_REC';
    rec$(36).col$:= 'CUA_INT_ACC_CUST';
    rec$(36).col_old:= TO_CHAR(:OLD.CUA_INT_ACC_CUST);
    rec$(36).col_new:= TO_CHAR(:NEW.CUA_INT_ACC_CUST);
    rec$(37).tab$:= 'CUACCTYY_REC';
    rec$(37).col$:= 'CUA_INT_RATE_DB';
    rec$(37).col_old:= TO_CHAR(:OLD.CUA_INT_RATE_DB);
    rec$(37).col_new:= TO_CHAR(:NEW.CUA_INT_RATE_DB);
    rec$(38).tab$:= 'CUACCTYY_REC';
    rec$(38).col$:= 'CUA_INT_RATE_CR';
    rec$(38).col_old:= TO_CHAR(:OLD.CUA_INT_RATE_CR);
    rec$(38).col_new:= TO_CHAR(:NEW.CUA_INT_RATE_CR);
    rec$(39).tab$:= 'CUACCTYY_REC';
    rec$(39).col$:= 'CUA_HIGH_OVRDRAFT';
    rec$(39).col_old:= TO_CHAR(:OLD.CUA_HIGH_OVRDRAFT);
    rec$(39).col_new:= TO_CHAR(:NEW.CUA_HIGH_OVRDRAFT);
    rec$(40).tab$:= 'CUACCTYY_REC';
    rec$(40).col$:= 'CUA_LCLOSING_BAL';
    rec$(40).col_old:= TO_CHAR(:OLD.CUA_LCLOSING_BAL);
    rec$(40).col_new:= TO_CHAR(:NEW.CUA_LCLOSING_BAL);
    rec$(41).tab$:= 'CUACCTYY_REC';
    rec$(41).col$:= 'CUA_ACCR_TOT_ONT';
    rec$(41).col_old:= TO_CHAR(:OLD.CUA_ACCR_TOT_ONT);
    rec$(41).col_new:= TO_CHAR(:NEW.CUA_ACCR_TOT_ONT);
    rec$(42).tab$:= 'CUACCTYY_REC';
    rec$(42).col$:= 'CUA_RET_PER_PERIOD';
    rec$(42).col_old:= TO_CHAR(:OLD.CUA_RET_PER_PERIOD);
    rec$(42).col_new:= TO_CHAR(:NEW.CUA_RET_PER_PERIOD);
    rec$(43).tab$:= 'CUACCTYY_REC';
    rec$(43).col$:= 'CUA_AVAILABLE_BAL';
    rec$(43).col_old:= TO_CHAR(:OLD.CUA_AVAILABLE_BAL);
    rec$(43).col_new:= TO_CHAR(:NEW.CUA_AVAILABLE_BAL);
    rec$(44).tab$:= 'CUACCTYY_REC';
    rec$(44).col$:= 'CUA_POST_VALUE_BAL';
    rec$(44).col_old:= TO_CHAR(:OLD.CUA_POST_VALUE_BAL);
    rec$(44).col_new:= TO_CHAR(:NEW.CUA_POST_VALUE_BAL);
    rec$(45).tab$:= 'CUACCTYY_REC';
    rec$(45).col$:= 'CUA_RESERVE_BAL';
    rec$(45).col_old:= TO_CHAR(:OLD.CUA_RESERVE_BAL);
    rec$(45).col_new:= TO_CHAR(:NEW.CUA_RESERVE_BAL);
    rec$(46).tab$:= 'CUACCTYY_REC';
    rec$(46).col$:= 'CUA_LY_BAL';
    rec$(46).col_old:= TO_CHAR(:OLD.CUA_LY_BAL);
    rec$(46).col_new:= TO_CHAR(:NEW.CUA_LY_BAL);
    rec$(47).tab$:= 'CUACCTYY_REC';
    rec$(47).col$:= 'CUA_LEXT_BAL';
    rec$(47).col_old:= TO_CHAR(:OLD.CUA_LEXT_BAL);
    rec$(47).col_new:= TO_CHAR(:NEW.CUA_LEXT_BAL);
    rec$(48).tab$:= 'CUACCTYY_REC';
    rec$(48).col$:= 'CUA_YTD_DEBIT';
    rec$(48).col_old:= TO_CHAR(:OLD.CUA_YTD_DEBIT);
    rec$(48).col_new:= TO_CHAR(:NEW.CUA_YTD_DEBIT);
    rec$(49).tab$:= 'CUACCTYY_REC';
    rec$(49).col$:= 'CUA_YTD_CREDIT';
    rec$(49).col_old:= TO_CHAR(:OLD.CUA_YTD_CREDIT);
    rec$(49).col_new:= TO_CHAR(:NEW.CUA_YTD_CREDIT);
    rec$(50).tab$:= 'CUACCTYY_REC';
    rec$(50).col$:= 'CUA_CLR_BAL';
    rec$(50).col_old:= TO_CHAR(:OLD.CUA_CLR_BAL);
    rec$(50).col_new:= TO_CHAR(:NEW.CUA_CLR_BAL);
    rec$(51).tab$:= 'CUACCTYY_REC';
    rec$(51).col$:= 'CUA_MTD_CREDIT';
    rec$(51).col_old:= TO_CHAR(:OLD.CUA_MTD_CREDIT);
    rec$(51).col_new:= TO_CHAR(:NEW.CUA_MTD_CREDIT);
    rec$(52).tab$:= 'CUACCTYY_REC';
    rec$(52).col$:= 'CUA_NO_OF_DEBITS';
    rec$(52).col_old:= TO_CHAR(:OLD.CUA_NO_OF_DEBITS);
    rec$(52).col_new:= TO_CHAR(:NEW.CUA_NO_OF_DEBITS);
    rec$(53).tab$:= 'CUACCTYY_REC';
    rec$(53).col$:= 'CUA_NO_OF_CREDITS';
    rec$(53).col_old:= TO_CHAR(:OLD.CUA_NO_OF_CREDITS);
    rec$(53).col_new:= TO_CHAR(:NEW.CUA_NO_OF_CREDITS);
    rec$(54).tab$:= 'CUACCTYY_REC';
    rec$(54).col$:= 'CUA_ACCR_TOT_INT_LC';
    rec$(54).col_old:= TO_CHAR(:OLD.CUA_ACCR_TOT_INT_LC);
    rec$(54).col_new:= TO_CHAR(:NEW.CUA_ACCR_TOT_INT_LC);
    rec$(55).tab$:= 'CUACCTYY_REC';
    rec$(55).col$:= 'CUA_WAIT_BAL';
    rec$(55).col_old:= TO_CHAR(:OLD.CUA_WAIT_BAL);
    rec$(55).col_new:= TO_CHAR(:NEW.CUA_WAIT_BAL);
    rec$(56).tab$:= 'CUACCTYY_REC';
    rec$(56).col$:= 'CUA_MTD_DEBIT';
    rec$(56).col_old:= TO_CHAR(:OLD.CUA_MTD_DEBIT);
    rec$(56).col_new:= TO_CHAR(:NEW.CUA_MTD_DEBIT);
    rec$(57).tab$:= 'CUACCTYY_REC';
    rec$(57).col$:= 'CUA_LMOD_DATE';
    rec$(57).col_old:= TO_CHAR(:OLD.CUA_LMOD_DATE);
    rec$(57).col_new:= TO_CHAR(:NEW.CUA_LMOD_DATE);
    rec$(58).tab$:= 'CUACCTYY_REC';
    rec$(58).col$:= 'CUA_EMET_OPER';
    rec$(58).col_old:= TO_CHAR(:OLD.CUA_EMET_OPER);
    rec$(58).col_new:= TO_CHAR(:NEW.CUA_EMET_OPER);
    rec$(59).tab$:= 'CUACCTYY_REC';
    rec$(59).col$:= 'CUA_ENCOD_OPER';
    rec$(59).col_old:= TO_CHAR(:OLD.CUA_ENCOD_OPER);
    rec$(59).col_new:= TO_CHAR(:NEW.CUA_ENCOD_OPER);
    rec$(60).tab$:= 'CUACCTYY_REC';
    rec$(60).col$:= 'CUA_LIM_EXP_DATE';
    rec$(60).col_old:= TO_CHAR(:OLD.CUA_LIM_EXP_DATE);
    rec$(60).col_new:= TO_CHAR(:NEW.CUA_LIM_EXP_DATE);
    rec$(61).tab$:= 'CUACCTYY_REC';
    rec$(61).col$:= 'CUA_PLA_DB';
    rec$(61).col_old:= TO_CHAR(:OLD.CUA_PLA_DB);
    rec$(61).col_new:= TO_CHAR(:NEW.CUA_PLA_DB);
    rec$(62).tab$:= 'CUACCTYY_REC';
    rec$(62).col$:= 'CUA_DAILY_PLA_DB';
    rec$(62).col_old:= TO_CHAR(:OLD.CUA_DAILY_PLA_DB);
    rec$(62).col_new:= TO_CHAR(:NEW.CUA_DAILY_PLA_DB);
    rec$(63).tab$:= 'CUACCTYY_REC';
    rec$(63).col$:= 'CUA_PLAFOND_DB';
    rec$(63).col_old:= TO_CHAR(:OLD.CUA_PLAFOND_DB);
    rec$(63).col_new:= TO_CHAR(:NEW.CUA_PLAFOND_DB);
    rec$(64).tab$:= 'CUACCTYY_REC';
    rec$(64).col$:= 'CUA_ATTEST';
    rec$(64).col_old:= TO_CHAR(:OLD.CUA_ATTEST);
    rec$(64).col_new:= TO_CHAR(:NEW.CUA_ATTEST);
    rec$(65).tab$:= 'CUACCTYY_REC';
    rec$(65).col$:= 'CUA_USING_CODE';
    rec$(65).col_old:= TO_CHAR(:OLD.CUA_USING_CODE);
    rec$(65).col_new:= TO_CHAR(:NEW.CUA_USING_CODE);
    rec$(66).tab$:= 'CUACCTYY_REC';
    rec$(66).col$:= 'CUA_ACTUAL_INT_DB';
    rec$(66).col_old:= TO_CHAR(:OLD.CUA_ACTUAL_INT_DB);
    rec$(66).col_new:= TO_CHAR(:NEW.CUA_ACTUAL_INT_DB);
    rec$(67).tab$:= 'CUACCTYY_REC';
    rec$(67).col$:= 'CUA_ACTUAL_INT_CR';
    rec$(67).col_old:= TO_CHAR(:OLD.CUA_ACTUAL_INT_CR);
    rec$(67).col_new:= TO_CHAR(:NEW.CUA_ACTUAL_INT_CR);
    rec$(68).tab$:= 'CUACCTYY_REC';
    rec$(68).col$:= 'CUA_FREE_ZONE_CODE';
    rec$(68).col_old:= TO_CHAR(:OLD.CUA_FREE_ZONE_CODE);
    rec$(68).col_new:= TO_CHAR(:NEW.CUA_FREE_ZONE_CODE);
    rec$(69).tab$:= 'CUACCTYY_REC';
    rec$(69).col$:= 'CUA_DATE_CREATED';
    rec$(69).col_old:= TO_CHAR(:OLD.CUA_DATE_CREATED);
    rec$(69).col_new:= TO_CHAR(:NEW.CUA_DATE_CREATED);
    rec$(70).tab$:= 'CUACCTYY_REC';
    rec$(70).col$:= 'CUA_USER_CREATED';
    rec$(70).col_old:= TO_CHAR(:OLD.CUA_USER_CREATED);
    rec$(70).col_new:= TO_CHAR(:NEW.CUA_USER_CREATED);
    rec$(71).tab$:= 'CUACCTYY_REC';
    rec$(71).col$:= 'CUA_DATE_MODIFIED';
    rec$(71).col_old:= TO_CHAR(:OLD.CUA_DATE_MODIFIED);
    rec$(71).col_new:= TO_CHAR(:NEW.CUA_DATE_MODIFIED);
    rec$(72).tab$:= 'CUACCTYY_REC';
    rec$(72).col$:= 'CUA_USER_MODIFIED';
    rec$(72).col_old:= TO_CHAR(:OLD.CUA_USER_MODIFIED);
    rec$(72).col_new:= TO_CHAR(:NEW.CUA_USER_MODIFIED);
    rec$(73).tab$:= 'CUACCTYY_REC';
    rec$(73).col$:= 'CUA_ACCR_TOT_INT';
    rec$(73).col_old:= TO_CHAR(:OLD.CUA_ACCR_TOT_INT);
    rec$(73).col_new:= TO_CHAR(:NEW.CUA_ACCR_TOT_INT);
    rec$(74).tab$:= 'CUACCTYY_REC';
    rec$(74).col$:= 'CUA_PLA';
    rec$(74).col_old:= TO_CHAR(:OLD.CUA_PLA);
    rec$(74).col_new:= TO_CHAR(:NEW.CUA_PLA);
    rec$(75).tab$:= 'CUACCTYY_REC';
    rec$(75).col$:= 'CUA_GARANTIE_BAL';
    rec$(75).col_old:= TO_CHAR(:OLD.CUA_GARANTIE_BAL);
    rec$(75).col_new:= TO_CHAR(:NEW.CUA_GARANTIE_BAL);
    rec$(76).tab$:= 'CUACCTYY_REC';
    rec$(76).col$:= 'CUA_EXP_GAR_DATE';
    rec$(76).col_old:= TO_CHAR(:OLD.CUA_EXP_GAR_DATE);
    rec$(76).col_new:= TO_CHAR(:NEW.CUA_EXP_GAR_DATE);
    rec$(77).tab$:= 'CUACCTYY_REC';
    rec$(77).col$:= 'CUA_INTERET_RETARD';
    rec$(77).col_old:= TO_CHAR(:OLD.CUA_INTERET_RETARD);
    rec$(77).col_new:= TO_CHAR(:NEW.CUA_INTERET_RETARD);
    rec$(78).tab$:= 'CUACCTYY_REC';
    rec$(78).col$:= 'CUA_COMMENT';
    rec$(78).col_old:= TO_CHAR(:OLD.CUA_COMMENT);
    rec$(78).col_new:= TO_CHAR(:NEW.CUA_COMMENT);
    rec$(79).tab$:= 'CUACCTYY_REC';
    rec$(79).col$:= 'CUA_FILLER';
    rec$(79).col_old:= TO_CHAR(:OLD.CUA_FILLER);
    rec$(79).col_new:= TO_CHAR(:NEW.CUA_FILLER);
    rec$(80).tab$:= 'CUACCTYY_REC';
    rec$(80).col$:= 'CUA_DEC_TYPE';
    rec$(80).col_old:= TO_CHAR(:OLD.CUA_DEC_TYPE);
    rec$(80).col_new:= TO_CHAR(:NEW.CUA_DEC_TYPE);
    rec$(81).tab$:= 'CUACCTYY_REC';
    rec$(81).col$:= 'CUA_SEGMENT_CODE';
    rec$(81).col_old:= TO_CHAR(:OLD.CUA_SEGMENT_CODE);
    rec$(81).col_new:= TO_CHAR(:NEW.CUA_SEGMENT_CODE);
    rec$(82).tab$:= 'CUACCTYY_REC';
    rec$(82).col$:= 'CUA_AUTH_FLAG';
    rec$(82).col_old:= TO_CHAR(:OLD.CUA_AUTH_FLAG);
    rec$(82).col_new:= TO_CHAR(:NEW.CUA_AUTH_FLAG);
    rec$(83).tab$:= 'CUACCTYY_REC';
    rec$(83).col$:= 'CUA_AUTH_BY';
    rec$(83).col_old:= TO_CHAR(:OLD.CUA_AUTH_BY);
    rec$(83).col_new:= TO_CHAR(:NEW.CUA_AUTH_BY);
    rec$(84).tab$:= 'CUACCTYY_REC';
    rec$(84).col$:= 'CUA_AUTH_DATE';
    rec$(84).col_old:= TO_CHAR(:OLD.CUA_AUTH_DATE);
    rec$(84).col_new:= TO_CHAR(:NEW.CUA_AUTH_DATE);
    rec$(85).tab$:= 'CUACCTYY_REC';
    rec$(85).col$:= 'CUA_FONCTION_GROUPE';
    rec$(85).col_old:= TO_CHAR(:OLD.CUA_FONCTION_GROUPE);
    rec$(85).col_new:= TO_CHAR(:NEW.CUA_FONCTION_GROUPE);
    rec$(86).tab$:= 'CUACCTYY_REC';
    rec$(86).col$:= 'CUA_AGIOS';
    rec$(86).col_old:= TO_CHAR(:OLD.CUA_AGIOS);
    rec$(86).col_new:= TO_CHAR(:NEW.CUA_AGIOS);
    rec$(87).tab$:= 'CUACCTYY_REC';
    rec$(87).col$:= 'CUA_WAITING_BAL';
    rec$(87).col_old:= TO_CHAR(:OLD.CUA_WAITING_BAL);
    rec$(87).col_new:= TO_CHAR(:NEW.CUA_WAITING_BAL);
    rec$(88).tab$:= 'CUACCTYY_REC';
    rec$(88).col$:= 'CUA_CAB';
    rec$(88).col_old:= TO_CHAR(:OLD.CUA_CAB);
    rec$(88).col_new:= TO_CHAR(:NEW.CUA_CAB);
    rec$(89).tab$:= 'CUACCTYY_REC';
    rec$(89).col$:= 'CUA_IND_FRAIS';
    rec$(89).col_old:= TO_CHAR(:OLD.CUA_IND_FRAIS);
    rec$(89).col_new:= TO_CHAR(:NEW.CUA_IND_FRAIS);
    rec$(90).tab$:= 'CUACCTYY_REC';
    rec$(90).col$:= 'CUA_CAB_STATUS';
    rec$(90).col_old:= TO_CHAR(:OLD.CUA_CAB_STATUS);
    rec$(90).col_new:= TO_CHAR(:NEW.CUA_CAB_STATUS);
    rec$(91).tab$:= 'CUACCTYY_REC';
    rec$(91).col$:= 'CUA_IND_OUVERT_FRAIS';
    rec$(91).col_old:= TO_CHAR(:OLD.CUA_IND_OUVERT_FRAIS);
    rec$(91).col_new:= TO_CHAR(:NEW.CUA_IND_OUVERT_FRAIS);
    rec$(92).tab$:= 'CUACCTYY_REC';
    rec$(92).col$:= 'CUA_OUVERT_FRAIS';
    rec$(92).col_old:= TO_CHAR(:OLD.CUA_OUVERT_FRAIS);
    rec$(92).col_new:= TO_CHAR(:NEW.CUA_OUVERT_FRAIS);
    rec$(93).tab$:= 'CUACCTYY_REC';
    rec$(93).col$:= 'CUA_IND_CALC_INTERET';
    rec$(93).col_old:= TO_CHAR(:OLD.CUA_IND_CALC_INTERET);
    rec$(93).col_new:= TO_CHAR(:NEW.CUA_IND_CALC_INTERET);
    rec$(94).tab$:= 'CUACCTYY_REC';
    rec$(94).col$:= 'CUA_ANC_CPT';
    rec$(94).col_old:= TO_CHAR(:OLD.CUA_ANC_CPT);
    rec$(94).col_new:= TO_CHAR(:NEW.CUA_ANC_CPT);
    rec$(95).tab$:= 'CUACCTYY_REC';
    rec$(95).col$:= 'CUA_ANC_ID';
    rec$(95).col_old:= TO_CHAR(:OLD.CUA_ANC_ID);
    rec$(95).col_new:= TO_CHAR(:NEW.CUA_ANC_ID);
    rec$(96).tab$:= 'CUACCTYY_REC';
    rec$(96).col$:= 'CUA_FULL_ANC_CPT';
    rec$(96).col_old:= TO_CHAR(:OLD.CUA_FULL_ANC_CPT);
    rec$(96).col_new:= TO_CHAR(:NEW.CUA_FULL_ANC_CPT);
    rec$(97).tab$:= 'CUACCTYY_REC';
    rec$(97).col$:= 'CUA_ANC_INT';
    rec$(97).col_old:= TO_CHAR(:OLD.CUA_ANC_INT);
    rec$(97).col_new:= TO_CHAR(:NEW.CUA_ANC_INT);
    rec$(98).tab$:= 'CUACCTYY_REC';
    rec$(98).col$:= 'CUA_ID_PROD';
    rec$(98).col_old:= TO_CHAR(:OLD.CUA_ID_PROD);
    rec$(98).col_new:= TO_CHAR(:NEW.CUA_ID_PROD);
    rec$(99).tab$:= 'CUACCTYY_REC';
    rec$(99).col$:= 'CUA_INTERET_ANNUEL';
    rec$(99).col_old:= TO_CHAR(:OLD.CUA_INTERET_ANNUEL);
    rec$(99).col_new:= TO_CHAR(:NEW.CUA_INTERET_ANNUEL);
    rec$(100).tab$:= 'CUACCTYY_REC';
    rec$(100).col$:= 'CUA_SOLDE_PART_SOC_RESTANT';
    rec$(100).col_old:= TO_CHAR(:OLD.CUA_SOLDE_PART_SOC_RESTANT);
    rec$(100).col_new:= TO_CHAR(:NEW.CUA_SOLDE_PART_SOC_RESTANT);
    rec$(101).tab$:= 'CUACCTYY_REC';
    rec$(101).col$:= 'CUA_DECOUVERT_MAX';
    rec$(101).col_old:= TO_CHAR(:OLD.CUA_DECOUVERT_MAX);
    rec$(101).col_new:= TO_CHAR(:NEW.CUA_DECOUVERT_MAX);
    rec$(102).tab$:= 'CUACCTYY_REC';
    rec$(102).col$:= 'CUA_DECOUVERT_DATE_UTIL';
    rec$(102).col_old:= TO_CHAR(:OLD.CUA_DECOUVERT_DATE_UTIL);
    rec$(102).col_new:= TO_CHAR(:NEW.CUA_DECOUVERT_DATE_UTIL);
    rec$(103).tab$:= 'CUACCTYY_REC';
    rec$(103).col$:= 'CUA_AD_ETAT';
    rec$(103).col_old:= TO_CHAR(:OLD.CUA_AD_ETAT);
    rec$(103).col_new:= TO_CHAR(:NEW.CUA_AD_ETAT);
    rec$(104).tab$:= 'CUACCTYY_REC';
    rec$(104).col$:= 'CUA_SAL_FRAIS';
    rec$(104).col_old:= TO_CHAR(:OLD.CUA_SAL_FRAIS);
    rec$(104).col_new:= TO_CHAR(:NEW.CUA_SAL_FRAIS);
    rec$(105).tab$:= 'CUACCTYY_REC';
    rec$(105).col$:= 'CUA_LINT_BAL';
    rec$(105).col_old:= TO_CHAR(:OLD.CUA_LINT_BAL);
    rec$(105).col_new:= TO_CHAR(:NEW.CUA_LINT_BAL);
    rec$(106).tab$:= 'CUACCTYY_REC';
    rec$(106).col$:= 'CUA_DT_ATTENTE';
    rec$(106).col_old:= TO_CHAR(:OLD.CUA_DT_ATTENTE);
    rec$(106).col_new:= TO_CHAR(:NEW.CUA_DT_ATTENTE);
    rec$(107).tab$:= 'CUACCTYY_REC';
    rec$(107).col$:= 'CUA_CORR_FLAG';
    rec$(107).col_old:= TO_CHAR(:OLD.CUA_CORR_FLAG);
    rec$(107).col_new:= TO_CHAR(:NEW.CUA_CORR_FLAG);
    rec$(108).tab$:= 'CUACCTYY_REC';
    rec$(108).col$:= 'CUA_LAST_MVT';
    rec$(108).col_old:= TO_CHAR(:OLD.CUA_LAST_MVT);
    rec$(108).col_new:= TO_CHAR(:NEW.CUA_LAST_MVT);
    rec$(109).tab$:= 'CUACCTYY_REC';
    rec$(109).col$:= 'CUA_LAST_MVT_SOLDE';
    rec$(109).col_old:= TO_CHAR(:OLD.CUA_LAST_MVT_SOLDE);
    rec$(109).col_new:= TO_CHAR(:NEW.CUA_LAST_MVT_SOLDE);
    rec$(110).tab$:= 'CUACCTYY_REC';
    rec$(110).col$:= 'CUA_HIST_ACC_INT';
    rec$(110).col_old:= TO_CHAR(:OLD.CUA_HIST_ACC_INT);
    rec$(110).col_new:= TO_CHAR(:NEW.CUA_HIST_ACC_INT);
    FOR i IN 1..rec$.COUNT LOOP
        IF nvl(rec$(i).col_old,'X') != nvl(rec$(i).col_new,'X') AND actionFlag ='M' THEN
           INSERT INTO AUDIT_REC (TABLE_NAME,   COLUMN_NAME, ROW_ID ,OLD_VALUE,      NEW_VALUE,     USER_NAME, DATE_MODIFIED, ACTION_FLAG,SQL_TXT,TERMINAL,OSIP,MODULE,ACC_CUST) VALUES
                                 (rec$(i).tab$, rec$(i).col$,:OLD.ROWID ,rec$(i).col_old,rec$(i).col_new, USER2,    SYSDATE,actionFlag,sys_context('userenv','current_sql'),sys_context('userenv','host'),sys_context('USERENV','IP_ADDRESS'),sys_context('userenv','module'),:OLD.cua_acc_cust);
        ELSIF actionFlag ='S' THEN
           INSERT INTO AUDIT_REC (TABLE_NAME,   COLUMN_NAME, ROW_ID , OLD_VALUE,      NEW_VALUE,     USER_NAME, DATE_MODIFIED, ACTION_FLAG,SQL_TXT,TERMINAL,OSIP,MODULE,ACC_CUST) VALUES
                                 (rec$(i).tab$, rec$(i).col$, :OLD.ROWID ,rec$(i).col_old,rec$(i).col_new, USER2,    SYSDATE,actionFlag,sys_context('userenv','current_sql'),sys_context('userenv','host'),sys_context('USERENV','IP_ADDRESS'),sys_context('userenv','module'),:OLD.cua_acc_cust);
        END IF;
      END LOOP;
    END;
    /
    Regards!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Thank u Zargon. I tried with sys_context but it still return server information. I point out that all my application users are not created directly in the databse(v$session) but in the table which I created.
    Are they other way to do? please! thank you.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How does it return server information when using USERENV? I've never had it behave that way.

    Post what you are seeing.
     
  5. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Did u see the trigger above? Lets give you the last part of my trigger I posted above :
    Code (SQL):
    FOR i IN 1..rec$.COUNT LOOP
        IF nvl(rec$(i).col_old,'X') != nvl(rec$(i).col_new,'X') AND actionFlag ='M' THEN
           INSERT INTO AUDIT_REC (TABLE_NAME,   COLUMN_NAME, ROW_ID ,OLD_VALUE,      NEW_VALUE,     USER_NAME, DATE_MODIFIED, ACTION_FLAG,SQL_TXT,TERMINAL,OSIP,MODULE,ACC_CUST) VALUES
                                 (rec$(i).tab$, rec$(i).col$,:OLD.ROWID ,rec$(i).col_old,rec$(i).col_new, USER2,    SYSDATE,actionFlag,sys_context('userenv','current_sql'),sys_context('userenv','host'),sys_context('USERENV','IP_ADDRESS'),sys_context('userenv','module'),:OLD.cua_acc_cust);
        ELSIF actionFlag ='S' THEN
           INSERT INTO AUDIT_REC (TABLE_NAME,   COLUMN_NAME, ROW_ID , OLD_VALUE,      NEW_VALUE,     USER_NAME, DATE_MODIFIED, ACTION_FLAG,SQL_TXT,TERMINAL,OSIP,MODULE,ACC_CUST) VALUES
                                 (rec$(i).tab$, rec$(i).col$, :OLD.ROWID ,rec$(i).col_old,rec$(i).col_new, USER2,    SYSDATE,actionFlag,sys_context('userenv','current_sql'),sys_context('userenv','host'),sys_context('USERENV','IP_ADDRESS'),sys_context('userenv','module'),:OLD.cua_acc_cust);
        END IF;
      END LOOP;
    END;
    /
    I used the <sys_context('userenv','host')> : this return server name ,<sys_context('USERENV','IP_ADDRESS')>: return server IP, <utl_inaddr.get_host_name(sys_context('USERENV','IP_ADDRESS'))>: return server name too, <utl_inaddr.get_host_address(sys_context('userenv','host')> : return server IP too.

    I use two structure of network : 2-tiers and 3-tiers, some PC connect directly on the Server(this one have Data and Application), others connect to the application server (this one have application only)
     
    Last edited: Nov 24, 2016
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You didn't PROVE that with output; please POST the values you are seeing PLUS the server host name and server IP address. Code proves nothing in this case.
     
  7. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    I'm sorry, I didn't have a internet.
    When I execute for exemple, from a client PC, :
    Code (SQL):
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual
    , it returns : 127.0.0.1 and this one
    Code (SQL):
    SELECT UTL_INADDR.GET_HOST_NAME(SYS_CONTEXT('USERENV','IP_ADDRESS')) FROM dual
    returns a name of my database Server : SUPERMAN-PC
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're doing this from a session connected from the db server; you need to do it from the client connection to return the proper values.

    I have used the following triggers to perform connection auditing and they have always returned the correct values:

    Code (SQL):
    CONNECT / AS sysdba

    CREATE TABLE
       stats$user_log
    (
       user_id           varchar2(30),
       session_id           NUMBER(8),
       host              varchar2(64),
       last_program      varchar2(48),
       last_action       varchar2(32),
       last_module       varchar2(48),
       logon_day                 DATE,
       logon_time        varchar2(10),
       logoff_day                DATE,
       logoff_time       varchar2(10),
       elapsed_minutes       NUMBER(8),
       elapsed_seconds       NUMBER(8)
    )
    tablespace tools
    ;

    CREATE OR REPLACE TRIGGER
       logon_audit_trigger
    AFTER LOGON ON DATABASE
    BEGIN
    IF USER <> 'SYS' THEN
    INSERT INTO stats$user_log
    (  user_id       ,
       session_id    ,
       host          ,
       last_program  ,
       last_action   ,
       last_module   ,
       logon_day     ,
       logon_time    ,
       logoff_day    ,
       logoff_time   ,
       elapsed_minutes,
       elapsed_seconds)
    VALUES(
       USER,
       sys_context('USERENV','SESSIONID'),
       sys_context('USERENV','HOST'),
       NULL,
       NULL,
       NULL,
       sysdate,
       to_char(sysdate, 'hh24:mi:ss'),
       NULL,
       NULL,
       NULL,
       NULL
    );
    END IF;
    END;
    /

    CREATE OR REPLACE TRIGGER logoff_audit_trigger
    BEFORE LOGOFF ON DATABASE
    DECLARE
        lo_dt DATE:=sysdate;
    BEGIN
    -- ***************************************************
    -- Update the user record
    --
    -- Set last_action, last_program, last_module,
    -- logoff day and time and total minutes connected
    -- ***************************************************
    IF USER <> 'SYS' THEN
    UPDATE
    stats$user_log
    SET
    last_action = (SELECT action FROM v$session WHERE sys_context('USERENV','SESSIONID') = audsid),
    last_program = (SELECT program FROM v$session WHERE sys_context('USERENV','SESSIONID') = audsid),
    last_module = (SELECT module FROM v$session WHERE sys_context('USERENV','SESSIONID') = audsid),
    logoff_day = lo_dt,
    logoff_time = to_char(lo_dt, 'hh24:mi:ss'),
    elapsed_minutes = round((lo_dt - logon_day)*1440),
    elapsed_seconds = round((lo_dt - logon_day)*86400)
    WHERE
    sys_context('USERENV','SESSIONID') = session_id;
    END IF;

    END;
    /

     
    You might want to try this and see what results you get.
     
  9. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Hello Zargon, I'm sorry, I was busy this week end.
    Thank you for your answer. (SYS_CONTEXT) return the correct value when network architecture is 2-tiers but I want to get client Hostname and IP_address when I'm on 3-tiers. I mean the user connect Database server via other application server.

    Please, help me to resolve this issue, I'm jamed. Thank you very much
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do not have access to that network configuration so I am unable to produce your results or find a suitable work-around. You might think about using the DBMS_APPLICATION_INFO package as there is a SET_CLIENT_INFO procedure you could use to populate the CLIENT_INFO column in v$session with the required information,obtained from a local script and a system calls to get the hostname and IP address of the client computer.
     
  11. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Thank you Zargon. Do you have any script on PL/SQL or Java or other language but wich can be implemented in PL/SQL to get the hostname and IP address of the client computer., please?
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you can download Unix utilities for Windows from here:

    https://sourceforge.net/projects/unxutils/

    Once installed and the path is adjusted you can do this:

    Code (SQL):
    C:\Users>ipconfig | grep IPv4 | gawk -F":" "{print $2}"
    10.0.0.21

    C:\Users>hostname
    lenovo-pc

    C:\Users>
    You can then write a batch file to collect the information and pass that to DBMS_APPLICATION_INFO and populate CLIENT_INFO in V$SESSION then you can query V$SESSION for the user in question and get the desired information.

    I leave the rest up to you.
     
    Sadik likes this.
  13. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Thank you very much, I see this can work correctly but after unzip the unxUtil file zip, I cannot see the .exe file to install this. How can I do to install this unixUtil, please?
     
  14. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    In the unixUtil.zip, I find 2 folders and 2 files ==> - folders : <bin> and <usr>
    - files : <StdDisclaimer> and <UnxUtilsDist>
    Please to find how to insatall this utility please! I find this methode can return correct value
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    372
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You simply find a place to put those directories; there is no installation wizard. Once unzipped you simply add the directory to your path and you are set. I created a base directory called UnxUtils on the C: drive and unzipped the file there. Using that base directory you would add:

    C:\UnxUtils\usr\local\wbin

    to your PATH. You should then have access to those utilities.
     
  16. rezacolomb

    rezacolomb Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bujumbura
    Bonjour Zargon, j'ai fini par trouver en utilisant la librairie WEBUTIL. Je vous remercie pour ton aide. Tu m'as vraiment guidé. Que Dieu te bénisse.
    Merci.
    A la prochaine. Ciao