1 -- This file is part of Moodle - http://moodle.org/
3 -- Moodle is free software: you can redistribute it and/or modify
4 -- it under the terms of the GNU General Public License as published by
5 -- the Free Software Foundation, either version 3 of the License, or
6 -- (at your option) any later version.
8 -- Moodle is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with Moodle. If not, see <http://www.gnu.org/licenses/>.
19 * @copyright 2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
20 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
21 * @version 20091010 (plz, keep this updated for easier reference)
25 * This sql script generates various PL/SQL packages needed to provide
26 * cross-db compatibility in the Moodle 2.x DB API with some operations
27 * not natively supported by Oracle, namely:
28 * - MOODLE_LOCKS: Application locks used by Moodle DB sessions. It uses
29 * the DBMS_LOCK package so execution must be granted
30 * to the Moodle DB user by SYS to work properly.
31 * - MOODLE_BITS: To provide cross-db bitwise operations to be used by the
32 * sql_bitXXX() helper functions
35 CREATE OR REPLACE PACKAGE MOODLE_BITS AS
37 FUNCTION BITOR (value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
38 FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER;
43 CREATE OR REPLACE PACKAGE BODY MOODLE_BITS AS
45 FUNCTION BITOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
48 RETURN value1 + value2 - BITAND(value1,value2);
51 FUNCTION BITXOR(value1 IN INTEGER, value2 IN INTEGER) RETURN INTEGER IS
54 RETURN MOODLE_BITS.BITOR(value1,value2) - BITAND(value1,value2);
60 CREATE OR REPLACE PACKAGE MOODLE_LOCKS AS
62 FUNCTION GET_HANDLE (lock_name IN VARCHAR2) RETURN VARCHAR2;
63 FUNCTION GET_LOCK (lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER;
64 FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER;
69 CREATE OR REPLACE PACKAGE BODY MOODLE_LOCKS AS
71 FUNCTION GET_HANDLE(lock_name IN VARCHAR2) RETURN VARCHAR2 IS
72 PRAGMA AUTONOMOUS_TRANSACTION;
73 lock_handle VARCHAR2(128);
76 DBMS_LOCK.ALLOCATE_UNIQUE (
77 lockname => lock_name,
78 lockhandle => lock_handle,
79 expiration_secs => 864000);
83 FUNCTION GET_LOCK(lock_name IN VARCHAR2, lock_timeout IN INTEGER) RETURN INTEGER IS
86 lock_status := DBMS_LOCK.REQUEST(
87 lockhandle => GET_HANDLE(lock_name),
88 lockmode => DBMS_LOCK.X_MODE, -- eXclusive
89 timeout => lock_timeout,
90 release_on_commit => FALSE);
93 WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
94 WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
95 ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' || lock_status);
100 FUNCTION RELEASE_LOCK(lock_name IN VARCHAR2) RETURN INTEGER IS
103 lock_status := DBMS_LOCK.RELEASE(
104 lockhandle => GET_HANDLE(lock_name));
105 IF lock_status > 0 THEN
106 RAISE_APPLICATION_ERROR(-20000,'release lock failed - ' || lock_status);