Logging Table in Oracle

In Oracle 12c  you create table:

CREATE TABLE msg_log (
    id            NUMBER
        GENERATED BY DEFAULT ON NULL AS IDENTITY,
    ts            TIMESTAMP WITH LOCAL TIME ZONE,
    schema_name   VARCHAR2(15),
    message       VARCHAR2(250)
);

 Create procedure:

CREATE OR REPLACE PROCEDURE log_message (
    message IN VARCHAR2
) AS
    PRAGMA autonomous_transaction;
    schema_name VARCHAR2(15);
BEGIN
    SELECT
        sys_context('userenv', 'current_schema')
    INTO schema_name
    FROM
        dual;

    INSERT /*+ APPEND */ INTO msg_log (
        ts,
        schema_name,
        message
    ) VALUES (
        SYSDATE,
        schema_name,
        message
    );

    COMMIT;
END log_message;

Test

EXEC log_message('foo');

SELECT
    id,
    TO_CHAR(ts, 'YYYY-MM-DD HH24:MM:SS') AS log_date,
    message
FROM
    msg_log; -- where ts > '17-DEC-20 05.05.52.000000000 PM';

Oracle 12c Create Table with Auto Increment and Flashback

CREATE TABLE table_name ( 
    id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ) NOT NULL, 
    ctimestamp TIMESTAMP DEFAULT current_timestamp 
) NOLOGGING; 

CREATE UNIQUE INDEX table_name_pk ON table_name (id ASC ); 

ALTER TABLE table_name ADD CONSTRAINT table_name_pk PRIMARY KEY ( id ); 

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "NOW" FROM DUAL; 

CREATE TABLESPACE archivetest_tabspace DATAFILE SIZE 10M SEGMENT SPACE MANAGEMENT AUTO; 

CREATE FLASHBACK ARCHIVE archivetest TABLESPACE archivetest_tabspace QUOTA 50M RETENTION 7 DAY; 

ALTER TABLE table_name FLASHBACK ARCHIVE archivetest; 

SELECT * FROM table_name AS OF TIMESTAMP TO_TIMESTAMP ('2018-11-29 14:05:11', 'YYYY-MM-DD HH24:MI:SS');

Oracle 12c parsing table row by row with for and switch case

Function that will be used:

CREATE OR REPLACE FUNCTION fun2( p_c_id IN CHAR, p_name IN VARCHAR2, p_regio IN NUMBER) 
RETURN VARCHAR2 
AS 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO CON (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
  VALUES ( p_c_id, p_name, p_regio);
COMMIT;
RETURN p_name || ' mod';
END;
/

Parse row by row using CASE SWITCH:

SELECT REGION_ID,
       CASE region_id
           WHEN 2 THEN fun2(country_id, country_name, region_id )
           WHEN 4 THEN 'four'
           ELSE 'not'
       END AS c
FROM countries

Parse row by row using loop

BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) 
  LOOP 
    my_proc(c.field1, c.field2);
  END LOOP;
END;