MacLochlainns Weblog

Finally, I got to install Oracle Database 11g Express Edition (XE) tonight. Unfortunately, I ran into an error during the installation. A quick Google search landed me in the forum, where it gave me the impression it wouldn’t work on Windows 7, 64-bit. However, I’m tenacious with problems like this. Here’s the screenshot for the error:

The text from the image is noted below to help folks find the solution.

The installer is unable to instantiate the file C:\Users\McLAUGH~1\AppData\Local\Temp\{078E83D7-3FCC-4A72-903B-995C7CE44681}\KEY_XE.reg. The file does not appear to exist.

The physical file isn’t missing, what failed to happen was that the installer didn’t write a Registry Key’s Data value. You can add the missing registry key’s Data value, and then click OK on the Error dialog box to continue the installation. (Replying to Andrea, I’ve provided screen shots on editing the Registry in a comment below.) It will succeed, at least it succeeded for me on Windows 7 64-bit. Rather than use abbreviations like the forum entry, the missing key is found here in the Windows Registry:

- HKEY_CLASSES_ROOT\Installer\Products\266B7A503A089BE4EAD1986A429434C1\SourceList\Media

A screenshot shows that the Data value of the key wasn’t populated by the MSI (Microsoft Software Installer), the arguments remain for a script to populate:

You need to add the message value from your Error dialog as Data value of the Name entry 1, like this screenshot shows:

Since I believe the only things that should change are your Administrator’s hostname and the key value (type in the one that shows in the your dialog box), here’s the text you’ll need to enter:

C:\Users\hostname\AppData\Local\Temp\{078E83D7-3FCC-4A72-903B-995C7CE44681}\

This fix worked for me, and I hope the same holds true for you.

It’s finally available for download, and the documentation is posted too. However, there’s no official word on the next release of the Oracle database.

I’ve posted Step-by-step Installation Instructions and ran into a registry problem on Window 7 64-bit, the instructions for fixing the registry and salvaging the in-progress installation are in this blog post.

A couple years ago I showed how to get the last day of the month and count past leap year. Somebody asked how to get the first day of the current month.

The two easiest ways I know how to do that in Oracle is to combine the ADD_MONTHS and LAST_DAY functions, like:

SELECT
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1),'DD-MON-YY HH24:MI') AS "One Way"
, TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)),'DD-MON-YY HH24:MI') AS "Another Way"
FROM  dual

It gives you a truncated datetime value, as you can see:

One Way         Another Way
--------------- ---------------
01-JUN-11 00:00 01-JUN-11 00:00

As the comments point out, the following is best for getting the first day of the month:

SELECT TO_CHAR(TRUNC(SYSDATE,'MM'),'DD-MON-YYYY HH24:MI') FROM dual;

You can also return the first day of the year like this:

SELECT TO_CHAR(TRUNC(SYSDATE,'YY'),'DD-MON-YYYY HH24:MI') FROM dual;

If you’re planning to do this in an application and Oracle Database 11g, you may want to write it as a deterministic function, like this:

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION first_day
( date_in DATE ) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TRUNC(date_in,'MM');
END;
/

Naturally, you can also do this with the EXTRACT function but it would get very complex quickly. Hope this answers the question.

It started as a smart remark, “The DELETE FROM statement always removes one or more rows from a table when any are found, except when the table isn’t a table.” That exception is true when the table isn’t a schema-level table but a nested table in an Oracle database. Then, the DELETE FROM statement may remove a row from the table or a row from collection held by a row.

Naturally, everybody would like an example. It took more than a couple steps to work one up and the Oracle documentation isn’t very helpful on the topic.

1. Create a SQL nested table collection type of 30-character variable length strings:

1
2

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/

2. Create a SQL user-defined object type for addresses:

1
2
3
4
5
6
7

CREATE OR REPLACE TYPE address_type AS OBJECT
( address_id     NUMBER
, street_address STREET_LIST
, city           VARCHAR2(30)
, state          VARCHAR2(2)
, postal_code    VARCHAR2(10));
/

