loiclefevre

CREATE TABLE j_order (
    id VARCHAR2(255) DEFAULT SYS_GUID()
        NOT NULL PRIMARY KEY,
    purchase_order JSON
);
    
INSERT INTO j_order (purchase_order)
    VALUES ('{"PONumber": 1600, "Requestor": "Alexis Bull",
                "Items": [ {"Description": "One Magic Christmas",
                            "UnitPrice": 9.95, "Quantity": 1.0},

loiclefevre

-- Getting list of columns per table
SELECT table_name, 
       LISTAGG(column_name, ', ' 
               ON OVERFLOW TRUNCATE 
               '... with more column(s) '
               WITH COUNT
               ) WITHIN GROUP 
       (ORDER BY column_id) AS columns
  FROM all_tab_columns

loiclefevre

 
CREATE TABLE planets (
    json_document BLOB,
    CONSTRAINT json_document_is_json CHECK (json_document IS JSON)
);

INSERT INTO planets (json_document) 
VALUES ( '[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, 
            {"name":"Mars"}, {"name":"Jupiter"}, {"name":"Saturn"}, 
            {"name":"Uranus"}, {"name":"Neptune"} ]' );

loiclefevre

-- Supporting 128 bytes long identifiers since 2017
CREATE TABLE my_table_which_really_deserves_such_a_long_name (
  unique_identifier_that_explains_it_all_very_easily NUMBER,
  -- Also supporting 32767 bytes for VARCHAR2 since 2013
  message VARCHAR2(32767)
);

INSERT INTO my_table_which_really_deserves_such_a_long_name
VALUES ( 1, 'Hello World!' );

loiclefevre

CREATE TABLE customers (
    id                NUMBER  PRIMARY KEY,
    children          NUMBER,
    age               NUMBER,
    gender            CHAR(1),
    salary            NUMBER,
    married           CHAR(1),
    will_buy_an_house NUMBER
);

loiclefevre

CREATE TABLE very_important_data (
    salary_wanted NUMBER CHECK (salary_wanted > 1000000)
);

INSERT INTO very_important_data (salary_wanted) VALUES( 2000000 );
COMMIT;

DROP TABLE very_important_data;

loiclefevre

CREATE TABLE doctors ( first_name VARCHAR2(100), 
                       last_name  VARCHAR2(100) );

INSERT INTO doctors VALUES ( 'John', 'Doe' );
INSERT INTO doctors VALUES ( 'Foo', 'Bar' );
COMMIT;

loiclefevre

sudo yum-config-manager --enable ol8_codeready_builder
sudo yum install -y libstdc++-static
sudo yum install -y gcc glibc-devel zlib-devel

loiclefevre

create or replace function get_event_data(p_raw in raw, p_blob in blob) return clob
as
  key_len          PLS_INTEGER;
  value_offset     PLS_INTEGER;
  value_len        PLS_INTEGER;
  value_len_offset PLS_INTEGER;
  value_raw        raw(2000);
  temp_raw         raw(8);
  len_size         PLS_INTEGER := 4;
  begin_offset     PLS_INTEGER := 1;