Oracle database support

[butschster]

• Added oracle driver
• Added tests for oracle driver
• Added oracle-xe-11g-r2 docker container
• Added docker container PHP with pdo_oci
• Added bash script with pdo_oci installer

Oracle Features

Uppercase

Naming system tables and fields in uppercase (at least in version 11). In SQL queries, lowercase can be used, but the returned arrays will have uppercase.

Schemas

In Oracle, there's a concept called schema and by default, every user upon connection has access to a schema with the username. The work with schemas from the Postgres driver was used as a basis, as it is more similar to Oracle.

By default, after launching a Docker container, the user SYSTEM is available and it has a bunch of system tables. This feature can complicate testing the retrieval of the entire table list through getTableNames.

Auto increment

In Oracle DB, auto increment is implemented through Sequences. It's necessary to create a Sequence, name it, and when adding a new record to the DB, specify which field should use which Sequence. There's no direct link between the table field and the sequence, i.e., it's impossible to know which field corresponds to which sequence.

Creating a Sequence

CREATE SEQUENCE author_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE

Inserting a row into the DB

create table author(
   id             NUMBER(6) PRIMARY KEY,
   fname          VARCHAR2(20) NOT NULL,
   lname          VARCHAR2(20) NOT NULL,
   email          VARCHAR2(50),
   phone_number   VARCHAR2(20)
)

INSERT INTO author
VALUES
  (author_seq.nextval, 'Stephen', 'King', '[email protected]', NULL)

Detailed example here

Column types

Oracle has its own typing for fields. You can view the list of types, for instance, here. Pay attention to this when mapping.

LIMIT OFFSET

In Oracle, working with LIMIT OFFSET is similar to the SqlServer driver.

Sample

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

Note the syntax as there are variations.

[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ] 

SELECT ... FOR UPDATE

The SELECT FOR UPDATE command allows you to lock the records in the resulting cursor set. The record lock is released when the following commit or rollback commands are executed.

CURSOR c1
IS
  SELECT course_number, instructor
  FROM courses_tbl
  FOR UPDATE OF instructor;

Foreign keys

As far as I understood, in version 11 of Oracle DB, there is no possibility to use ON UPDATE, instead triggers are suggested.

create table table1(
    column1 number primary key
);
create table table2(
    column2 number references table1(column1)
);

create or replace trigger cascade_update
after update of column1 on table1
for each row
begin
   update table2
   set column2 = :new.column1 
   where column2 = :old.column1;
end;

Drop constraint drop index

Dropping an index with constraint is allowable as follows

alter table foo drop constraint un_foo drop index

There might be other specific things, possibly from version 11 Oracle added a bunch of innovations and it's worth updating the Docker container to the latest version. The current version is 21c.