3. Create a SQL nested table of the user-defined object type for addresses:

1
2

CREATE OR REPLACE TYPE address_list AS TABLE OF address_type;
/

4. Create a schema-level table that uses the user-defined nested table of a user-defined object type, and a SEQUENCE for the table:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE employee
( employee_id    NUMBER
, first_name     VARCHAR2(20)
, middle_name    VARCHAR2(20)
, last_name      VARCHAR2(20)
, home_address   ADDRESS_LIST)
  NESTED TABLE home_address STORE AS address_table
   (NESTED TABLE street_address STORE AS street_table);
 
CREATE SEQUENCE employee_s1;

5. Insert data into the table:

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

INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Sam'
,'Yosemite'
, address_list(
    address_type( 1
                , street_list('1111 Broadway','Suite 322')
                ,'Oakland'
                ,'CA'
                ,'94612')
  , address_type( 2
                , street_list('1111 Broadway','Suite 525')
                ,'Oakland'
                ,'CA'
                ,'94612')));
 
INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Bugs'
,'Bunny'
, address_list(
    address_type( 1
                , street_list('21 Rodeo Drive','Suite 1000')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')
  , address_type( 2
                , street_list('21 Rodeo Drive','Suite 2010')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')));

6. Create a SQL view to explode nesting into a meaningful data set:

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

CREATE OR REPLACE VIEW explode_employee AS
SELECT   e.employee_id
,        e.first_name || ' ' || e.last_name AS full_name
,        st.address_id
,        sa.column_value AS st_address
,        st.city
,        st.state
,        st.postal_code
FROM     employee e CROSS JOIN
         TABLE(e.home_address) st CROSS JOIN
         TABLE(street_address) sa
ORDER BY 1, 2, 3;

7. Format output and query view:

1
2
3
4
5
6
7
8
9
10
11

-- SQL*Plus formattting
COLUMN employee_id FORMAT 999 HEADING "ID|EMP"
COLUMN full_name   FORMAT A16 HEADING "Full Name"
COLUMN address_id  FORMAT 999 HEADING "ID|UDT"
COLUMN st_address  FORMAT A16 HEADING "Street Address"
COLUMN city        FORMAT A14 HEADING "City"
COLUMN state       FORMAT A5  HEADING "State"
COLUMN postal_code FORMAT A5  HEADING "Zip|Code"
 
-- Query all from view.
SELECT * FROM explode_employee;

  ID                    ID                                       Zip
 EMP Full Name         UDT Street Address   City           State Code
---- ---------------- ---- ---------------- -------------- ----- -----
   1 Sam Yosemite        1 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        1 Suite 322        Oakland        CA    94612
   1 Sam Yosemite        2 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        2 Suite 525        Oakland        CA    94612
   2 Bugs Bunny          1 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          1 Suite 1000       Beverly Hills  CA    90210
   2 Bugs Bunny          2 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          2 Suite 2010       Beverly Hills  CA    90210

8. UPDATE statement against a nested table:

1
2
3
4
5

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.city = 'Fremont'
WHERE e.address_id = 1;

9. UPDATE statement against a nested table and scalar nested table within it:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.street_address = street_list('1111 Broadway','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

10. DELETE statement against a nested table, which only removes the nested row:

1
2
3
4

DELETE FROM TABLE (SELECT e.home_address
                   FROM   employee e
                   WHERE  e.employee_id = 1) ha
WHERE  ha.address_id = 1;

As always, hope this helps somebody looking for syntax or approach.

While reviewing some material and explaining list partitioning in MySQL, I heard a rumor that sounded false. The rumor was that you can’t partition on anything other than the primary key column. That’s untrue, you can partition on another column provided it’s an integer column.

It appears the culprit that led to the rumor is a misunderstanding around ERROR 1503, which returns the following:

ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function

The problem can be recreated by defining a table with an inline primary key constraint instead of an INDEX on the auto incrementing column. Here’s an example of how to create the error message:

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE franchise
( franchise_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20))
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The table should be defined without the inline PRIMARY KEY constraint on the auto incrementing column when you want to partition on another column. An index on the auto incrementing column doesn’t conflict with primary key rules. The correct syntax for a list partition would be:

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

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, INDEX idx (franchise_id)) engine=innodb
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The same problem can manifest itself on range partitioning. The fix is the same and here’s a code example:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

The MySQL 5.6 Partitioning Keys, Primary Keys, and Unique Keys article is the key reference. It’s unfortunate that MySQL can’t partition on something other than an integer with PARTITION BY LIST syntax (check the changes in MySQL 5.6 online documentation). As you’ll notice in my more recent comment back to Tim, you can when you change the SQL phrase to PARTITION BY LIST COLUMNS. MySQL now has another feature that the Oracle Database 11g supports, that is list partitioning on variable-length string columns.

There are a few required changes but here’s a working example. The index must work against the auto incrementing column. The example comes from my reply to Tim, who raised a great question.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, KEY idx (franchise_id)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

While that works, the better approach creates two unique constraints. One unique constraint on the natural key of the franchise_number and franchise_name, which serves to optimize access to data sets looking data up in the table based on a natural key search. Another unique constraint on the surrogate (auto incrementing column) and the two natural key columns, which optimizes joins from foreign keys to the partitioned table’s primary key.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, UNIQUE ink (franchise_number, franchise_name)
, UNIQUE enk (franchise_id, franchise_number, franchise_name)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

Somebody asked for a composite partitioning (MySQL terminology is subpartitioning) example, so here it is:

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

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, store_id         INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
  SUBPARTITION BY HASH(store_id) SUBPARTITIONS 4
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

Hope this helps those researching ERROR 1503 on list or range partitioning.

While preparing for next week’s classes, I ran across a new future feature of MySQL – the virtual column. It appears, according to the article, that MySQL will have virtual columns in MySQL 6.

The syntax is virtually identical to that found in Oracle databases. For example, here’s what suppose to work in the future:

1
2
3
4
5

CREATE TABLE salary
( salary_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, salary       DOUBLE NOT NULL
, bonus        DOUBLE
, compensation DOUBLE AS (salary + bonus));

It’s virtually (excuse the pun) how it would work in Oracle Database 11g. I wonder what else we see move over into MySQL. Just a note, virtual columns moved from Oracle RDB (a prior acquisition from Digital Equipment Corporation in 1996) to the Oracle Database 11g.

Equivalent syntax in Oracle Database 11g would be like this for concatenation:

1
2
3
4
5

CREATE TABLE employee
( employee_id NUMBER
, first_name  VARCHAR2(20)
, last_name   VARCHAR2(20)
, full_name   VARCHAR2(41) AS (first_name || ' ' || last_name));

And like this for a math operation (like the one noted above for a future release of MySQL):

1
2
3
4
5

CREATE TABLE salary
( salary_id    NUMBER       CONSTRAINT pk_salary PRIMARY KEY
, salary       NUMBER(15,2) CONSTRAINT nn_salary_01 NOT NULL
, bonus        NUMBER(15,2)
, compensation NUMBER(15,2) AS (salary + bonus));

Finally, the Oracle 11g XE Beta has arrived. Actually, it appears I missed it when Justin announce it. Anyway, it is available for download here. It’s now complete and downloadable, the links are here.

A student encountered a connection problem with a PHP application that failed to resolve to the database. The steps to validate this are to check the PHP credentials, which are in this older post. Once you’ve done that, you should do:

1. Check the tnsnames.ora file contents, they should have a hostname value not an IP address. When you’ve not set your machine hostname in Microsoft Windows host file (mine in the example is: McLaughlin7x64), Oracle opts for the IP address.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

2. Check if the Oracle TNS (Transparent Network Substrate) validates with this command:

tnsping xe

3. If the prior step fails, check to see if you’re listener is running and that it’s configuration file looks more or less like this sample. If it doesn’t, you should edit this listener.ora file and start the listener process through the services dialog.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    )
  )

