$ docker run -d -p 1521:1521 -e ORACLE_PASSWORD=pass \
  -v oracle-volume:/opt/oracle/oradata gvenzl/oracle-xe

sql developer (new connection):
    username: system
    password: <PASS>
    Conn type: Basic
        hostname: localhost
        port: 1521
        SID: xe

SET SERVEROUTPUT ON;
exec DBMS_OUTPUT.PUT_LINE('this is a print ' || 'function');

CREATE TABLE my_table(
    id NUMBER NOT NULL,
    col_num0 NUMBER,
    col_num1 NUMBER,
    col_char VARCHAR(255),
    PRIMARY KEY(id)
);

-- From other table
CREATE TABLE my_other_table AS
(SELECT * FROM MY_TABLE);

CREATE UNIQUE INDEX col_char ON my_table(col_char);
CREATE INDEX index_num ON my_table(col_num0, col_num1);

-- Compute statistics (useful after large changes)
ANALYZE TABLE my_table COMPUTE STATISTICS;

DROP TABLE my_table;

DROP INDEX index_id;

INSERT INTO my_table
    (id, col_num0, col_num1, col_char)
VALUES
    (1, 10, 100, 'First Table');

-- With Select (direct values from dual)
INSERT INTO my_table
    (id, col_num0, col_num1, col_char)
SELECT 2, 20, 200, 'First Table' FROM DUAL UNION
SELECT 3, 30, 300, 'First Table' FROM DUAL

-- With Select (from other table)
INSERT INTO my_other_table
    (id, col_num0, col_num1, col_char)
SELECT
    id+100, col_num0*2, col_num1*col_num1, 'other table'
FROM my_table;

UPDATE my_table
  SET col_num0=0, col_char='updated'
WHERE
    ID >= 2

DELETE FROM my_table
WHERE ID=1
-- Warning: the WHERE is `optional`

DECLARE
      my_var NUMBER;
BEGIN
   SELECT count(*)
   INTO my_var
   FROM my_table;

   DBMS_OUTPUT.PUT_LINE( my_var );
END;

CREATE OR REPLACE FUNCTION plus_three (my_num IN NUMBER) RETURN NUMBER
AS
    res NUMBER;
BEGIN
    res := my_num + 3;
    return res;
END;

-- example:
SELECT plus_three(2) FROM dual;

CREATE OR REPLACE PROCEDURE update_to_alpha_square
    (target_id IN NUMBER, alpha IN NUMBER)
IS
    res NUMBER;
BEGIN
    res := alpha * alpha;
    -- it does not require `EXEC`
    DBMS_OUTPUT.PUT_LINE('alpha_square' || res);

    UPDATE my_table
      SET col_num0=res
    WHERE
        id=target_id;
    -- the parameter name `target_id` should be different than the column name `id`
END;

-- How to execute
exec update_to_alpha_square(target_id=>3, alpha=>9)

-- With Output parameter

CREATE OR REPLACE PROCEDURE calc_alpha_square (alpha IN NUMBER, alpha_square OUT NUMBER)
IS
BEGIN
    alpha_square := alpha * alpha;
END;

-- How to execute

DECLARE
      output NUMBER; -- create variable
BEGIN
   calc_alpha_square(alpha=>7, alpha_square=>output);
   DBMS_OUTPUT.PUT_LINE(output);
END;

DECLARE
    weighted_sum NUMBER:=0;
    CURSOR my_cur IS SELECT * FROM my_table;
BEGIN
    FOR cur IN my_cur LOOP
        weighted_sum := weighted_sum + (cur.col_num0 * cur.col_num1);
    END LOOP;
    dbms_output.put_line( 'weighted_sum ' || weighted_sum);
END;

CREATE OR REPLACE TRIGGER my_trigger
BEFORE -- options: BEFORE | AFTER | INSTEAD OF
UPDATE -- options: INSERT [OR] | UPDATE [OR] | DELETE
-- optional: OF [column]
ON my_table
-- REFERENCING OLD AS old_values NEW AS new_values -- optional
FOR EACH ROW -- optional
--WHEN (id > 100) -- only if `FOR EACH ROW`
DECLARE
BEGIN
    dbms_output.put_line('Update my_table::col_char: ' || chr(10));
    dbms_output.put_line(chr(9)||'OLD.col_char: ' || :old.col_num0);
    dbms_output.put_line(chr(9)||'NEW.col_char: ' || :new.col_num0);
    :new.col_num0 := :new.col_num0 * 2;
    dbms_output.put_line(chr(9)||'final value: ' || :new.col_num0);
