Databases: oci_native_moodle_package.sql

Databases: oci_native_moodle_package.sql

by mark hedley -
Number of replies: 2

Hello

following an upgrade from 1.9 to 2.1, with an oracle backend, I was informed that:

"Oracle PL/SQL Moodle support packages are not installed! Database administrator has to execute /lib/dml/oci_native_moodle_package.sql script."

So I ran the script:

oci_native_moodle_package.sql

as the moodb user

and got the following error:

PLS-00201: identifier 'DBMS_LOCK' must be declared

 

This script requires higher permissions, according to the comments, than the moodb user. why?

 

 *  - MOODLE_LOCKS: Application locks used by Moodle DB sessions. It uses
 *                  the DBMS_LOCK package so execution must be granted
 *                  to the Moodle DB user by SYS to work properly.

 

so the system user has to

grant execute on DBMS_LOCKS to moodb

is this right?

thank you

mark

Average of ratings: -
In reply to mark hedley

Re: Databases: oci_native_moodle_package.sql

by Simon Cole -

No. You need to run this as SYS (not SYSTEM), as it's that user that owns DBMS_LOCK.

select object_type, owner from dba_objects where object_name = 'DBMS_LOCK' and object_type like 'PACKAGE%';

OBJECT_TYPE         OWNER
------------------- ------------------------------
PACKAGE             SYS
PACKAGE BODY        SYS

It's the SYS user that creates these objects when the database is created (the create itself is run from dbmslock.sql, which is in turn run from catproc.sql)

Hope this helps! 

 

Average of ratings: -
In reply to Simon Cole

Re: Databases: oci_native_moodle_package.sql

by Rafiq Muhammad -

Hi,

Although this thread is a bit old, but it is relevant. I tried to install Moodle 2.8 on Oracle and came to the same problem. This is the only thread which is close to useful in regard to the matter. As a non oracle dba, it is quite difficult to understand and resolve the matter, but finally I managed to resolve it. So I will pen down the step by step solution for future reference because I believe anyone will definitely come to this thread if they stumble to the same problem.

I assume that you have created the database schema (user) for your moodle. Lets call the user moodle.

1. Start your oracle SQL Plus or Run SQL Command Line if you are using XE.

2. Login as the user moodle

3. As mentioned in the thread above, you cannot create the package in your moodle user. You have to create it under the sys account
4. So, switch to your sys account

SQL> conn sys/<your sys password> as sysdba;

5. Now run the script under the sysdba account.

SQL> @{moodle root}\lib\dml\oci_native_moodle_package.sql;

If everything is successful, you will see the message:

Package created.
Package body created.
No errors.
Package body created.

6. Now here is the tricky part where a non-oracle dba like me will miss out. After creating the package, you must GRANT execute to the user and there are 2 grants you need to do. First is the moodlelib package itself and second, is the DBMS_LOCK as mentioned above.

SQL> GRANT EXECUTE ON moodlelib to moodle;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_LOCK to moodle;
Grant succeeded.

7. If everything works well, you are ready to kick of your installation

Hope this will save others the hours spend to resolve the matter.

Thanks

Average of ratings: Useful (2)