4. You should then be able to connect like this:

sqlplus username/password@xe

Hope this helps a few people.

Writing an anonymous block to conditionally drop tables and sequences got very old. I figured it was time to simply put a procedure that would simplify it. Avoiding repetition was important too, so it supports an invoker rights model. This mimics the IF EXISTS syntax available in MySQL.

You need to create this as the SYSTEM user and then grant execute permission on the procedure to PUBLIC. Then, you need to create a couple public synonyms.

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

-- Create a procedure to simplify dropping objects.
CREATE OR REPLACE PROCEDURE drop_ifexists
( pv_type   VARCHAR2
, pv_table  VARCHAR2 ) AUTHID CURRENT_USER IS
 
  -- String for DDL command.
  sql_text  VARCHAR2(2000);
 
  -- Declare a parameterized cursor.
  CURSOR find_object
  ( cv_type   VARCHAR2
  , cv_table  VARCHAR2 ) IS
    SELECT   uo.object_name
    ,        uo.object_type
    FROM     user_objects uo
    WHERE    uo.object_name = UPPER(cv_table)
    AND      uo.object_type = UPPER(cv_type);
 
BEGIN
 
  -- Open the cursor with the input variables.
  FOR i IN find_object(pv_type, pv_table) LOOP
 
    -- Check for a table object and append cascade constraints.
    IF i.object_type = 'TABLE' THEN
      sql_text := 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      sql_text := 'DROP '||i.object_type||' '||i.object_name;
    END IF;
 
    -- Run dynamic command.
    EXECUTE IMMEDIATE sql_text;
 
  END LOOP;
 