END;
-- chr(9) == '\t'
-- chr(10) == '\n'

SELECT x2 FROM table_pow WHERE x = VALUE

CREATE TABLE table_pow(
    x NUMBER,
    x2 NUMBER
);


BEGIN
  FOR x IN 0..1000000
  LOOP
    INSERT INTO table_pow (x, x2)
    SELECT x, power(x, 2) FROM DUAL;
  END LOOP;
END;

CREATE INDEX index_x ON table_pow(x);

CREATE OR REPLACE PROCEDURE sum_array_if_mychar_is_U(
    my_numbers IN sys.odcinumberlist,
    my_chars IN sys.odcivarchar2list,
    sum_out OUT NUMBER
)
IS
BEGIN
    sum_out := 0;
    FOR i IN 1..my_numbers.count LOOP
        IF my_chars(i) = 'U' THEN
            sum_out := sum_out + my_numbers(i);
        END IF;
    END LOOP;
END;

DECLARE
    my_numbers sys.odcinumberlist := sys.odcinumberlist(10, 20, 30, 40, 50);
    my_chars sys.odcivarchar2list := sys.odcivarchar2list('A', 'U', 'I', 'O', 'U');
    sum_out NUMBER;
BEGIN
    sum_array_if_mychar_is_U(my_numbers, my_chars, sum_out);
    dbms_output.put_line('Sum of my_numbers values where my_chars is "U": ' || sum_out);
    -- 70
END;

SELECT TO_DATE('2024-08-19', 'YYYY-MM-DD') AS converted_date FROM dual;
SELECT TO_DATE('2024-08-19 11:30:00', 'YYYY-MM-DD HH24:MI:SS') AS converted_date FROM dual;
SELECT TO_DATE('19-AUG-2024', 'DD-MON-YYYY') AS converted_date FROM dual;

SELECT TRUNC(SYSDATE) AS truncated_date FROM dual;-- 2024-08-19 00:00:00.000
SELECT TRUNC(SYSDATE, 'MM') AS truncated_date FROM dual; -- 2024-08-01 00:00:00.000
SELECT TRUNC(SYSDATE, 'YYYY') AS truncated_date FROM dual; -- 2024-01-01 00:00:00.000

SELECT ADD_MONTHS(SYSDATE, 3) AS three_months_later FROM dual;
SELECT LAST_DAY(SYSDATE) AS last_day_of_month FROM dual;
SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM dual; -- date of the next specified weekday
SELECT MONTHS_BETWEEN(TO_DATE('2024-12-31', 'YYYY-MM-DD'), SYSDATE) AS months_between FROM dual; -- float
SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual; -- 2024
SELECT EXTRACT(MONTH FROM SYSDATE) AS current_month FROM dual; -- 8
SELECT SYSDATE + INTERVAL '10' DAY AS date_plus_10_days FROM dual; -- 2024-08-29 11:21:10.000
SELECT SYSDATE + INTERVAL '10' MONTH AS date_plus_10_days FROM dual; -- 2025-06-19 11:21:10.000
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS date_as_string FROM dual; -- '2024-08-19 11:21:10.000'
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS date_as_string FROM dual; -- '2024-08-19'

DROP TABLE my_random_table;
CREATE TABLE my_random_table(
    id NUMBER NOT NULL,
    x NUMBER
);

CREATE OR REPLACE PROCEDURE populate_gaussian (
    p_id         IN NUMBER,
    p_num_rows   IN NUMBER,
    p_mean       IN NUMBER
) IS
BEGIN
    FOR i IN 1..p_num_rows LOOP
        INSERT INTO my_random_table (id, x)
        VALUES (p_id, DBMS_RANDOM.normal + p_mean);
    END LOOP;
    COMMIT;
END;


exec populate_gaussian(1, 10, 1);
exec populate_gaussian(2, 100, 2);
exec populate_gaussian(3, 1000, 3);
exec populate_gaussian(4, 10000, 4);
exec populate_gaussian(5, 100000, 5);

select * from my_random_table;

