Paginator query doesn't work using Oracle

Here is the error message sent by oracle:
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 29 Column: 4

Also, if I remove the keyword AS, and let the _dctrn_result, I get the error:
ORA-00911: invalid character
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action:
Error at Line: 29 Column: 4

So, If I remove the _ from the alias it work.

I would also point out the the result that I get is not correct when we use an order by clause.

To get the correct result when an order by clause is provided, I need to add another level of query.
here is the query that need to be executed to get the result in the proper order by clause.

SELECT a.*
FROM
(
select b.*, rownum as rn
from
(
SELECT DISTINCT ID0
FROM
(
SELECT
f0_.id AS ID0, f0_.title AS TITLE1,
f1_.id AS ID2,
f2_.id AS ID3, f2_.first_name AS FIRST_NAME4, f2_.middle_name AS MIDDLE_NAME5, f2_.last_name AS LAST_NAME6, f2_.phone AS PHONE7, f2_.url AS URL8,
f2_.email AS EMAIL9, f2_.profile AS PROFILE10, f2_.created_date AS CREATED_DATE11, f2_.last_updated_date AS LAST_UPDATED_DATE12,
f2_.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUS_ID15,
f3_.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS END_DATE19,
f4_.id AS ID20, f4_.name AS NAME21,
f5_.id AS ID22,
c6_.id AS ID23,
d7_.id AS ID24
FROM fo_deal f0_
INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
INNER JOIN fo_position f3_ ON f2_.id = f3_.people_id
INNER JOIN fo_company f4_ ON f3_.company_id = f4_.id
LEFT JOIN fo_people_asset f5_ ON f2_.id = f5_.people_id
LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
WHERE f2_.object_status_id <> 3
AND f2_.publishing_status_id = 2
ORDER BY f0_.deal_date DESC, f0_.published_date DESC
) dctrn_result
) b
) a
WHERE a.rn <= 3

I've fix the limit order by issue (for oracle only)

here is the change I've made to the file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
method: walkSelectStatement
starting at lime 129.

I've replace this code:
$sql = $this->platform->modifyLimitQuery(
$sql, $this->maxResults, $this->firstResult
);

for this one:
if ($this->firstResult > 0)

else

The countOutputWalker also need to be modified.

file: Doctrine/ORM/Tools/Pagination/CountOutputWalker.php
method: walkSelectStatement

for oracle it doesn't work:
return sprintf('SELECT %s AS _dctrn_count FROM (SELECT DISTINCT %s FROM (%s) AS _dctrn_result) AS _dctrn_table',

need to be changed for:
return sprintf('SELECT %s AS DCTRN_COUNT FROM (SELECT DISTINCT %s FROM (%s) DCTRN_RESULT) DCTRN_TABLE',

I want to point out the I've change the alias to UPPER CASE, cause oracle return the array result with field names in upper case.
This affect the count method of the file Doctrine/ORM/Tools/Pagination/Paginator.php that need to be modified.

file: Doctrine/ORM/Tools/Pagination/Paginator.php
method: count
line: 131 change $rsm->addScalarResult('_dctrn_count', 'count'); for $rsm->addScalarResult('DCTRN_COUNT', 'count');

Fixed, you are not using 2.2.2 btw, this code is only in master.