MacLochlainns Weblog

If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.

The error by itself isn’t very handy. This is a sample:

ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150)

You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.

The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.

=====================================
100130 17:34:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11, signal count 11
Mutex spin waits 0, rounds 80, OS waits 2
RW-shared spins 18, OS waits 9; RW-excl spins 1, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error in foreign key constraint of table sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 12958
Purge done for trx's n:o < 0 12956 undo n:o < 0 0
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3924
MySQL thread id 13, query id 800 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 0 12957, not started, OS thread id 3276
MySQL thread id 2, query id 797 localhost 127.0.0.1 student
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
136 OS file reads, 1112 OS file writes, 704 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 195193, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 9122680
Log flushed up to   0 9122680
Last checkpoint at  0 9122680
0 pending log writes, 0 pending chkp writes
678 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 58341818; in additional pool allocated 1481088
Dictionary memory allocated 78248
Buffer pool size   3008
Free buffers       2879
Database pages     128
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 126, created 2, written 425
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3408, state: waiting for server activity
Number of rows inserted 454, updated 6, deleted 0, read 6026
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.

Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.

You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.

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

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare local variables that are anchored to column data types.
  lv_title     item.item_title%TYPE;
  lv_subtitle  item.item_subtitle%TYPE;
  lv_rating    item.item_rating%TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the three local variables.
    FETCH c
    INTO  lv_title
    ,     lv_subtitle
    ,     lv_rating;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variables on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||lv_title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||lv_rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

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

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a local record data type, with explicit data types (you could use %TYPE here too).
  TYPE title_type IS RECORD
  ( title     VARCHAR2(60)
  , subtitle  VARCHAR2(60)
  , rating    VARCHAR2(8));
 
  -- Declare a local variable of the local record structure data type.
  item_record TITLE_TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

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

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

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

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block.
  FOR i IN c LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

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

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block with the static cursor in the definition.
  FOR i IN (SELECT   i.item_title AS title
	        ,        i.item_subtitle AS subtitle
	        ,        i.item_rating AS rating
	        FROM     item i) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

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

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Fetch the first record to put into context the cursor attributes, like %FOUND.
  FETCH c INTO item_record;
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (c%FOUND) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
    -- Fetch the second and subsequent rows of the cursor and assign it to a local variables.
    FETCH c
    INTO  item_record;
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

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

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (TRUE) LOOP
 
    -- Fetch the record set into a user-defined variable.
    FETCH c INTO item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

I’m sure this will help my students and hope it helps somebody else.

I was working on a code example for my database class, got in a hurry, and changed a table name without dropping the original table. Oops!

Naturally, I got this error message.

  LOB (administrator_photo) STORE AS admin_photo
                                     *
ERROR at line 5:
ORA-00955: name IS already used BY an existing object

The LOB segment name existed but why and where. It was in the table that I forgot to drop. This query find the latent table and column while illustrating the relationship (for my students and others) between an OBJECT_NAME and SEGMENT_NAME:

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');

Now, I can grab it later because it’ll happen again. 😉 At Samy mentioned in his comment you also have the option of using ALL_ or DBA_ views when you’re a DBA.

The rumor is that my database is hard, but I’ve always hoped it was fun and laid the foundation of success for my students. As I walked into class to give them their final exam, they had their jackets on, which isn’t uncommon for Rexburg, Idaho in December. However, that’s normal outside but unusual in the heated classroom. Then, they all took their jackets off to show their new t-shirts.

They thought it would be fun to post on the blog, so here it is. The shirts says:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    class= CIT 320;

It’s unfortunate that they missed the enclosing quote marks around the string literal. 😉 It should be like this:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    i.class= 'CIT 320';

Here’s to a great group of students who know how to read, write, and think SQL. Any openings out there for internships, please drop me a note.

Happy holidays!

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

1. Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.

-- This resets the default port so that it doesn't conflict with other environment.
EXECUTE dbms_xdb.SETHTTPPORT(8181);
 
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
 
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');

2. Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.

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

CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
/

3. Open a browser of your choice, and enter the following URL.

http://localhost:8181/sampledb/helloworld

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

4. Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:

SQL> @?/rdbms/admin/epgstat.sql

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+
 
Allow repository anonymous access?
----------------------------------
false
 
1 row selected.

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

