MacLochlainns Weblog

I was working through some example files and test scripts with Virtual Private Databases and discovered a nifty and potentially misleading error. Google didn’t pick up any search results with it, so I thought noting it would be a good idea.

When you create a security policy with DBMS_RLS.ADD_POLICY incorrectly, and then try to drop it, you must make sure to include the OBJECT_SCHEMA parameter. If you don’t and provide named parameters like the following, you’ll raise an error.

BEGIN
  DBMS_RLS.DROP_POLICY(object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

The error is quite misleading, as shown below.

BEGIN
*
ERROR at line 1:
ORA-28103: adding a policy TO an object owned BY SYS IS NOT allowed
ORA-06512: at "SYS.DBMS_RLS", line 59
ORA-06512: at line 2

The error is actually triggered when the OBJECT_SCHEMA is required. The default value is a NULL in the DBMS_RLS package specification.

The correct syntax is:

BEGIN
  DBMS_RLS.DROP_POLICY(object_schema=>'valid_schema'
                      ,object_name=>'valid_table'
                      ,policy_name=>'valid_policy');
END;
/

Ruairi asked how you could disable automatic start of the Oracle Service for Oracle 11g on Windows 7 (a comment here). Ruairi also provided a nice Windows shell script that you can copy for starting and stopping the Oracle Service in his last comment.

The simplest way is to launch a command shell because I don’t want to provide all the navigation variations for different Windows versions.

Basically, you do that by clicking the Windows Start button and type cmd word in the run entry box. This launches a command session. Type the following from the prompt. It launches the Windows Services console in all relevant versions:

C:\> services.msc

Now you’ll see the Windows Services console. Navigate to the Oracle Service and right click on it. You choose Properties.

That will bring you to this screen. Click on the drop down for the Startup type and choose Manual. Click the OK button to complete the step. That’s it, the next time you start the machine the Oracle database won’t start automatically. You should do the same to the other Oracle Services.

If you don’t have a lot of memory and it’s a development machine, this makes a lot of sense.

I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.

Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.

I once used the comma-delimited tables (like everybody else), but now I try to always use the newer CROSS JOIN syntax. In both cases the range join is put in the WHERE clause. The new syntax uses an INNER JOIN and an ON clause to hold the range match. Examples of all are below.

Comma-delimited Filtered Cross Join

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c, transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Filtered CROSS JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c CROSS JOIN transaction_join t
WHERE    t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Range filtered INNER JOIN

1
2
3
4
5

SELECT   c.month_short_name
,        t.transaction_amount
FROM     calendar_join c INNER JOIN transaction_join t
ON      (t.transaction_date BETWEEN c.start_date AND c.end_date)
ORDER BY EXTRACT(MONTH FROM t.transaction_date);

Without an INDEX on the start and end date of the CALENDAR_JOIN table, the Oracle explain plan for all three queries is:

1
2
3
4
5
6
7
8
9
10

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 9
  SORT ORDER BY
    MERGE JOIN
      SORT JOIN
        TABLE ACCESS FULL TRANSACTION_JOIN
      FILTER
        SORT JOIN
          TABLE ACCESS FULL CALENDAR_JOIN

Naturally, an INDEX on the START_DATE and END_DATE columns improves performance. The results again for all three are the same.

1
2
3
4
5
6
7
8

Query Plan
----------------------------------------------
SELECT STATEMENT   Cost = 6
  SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN
      NESTED LOOPS
        TABLE ACCESS FULL TRANSACTION_JOIN
        INDEX RANGE SCAN DATE_RANGE

Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉

Comments are always welcome …

An interesting question came up today while discussing PL/SQL database triggers. Could you create a trigger on a MERGE statement, like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE MERGE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

The answer is, no you can’t. It’ll raise an ORA-04073 error if you attempt it, like this:

BEFORE MERGE OF last_name ON contact
             *
ERROR at line 2:
ORA-04073: COLUMN list NOT valid FOR this TRIGGER TYPE

The only supported DML events are INSERT, UPDATE, and DELETE. The following DML trigger works against a MERGE statement. After all a MERGE statement is nothing more than an INSERT or UPDATE statement.

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'CONTACT_MERGE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE contact_merge';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE contact_merge
( contact_id                  NUMBER
, member_id                   NUMBER       NOT NULL
, contact_type                NUMBER       NOT NULL
, first_name                  VARCHAR2(20) NOT NULL
, middle_name                 VARCHAR2(20)
, last_name                   VARCHAR2(20) NOT NULL
, created_by                  NUMBER       NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             NUMBER       NOT NULL
, last_update_date            DATE
, CONSTRAINT contact_merge_pk PRIMARY KEY(contact_id));
 
-- Create the trigger to enforce hyphenated last names..
CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/
 
-- Merge statement that violates business rule.
MERGE INTO contact_merge target
USING
( SELECT 2001 AS contact_id
  ,      1001 AS member_id
  ,      1001 AS contact_type
  ,'Catherine' AS first_name
  ,'' AS middle_name
  ,'Zeta Jones' AS last_name
  , 2 AS created_by
  , SYSDATE AS creation_date
  , 2 AS last_updated_by
  , SYSDATE AS last_update_date
FROM dual) SOURCE
ON (target.contact_id = SOURCE.contact_id)
WHEN MATCHED THEN
UPDATE SET target.last_updated_by = 3
WHEN NOT MATCHED THEN
INSERT VALUES
( SOURCE.contact_id
, SOURCE.member_id
, SOURCE.contact_type
, SOURCE.first_name
, SOURCE.middle_name
, SOURCE.last_name
, SOURCE.created_by
, SOURCE.creation_date
, SOURCE.last_updated_by
, SOURCE.last_update_date );
 
-- Query results.
SELECT first_name||DECODE(middle_name,NULL,' ',' '||middle_name||' ')||last_name AS full_name
FROM   contact_merge
WHERE  first_name = 'Catherine';

I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:

1
2
3
4
5

DELETE FROM common_lookup
WHERE  (common_lookup_table,common_lookup_column) IN
         (('TRANSACTION','PAYMENT_METHOD_TYPE')
         ,('TRANSACTION','TRANSACTION_TYPE')
         ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));

