Databáze ZoraData

Programový balík pro generování náhodných hodnot

Balík obsahuje funkce pro generování náhodného celého a desetinného čísla, náhodného znaku a řetězce a náhodného datumu. Balík je odzkoušen na Oracle 11G.

CREATE OR REPLACE PACKAGE zd_random_pkg AS

-- FUNCTION get_int
--
-- Vrací náhodné číslo integer v zadaném rozsahu
--
   FUNCTION get_int ( i_min_range PLS_INTEGER,
                      i_max_range PLS_INTEGER ) RETURN PLS_INTEGER;

-- FUNCTION get_decimal
--
-- Vrací náhodné desetinné číslo v zadaném rozsahu a přesnosti
--
   FUNCTION get_decimal ( i_min_range NUMBER,
                          i_max_range NUMBER,
                          i_decimal_count PLS_INTEGER ) RETURN NUMBER;

-- FUNCTION get_char
--
-- Vrací náhodný znak ze zadané skupiny znaků
--
   FUNCTION get_char ( i_chars VARCHAR2 DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' ) RETURN CHAR;
     
-- FUNCTION get_date
--
-- Vrací náhodné datum v zadaném rozsahu
--
   FUNCTION get_date ( i_min_date DATE,
                       i_max_date DATE  ) RETURN DATE;

-- FUNCTION get_string
--
-- Vrací náhodný řetězec ze zadaných znaků o zadané délce.
--
   FUNCTION get_string ( i_length PLS_INTEGER,
    i_chars VARCHAR2 DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' ) RETURN VARCHAR2;
    
END zd_random_pkg;


CREATE OR REPLACE PACKAGE BODY zd_random_pkg AS

   FUNCTION get_int ( i_min_range PLS_INTEGER,
                      i_max_range PLS_INTEGER ) RETURN PLS_INTEGER IS
                      x_output     PLS_INTEGER;
   BEGIN
      x_output := i_min_range + FLOOR(DBMS_RANDOM.VALUE * (i_max_range - i_min_range + 1));
      RETURN x_output;
   END;
   
   FUNCTION get_decimal ( i_min_range NUMBER,
                          i_max_range NUMBER,
     i_decimal_count PLS_INTEGER ) RETURN NUMBER IS
      x_output     NUMBER;
      x_min_range  NUMBER;
      x_max_range  NUMBER;
      x_result     NUMBER;
   BEGIN
      x_min_range := i_min_range * POWER(10, i_decimal_count);
      x_max_range := i_max_range * POWER(10, i_decimal_count);
      x_result := x_min_range + FLOOR(DBMS_RANDOM.VALUE * (x_max_range - x_min_range + 1));
      x_output := ROUND(x_result / POWER(10, i_decimal_count), i_decimal_count);
      RETURN x_output;
   END;
   
   FUNCTION get_char ( i_chars VARCHAR2 ) RETURN CHAR IS
      x_output     CHAR(1);
   BEGIN
      x_output := SUBSTR(i_chars, get_int(1, LENGTH(i_chars)), 1);
      RETURN x_output;
   END;
   
   FUNCTION get_date ( i_min_date DATE,
                       i_max_date DATE  ) RETURN DATE IS
      x_output     DATE;
      x_diff       PLS_INTEGER;
      x_add	   PLS_INTEGER;
   BEGIN
      x_diff := i_max_date - i_min_date;
      x_add := get_int(0, x_diff);
      x_output := i_min_date + x_add;
      RETURN x_output;
   END;
   
   FUNCTION get_string ( i_length PLS_INTEGER,
                         i_chars VARCHAR2 ) RETURN VARCHAR2 IS
      x_output     VARCHAR2(4000) DEFAULT '';
   BEGIN
      FOR i IN 1..i_length LOOP
         x_output := x_output || get_char(i_chars);
      END LOOP; 
      RETURN x_output;
   END;

END zd_random_pkg;
/

Balík pro logování událostí nad záznamy tabulky

Vzorové řešení pro automatické logování údajů o založení záznamu a poslední změně záznamu v libovolné databázové tabulce. Obsahuje rovněž vzorové řešení pro načítání jednoznačného primárního klíče (id záznamu) ze sekvence (obdoba AUTO_INCREMENT v MySQL). Odzkoušeno na Oracle 11G.

CREATE OR REPLACE PACKAGE zd_log_record_pkg AS

--  PROCEDURE set_appl_user
--
--  Nastavuje ID aktuálního (právě přihlášeného) aplikačního uživatele (do proměnné udržované po dobu databázové session)
--  Parametry: i_user_id        - ID aktuálního uživateke
--
    PROCEDURE set_appl_user (i_user_id NUMBER);
    
--  FUNCTION get_appl_user
--
--  Vrací ID aktuálního (právě přihlášeného) aplikačního uživatele (z proměnné udržované po dobu trvání databázové session)
--
    FUNCTION get_appl_user RETURN NUMBER;

--  FUNCTION get_db_user
--
--  Vrací jméno aktuálního databázového uživatele
--
    FUNCTION get_db_user RETURN VARCHAR2;

--  FUNCTION set_stamp
--
--  Nastavuje údaje o záznamu (pro použití v triggerech before insert a before update)
--
    PROCEDURE set_stamp (o_action_at IN OUT DATE, o_action_by IN OUT NUMBER, o_action_by_db IN OUT VARCHAR2); 

END zd_log_record_pkg;
/

CREATE PACKAGE BODY zd_log_record_pkg AS
 
   x_user_id              NUMBER(14);

   PROCEDURE set_appl_user (i_user_id NUMBER) IS
   BEGIN
      x_user_id := i_user_id;
      DBMS_APPLICATION_INFO.set_client_info('UserId=' || TO_CHAR(x_user_id)); 
   END;

   FUNCTION get_appl_user RETURN NUMBER IS
   BEGIN
      RETURN x_user_id;
   END;

   FUNCTION get_db_user RETURN VARCHAR2 IS
   BEGIN
      RETURN user;
   END;
   
   PROCEDURE set_stamp (o_action_at IN OUT DATE,
                        o_action_by IN OUT NUMBER,
                        o_action_by_db IN OUT VARCHAR2) AS 
   BEGIN
      o_action_at := SYSDATE;
      o_action_by := get_appl_user;
      o_action_by_db := get_db_user;
   END;

END zd_log_record_pkg;
/

CREATE TABLE test_log_record
        ( id                     NUMBER(14) NOT NULL,
          content                VARCHAR2(200 CHAR),
          created_at             DATE,
          created_by             NUMBER(10),
          created_by_db          VARCHAR2(60 CHAR),
          updated_at             DATE,
          updated_by             NUMBER(10),
          updated_by_db          VARCHAR2(60 CHAR) );

COMMENT ON TABLE test_log_record IS 'Testovací tabulka pro logování změny záznamů';
COMMENT ON COLUMN test_log_record.id IS 'Promární klíč záznamu';
COMMENT ON COLUMN test_log_record.content IS 'Obsah záznamu';
COMMENT ON COLUMN test_log_record.created_at IS 'Datum a čas vytvoření záznamu';
COMMENT ON COLUMN test_log_record.created_by IS 'Id aplikačního uživatele, který vytvořil záznam';
COMMENT ON COLUMN test_log_record.created_by_db IS 'Jméno databázového uživatele, který vytvořil záznam';
COMMENT ON COLUMN test_log_record.updated_at IS 'Datum a čas poslední změny záznamu';
COMMENT ON COLUMN test_log_record.updated_by IS 'Id aplikačního uživatele, který poslední změnil záznam';
COMMENT ON COLUMN test_log_record.updated_by_db IS 'Jméno databázového uživatele, který poslední změnil záznam';

ALTER TABLE test_log_record
    ADD CONSTRAINT test_log_record_pk PRIMARY KEY ( id ) USING INDEX;
        
CREATE SEQUENCE test_log_record_s  
   MINVALUE 1
   MAXVALUE 99999999999999
   INCREMENT BY 1
   NOCYCLE
   CACHE 10;

CREATE TRIGGER test_log_record_bi BEFORE INSERT ON test_log_record
   FOR EACH ROW
BEGIN
   IF :NEW.id IS NULL
   THEN SELECT test_log_record_s.NEXTVAL INTO :NEW.id FROM dual;
   END IF;
   zd_log_record_pkg.set_stamp(:NEW.created_at, :NEW.created_by, :NEW.created_by_db);
END;
/

CREATE TRIGGER test_log_record_bu BEFORE UPDATE ON test_log_record
   FOR EACH ROW
BEGIN
   zd_log_record_pkg.set_stamp(:NEW.updated_at, :NEW.updated_by, :NEW.updated_by_db);
END;
/

Řešení automatického zaznamenávání Oracle chyb

Vzorové řešení obsahuje návrh, jak lze zaznamenávat vzniklé databázové chyby do logovací tabulky. Řešení je odzkoušeno na Oracle 11G

-- Je třeba nastavit práva pro vlastníka schématu ve kterém budou objekty pro logování vytvořeny
--
GRANT ADMINISTER DATABASE TRIGGER TO x_user;

-- Tabulka pro zaznamenání chyb
--
CREATE TABLE zd_log_error
       ( id                    NUMBER(14) NOT NULL,
         error_timestamp       DATE DEFAULT SYSDATE NOT NULL,
         database_user         VARCHAR2(30 CHAR) NOT NULL,
         instance              NUMBER(14) NOT NULL,
         database_name         VARCHAR2(50) NOT NULL,
         error_number          NUMBER(10) NOT NULL,
         error_message         VARCHAR2(2000 CHAR),
         error_stack           VARCHAR2(4000 CHAR),
         sql_statement         CLOB,
         session_id            NUMBER(12),
         host                  VARCHAR2(4000 CHAR),
         os_user               VARCHAR2(200 CHAR) ,
         ip_address            VARCHAR2(200 CHAR),
         client_info           VARCHAR2(2000 CHAR) );

COMMENT ON TABLE zd_log_error IS 'Logování chyb';
COMMENT ON COLUMN zd_log_error.id IS 'Id záznamu';
COMMENT ON COLUMN zd_log_error.error_timestamp IS 'Datum a čas vzniku chyby';
COMMENT ON COLUMN zd_log_error.database_user IS 'Aktuální databázový uživatel';
COMMENT ON COLUMN zd_log_error.instance IS 'Číslo instance databáze';
COMMENT ON COLUMN zd_log_error.database_name IS 'Jméno databáze';
COMMENT ON COLUMN zd_log_error.error_number IS 'Číslo chyby';
COMMENT ON COLUMN zd_log_error.error_message IS 'Text chyby';
COMMENT ON COLUMN zd_log_error.error_stack IS 'Zásobník s chybovým výpisem';
COMMENT ON COLUMN zd_log_error.sql_statement IS 'SQL příkaz, který způsobil chybu';
COMMENT ON COLUMN zd_log_error.session_id IS 'Identifikátor Oracle session';
COMMENT ON COLUMN zd_log_error.host IS 'Host';
COMMENT ON COLUMN zd_log_error.os_user IS 'Uživatel operačního systému';
COMMENT ON COLUMN zd_log_error.ip_address IS 'IP adresa';
COMMENT ON COLUMN zd_log_error.client_info IS 'Informace o klientovi';

ALTER TABLE zd_log_error
    ADD CONSTRAINT zd_log_error_pk PRIMARY KEY ( id ) USING INDEX;

CREATE INDEX zd_log_error_i1 ON zd_log_error ( error_timestamp DESC); 
                  
CREATE INDEX zd_log_error_i2 ON zd_log_error ( session_id); 
                  
-- Sekvence pro primární klíč tabulky
--
CREATE SEQUENCE zd_log_error_s
   START WITH 1
   INCREMENT BY 1
   CACHE 10
   NOCYCLE;
 
-- Trigger pro načítání hodnoty primárního indexu (id záznamu)
--
CREATE TRIGGER zd_log_error_bi BEFORE INSERT ON zd_log_error
   FOR EACH ROW
BEGIN
   IF :NEW.id IS NULL
   THEN SELECT zd_log_error_s.NEXTVAL INTO :NEW.id FROM dual;
   END IF;
END;
/

-- Trigger zaznamenávající vzniklé Oracle chyby do tabulky
--
CREATE OR REPLACE TRIGGER zd_log_error_a AFTER SERVERERROR ON DATABASE
DECLARE
   x_sql_text     ora_name_list_t;
   x_error        NUMBER(12);
   x_message      VARCHAR2(2000);
   x_statement    CLOB;
BEGIN
   FOR i IN 1 .. ORA_SERVER_ERROR_DEPTH LOOP
      x_message := x_message || ORA_SERVER_ERROR_MSG(i);
   END LOOP;
   FOR i IN 1 .. ORA_SQL_TXT(x_sql_text) LOOP
      x_statement := x_statement || x_sql_text(i);
   END LOOP;
   x_error := ORA_SERVER_ERROR(1);
   INSERT INTO zd_log_error (database_user, instance, database_name, error_number, error_message, error_stack, 
                             sql_statement, session_id, host, os_user, ip_address, client_info) 
   VALUES (SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, x_error, x_message, SYS.DBMS_UTILITY.FORMAT_ERROR_STACK, 
           x_statement, sys_context('USERENV', 'SESSIONID'), sys_context('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'OS_USER'), 
           SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'CLIENT_INFO'));
END;
/

Řešení automatického zaznamenávání přihlášení a odhlášení

Vzorové řešení obsahuje návrh, jak lze zaznamenávat přihlášení do databáze a odhlášení od databáze. Údaje jsou zaznamenávány do logovací tabulky. Řešení je odzkoušeno na Oracle 11G.

-- Je třeba nastavit práva pro vlastníka schématu ve kterém budou objekty vytvořeny
--
GRANT ADMINISTER DATABASE TRIGGER TO tt_system;

-- Tabulka pro zaznamenávání přihlášení
--
CREATE TABLE zd_log_logon
       ( id                     NUMBER(14) NOT NULL,
         logon_timestamp        DATE DEFAULT SYSDATE NOT NULL,
         logoff_timestamp       DATE,
         database_user          VARCHAR2(30 CHAR) NOT NULL,
         instance               NUMBER(14) NOT NULL,
         database_name          VARCHAR2(50) NOT NULL,
         session_id             NUMBER(12),
         host                   VARCHAR2(4000 CHAR),
         os_user                VARCHAR2(200 CHAR),
         ip_address             VARCHAR2(200 CHAR),
         client_info            VARCHAR2(2000 CHAR) );

COMMENT ON TABLE zd_log_logon IS 'Logování přihlášení a odhlášení';
COMMENT ON COLUMN zd_log_logon.id IS 'Id záznamu';
COMMENT ON COLUMN zd_log_logon.logon_timestamp IS 'Datum a čas přihlášení';
COMMENT ON COLUMN zd_log_logon.logoff_timestamp IS 'Datum a čas odhlášení';
COMMENT ON COLUMN zd_log_logon.database_user IS 'Aktuální databázový uživatel';
COMMENT ON COLUMN zd_log_logon.instance IS 'Číslo instance databáze';
COMMENT ON COLUMN zd_log_logon.database_name IS 'Jméno databáze';
COMMENT ON COLUMN zd_log_logon.session_id IS 'Identifikátor Oracle session';
COMMENT ON COLUMN zd_log_logon.host IS 'Host';
COMMENT ON COLUMN zd_log_logon.os_user IS 'Uživatel operačního systému';
COMMENT ON COLUMN zd_log_logon.ip_address IS 'IP adresa';
COMMENT ON COLUMN zd_log_logon.client_info IS 'Informace o klientovi';

ALTER TABLE  zd_log_logon ADD CONSTRAINT  zd_log_logon_pk PRIMARY KEY ( id ) USING INDEX;

CREATE INDEX zd_log_logon_i1 ON zd_log_logon ( logon_timestamp DESC ); 
                  
CREATE INDEX zd_log_logon_i2 ON zd_log_logon ( session_id ); 
                  
-- Sekvence pro primární klíč tabulky
--
CREATE SEQUENCE zd_log_logon_s
   START WITH 1
   INCREMENT BY 1
   CACHE 10
   ORDER
   NOCYCLE;
 
-- Trigger pro načítání hodnoty primárního indexu (id záznamu)
--
CREATE TRIGGER zd_log_logon_bi BEFORE INSERT ON zd_log_logon
   FOR EACH ROW
BEGIN
   IF :NEW.id IS NULL
   THEN SELECT zd_log_logon_s.NEXTVAL INTO :NEW.id FROM dual;
   END IF;
END;
/

-- Trigger zaznamenávající přihlášení
--
CREATE OR REPLACE TRIGGER zd_log_logon_a AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO zd_log_logon (logon_timestamp, database_user, instance, database_name, session_id, host, os_user, ip_address, client_info) 
   VALUES (sysdate, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, sys_context('USERENV', 'SESSIONID'), 
           sys_context('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
           SYS_CONTEXT('USERENV', 'CLIENT_INFO'));
END;
/

-- Trigger zaznamenávající odhlášení
--
CREATE OR REPLACE TRIGGER zd_log_logoff_b BEFORE LOGOFF ON DATABASE
BEGIN
   UPDATE zd_log_logon
   SET    logoff_timestamp = sysdate
   WHERE  session_id = sys_context('USERENV', 'SESSIONID')
   AND    logoff_timestamp IS NULL; 
END;
/

Řešení automatického zaznamenávání prováděných DDL příkazů

Vzorové řešení obsahuje návrh, jak lze zaznamenávat prováděne SQL DDL příkazy. Údaje o příkazech a měněných objektech jsou zaznamenávány do logovací tabulky. Řešení je odzkoušeno na Oracle 11G.

-- Je třeba nastavit práva pro vlastníka schématu, ve kterém budou objekty pro logování vytvořeny
--
GRANT ADMINISTER DATABASE TRIGGER TO tt_system;

-- Tabulka pro logování DDL příkazů
--
CREATE TABLE zd_log_ddl
       ( id                    NUMBER(14) NOT NULL,
         ddl_timestamp         DATE DEFAULT SYSDATE NOT NULL,
         object_owner          VARCHAR2(30),
         object_type           VARCHAR2(30),
         object_name           VARCHAR2(30),
         sysevent              VARCHAR2(30),
         database_user         VARCHAR2(30 CHAR) NOT NULL,
         instance              NUMBER(14) NOT NULL,
         database_name         VARCHAR2(50) NOT NULL,
         session_id            NUMBER(12),
         language              VARCHAR2(200 CHAR),
         sql_statement         CLOB,
         host                  VARCHAR2(4000 CHAR),
         os_user               VARCHAR2(200 CHAR),
         machine               VARCHAR2(200 CHAR),
         terminal              VARCHAR2(200 CHAR),
         ip_address            VARCHAR2(200 CHAR),
         client_info           VARCHAR2(4000 CHAR),
         authentication_type   VARCHAR2(200 CHAR),
         module                VARCHAR2(200 CHAR),
         action                VARCHAR2(200 CHAR),
         bg_job_id             NUMBER(12) );

COMMENT ON TABLE zd_log_ddl IS 'Logování DDL příkazů';
COMMENT ON COLUMN zd_log_ddl.id IS 'Id záznamu';
COMMENT ON COLUMN zd_log_ddl.ddl_timestamp IS 'Datum a čas provedení DDL příkaztu';
COMMENT ON COLUMN zd_log_ddl.database_user IS 'Aktuální databázový uživatel';
COMMENT ON COLUMN zd_log_ddl.instance IS 'Číslo instance databáze';
COMMENT ON COLUMN zd_log_ddl.database_name IS 'Jméno databáze';
COMMENT ON COLUMN zd_log_ddl.session_id IS 'Identifikátor Oracle session';
COMMENT ON COLUMN zd_log_ddl.language IS 'Jazykové nastavení session';
COMMENT ON COLUMN zd_log_ddl.sql_statement IS 'DDl SQl příkaz';
COMMENT ON COLUMN zd_log_ddl.host IS 'Host';
COMMENT ON COLUMN zd_log_ddl.os_user IS 'Uživatel operačního systému';
COMMENT ON COLUMN zd_log_ddl.machine IS 'Stroj';
COMMENT ON COLUMN zd_log_ddl.terminal IS 'Terminál';
COMMENT ON COLUMN zd_log_ddl.ip_address IS 'IP adresa';
COMMENT ON COLUMN zd_log_ddl.client_info IS 'Informace o klientovi';
COMMENT ON COLUMN zd_log_ddl.authentication_type IS 'Typ přihlášení';
COMMENT ON COLUMN zd_log_ddl.module IS 'Modul aplikace';
COMMENT ON COLUMN zd_log_ddl.action IS 'Akce aplikace';
COMMENT ON COLUMN zd_log_ddl.bg_job_id IS 'Id jobu';

ALTER TABLE  zd_log_ddl ADD CONSTRAINT  zd_log_ddl_pk PRIMARY KEY ( id ) USING INDEX;

CREATE INDEX zd_log_ddl_i1 ON zd_log_ddl ( ddl_timestamp DESC ); 
                  
CREATE INDEX zd_log_ddl_i2 ON zd_log_ddl ( session_id ); 
                  
CREATE INDEX zd_log_ddl_i3 ON zd_log_ddl ( object_owner, object_name ); 
                  
CREATE INDEX zd_log_ddl_i4 ON zd_log_ddl ( object_type, object_owner, object_name ); 
                  
-- Sekvence pro primární klíč tabulky
--
CREATE SEQUENCE zd_log_ddl_s
   START WITH 1
   INCREMENT BY 1
   CACHE 10
   ORDER
   NOCYCLE;
 
-- Trigger pro načítání hodnoty primárního indexu (id záznamu)
--
CREATE TRIGGER zd_log_ddl_bi BEFORE INSERT ON zd_log_ddl
   FOR EACH ROW
BEGIN
   IF :NEW.id IS NULL
   THEN SELECT zd_log_ddl_s.NEXTVAL INTO :NEW.id FROM dual;
   END IF;
END;
/

-- Trigger zaznamenávající prováděné DDL příkazy
--
CREATE OR REPLACE TRIGGER zd_log_ddl_a AFTER DDL ON DATABASE
DECLARE
   x_sql_text     ora_name_list_t;
   x_statement    CLOB;
BEGIN
   FOR i IN 1 .. ORA_SQL_TXT(x_sql_text) LOOP
      x_statement := x_statement || x_sql_text(i);
   END LOOP;
   INSERT INTO zd_log_ddl (ddl_timestamp, object_owner, object_type, object_name, sysevent, database_user, instance, 
                           database_name, sql_statement, session_id, language, host, os_user, ip_address, client_info,
                           authentication_type, module, action, bg_job_id) 
   VALUES (sysdate, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_SYSEVENT, 
           SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, x_statement, sys_context('USERENV', 'SESSIONID'), 
           sys_context('USERENV', 'LANGUAGE'), sys_context('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'OS_USER'), 
           SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_CONTEXT('USERENV', 'CLIENT_INFO'),
           SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE'), SYS_CONTEXT('USERENV', 'MODULE'), 
           SYS_CONTEXT('USERENV', 'ACTION'), SYS_CONTEXT('USERENV', 'BG_JOB_ID'));
END;
/

Databázové služby

Vzorové přiklady pro Oracle

Programový balík pro generování náhodných hodnot

Balík pro logování událostí nad záznamy tabulky

Řešení automatického zaznamenávání Oracle chyb

Řešení automatického zaznamenávání přihlášení a odhlášení

Řešení automatického zaznamenávání prováděných DDL příkazů