SET SERVEROUTPUT ON
DECLARE
  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  lv_configxml := DBMS_XDB.cfg_get();
 
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           ( DBMS_XDB.cfg_get()
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()'
           , lv_value
           , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  -- Configure the element.
  DBMS_XDB.cfg_update(lv_configxml);
  DBMS_XDB.cfg_refresh;
END;
/

5. These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.

-- Unlock the user account.
ALTER USER anonymous ACCOUNT UNLOCK;
-- Ensure a password is assigned to the account so you can create a synonym later.
ALTER USER anonymous IDENTIFIED BY ANONYMOUS;

6. These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.

-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
 
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
 
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');

7. Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.

SQL> GRANT EXECUTE ON helloworld TO anonymous;

8. Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)

SQL> CREATE SYNONYM helloworld FOR student.helloworld;

SQL> ALTER USER anonymous IDENTIFIED BY NULL;

9. Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.

http://localhost:8181/db/helloworld

While working on one of the labs, my students wanted a quick way to seed their CALENDAR table. Here’s a small script to seed twenty years of a calendar.

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'MOCK_CALENDAR') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE mock_calendar
( short_month  VARCHAR2(3)
, long_month   VARCHAR2(9)
, start_date   DATE
, end_date     DATE );
 
-- Seed the table with 10 years of data.
DECLARE
  -- Create local collection data types.
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  long_month  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');
 
  -- Declare base dates.
  start_date DATE := '01-JAN-79';
  end_date   DATE := '31-JAN-79';
 
  -- Declare years.
  years      NUMBER := 20;
 
BEGIN
 
  -- Loop through years and months.
  FOR i IN 1..years LOOP
    FOR j IN 1..short_month.COUNT LOOP
      INSERT INTO mock_calendar VALUES
      ( short_month(j)
      , long_month(j)
      , add_months(start_date,(j-1)+(12*(i-1)))
      , add_months(end_date,(j-1)+(12*(i-1))));
    END LOOP;
  END LOOP;
 
END;
/
 
-- Format set break for output.
SET PAGESIZE 16
 
-- Format column output.
COL short_month FORMAT A5 HEADING "Short|Month"
COL long_month  FORMAT A9 HEADING "Long|Month"
COL start_date  FORMAT A9 HEADING "Start|Date"
COL end_date    FORMAT A9 HEADING "End|Date" 
 
SELECT * FROM mock_calendar;

The output will look like this with a page break by year:

Short Long      Start     End
Month Month     Date      Date
----- --------- --------- ---------
JAN   January   01-JAN-79 31-JAN-79
FEB   February  01-FEB-79 28-FEB-79
MAR   March     01-MAR-79 31-MAR-79
APR   April     01-APR-79 30-APR-79
MAY   May       01-MAY-79 31-MAY-79
JUN   June      01-JUN-79 30-JUN-79
JUL   July      01-JUL-79 31-JUL-79
AUG   August    01-AUG-79 31-AUG-79
SEP   September 01-SEP-79 30-SEP-79
OCT   October   01-OCT-79 31-OCT-79
NOV   November  01-NOV-79 30-NOV-79
DEC   December  01-DEC-79 31-DEC-79

Oracle™ spoils us for other databases. We begin to expect too much of other database products. At least, that’s the way that I felt while working comparative syntax samples out for my students. I assumed wrongly that all strings would compare based on case sensitive strings. I found that Oracle does that, but MySQL and SQL Server don’t do that with an ordinary = (equals) comparison operator.

Oracle

The = (equals) symbol compares case sensitive strings. The following query returns nothing because a title case string isn’t equal to an uppercase string (databases, as they should be).

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

The query only resolves when the strings are case sensitive matches, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'String';