END drop_ifexists;
/

After creating the procedure in the SYSTEM user schema, you should run these Data Control Language (DCL) commands:

GRANT EXECUTE ON drop_ifexists TO PUBLIC;
CREATE PUBLIC SYNONYM drop_ifexists FOR system.drop_ifexists;

Assuming you have a table named MESSAGE, you can call the drop_ifexists procedure to conditionally delete it as follows below. The same procedure works with any object in a user’s schema.

EXECUTE drop_ifexists('table','message');

The only problem comes with types because they can have dependents. When they have dependents you can’t drop them until you’ve removed all the dependents. There are two types of dependents types and collection types.

Since discovering those may be difficult for new users, here’s another invoker rights function to discover user defined types. You should note that you’ll need to create the types before you try to compile the code (create the stored program in the Oracle database).

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

-- Create a SQL structure.
CREATE OR REPLACE TYPE dependent_type_obj IS OBJECT
( dependent_type  VARCHAR2(10)
, type_name       VARCHAR2(30));
/
 
-- Create a SQL collection of a user-defined data structure.
CREATE OR REPLACE TYPE dependent_type_tab IS TABLE OF dependent_type_obj;
/
 
-- Create a procedure to discover type dependents.
CREATE OR REPLACE FUNCTION type_dependents
( pv_type_name  VARCHAR2 ) RETURN dependent_type_tab AUTHID CURRENT_USER IS
 
  -- Declare a counter for the collection variable.
  c NUMBER := 1;
 
  -- Declare a return type variable.
  list DEPENDENT_TYPE_TAB := dependent_type_tab();
 
  -- Declare a parameterized cursor for dependent types.
  CURSOR find_dependent_types
  ( cv_type  VARCHAR2 ) IS
    SELECT   'STANDALONE' AS dependent_type
    ,        at.type_name
    FROM     all_types at INNER JOIN all_type_attrs ata
    ON       at.type_name = ata.type_name
    WHERE    at.owner = USER
    AND      ata.attr_type_name = UPPER(cv_type)
    UNION ALL
    SELECT   'COLLECTION' AS dependent_type
    ,        act.type_name
    FROM     all_types at INNER JOIN all_coll_types act
    ON       at.type_name = act.elem_type_name
    WHERE    at.owner = USER
    AND      act.elem_type_name = UPPER(cv_type); 
 