When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.

1
2
3

DELETE FROM common_lookup
WHERE  common_lookup_table IN ('TRANSACTION','RENTAL_ITEM')
AND    common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');

Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).

I guess we can hope that Oracle will implement the feature in MySQL now that they own it. 🙂 Let me know if I’ve missed some fabulous syntax alternative.

As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.

A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.

The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).

Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.

Oracle PL/SQL Assignments from SQL Expressions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Enable output printing.
SET SERVEROUTPUT ON SIZE 1000000
 
-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3));
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.

-- Define an anonymous block.
DECLARE
 
  -- Declare a source variable.
  lv_right_operand VARCHAR2(10) := 'March';
 
  -- Define a target variable for the assignment.
  lv_left_operand  VARCHAR2(10);
 
BEGIN
 
  -- Return the expression from a nested call parameter of the source variable.
  SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual;
 
  -- Print it to console.
  dbms_output.put_line('Output ['||lv_left_operand||']');
 
END;
/

That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.

MySQL PSM Assignment from SQL Expressions

First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Conditionally drop procedure.
SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement";
DROP PROCEDURE IF EXISTS assignit;
 
-- Create the proceudre
SELECT 'CREATE PROCEDURE assignit' AS "Statement";
 
DELIMITER $$
 
-- Define the procedure.
CREATE PROCEDURE assignit()
BEGIN
  /* Declare a source variable. */
  DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March';
 
  /* Define a target variable for the assignment. */
  DECLARE lv_left_operand  VARCHAR(3);
 
  /* Assign the modified value through the SELECT-INTO model. */
  SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand;
 
  /* Display assigned value. */
  SELECT lv_left_operand;
 
END;
$$
 
DELIMITER ;
 
-- Call the procedure.
CALL assignit();

The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.

Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.

As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

1. Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

2. Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( TABLE_NAME VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = TABLE_NAME
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

3. Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

4. You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( TABLE_NAME  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(TABLE_NAME, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content. 😉

My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.

This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the ON clause. The ON clause is where the MERGE statment matches the source query’s rows against the target table’s rows. The best join condition in a MERGE statement is one between a surrogate primary and foreign key column.

Error: ORA-30926
Text:  Unable TO GET a stable SET OF ROWS IN the SOURCE TABLES.
--------------------------------------------------------------------
Cause:  A stable SET OF ROWS could NOT be got because OF LARGE dml 
        activity OR a non-deterministic WHERE clause. 
 
Action: Remove any non-deterministic WHERE clauses AND reissue the dml.

The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422 error in Oracle.

As a rule, I’ve found that using the DISTINCT operator in the source SELECT statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE clause. It also happens when the SELECT clause returns date-time data types like SYSDATE instead of date data types. A date-time can be converted by using the TRUNCate function like this:

MERGE INTO TABLE_NAME target
USING
 (SELECT   ...
  ,        TRUNC(SYSDATE) AS creation_date
  FROM     ...) SOURCE
ON (target.primary_key_column = SOURCE.primary_key_column)
WHEN MATCHED THEN
UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date
WHEN NOT MATCHED THEN
INSERT VALUES
( column_list_of_values );

Hope this helps my students and others … 😉

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Procedure (or subroutines according to some documentation). You can see this post for how to create an equivalent solutions for MySQL functions.

In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related functions and procedures, and are a corollary to System.out.println() in Java.

Before you can see the output at the command-line in Oracle (that is if you’re not using SQL*Developer or Toad), you must set a SQL*Plus environment variable. These variables don’t exist in MySQL or SQL Server command-line tools because they never served the function of a report writer like SQL*Plus.

You enable output display in Oracle by setting this in SQL*Plus:

SQL> SET SERVEROUTPUT ON SIZE 1000000

You can test your anonymous or named block. Since MySQL doesn’t support anonymous named block, the examples using a trivial procedure that prints Hello World! (orginal, right ;-)).

1
2
3
4
5
6
7
8
9
10
11
12

-- Create a procedure in Oracle.
CREATE OR REPLACE PROCEDURE hello_world IS
BEGIN
  -- Print a word without a line return.
  DBMS_OUTPUT.put('Hello ');
  -- Print the rest of the phrase and a line return.
  DBMS_OUTPUT.put_line('World!');
END;
/
 
-- Call the procedure.
EXECUTE hello_world;

It’s seems useless to print the output because it should be evident. MySQL procedures are a bit different because there’s no OR REPLACE syntax. The equivalent to calling the DBMS_OUTPUT package procedures in MySQL is to simply select a string. Now you can do this with or without the FROM dual clause in MySQL, don’t we wish we could do the same thing in Oracle. 🙂

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE hello_world' AS "Statement";
DROP PROCEDURE IF EXISTS hello_world;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE hello_world()
BEGIN
  -- Print the phrase and a line return.
  SELECT 'Hello World!';
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL hello_world' AS "Statement";
CALL hello_world();

Originally, I tried to keep this short but somebody wanted an example in a loop. Ouch, loops are so verbose in MySQL. Since I was modifying this post, it seemed like a good idea to put down some guidelines for successful development too.

Guidelines for Development of Procedures

Declaration Guidelines

The sequencing of components in MySQL procedures is important. Unlike, PL/SQL, there’s no declaration block, declarations must be at the top of the execution block. They also must appear in the following order:

1. Variable declarations must go first, you can assign initial values with the DEFAULT keyword. While not required, you should:

2. After local variables and before handlers, you put your cursor definitions. You should note that MySQL doesn’t support explicit dynamic cursors, which means you can’t define one with a formal signature. However, you do have prepared statements and they mimic dynamic cursor behaviors.
3. Last in your declaration block, you declare your handler events.

Execution Guidelines

1. Variable assignments are made one of two ways:

2. You must assign values from cursors called in a loop into local variables when you want to use the results in nested SQL statements or loops.
3. You must reset looping variables, like the fetched control variable at the end of the loop to reuse the handler variable in subsequent loops.
4. You must assign values to local variables if you want to use them in the exception handler.
5. If you’ve started a transaction, don’t forget to COMMIT your work.

Exception Guidelines

1. Leave out the exception handler until you’ve tested all outcomes, and make sure you document them and add them as potential handlers.
2. When you deploy exception blocks, they’re the last element at the bottom of the exception block.
3. You should consider explicit exception handlers for each error unless the action taken is the same.
4. You should consider grouping all exception handlers when the action taken is the same.
5. You should include a ROLLBACK whenever you’ve performed two or more SQL statements that may modify data.

Below is an example for putting debug code inside a loop.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- Conditionally drop a sample table.
SELECT 'DROP TABLE IF EXISTS sample' AS "Statement";
DROP TABLE IF EXISTS sample;
 
-- Create a table.
CREATE TABLE sample
( sample_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sample_msg   VARCHAR(20));
 
-- Insert into sample.
INSERT INTO sample (sample_msg) VALUES
 ('Message #1')
,('Message #2')
,('Message #3');
 
-- Conditionally drop the procedure.
SELECT 'DROP PROCEDURE debug_loop' AS "Statement";
DROP PROCEDURE IF EXISTS debug_loop;
 
-- Reset the delimiter to write a procedure.
DELIMITER $$
 
-- Create a procedure in Oracle.
CREATE PROCEDURE debug_loop()
BEGIN
 
  /* Declare a counter variable. */
  DECLARE lv_counter INT DEFAULT 1;
 
  /* Declare local control loop variables. */
  DECLARE lv_sample_id  INT;
  DECLARE lv_sample_msg VARCHAR(20);
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE fetched INT DEFAULT 0;
 
  /* Declare a SQL cursor fabricated from local variables. */  
  DECLARE sample_cursor CURSOR FOR
    SELECT * FROM sample;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT all_or_none;
 
  /* Open a sample cursor. */
  OPEN sample_cursor;
  cursor_sample: LOOP
 
    /* Fetch a row at a time. */  
    FETCH sample_cursor
    INTO  lv_sample_id
    ,     lv_sample_msg;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_sample; END IF;
 
    -- Print the cursor values.
    SELECT CONCAT('Row #',lv_counter,' [',lv_sample_id,'][',lv_sample_msg,']') AS "Rows";
 
    -- Increment counter variable.
    SET lv_counter = lv_counter + 1;
 
  END LOOP cursor_sample;
  CLOSE sample_cursor;
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter back to a semicolon to work again.
DELIMITER ;
 
-- Call the procedure.
SELECT 'CALL debug_loop' AS "Statement";
CALL debug_loop();

This post certainly answers the student question. Hopefully, it also helps other who must migrate Oracle skills to MySQL. Since IBM DB2 has introduced a PL/SQL equivalent, wouldn’t it be nice if Oracle did that for MySQL. That is, migrate PL/SQL to MySQL. Don’t tell me if you think that’s a pipe dream, I’d like to hope for that change.

I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.

My first take at the post …

This is a lesson on basic aggregation in SQL. Aggregation in SQL means counting, adding, and grouping by results of counts or sums. Aggregation is a critical part of using the SQL language. At a basic level, aggregation includes the COUNT, SUM, AVERAGE, MAX, and MIN aggregation functions; and the ORDER BY, GROUP BY, and HAVING clauses.

You’ll find the setup scripts for these examples at the bottom of this blog page. The best way to use this page is to copy the setup code, run it in your database, and then test the examples as you work though them.

-- Oracle only SQL*Plus formatting command to smooth output display.
SET PAGESIZE 99
 
-- Query ordered by the sets and then the integers in ascending order.
SELECT * FROM ordinal ORDER BY 2, 4;

ORDINAL_ID LIST_SET             LIST_NAME  LIST_VALUE
---------- -------------------- ---------- ----------
         1 Value Set A          Zero                0
         2 Value Set A          One                 1
         3 Value Set A          Two                 2
         4 Value Set A          Three               3
         5 Value Set A          Four                4
         6 Value Set A          Five                5
         7 Value Set A          Six                 6
         8 Value Set A          Seven               7
         9 Value Set A          Eight               8
        10 Value Set A          Nine                9
        11 Value Set A
        12 Value Set B          Zero                0
        13 Value Set B          One                 1
        14 Value Set B          Two                 2
        15 Value Set B          Three               3
        16 Value Set B          Four                4
        17 Value Set B          Five                5
        18 Value Set B          Six                 6
        19 Value Set B          Seven               7
        20 Value Set B          Eight               8
        21 Value Set B          Nine                9
        22 Value Set B

SELECT COUNT(*) FROM ordinal;

  COUNT(*)
----------
        22

SELECT COUNT(list_set) FROM ordinal;

  COUNT(*)
----------
        22

SELECT COUNT(DISTINCT list_set) FROM ordinal;

  COUNT(*)
----------
         2

SELECT COUNT(ALL list_name) AS name_number
,      COUNT(DISTINCT list_value) AS value_number
FROM   ordinal;

NAME_NUMBER VALUE_NUMBER
----------- ------------
         20           10

SELECT SUM(ALL list_value) AS sum_all
,      SUM(DISTINCT list_value) AS sum_distinct
,      AVG(ALL list_value) AS avg_all
,      AVG(DISTINCT list_value) AS avg_distinct
FROM   ordinal;

   SUM_ALL SUM_DISTINCT    AVG_ALL AVG_DISTINCT
---------- ------------ ---------- ------------
        90           45        4.5          4.5

SELECT MIN(ALL list_value) AS min_all
,      MIN(DISTINCT list_value) AS min_distinct
,      MAX(ALL list_value) AS max_all
,      MAX(DISTINCT list_value) AS max_distinct
FROM   ordinal;

   MIN_ALL MIN_DISTINCT    MAX_ALL MAX_DISTINCT
---------- ------------ ---------- ------------
         0            0          9            9

1
2
3
4
5

SELECT   list_set AS grouping_by_column
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
GROUP BY list_set;

GROUPING_BY_COLUMN     COUNT(*) COUNT(LIST_VALUE)
-------------------- ---------- -----------------
VALUE SET A                  11                10
VALUE SET B                  11                10

1
2
3
4
5
6
7
8

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
VALUE SET A          Zero                       1                 1
VALUE SET A          Five                       1                 1
VALUE SET A          Three                      1                 1
VALUE SET A          Four                       1                 1
VALUE SET A          One                        1                 1
VALUE SET A          Two                        1                 1
VALUE SET A          Eight                      1                 1
VALUE SET A          Nine                       1                 1
VALUE SET A          Seven                      1                 1
VALUE SET A          Six                        1                 1
VALUE SET A                                     1                 0

1
2
3
4
5
6
7
8
9

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY list_value;

ORDER BY list_value
         *
ERROR at line 9:
ORA-00979: NOT a GROUP BY expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        COUNT(*)
,        COUNT(list_value)
FROM     ordinal
WHERE    list_set = 'Value Set A'
GROUP BY list_set
,        list_name
ORDER BY CASE
           WHEN list_name = 'Zero'  THEN 0
           WHEN list_name = 'One'   THEN 1
           WHEN list_name = 'Two'   THEN 2
           WHEN list_name = 'Three' THEN 3
           WHEN list_name = 'Four'  THEN 4
           WHEN list_name = 'Five'  THEN 5
           WHEN list_name = 'Six'   THEN 6
           WHEN list_name = 'Seven' THEN 7
           WHEN list_name = 'Eight' THEN 8
           WHEN list_name = 'Nine'  THEN 9
         END;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO   COUNT(*) COUNT(LIST_VALUE)
-------------------- ----------------- ---------- -----------------
VALUE SET A          Zero                       1                 1
VALUE SET A          One                        1                 1
VALUE SET A          Two                        1                 1
VALUE SET A          Three                      1                 1
VALUE SET A          Four                       1                 1
VALUE SET A          Five                       1                 1
VALUE SET A          Six                        1                 1
VALUE SET A          Seven                      1                 1
VALUE SET A          Eight                      1                 1
VALUE SET A          Nine                       1                 1
VALUE SET A                                     1                 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.row_count
,        ilv.column_count
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        COUNT(*) AS ROW_COUNT
         ,        COUNT(list_value) AS column_count
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO  ROW_COUNT COLUMN_COUNT
-------------------- ----------------- ---------- ------------
Value Set A          Zero                       1            1
Value Set A          One                        1            1
Value Set A          Two                        1            1
Value Set A          Three                      1            1
Value Set A          Four                       1            1
Value Set A          Five                       1            1
Value Set A          Six                        1            1
Value Set A          Seven                      1            1
Value Set A          Eight                      1            1
Value Set A          Nine                       1            1
Value Set A                                     1            0

SELECT   list_set AS grouping_by_not_null
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
,        MIN(list_value) AS ordinal_min
,        MAX(list_value) AS ordinal_max
FROM     ordinal
GROUP BY list_set;

GROUPING_BY_NOT_NULL ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------- ----------- ----------- -----------
VALUE SET A                   45         4.5           0           9
VALUE SET B                   45         4.5           0           9

1
2
3
4
5
6
7
8
9
10

SELECT    list_set AS grouping_by_not_null
,         list_name AS group_by_null_too
,         SUM(list_value) AS ordinal_sum
,         AVG(list_value) AS ordinal_avg
,         MIN(list_value) AS ordinal_min
,         MAX(list_value) AS ordinal_max
FROM      ordinal
WHERE NOT list_set = 'Value Set B'
GROUP BY  list_set
,         list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
VALUE SET A          Zero                        0           0           0           0
VALUE SET A          Five                        5           5           5           5
VALUE SET A          Three                       3           3           3           3
VALUE SET A          Four                        4           4           4           4
VALUE SET A          One                         1           1           1           1
VALUE SET A          Two                         2           2           2           2
VALUE SET A          Eight                       8           8           8           8
VALUE SET A          Nine                        9           9           9           9
VALUE SET A          Seven                       7           7           7           7
VALUE SET A          Six                         6           6           6           6
VALUE SET A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
,        ilv.ordinal_min
,        ilv.ordinal_max
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         ,        MIN(list_value) AS ordinal_min
         ,        MAX(list_value) AS ordinal_max
         FROM     ordinal
         WHERE    list_set = 'Value Set B'
         GROUP BY list_set
         ,         list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG ORDINAL_MIN ORDINAL_MAX
-------------------- ----------------- ----------- ----------- ----------- -----------
VALUE SET B          Zero                        0           0           0           0
VALUE SET B          One                         1           1           1           1
VALUE SET B          Two                         2           2           2           2
VALUE SET B          Three                       3           3           3           3
VALUE SET B          Four                        4           4           4           4
VALUE SET B          Five                        5           5           5           5
VALUE SET B          Six                         6           6           6           6
VALUE SET B          Seven                       7           7           7           7
VALUE SET B          Eight                       8           8           8           8
VALUE SET B          Nine                        9           9           9           9
VALUE SET B

1
2
3
4
5
6
7
8
9
10
11

SELECT   list_set AS grouping_by_not_null
,        list_name AS group_by_null_too
,        SUM(list_value) AS ordinal_sum
,        AVG(list_value) AS ordinal_avg
FROM     ordinal
WHERE    list_set = 'Value Set A'
HAVING   SUM(CASE
               WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
             END) > 0
GROUP BY list_set
,        list_name;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          Five                        5           5
Value Set A          Three                       3           3
Value Set A          One                         1           1
Value Set A          Nine                        9           9
Value Set A          Seven                       7           7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   ilv.grouping_by_not_null
,        ilv.group_by_null_too
,        ilv.ordinal_sum
,        ilv.ordinal_avg
FROM    (SELECT   list_set AS grouping_by_not_null
         ,        list_name AS group_by_null_too
         ,        SUM(list_value) AS ordinal_sum
         ,        AVG(list_value) AS ordinal_avg
         FROM     ordinal
         WHERE    list_set = 'Value Set A'
         HAVING   SUM(CASE
                        WHEN MOD(list_value,2) = 1 THEN list_value ELSE 0
                      END) > 0
         GROUP BY list_set
         ,        list_name) ilv LEFT JOIN ordinal o
ON       ilv.grouping_by_not_null = o.list_set
AND      ilv.group_by_null_too = o.list_name
ORDER BY o.list_value;

GROUPING_BY_NOT_NULL GROUP_BY_NULL_TOO ORDINAL_SUM ORDINAL_AVG
-------------------- ----------------- ----------- -----------
Value Set A          One                         1           1
Value Set A          Three                       3           3
Value Set A          Five                        5           5
Value Set A          Seven                       7           7
Value Set A          Nine                        9           9

-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'ORDINAL') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name = 'ORDINAL_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create the aggregation sample table and sequence.
CREATE TABLE ordinal
( ordinal_id  NUMBER
, list_set    VARCHAR2(20)
, list_name   VARCHAR2(5)
, list_value  NUMBER);
 
CREATE SEQUENCE ordinal_s1;
 
-- Seeding values in the aggregation sample table.
DECLARE
  -- Define local types.
  TYPE listg IS TABLE OF VARCHAR2(20);
  TYPE listn IS TABLE OF NUMBER;
  TYPE lists IS TABLE OF VARCHAR2(5);
 
  -- Declare local variables.
  groups LISTG := listg('Value Set A','Value Set B');
  labels LISTS := lists('Zero','One','Two','Three','Four','Five','Six','Seven','Eight','Nine');
  valuen LISTN := listn(0,1,2,3,4,5,6,7,8,9);
 
BEGIN
  -- Outer loop sets the group level.
  FOR i IN 1..groups.COUNT LOOP
    -- Inner loop sets the row level.
    FOR j IN 1..labels.COUNT LOOP
      INSERT INTO ordinal
      VALUES (ordinal_s1.NEXTVAL,groups(i),labels(j),valuen(j));
    END LOOP;
 
    -- Insert the null values for each group.
    INSERT INTO ordinal
    VALUES (ordinal_s1.NEXTVAL,groups(i),NULL,NULL);
  END LOOP;
  COMMIT;
END;
/
 
-- Commit the inserts.
COMMIT;

-- Conditionally drop the table.
SELECT 'DROP TABLE ordinal' AS Statement;
DROP TABLE IF EXISTS ordinal;
 
-- Create the table.
SELECT 'CREATE TABLE ordinal' AS Statement;
CREATE TABLE ordinal
( ordinal_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, list_set    VARCHAR(20)
, list_name   VARCHAR(5)
, list_value  INT UNSIGNED);
 
-- Seed the row values.
SELECT 'INSERT INTO ordinal' AS Statement;
INSERT INTO ordinal
VALUES
 (NULL,'Value Set A','Zero','0')
,(NULL,'Value Set A','One','1')
,(NULL,'Value Set A','Two','2')
,(NULL,'Value Set A','Three','3')
,(NULL,'Value Set A','Four','4')
,(NULL,'Value Set A','Five','5')
,(NULL,'Value Set A','Six','6')
,(NULL,'Value Set A','Seven','7')
,(NULL,'Value Set A','Eight','8')
,(NULL,'Value Set A','Nine','9')
,(NULL,'Value Set A',NULL,NULL)
,(NULL,'Value Set B','Zero','0')
,(NULL,'Value Set B','One','1')
,(NULL,'Value Set B','Two','2')
,(NULL,'Value Set B','Three','3')
,(NULL,'Value Set B','Four','4')
,(NULL,'Value Set B','Five','5')
,(NULL,'Value Set B','Six','6')
,(NULL,'Value Set B','Seven','7')
,(NULL,'Value Set B','Eight','8')
,(NULL,'Value Set B','Nine','9')
,(NULL,'Value Set B',NULL,NULL);
 
-- Commit the inserts.
COMMIT;