MySQL

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (a case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol. You may also chuckle that you need the DUAL pseudo table to make this work when there’s a WHERE clause, covered in yesterday’s blog.

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE 'String' = 'STRING';

You can make it case sensitive by using the strcmp function, like this:

SQL> SELECT 'Truth' AS OUTPUT FROM dual WHERE strcmp('String','STRING');

You can also make it case sensitive by using collation, which is the ability to shift it’s character set. Unfortunately, MySQL doesn’t support casting to a binary string, so you must apply a different character set for the equality comparision.

SQL> SELECT 'Truth' AS OUTPUT
  2> FROM dual WHERE 'String' COLLATE latin1_bin = 'STRING' COLLATE latin1_bin;

SQL Server

The = (equals) symbol doesn’t compare case sensitive strings. The following query returns Truth (another case sensitive irony) because a title case string is equal to an uppercase string when you make the comparison with an equality symbol.

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('STRING' AS VARBINARY);
3> GO

You can make SQL Server resolve case sensitive strings by casting both of the strings to VARBINARY data types, like this:

1> SELECT 'Truth' AS Output
2> WHERE  CAST('String' AS VARBINARY) = CAST('String' AS VARBINARY);
3> GO

There is a clear lack of portability for basic comparison operations. I think its time that folks drop that time worn database agnosticism line because all it means is I don’t want to use that other database. I’ve heard the line too often with regard to PL/SQL, but oddly not about MySQL’s stored procedures or Microsoft’s T-SQL. Maybe I’m an Oracle bigot but it sure seems more ANSI standard like to me to compare strings with a simple = (equals) operator.

All this time I thought MySQL was so progressive by eliminating the DUAL pseudo table when selecting a string literal. I also thought it interesting that they also supported selecting a string literal from the DUAL pseudo table. While playing around with examples for my students, I found out that there is a use case when MySQL requires you to use the DUAL pseudo table.

You must use the DUAL pseudo table when you select a literal that includes a WHERE clause. For example, this fails:

mysql> SELECT 'Truth' WHERE 'STRING1' >= 'string1';

with the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WHERE 'string1' >= 'STRING1'' at line 1

It’s complaining that you haven’t designated a table. However, this succeeds when you add the FROM dual and it does case promotion or demotion to resolve whether the strings are case insensitive matches.

mysql> SELECT 'Truth' FROM dual WHERE 'STRING1' >= 'string1';

It looks so much like Oracle. 😉 The only difference is that Oracle does compare them on the basis of their ASCII values, and would return no rows for this query.

One of my students asked how you validate the foreign keys in a MySQL database. First off, this only works if the database engine supports referential integrity (the fancy word for foreign keys as database level constraints). InnoDB and Falcon support referential integrity. The answer can be found by leveraging the data catalog in the INFORMATION_SCHEMA.

Here’s the query:

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name;

It’s a bit different then the USER_CONSTRAINTS and USER_CONS_COLUMNS views in Oracle, which are covered in this prior post.

I recently ran across another interesting detail on MySQL foreign keys creation and removal that has to do with the case sensitivity of constraints. If you create the constraint in lower case and then try to drop the foreign key constraint in upper case, you may encounter the following error:

ERROR 1025 (HY000): Error on rename of '.\database_name\table_name' to '.\database_name\#sql2-79c-1' (errno: 152)

The reason appears to be that MySQL can’t find the table with the constraint name, and therefore throws an error that appears related to failure writing the new structure. It can be misleading.

I always tell my students how to protect their Oracle credentials but hesitated to do the same for MySQL because it involves Windows batch shell scripting (albeit simple one line scripting). Oracle is nice and clean about hiding credentials. If you only provide the USER name at the command prompt, then your PASSWORD is never displayed. Only the user name is displayed in the DOS command prompt session’s window frame. You do that in Oracle with a student user name, as follows:

C:\Data\Oracle> sqlplus student
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 09:20:25 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:

MySQL isn’t quite so convenient as a command-line tool. You must enter the -u or --user followed by the user name and -p or --password followed by the password when connecting to the database. For example, this shows a typical command prompt window frame:

As noted by Stephen in his comment, you can also mimic Oracle by using the following syntax:

C:\Data\MySQL> mysql -ustudent -p
Enter password:

Unless you use that syntax, anybody walking up can see your USER and PASSWORD in clear text. Not exactly the best security practice, eh? You can also suppress the USER name from displaying by writing a small batch file with your text editor or at the command-line, like this:

C:\Data\MySQL>COPY CON mysqlcmd.bat
@mysql -ustudent -p
^Z

While you’ll need to enter your password because you really don’t want to store that in a file. The @ symbol suppresses echo of the command, which means you won’t display your USER name or PASSWORD when you start the mysql client tool. It will only display the batch file name and the welcome message:

Hope this helps you when you’re working at the MySQL command-line. However, I’m often stunned by how few use it. They prefer phpMyAdmin or Quest’s Toad for MySQL.