BEGIN
 
  -- Loop through all returns and add them to the return collection.
  FOR i IN find_dependent_types(pv_type_name) LOOP
    list.EXTEND;
    list(c) := dependent_type_obj(i.dependent_type, i.type_name);
	c := c + 1;
  END LOOP;
 
  -- Return the list.
  RETURN list;
END type_dependents;
/

You should also make the following grants and synonyms:

GRANT EXECUTE ON system.type_dependents TO PUBLIC;
CREATE PUBLIC SYNONYM type_dependents FOR system.type_dependents;
GRANT ALL ON system.dependent_type_obj TO PUBLIC;
GRANT ALL ON system.dependent_type_tab TO PUBLIC;

Then, you can call the type_dependents function to find any dependent user-defined data types. If so, you must drop them from the bottom or most specialized straight up the type tree to the root node data type.

SELECT *
FROM   TABLE(type_dependents('&type_name'));

Somebody raised the question about writing stored functions while we were discussing scalar subqueries against COMMON_LOOKUP tables. Common look up tables store collections of possible <OPTION> elements. They’re basically generalized tables that contain a set of smaller tables, where the row sets make up a list of unique values.

When you write a function that must return one and only one row, the SELECT-INTO syntax is ideal in Oracle because it automatically raises an exception when the query returns no row or two or more rows. MySQL doesn’t raise an automatic exception when a SELECT-INTO fails to return a row but it does raise an ERROR 1722 when two or more rows are found.

Here are sample implementations written in Oracle’s PL/SQL and MySQL’s SQL/PSM languages. If you’re an Oracle developer and new to MySQL, the biggest oddity may be the delimiter, or it may be the sizing of formal parameters. Hopefully, its not the lack of formal declaration and exception blocks in SQL/PSM. For those coming from MySQL, the OR REPLACE command or dynamic sizing of formal parameter list values are the big changes.

Oracle PL/SQL Function

The Oracle PL/SQL function is very straightforward and helped by pre-defined exceptions for both the no data found and too many rows found errors. Formal parameters inherit their physical size at run time from the calling scope program.

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

-- Create or replace existing function with new implementation.
CREATE OR REPLACE FUNCTION get_common_lookup_id
( pv_table   VARCHAR2
, pv_column  VARCHAR2
, pv_type    VARCHAR2 )
RETURN NUMBER IS
 
  -- Declare a return variable.
  lv_return  NUMBER;
 
BEGIN
 
  -- Query data and assign it to a local variable.
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  -- Return the value found.
  RETURN lv_return;
 
EXCEPTION
 
  -- Handle errors.
  WHEN NO_DATA_FOUND THEN
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    RAISE;
 
END;
/

MySQL SQL/PSM Function

The lack of an implicitly raised exception when a SELECT-INTO structure fails to return a row adds several steps to a SQL/PSM function. You must define a custom exception and an if-block to raise the exception, which is trigger by returning a null value into the lv_return local variable.

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

/* Conditionally drop the function before trying to create it. */
DROP FUNCTION IF EXISTS get_common_lookup_id;
 
/* Change the delimiter to write a stored procedure into the database. */
DELIMITER $$
 
/* Create a function that replaces a scalar subquery. */
CREATE FUNCTION get_common_lookup_id
( pv_table   VARCHAR(30)
, pv_column  VARCHAR(30)
, pv_type    VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a return variable. */
  DECLARE lv_return  INT UNSIGNED;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE no_data_found CONDITION FOR SQLSTATE '99001';
 
  /* Query data and assign it to a local variable. */
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  /* The local variable is only null when no rows are returned by the implicit cursor. */
  IF lv_return IS NULL THEN
    SIGNAL no_data_found SET MESSAGE_TEXT = 'Result was no rows found.';
  END IF;  
 
  /* Return the value found. */
  RETURN lv_return;
 
END;
$$
 
/* Reset the delimiter to the default to run other programs. */
DELIMITER ;

Hope this help solve your problems.