v2.6 +Oracle 10 database error

v2.6 +Oracle 10 database error

by Gary Lyon -
Number of replies: 8

I'm running v2.6 with Oracle 10 d/b. When I go to view the certificates that have been issued in any course I get the following ( with Developer-level debug on):

Debug info: ORA-00918: column ambiguously defined
SELECT *
FROM (SELECT u.*, ci.code, ci.timecreated
FROM m_user u
INNER JOIN m_certificate_issues ci
ON u.id = ci.userid
WHERE u.deleted = 0
AND ci.certificateid = :o_certificateid
AND NOT u.id IN (:o_cert7,:o_cert8,:o_cert9,:o_cert10,:o_cert11) 

ORDER BY MOODLELIB.UNDO_MEGA_HACK(MOODLELIB.TRICONCAT(firstname, '*OCISP*', lastname)) )
WHERE rownum <= :o_oracle_num_rows
[array (
'o_certificateid' => '943',
'o_cert7' => 4968,
'o_cert8' => 257,
'o_cert9' => 114,
'o_cert10' => 192,
'o_cert11' => 86,
'o_oracle_num_rows' => 30,
)]
Error code: dmlreadexception
Stack trace:
line 441 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
line 744 of /mod/certificate/lib.php: call to oci_native_moodle_database->get_records_sql()
line 102 of /mod/certificate/report.php: call to certificate_get_issues()

My SQL go-to person tested the query without 'u.*' and it works fine. I am not a programmer but I greped the files for 'u.*' and the only reference I found was in lib.php. 

Any suggestions on what I can do are greatly appreciated.

Thanks

Average of ratings: -
In reply to Gary Lyon

Re: v2.6 +Oracle 10 database error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

A better fix would probably be to change firstname and lastname to u.firstname and u.lastname.

To do that you would need to change this line of code: https://github.com/markn86/moodle-mod_certificate/blob/f6c3bc5b2fb0c6e85e5f0bc9e1759108c21b3056/report.php#L102. Change $DB->sql_fullname() to $DB->sql_fullname('u.firstname', 'u.lastname')

Average of ratings: -
In reply to Tim Hunt

Re: v2.6 +Oracle 10 database error

by Jean-Michel Védrine -

Thanks Tim for this suggestion.

Gary, can you confirm it solve your problem (I don't have access to an Oracle install) ? If this is the case, I will create a tracker issue and prepare a github patch.

Average of ratings: -
In reply to Tim Hunt

Re: v2.6 +Oracle 10 database error

by Gary Lyon -

Hi Tim, 

No luck. Getting the following after making the change. Thanks for any suggestions:

Debug info: ORA-00918: column ambiguously defined
SELECT *
FROM (SELECT u.*, ci.code, ci.timecreated
FROM m_user u
INNER JOIN m_certificate_issues ci
ON u.id = ci.userid
WHERE u.deleted = 0
AND ci.certificateid = :o_certificateid
AND NOT u.id IN (:o_cert7,:o_cert8,:o_cert9,:o_cert10,:o_cert11,:o_cert12,:o_cert13,:o_cert14) 

ORDER BY MOODLELIB.UNDO_MEGA_HACK(MOODLELIB.TRICONCAT(u.firstname, '*OCISP*', u.lastname)) )
WHERE rownum <= :o_oracle_num_rows
[array (
'o_certificateid' => '1921',
'o_cert7' => 612,
'o_cert8' => 4968,
'o_cert9' => 257,
'o_cert10' => 5507,
'o_cert11' => 62064,
'o_cert12' => 202,
'o_cert13' => 114,
'o_cert14' => 86,
'o_oracle_num_rows' => 30,
)]
Error code: dmlreadexception
Stack trace:
    • line 441 of /lib/dml/moodle_database.php: dml_read_exception thrown
    • line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
    • line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
    • line 744 of /mod/certificate/lib.php: call to oci_native_moodle_database->get_records_sql()
    • line 102 of /mod/certificate/report.php: call to certificate_get_issues()

 

 

Average of ratings: -
In reply to Gary Lyon

Re: v2.6 +Oracle 10 database error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Eloy has another theory, which I am just passing on:

The user table has a column called timecreated, which may be clashing with ci.timecreated.

Can you try changing https://github.com/markn86/moodle-mod_certificate/blob/f6c3bc5b2fb0c6e85e5f0bc9e1759108c21b3056/lib.php#L735

Change

SELECT u.*, ci.code, ci.timecreated

in that line to

SELECT u.*, ci.code, ci.timecreated AS citimecreated

and see if that gets rid of the error.

If that satisfies Oracle, then other parts of the code will also have to be changed to make it work with the new column name in the results.

Average of ratings: -
In reply to Tim Hunt

Re: v2.6 +Oracle 10 database error

by Gary Lyon -

Tim....I love people smarter than me....it worked! What would be the other changes I would have to make?

Average of ratings: -
In reply to Gary Lyon

Re: v2.6 +Oracle 10 database error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Yes. Eloy is great.

To find what needs to change, you need to find all the places which use the DB query we modified. Then se if they use the ->timemodified property at all. If they do, that would have to be changed to citimemodified.

Alternatively, just after that query is run, and before the return statement, just add this code:

foreach ($users as $user) {
    $user->timemodified = $user->citimemodified;
}
Average of ratings: -
In reply to Tim Hunt

Re: v2.6 +Oracle 10 database error

by Gary Lyon -

I greped /mod/certificate and it only occurs in line 735 of lib.php. I also grepped the entire /mod directory and found one other occurance in /mod/simplecertificate line 1201 of locallib.php. Thanks and much gratitude for the assist Tim. 

Average of ratings: -