DROP MATERIALIZED VIEW my_average_materialized_view;
CREATE MATERIALIZED VIEW my_average_materialized_view
REFRESH COMPLETE -- FAST (incremental refresh), COMPLETE (recompute all data)
ON DEMAND -- ON DEMAND, ON COMMIT, or NEVER:
AS
    SELECT
        count(*) AS counts,
        id,
        avg(x) AS mean,
        SUM(COS(x)*COS(x) + SIN(x)*SIN(x)) AS slow_count
    FROM my_random_table
    GROUP BY id;

-- Materialized views are not eligible for fast refresh if the defining query
-- contains an analytic function or the XMLTable function.
select * from my_average_materialized_view order by id;

exec populate_gaussian(-1, 10, -1);

select * from my_average_materialized_view order by id;
-- not updated

exec DBMS_MVIEW.refresh('my_average_materialized_view');

select * from my_average_materialized_view order by id;
-- up to date

-- If a high load is inserted into the table, the refresh may take more time;
-- however, it will not block read operations.
exec populate_gaussian(-2, 1000000, -2);
exec DBMS_MVIEW.refresh('my_average_materialized_view');
-- Other connection in parallel
select * from my_average_materialized_view order by id;

exec DBMS_SCHEDULER.DROP_JOB(job_name => '"SYSTEM"."REFRESH_MV_JOB"');
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'REFRESH_MV_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.refresh(''my_average_materialized_view'', method => ''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled         => TRUE
  );
END;

exec populate_gaussian(-4, 1000000, -4);

-- Disable the scheduler
exec DBMS_SCHEDULER.disable(name=>'"SYSTEM"."REFRESH_MV_JOB"', force => TRUE);

DROP TABLE documents;
CREATE TABLE documents (
    doc_id NUMBER PRIMARY KEY,
    text_content VARCHAR2(200)
);

-- Custom index for text scan
-- Supported type: VARCHAR2, CHAR, CLOB, BLOB, BFILE.
CREATE INDEX text_content_idx ON documents(text_content) INDEXTYPE IS CTXSYS.CONTEXT;

-- Add dummy data
INSERT INTO documents (doc_id, text_content)
VALUES (1, 'This is a sample document containing some text.');
INSERT INTO documents (doc_id, text_content)
VALUES (2, 'Oracle Text allows full-text search capabilities in Oracle.');
INSERT INTO documents (doc_id, text_content)
VALUES (3, 'The indexing process helps in faster search operations.');
INSERT INTO documents (doc_id, text_content)
VALUES (4, 'Oracle Database is a powerful relational database management system.');
INSERT INTO documents (doc_id, text_content)
VALUES (5, 'Text indexing improves search performance significantly.');
INSERT INTO documents (doc_id, text_content)
VALUES (6, 'Full-text search enables users to find information efficiently.');
INSERT INTO documents (doc_id, text_content)
VALUES (7, 'ORACLE Database is a powerful relational database management system.');

-- More dummy data
-- DROP SEQUENCE doc_id_seq;
CREATE SEQUENCE doc_id_seq START WITH 8 INCREMENT BY 1;

DECLARE
    random_text VARCHAR2(100);
BEGIN
    FOR i IN 1..1000000 LOOP
        random_text := DBMS_RANDOM.STRING('U', 3) || ' ' || DBMS_RANDOM.STRING('U', 3); -- Generating [A-Z]{3} [A-Z]{3}
        INSERT INTO documents (doc_id, text_content)
        VALUES (doc_id_seq.NEXTVAL, random_text);
    END LOOP;
    COMMIT;
END;

BEGIN
    CTX_DDL.SYNC_INDEX('text_content_idx');
END;

-- Unoptimized (Cost 682)
SELECT count(*)
FROM documents d
WHERE upper(text_content) LIKE '%RAA%'
-- Test with random strings
-- 96.0 ms ± 0.99 ms per loop (mean ± std. dev. of 10 runs, 7 loops each)

-- With Index (Cost 4)
SELECT count(*)
FROM documents d
WHERE CONTAINS(text_content, 'RAA') > 0;
-- Test with random strings
-- 15.9 ms ± 1.38 ms per loop (mean ± std. dev. of 10 runs, 7 loops each)

SELECT d.*
FROM documents d
WHERE CONTAINS(text_content, 'AAA OR BBB') > 0;

SELECT d.*
FROM documents d
WHERE CONTAINS(text_content, 'AAA') > 0
    AND CONTAINS(TEXT_CONTENT, 'LZF') = 0; -- not