Is it possible to use CakePHP with Oracle as a Database?

Question

I'm Trying to switch from MySQL to Oracle Database on a CakePHP 4 Project and it's seeming to be impossible. I'm using this Driver CakePHP Oracle Driver and i installed the instantcliente 19.6, oci8-2.2.0(for php7.4.33) and the pdo_oci as it is on the repository explained.

I Can only make work a query that is manually built like this one:

$sql = $this->oracleConnection
            ->newQuery()
            ->select('USERS.LOCKED, USERS.LOCK_TYPE')
            ->from('USERS')
            ->where('USERS.USERNAME = \'' . $username . '\' AND ROWNUM = 1')
            ->order([]);

But the default methods of CakePHP like find() and get() throw errors like this one : ORA-00911: invalid character

The find who gives the error:

$blockedUser = $this->blockedUser->find('all', [
            'conditions' => [
                'BlockedUsers.blocked_until >' => FrozenTime::now(),
                'BlockedUsers.username' => $username,
            ],
        ])->first();

And the query that it sends and gets the error:

SELECT *
FROM (
  SELECT cake_paging.*, (ROWNUM) AS _cake_page_rownum_
  FROM (
    SELECT BlockedUsers.ID AS BlockedUsers__ID,
           BlockedUsers.USERNAME AS BlockedUsers__USERNAME,
           BlockedUsers.OS AS BlockedUsers__OS,
           BlockedUsers.USER_AGENT AS BlockedUsers__USER_AGENT, 
           BlockedUsers.CREATED AS BlockedUsers__CREATED,
           BlockedUsers.BLOCKED_UNTIL AS BlockedUsers__BLOCKED_UNTIL
    FROM   blocked_users BlockedUsers
    WHERE (BlockedUsers.blocked_until > :c0 AND BlockedUsers.username = :c1)
  ) cake_paging
) cake_paging_out
WHERE cake_paging_out."_cake_page_rownum_" <= 1

And the BLOCKED_USERS TABLE:

BLOCKED_USERS

Also here it's my connection from app.php:

'default' => [
            'className' => 'CakeDC\OracleDriver\Database\OracleConnection',
            'driver' => 'CakeDC\OracleDriver\Database\Driver\OracleOCI',
            'persistent' => false,
            'host' => 'myhost',
            'port' => 'myport',
            'username' => 'myusername',
            'password' => 'mypassword',
            'database' => 'myDB',
            'sid' => 'mySID',
            'encoding' => 'WE8ISO8859P1',
            'timezone' => '+1:00',
            'flags' => [],
            'cacheMetadata' => true,
            'log' => false,
            'quoteIdentifiers' => false,
            'server_version' => 10,
            'url' => env('DATABASE_URL', null),
        ],

I Have already tried to test multiple type of connections in app.php, Uninstalling and installing php and the extensions. With all this i ask myself if I'm doing something wrong or isn't it possible?

Answer 1

The error is generated from:

 (ROWNUM) AS _cake_page_rownum_

It should be:

 (ROWNUM) AS "_cake_page_rownum_"

as _ is not a valid leading character for an unquoted identifier.

ORMs will, typically, use quoted identifiers throughout the code they generate; your code is (with one exception) not using any quoted identifiers which looks strange.