We are attempting a migration to Oracle (for "enterprise" reasons) and are hitting a hard stop when we try to add users to a course. Clicking the "Enrol users" button eventually invokes course_enrolment_manager->get_potential_users(). For Oracle, get_limit_sql in lib/dml/oci_native_moodle_database.php wraps the SQL call in order to limit the size of the result set. The resulting SQL looks like this:

SELECT * FROM (
SELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt,
u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email
FROM m_user u
WHERE id <> 1 AND u.deleted = 0 AND u.confirmed = 1 AND u.id NOT IN (
SELECT ue.userid
FROM m_user_enrolments ue
JOIN m_enrol e ON (e.id = ue.enrolid AND e.id = 21))
ORDER BY u.lastname ASC, u.firstname ASC)
WHERE rownum <= 25

The duplicate field names in the second SELECT results in failure (ORA-00918) in the first SELECT because it can't know which id (for example) is the right one to return. It fails for the same reason that this fails

SELECT x FROM (SELECT u.id x, 1 as x FROM m_user u);

and this succeeds

SELECT x FROM (SELECT u.id x, 1 as x2 FROM m_user u);

The duplicate field names are introduced by $ufields in this line in get_potential_users:

$fields = 'SELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt, '.$ufields;

This is the full error message from the error log:

[Thu Dec 02 19:33:00 2010] [error] [client xxx.xxx.xxx.xxx] Default exception handler: Error reading from database Debug: ORA-00918: column ambiguously defined\nSELECT u.id, u.firstname, u.lastname, u.username, u.email, u.lastaccess, u.picture, u.imagealt, u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email FROM m_user u\n WHERE id <> :guestid AND u.deleted = 0 AND u.confirmed = 1\n AND u.id NOT IN (SELECT ue.userid\n FROM m_user_enrolments ue\n JOIN m_enrol e ON (e.id = ue.enrolid AND e.id = :enrolid)) ORDER BY u.lastname ASC, u.firstname ASC\n[array (\n 'guestid' => '1',\n 'enrolid' => 21,\n 'oracle_num_rows' => 25,\n)]\n* line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown\n* line 268 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1043 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 298 of /enrol/locallib.php: call to oci_native_moodle_database->get_records_sql()\n* line 129 of /enrol/ajax.php: call to course_enrolment_manager->get_potential_users()\n, referer: https://<my.moodle.domain>/enrol/users.php?id=41