MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘sql’ Category

Placement over substance

with 5 comments

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 remaining built in functions that have specialized parsing and resolution markers.

A simplified version of the code that raises the error follows. As you’ll notice the opening parenthesis for the FORMAT and SUM function have intervening white space and a line return.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM
             (CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

Based on the comments, the SQL_MODE is:

mysql> SELECT @@version, @@sql_mode;
+-----------+----------------------------------------------------------------+
| @@version | @@sql_mode                                                     |
+-----------+----------------------------------------------------------------+
| 5.5.23    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

It raises the following error:

ERROR 1630 (42000): FUNCTION studentdb.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual

Moving ONLY the opening parenthesis to the end of the SUM keyword (or removing the line return and white space from between the SUM keyword and opening parenthesis) prevents the error but it would be more convenient if it supported both approaches. It seems odd that an intervening line return and white space for the SUM function raises an exception while the same intervening line return and white space doesn’t raise an exception for the FORMAT function. It strikes me the parser should support both or reject both. Here’s the fixed code that works without enabling the IGNORE_SPACE SQL Mode option.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM(
              CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

As noted by the comments, adding the IGNORE_SPACE to the SQL_MODE lets both queries work without moving the open parenthesis. You can do that in a session with the following syntax (which is covered in an older post):

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));

Hope this helps folks…

Written by maclochlainn

July 3rd, 2012 at 4:10 pm

Derived Table Aliases

without comments

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require aliases for inline views (what Oracle calls MySQL derived tables and Microsoft calls a Common Table Expression (CTE)).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) dt;

MySQL requires that every derived table have its own alias. This make sense when you think about query optimization engines work, but that’s a story for a much longer post.

You can find more about inline views, derived tables, and common table expressions in this older post of mine. This post was promised so that future students can simply google and answer rather than ask me or a class tutor.

Written by maclochlainn

June 23rd, 2012 at 1:03 am

Single Wildcard Operator

with 2 comments

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE parameter is set to OFF.

The SQL*Plus ESCAPE parameter default value is a backslash. and when the ESCAPE parameter is enabled a statement like this raises the following exception:

ERROR at line 3:
ORA-01425: escape CHARACTER must be CHARACTER string OF LENGTH 1

If you drop the ESCAPE '\' clause with the ESCAPE parameter enabled it will return all rows from the table not just those strings with an underscore in the string. Hope this helps if need to look for an underscore in a table.

Written by maclochlainn

June 22nd, 2012 at 12:41 am

Overloading Procedures

with 10 comments

A student asked, “Can you successfully overload MySQL stored procedures, like PL/SQL does in stored packages?” I said, “MySQL doesn’t formally support overloading stored procedures, and doesn’t support packages like Oracle 11g. You can, however, mimic (or fake) overloading with prepared statements. It requires a careful combination of stored procedures and session variables.”

Unfortunately, they asked for an example. So I wrote this set of code. It uses queries as the dynamic statements to make it simple to test but you can substitute INSERT, UPDATE, or DELETE statements. I didn’t provide type validation in the example, which would be required for dates or date-timestamps.

It’s presented in steps with test programs at each level. If you see an opportunity to improve on any of the example components, leave a comment. As to whether I’d implement this in production code, the answer is no but I believe all possibilities should at least be explored.

Step #1

The first step requires defining a stored procedure that sets a session variable. It’s fairly straight forward, but remember to change those DELIMITER values when testing. (As a note, you don’t require a semicolon after the END statement of a stored function or procedure in MySQL. You only need the redefined DELIMITER, which is $$ in these examples.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DELIMITER $$
 
-- Drop the procedure if it exists.
DROP PROCEDURE IF EXISTS set_session_var$$
 
-- Create the procedure.
CREATE PROCEDURE set_session_var
( pv_session_name   VARCHAR(32767)
, pv_session_value  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Insert statement with auto commit enabled. */
  SET @SQL := concat('SET',' ','@',pv_session_name,' := ','?');
  SELECT @SQL AS "SQL String";
  PREPARE stmt FROM @SQL;
  SET @sv_session_value := pv_session_value;
  EXECUTE stmt USING @sv_session_value;
  DEALLOCATE PREPARE stmt;
 
END;
$$

The test case for the set_session_var is:

CALL set_session_var('sv_filter1','One');
CALL set_session_var('sv_filter2','Two');
SELECT @sv_filter1, @sv_filter2;

Important note: If you call this from another stored program you can’t use the stmt variable name in the calling program’s scope.

Step #2

The second step exists because you can’t pass arrays in MySQL (a restriction that also exists for T-SQL in Microsoft SQL Server). You have to pass a varying list of parameters as a serialized string. This is often called flexible parameter passing, which many PHP programmers leverage outside the database (flexible parameter passing is covered in this PHP tutorial I wrote a while back).

The deserialize example does three things:

  • Takes a serialized set of parameters and deserializes the parameters into a set of name-value pairs, which are stored by their name as session level variables.
  • It records the number of name-value pairs written as session variables in a temporary table. The use of a temporary table is required because a MySQL stored function can’t hold a dynamic statement or a call to a procedure with a dynamic statement.
  • The script checks for the existence of the table during execution and truncates it when found. It creates the table when it isn’t found. While it would be ideal to use a temporary table, they’re not disclosed in the the information_schema and therefore more tedious to manage. Creating the table in the MEMORY database avoids fragmenting the InnoDB tablespace.

Here’s the working code:

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
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
  DECLARE lv_counter  INT DEFAULT 1;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET lv_counter := lv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Check for a temporary table that holds a control variable,
     create the table if it doesn't exist, and remove rows from
     the table. */
  IF EXISTS (SELECT   NULL
             FROM     information_schema.TABLES
             WHERE    TABLE_NAME = 'counter') THEN
    TRUNCATE TABLE counter;
  ELSE
    /* It would be ideal to use a temporary table here but then
       it's not recorded in the INFORMATION_SCHEMA and cleansing
       the temporary table is more tedious. */
    CREATE TABLE counter ( counter INT ) ENGINE=MEMORY;
  END IF;
 
  /* Insert the counter value for a list of parameters. */
  INSERT INTO counter VALUES ( lv_counter - 1 );  
 
END;
$$

Bill Karwin made a wonderful point about the overhead of looking up a table name and using a table rather than a temporary table. You can replace the logic above with a CONTINUE HANDLER and a temporary table. It should resolve the problems mentioned in the note but as mentioned earlier there are other solutions for production systems.

Bill also referenced a great post by Jay Pipes. It points out that there are serious problems scaling MySQL stored programs. This occurs because MySQL stored procedures are not compiled and stored in a global stored procedure cache, like Microsoft SQL Server or Oracle.

This deficit in MySQL certainly plays to the philosophy of developing everything in the application layer, suggested by Bill. At least, that is true when the application layer is co-resident on the server-tier as a Server Side Includes (SSI) library.

It does beg the question why Microsoft SQL Server and Oracle enable placing an efficient set of stored logic in the database server, effectively creating an application layer that can be split between an SSI library and the database server. After all, the server has been the most scalable component and the application layer can distribute to the middle-tier.

Today’s limitations on MySQL stored programs present a clear and compelling case toward their general avoidance and specific use for delivering a scalable solutions. Will that hold true beyond 2015 when licensing becomes completely proprietary to Oracle? I’d venture (a complete guess or hope on my part) a global stored procedure cache may be the future of MySQL procedures. It would certainly serve to distinguish MySQL from MariaDB and provide an incentive to remain on MySQL or to return from MariaDB to MySQL. Such a change would certainly make MySQL even more competitive against Microsoft SQL Server, which is the only rival purely relational database it competes against.

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
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
  DECLARE lv_counter  INT DEFAULT 1;
 
  DECLARE CONTINUE HANDLER FOR 1146
    BEGIN
      /* Create a temporary table. */
      CREATE TEMPORARY TABLE counter ( counter INT ) ENGINE=MEMORY;
    END;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,lv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET lv_counter := lv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Truncate existing table. */
  TRUNCATE TABLE counter;
 
  /* Insert the counter value for a list of parameters. */
  INSERT INTO counter VALUES ( lv_counter - 1 );  
 
END;
$$

The test program for deserialize follows below. If you’re curious, those are the Italian version of one through four. ;-) I’ve a partiality for Italian after living in Italy for two years.

CALL deserialize('Uno,Due,Tre,Quattro');
SELECT counter AS "Parameter #" FROM counter;
SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4;

You can also eliminate the temporary table completely by replacing the counter with a @sv_counter session variable, as mentioned by Bill in his comment. The following is the version of deserialize uses a session 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
42
43
CREATE PROCEDURE deserialize
( pv_param_list VARCHAR(32767))
CONTAINS SQL
BEGIN
  DECLARE lv_name     VARCHAR(9) DEFAULT 'sv_filter';
  DECLARE lv_length   INT;
  DECLARE lv_start    INT DEFAULT 1;
  DECLARE lv_end      INT DEFAULT 1;
 
  /* Set a session variable to enable a calling scope to
     read it without a function return. */
  SET @sv_counter := 1;
 
  /* Skip when call parameter list is null or empty. */	
  IF NOT (ISNULL(pv_param_list) OR LENGTH(pv_param_list) = 0) THEN
 
    /* Read line by line on a line return character. */
    parse: WHILE NOT (lv_end = 0) DO
 
      /* Check for line returns. */
      SET lv_end := LOCATE(',',pv_param_list,lv_start);
 
      /* Check whether line return has been read. */
      IF NOT lv_end = 0 THEN  /* Reset the ending substring value. */
        SET lv_end := LOCATE(',',pv_param_list,lv_start);
        CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,lv_end - lv_start));
      ELSE  /* Print the last substring with a semicolon. */
        CALL set_session_var(CONCAT(lv_name,@sv_counter),SUBSTR(pv_param_list,lv_start,LENGTH(pv_param_list)));
      END IF;
 
      /* Reset the beginning of the string. */
      SET lv_start := lv_end + 1;      
      SET @sv_counter := @sv_counter + 1;
 
    END WHILE parse;    
 
  END IF;
 
  /* Reduce by one for 1-based numbering of name elements. */
  SET @sv_counter := @sv_counter - 1;
 
END;
$$

The test case changes for this version because there isn’t a table involved. You would use the following:

CALL deserialize('Uno,Due,Tre,Quattro');
SELECT @sv_filter1, @sv_filter2, @sv_filter3, @sv_filter4;

Like the earlier procedure, there’s no exception handling but it wouldn’t be a bad idea to add it. Especially, if you’re using the new SIGNAL feature of MySQL 5.6, which is now backported to MySQL 5.5.

Step #3

The last step creates a store procedure that takes two parameters, a parameterized statement and a serialized list of parameters. This means you can build any statement on the fly as required. The example uses queries simply because they’re the easiest to demonstrate the process.

You should note that the prepared statement is dynamic_stmt not stmt because stmt is used inside the set_session_var procedure. That means using stmt in the calling scope program would impact the called scope program because they use the same identifier (more or less a namespace scope issue).

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
CREATE PROCEDURE prepared_dml
( pv_query   VARCHAR(32767)
, pv_filter  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE dynamic_stmt  VARCHAR(32767);
  DECLARE lv_counter    INT DEFAULT 0;
 
  /* Cleanup the message passing table when a case is not found. */
  DECLARE EXIT HANDLER FOR 1339
    BEGIN
      /* Step #5: */
      DEALLOCATE PREPARE dynamic_stmt;
 
      /* Cleanup the temporary table that exchanges data between
         procedures. */
      DROP TABLE IF EXISTS counter;
    END;
 
  /* Step #1:
     ========
     Set a session variable with two parameter markers. */
  SET @SQL := pv_query;
 
  /* Verify query is not empty. */
  IF NOT ISNULL(@SQL) THEN
 
    /* Step #2:
       ========
       Dynamically allocated and run statement. */
    PREPARE dynamic_stmt FROM @SQL;
 
    /* Step #3:
       ========
       Assign the formal parameters to session variables
       because prepared statements require them. */
    CALL deserialize(pv_filter);
 
    /* Secure the parameter count from a temporary table that
       exchanges data between procedures. */
    SELECT counter INTO lv_counter FROM counter;
 
    /* Step #4:
       ========
       Choose the appropriate overloaded prepared statement. */
    CASE
      WHEN lv_counter = 0 THEN
        EXECUTE dynamic_stmt;      
      WHEN lv_counter = 1 THEN
        EXECUTE dynamic_stmt USING @sv_filter1;
      WHEN lv_counter = 2 THEN
        EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2;
    END CASE;
 
    /* Step #5: */
    DEALLOCATE PREPARE dynamic_stmt;
 
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TABLE IF EXISTS counter; 
 
  END IF;
 
END;
$$

If you’re using the deserialize procedure that uses a temporary table, you should make the following changes prepared_dml procedure on line 19 and 62. They modify the DROP TABLE statement to a DROP TEMPORARY TABLE statement. Though the change isn’t technically required because a DROP TABLE works against temporary tables as well as ordinary tables, it generally clearer to those new to MySQL syntax.

17
18
19
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TEMPORARY TABLE IF EXISTS counter;

The line 62 change:

60
61
62
    /* Cleanup the temporary table that exchanges data between
       procedures. */
    DROP TEMPORARY TABLE IF EXISTS counter;

It would be much nicer to avoid the CASE statement or an if-else-if block but prepared statements are limited on what SQL statements they support. For example, you can create or drop stored functions or procedures, and you can’t dispatch a dynamic statement as a dynamic statement. That means you can’t replace the CASE statement with something like this:

43
44
45
46
    SET @dsql := 'EXECUTE dynamic_stmt USING @sv_filter1, @sv_filter2';
    PREPARE preparsed_stmt FROM @dsql;
    EXECUTE preparsed_stmt;
    DEALLOCATE PREPARE preparsed_stmt;

Attempt that, and you’ll get the following message. Does that mean there’s hope it’ll become a feature and possibility? Or, that it isn’t recommended?

ERROR 1295 (HY000): This command IS NOT supported IN the prepared statement protocol yet

The session variable solution requires another version of the prepared_dml procedure, as shown below. As you can imagine, removing the table and replacing a session variable simplifies the prepared_dml stored procedure version.

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
CREATE PROCEDURE prepared_dml
( pv_query   VARCHAR(32767)
, pv_filter  VARCHAR(32767))
CONTAINS SQL
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE dynamic_stmt  VARCHAR(32767);
  DECLARE lv_counter    INT DEFAULT 0;
 
  /* Cleanup the message passing table when a case is not found. */
  DECLARE EXIT HANDLER FOR 1339
    BEGIN
      /* Step #5: */
      DEALLOCATE PREPARE dynamic_stmt;
    END;
 
  /* Step #1:
     ========
     Set a session variable with two parameter markers. */
  SET @SQL := pv_query;
 
  /* Verify query is not empty. */
  IF NOT ISNULL(@SQL) THEN
 
    /* Step #2:
       ========
       Dynamically allocated and run statement. */
    PREPARE dynamic_stmt FROM @SQL;
 
    /* Step #3:
       ========
       Assign the formal parameters to session variables
       because prepared statements require them. */
    CALL deserialize(pv_filter);
 
     /* Step #4:
       ========
       Choose the appropriate overloaded prepared statement. */
    CASE
      WHEN @sv_counter = 0 THEN
        EXECUTE dynamic_stmt;      
      WHEN @sv_counter = 1 THEN
        EXECUTE dynamic_stmt USING @sv_filter1;
      WHEN @sv_counter = 2 THEN
        EXECUTE dynamic_stmt USING @sv_filter1,@sv_filter2;
    END CASE;
 
    /* Step #5: */
    DEALLOCATE PREPARE dynamic_stmt;
 
  END IF;
 
END;
$$

The test programs check without a parameter and with one or two parameters, as you can see below.

SELECT 'Test Case #1 ...' AS "Statement";
SET @param1 := 'SELECT "Hello World"';
SET @param2 := '';
CALL prepared_dml(@param1,@param2);
 
SELECT 'Test Case #2 ...' AS "Statement";
SET @param1 := 'SELECT item_title FROM item i WHERE item_title REGEXP ?';
SET @param2 := '^.*war.*$';
CALL prepared_dml(@param1,@param2);
 
SELECT 'Test Case #3 ...' AS "Statement";
SET @param1 := 'SELECT common_lookup_type FROM common_lookup cl WHERE common_lookup_table REGEXP ? AND common_lookup_column REGEXP ?';
SET @param2 := 'item,item_type';
CALL prepared_dml(@param1,@param2);

As always, I hope this helps those writing MySQL Stored Procedures. If you’re new to stored programs, you can find a chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.

Written by maclochlainn

June 16th, 2012 at 7:36 pm

Oracle Within Group

without comments

Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
COLUMN list      FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names     FORMAT A42
COLUMN members   FORMAT 9,990
 
 
SELECT   m.account_number AS account
,        c.last_name AS last_name
,        LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
           WITHIN GROUP (ORDER BY 2) AS names
,        COUNT(*) AS members
FROM     contact c INNER JOIN member m USING (member_id)
GROUP BY m.account_number
,        c.last_name
ORDER BY c.last_name;

It produces the following output:

ACCOUNT    LAST_NAME  NAMES                                      MEMBERS
---------- ---------- ------------------------------------------ -------
SLC-000021 Jonah      Gretelz S.                                       1
SLC-000020 Moss       Jane W.                                          1
SLC-000023 Nathan     Smith B.                                         1
SLC-000024 Potter     Albus S., Ginny, Harry, James S., Lily L.        5
SLC-000022 Royal      Jennifer E.                                      1
SJC-000003 Sweeney    Ian M., Matthew, Meaghan                         3
SJC-000002 Vizquel    Doreen, Oscar                                    2
SLC-000018 Ward       Clinton G.                                       1
SLC-000019 Ward       Brandt H.                                        1
SJC-000001 Winn       Brian, Randi                                     2
 
10 rows selected.

I also found some existing examples you might like, at Oracle-Base, and there they’re showing you how to make it work in prior releases of the database without the new LISTAGG function.

Let’s say you just wanted a concatenated list of users, you could use the following in-line view approach:

1
2
3
4
5
SELECT   list.names
FROM    (SELECT   'List' AS list
         ,        LISTAGG(last_name ||', '||first_name||DECODE(middle_name,NULL,NULL,' '||SUBSTR(middle_name,1,1)||'.'),'; ')
                  WITHIN GROUP (ORDER BY 2) AS names
         FROM     contact) list;

As always, I hope this helps somebody.

Written by maclochlainn

April 7th, 2012 at 3:49 pm

MySQL REGEXP Error

with one comment

While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an error had he replaced the metasequence with .* instead of the solitary *.

The original call to the procedure passes the following well formed regular expression:

CALL prepared_dml('(^|^.+)war(.+$|$)');

Or, they could eliminate the metasequences and use:

CALL prepared_dml('^.*war.*$');

Either returns the following entries from a column with movie titles from the sample code:

Charlie's War
Star Wars I
Star Wars II
Star Wars III

The dot (.) means any possible character, and the plus (+) means one-to-many possible repeating characters of a preceding character. When the dot precedes the plus, it means one-to-many wildcard characters. The student replaced the metasequence with an asterisk by itself and generated the badly formed regular expression error.

The misunderstanding occurs because the asterisk (*) by itself doesn’t mean zero-to-many wildcard. The combination of the dot and asterisk creates a zero-to-many wildcard, which works when there is or isn’t a character before the first character of a string or after the last character of a string. It also eliminates the need for a metasequence.

Here’s a small test case outside of the book’s stored procedure:

-- Conditionally drop the table.
DROP TABLE IF EXISTS list;
 
-- Create the table.
CREATE TABLE list ( list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, list_item VARCHAR(20));
 
-- Insert for rows.
INSERT INTO list (list_item) VALUES ('Star'),(' Star'),(' Star '),('Star ');
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many leading characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for zero-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many leading characters  "
,        LENGTH(list_item) AS "Length"
,        '^.+Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.+Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "Zero-to-many trailing characters"
,        LENGTH(list_item) AS "Length"
,        '^.*Star.*$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.*$';
 
-- Query for one-to-many leading characters.
SELECT   list_id AS "ID"
,        list_item AS "One-to-many trailing characters "
,        LENGTH(list_item) AS "Length"
,        '^.*Star.+$' AS "Regular Expression"
FROM     list
WHERE    list_item REGEXP '^.*Star.+$';

The output from the scripts is:

+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many leading characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many leading characters   | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  2 |  Star                            |      5 | ^.+Star.*$         |
|  3 |  Star                            |      6 | ^.+Star.*$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.00 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | Zero-to-many trailing characters | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  1 | Star                             |      4 | ^.*Star.*$         |
|  2 |  Star                            |      5 | ^.*Star.*$         |
|  3 |  Star                            |      6 | ^.*Star.*$         |
|  4 | Star                             |      5 | ^.*Star.*$         |
+----+----------------------------------+--------+--------------------+
4 rows in set (0.02 sec)
 
+----+----------------------------------+--------+--------------------+
| ID | One-to-many trailing characters  | Length | Regular Expression |
+----+----------------------------------+--------+--------------------+
|  3 |  Star                            |      6 | ^.*Star.+$         |
|  4 | Star                             |      5 | ^.*Star.+$         |
+----+----------------------------------+--------+--------------------+
2 rows in set (0.02 sec)

Hope this helps.

Written by maclochlainn

March 23rd, 2012 at 11:01 am

Oracle CSV Imports

with one comment

The first step in creating an effective import plan for comma-separated value (CSV) files is recognizing your options in a database. There are several options in an Oracle database. You can read the file with Java, C/C++, C#, PL/SQL (through the UTL_FILE package), PHP, Perl, or any other C-callable programming language; or you can use SQL*Loader as a standalone utility or through externally managed tables (known as external tables). The most convenient and non-programming solution is using external tables.

Adopting external tables as your import solution should drive you to consider how to manage the security surrounding this type of methodology. Host hardening is a critical security step because it shuts down most, hopefully all, unauthorized use of the operating system where the database and external files reside. Next, you need to manage the access to the external tables and ensure that exposure of business sensitive information in CSV files is minimized.

This post explains how to manage access and police (cleanup external files) once they’re read into the database. It assumes you have root-level permissions to the operating system and database. The SYS and SYSTEM accounts have the equivalent of root permissions for database configuration. The rule of thumb with these accounts is simple, manage as much as possible with the SYSTEM account before you use the SYS account.

Setting up the Import File System

While you can do all the setup of virtual directories in Oracle regardless of whether you’ve set them up in the operating system, it’s a good idea to set them up in the OS first. The example is using a Windows 7 OS, so you’ll need to change the directories when working in Linux or Unix. Here are the directories:

C:\Imports\ImportFiles
C:\Imports\ImportLogs

You may take note that there are two directories. That’s because you don’t want to grant write privileges to the Oracle virtual directory where you put the files. You can grant read-only privileges to the virtual directory and read-write privileges to the log directory.

Setting up the Import User/Schema

This step lets you create an IMPORT user/schema in the Oracle database. You need to connect as the SYSTEM user to perform these steps (or another authorized DBA account with adequate privileges):

CREATE USER import IDENTIFIED BY import
DEFAULT TABLESPACE users QUOTA 1000M ON users
TEMPORARY TABLESPACE temp;

After creating the user, grant the privileges like this as the SYSTEM user:

GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
,     CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER
,     CREATE TYPE,  CREATE VIEW TO import;

Setting up Virtual Directories

A virtual directory in Oracle acts maps an internal database directory name (known as a virtual directory) to a physical directory of the operating system. You create two virtual directories in this example, one holds read-only permissions to the directory where you’re putting the data file, and the other holds read-write permissions to the directory where you’re writing any log files from the external file process.

Log files are generated from this process when you query the data from the external file. Any error in the files conformity is written to a log file.

CREATE DIRECTORY upload_files AS 'C:\Imports\ImportFiles';
CREATE DIRECTORY upload_logs AS 'C:\Imports\ImportLogs';

After creating the virtual directories in the database, you must grant appropriate access to the user account that will access the data. This grants those permissions to the IMPORT user:

GRANT READ ON DIRECTORY upload_files TO import;
GRANT READ, WRITE ON DIRECTORY upload_logs TO import;

Setting up an External Table

An external table references both the UPLOAD_FILES and UPLOAD_LOGS virtual directories, and the virtual directories must map to physical directories that allow read and write privileges to the Oracle user. Here’s the external table for this example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE item_import_ext_table
( asin_number           VARCHAR2(10)
, item_type             VARCHAR2(15)
, item_title            VARCHAR2(60)
, item_subtitle          VARCHAR2(60)
, item_rating            VARCHAR2(8)
, item_rating_agency     VARCHAR2(4)
, item_release_date      DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload_files
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'UPLOAD_LOGS':'item_import_ext_table.bad'
      DISCARDFILE 'UPLOAD_LOGS':'item_import_ext_table.dis'
      LOGFILE     'UPLOAD_LOGS':'item_import_ext_table.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('item_import.csv'))
REJECT LIMIT UNLIMITED;

Setting up a Physical File

You should put the following in a item_import.csv physical file (case sensitivity won’t matter on the Windows 7 platform but will matter on the Linux or Unix platforms):

'B000W74EQC','DVD_WIDE_SCREEN','Harry Potter and the Sorcerer''s Stone',,'PG','MPAA','11-DEC-2007'
'B000W746GK','DVD_WIDE_SCREEN','Harry Potter and the Chamber of Secrets',,'PG','MPAA','11-DEC-2007'
'B000W796OM','DVD_WIDE_SCREEN','Harry Potter and the Prisoner of Azkaban',,'PG','MPAA','11-DEC-2007'
'B000E6EK2Y','DVD_WIDE_SCREEN','Harry Potter and the Goblet of Fire',,'PG-13','MPAA','07-MAR-2006'
'B000W7F5SS','DVD_WIDE_SCREEN','Harry Potter and the Order of the Phoenix',,'PG-13','MPAA','11-DEC-2007'
'B002PMV9FG','DVD_WIDE_SCREEN','Harry Potter and the Half-Blood Prince',,'PG','MPAA','08-DEC-2009'
'B001UV4XHY','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 1',,'PG-13','MPAA','15-APR-2011'
'B001UV4XIS','DVD_WIDE_SCREEN','Harry Potter and the Deathly Hallows, Part 2',,'PG-13','MPAA','11-NOV-2011'

Testing the External Table

After putting the item_import.csv file in the C:\Imports\ImportFiles directory, you can test the process at this point by running the following query:

SET PAGESIZE 99
 
COLUMN asin_number        FORMAT A11 HEADING "ASIN #"
COLUMN item_title         FORMAT A46 HEADING "ITEM TITLE"
COLUMN item_rating        FORMAT A6  HEADING "RATING"
COLUMN item_release_date  FORMAT A11 HEADING "RELEASE|DATE"
 
SELECT   asin_number
,        item_title
,        item_rating
,        TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date
FROM     item_import_ext_table;

It should return eight rows.

Extending Access to the Data Dictionary

The physical directory names of virtual directories are hidden from generic users. They’re available in the ALL_DIRECTORIES and DBA_DIRECTORIES administrative view for queries by the SYS, SYSTEM, and any DBA role privileged users.

While a privileged user can query the view, placing the view inside a function or procedure deployed in the privileged user’s schema would raise an ORA-00942 error. That error signals that the table or view does not exist.

This example deploys the view in the SYSTEM schema. That means it requires you make the following grant as the SYS user:

GRANT SELECT ON sys.dba_directories TO system;

After making the grant from the SYS schema to the SYSTEM schema, connect to the SYSTEM schema. Then, create the following GET_DIRECTORY_PATH function:

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
CREATE OR REPLACE FUNCTION get_directory_path
( virtual_directory IN VARCHAR2 )
RETURN VARCHAR2 IS
  -- Define RETURN variable.
  directory_path VARCHAR2(256) := '';
  --Define dynamic cursor.
  CURSOR get_directory (virtual_directory VARCHAR2) IS
    SELECT   directory_path
    FROM     sys.dba_directories
    WHERE    directory_name = virtual_directory;
  -- Define a LOCAL exception FOR name violation.
  directory_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_name,-22284);
BEGIN
  OPEN  get_directory (virtual_directory);
  FETCH get_directory
  INTO  directory_path;
  CLOSE get_directory;
  -- RETURN file name.
  RETURN directory_path;
EXCEPTION
  WHEN directory_name THEN
    RETURN NULL;
END get_directory_path;
/

It’s tempting to make the grant on this function to PUBLIC user but that would expose information that any DBA should try and limit. That means you grant EXECUTE privilege only to the IMPORT schema.

This grant should be made as the SYSTEM user:

GRANT EXECUTE ON get_directory_path TO import;

After granting the EXECUTE privilege to the IMPORT user, connect to the IMPORT schema and create a synonym to the GET_DIRECTORY_PATH function. The syntax for that command is:

CREATE SYNONYM get_directory_path FOR system.get_directory_path;

You can now test your access to the function with the following query from the IMPORT schema:

SELECT get_directory_path('UPLOAD_FILES') FROM dual;

You should return the following if you’ve got everything working at this point:

GET_DIRECTORY_PATH('UPLOAD_FILES')
------------------------------------
C:\Imports\ImportFiles

At this point, you’ve completed the second major configuration component. You now need the ability to read files outside the database, which can be done with Java in Oracle 10g or Oracle 11g (that’s not possible in Oracle 10g XE or Oracle 11g XE because they don’t support an internal JVM). The

Reading Virtual Directory Files

The GET_DIRECTORY_PATH function provides you with the ability to read the Oracle data catalog and find the absolute directory path of a virtual directory. In this framework, you need this value to find whether the item_import.csv physical file is present in the file system before you read the file.

There doesn’t appear to be a neat little function to read an external directory. At least, there’s not one in the UTL_FILE or DBMS_LOB packages where you’d think it should be found. Unfortunately, that leaves us with two alternatives. One is to write an external library in C, C++, or C#. Another is to write an internal Java library that reads the file system. You accomplish this by granting permissions to a target directory or directories.

The first step is to create a scalar array of VARCHAR2 variables, like

CREATE OR REPLACE TYPE file_list AS TABLE OF VARCHAR2(255);
/

The second step is to write the Java library file. You can write it three ways. One accepts default error handling and the others override the default exception handling. If you’re new to Java, you should take the basic library with default handling. If you’ve more experience, you may want to override the helpful message with something that causes the developer to check with the DBA or simply suppress the message to enhance security.

You should note that the database connection is an Oracle Database 11g internal database connection. The connection only does one thing. It allows you to map the ArrayDescriptor to a schema-level SQL collection type. The element types of these collections should be scalar variables, like DATE, NUMBER, or VARCHAR2 data types.

The more advanced method overrides exception handling by suppressing information about the java.properties settings. You can do it by catching the natively thrown exception and re-throw it or ignore it. The example ignores it because handling it in Java reports an unhandled exception at the PL/SQL or SQL layer, which leads end users to think you have a major design problem.

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
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.SQL.*;
  import java.util.Arrays;
  import oracle.SQL.driver.*;
  import oracle.SQL.ArrayDescriptor;
  import oracle.SQL.ARRAY;
 
  // Define the class.
  public class ListVirtualDirectory {
 
    // Define the method.
    public static ARRAY getList(String path) throws SQLException {
 
    // DECLARE variable AS a NULL, required because OF try-catch block.
    ARRAY listed = NULL;
 
    // Define a connection (this IS FOR Oracle 11g).
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
    // USE a try-catch block TO trap a Java permission error ON the directory.
    try {
      // DECLARE a class WITH the file list.
      File directory = NEW File(path);
 
      // DECLARE a mapping TO the schema-level SQL collection TYPE.
      ArrayDescriptor arrayDescriptor = NEW ArrayDescriptor("FILE_LIST",conn);
 
      // Translate the Java String[] TO the Oracle SQL collection TYPE.
      listed = NEW ARRAY(arrayDescriptor,conn,((Object[]) directory.list())); }
    catch (AccessControlException e) {}
  RETURN listed; }}
/

You can’t call an internal Java library without a PL/SQL wrapper function. Here’s the wrapper function for this Java library:

CREATE OR REPLACE FUNCTION list_files(path VARCHAR2) RETURN FILE_LIST IS
LANGUAGE JAVA
NAME 'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY';
/

You MUST grant the Oracle Database’s internal JVM authority to read the external directory before you can return the directory contents. Any attempt to read a directory without the proper permissions raises an ORA-29532 exception.

The following is an anonymous block to grant permissions to a directory. You must grant a minimum of read permissions but since you’ll also delete this file later in the post you should grant read, write, and delete. You must run it from the SYSDBA role as the SYS user.

1
2
3
4
5
6
7
BEGIN
  DBMS_JAVA.GRANT_PERMISSION('IMPORT'
                             ,'SYS:java.io.FilePermission'
                             ,'C:\Imports\ImportFiles'
                             ,'read,write,delete');
END;
/

While you’re connected, it’s a good idea to grant the same privileges to your log directory:

1
2
3
4
5
6
7
BEGIN
  DBMS_JAVA.GRANT_PERMISSION('IMPORT'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\Imports\ImportLogs'
                            ,'read,write,delete');
END;
/

You should now be able to read the contents of an external file from another PL/SQL block or from a SQL statement. Here’s an example of the SQL statement call that uses everything developed to this point:

SELECT   column_value AS "File Names"
FROM     TABLE(list_files(get_directory_path('UPLOAD_FILES')));

It should return the item_import.csv physical file as the only file in the physical directory, like:

File Names
-----------------
item_import.csv

Mapping an External Table to a source File

The next step leverages the user segment of the Oracle Database’s data catalog and all the components developed above to find and display the external table and external file. This query returns the results:

COLUMN TABLE_NAME FORMAT A30
COLUMN file_name  FORMAT A30
 
SELECT   xt.TABLE_NAME
,        xt.file_name
FROM    (SELECT   uxt.TABLE_NAME
         ,        ixt.column_value AS file_name
         FROM     user_external_tables uxt CROSS JOIN
         TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
JOIN     user_external_locations xl
ON       xt.TABLE_NAME = xl.TABLE_NAME AND xt.file_name = xl.location;

It should return the following:

TABLE_NAME                     FILE_NAME
------------------------------ ------------------------------
ITEM_IMPORT_EXT_TABLE          item_import.csv

You can migrate the query into the following function. It returns a zero when the file isn’t found and a one when it is found.

CREATE OR REPLACE FUNCTION external_file_found
( table_in VARCHAR2 ) RETURN NUMBER IS
  -- Define a default return value.
  retval NUMBER := 0;
 
  -- Decalre a cursor to find external tables.
  CURSOR c (cv_table VARCHAR2) IS
    SELECT   xt.TABLE_NAME
    ,        xt.file_name
    FROM    (SELECT   uxt.TABLE_NAME
             ,        ixt.column_value AS file_name
             FROM     user_external_tables uxt CROSS JOIN
             TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
    JOIN     user_external_locations xl ON xt.TABLE_NAME = xl.TABLE_NAME
    AND      xt.file_name = xl.location AND xt.TABLE_NAME = UPPER(cv_table);
BEGIN
  FOR i IN c(table_in) LOOP
    retval := 1;
  END LOOP;
  RETURN retval;
END;
/

With the EXTERNAL_FILE_FOUND function, you can create a function that returns rows when the external file is found and no rows when the external file isn’t found. The following view hides the logic required to make that work:

CREATE OR REPLACE VIEW item_import AS
SELECT   *
FROM     item_import_ext_table
WHERE    external_file_found('ITEM_IMPORT_EXT_TABLE') = 1;

Conveniently, you can now query the ITEM_IMPORT view without the risk of raising the following error when the file is missing:

SELECT * FROM item_import_ext_table
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file item_import.csv IN UPLOAD_FILES NOT found

You can now grant the SELECT privilege on the ITEM_IMPORT view to your application schema, like:

GRANT SELECT ON item_import TO application;

After granting the SELECT privilege on the ITEM_IMPORT view to the APPLICATION schema, you can create a synonym to hide the IMPORT schema.

CREATE SYNONYM item_import FOR item_import;

At this point, many developers feel they’re done. Enclosing the results in a schema-level function provides more utility than a view. The next section shows you how to replace the view with a schema-level function.

Replacing the View with an Object Table Function

Inside a schema-level function, you can assign the results from the query to a SQL collection of an object type. The object type should mirror the structure of the table, like the following:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE TYPE item_import_object IS OBJECT
( asin_number         VARCHAR2(10)
, item_type           VARCHAR2(15)
, item_title          VARCHAR2(60)
, item_subtitle       VARCHAR2(60)
, item_rating         VARCHAR2(8)
, item_rating_agency  VARCHAR2(4)
, item_release_date   DATE);
/

After creating the object type that mirrors the structure of the ITEM_IMPORT_EXT_TABLE table, you need to create a list like collection of the object type. The nested table collection type acts like a list in Oracle:

1
2
3
CREATE OR REPLACE TYPE item_import_object_table IS
  TABLE OF item_import_object;
/

After defining the object type and collection, you can access them in the following type of function:

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
CREATE OR REPLACE FUNCTION external_file_contents
( table_in VARCHAR2 ) RETURN item_import_object_table IS
 
  -- Define a local counter.
  lv_counter NUMBER := 1;
 
  -- Construct an empty collection of ITEM_IMPORT_OBJECT data types.
  lv_item_import_table ITEM_IMPORT_OBJECT_TABLE := item_import_object_table();
 
  -- Decalre a cursor to find external tables.
  CURSOR c (cv_table VARCHAR2) IS
    SELECT   *
    FROM     item_import_ext_table
    WHERE    external_file_found(cv_table) = 1;
 
BEGIN
  FOR i IN c(table_in) LOOP
    lv_item_import_table.EXTEND;
    lv_item_import_table(lv_counter) := item_import_object(i.asin_number
                                                          ,i.item_type
                                                          ,i.item_title
                                                          ,i.item_subtitle
                                                          ,i.item_rating
                                                          ,i.item_rating_agency
                                                          ,i.item_release_date);
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /*
   *  This is where you can place autonomous function calls:
   *  ======================================================
   *   - These can read source and log files, and write them
   *     to CLOB attributes for later inspection or review.
   *   - These can call Java libraries to delete files, but
   *     you should note that Java deletes any file rather
   *     than moving it to the trash bin (where you might
   *     recover it.
   */
 
  RETURN lv_item_import_table;
END;
/

Between the assignment to the collection and the return statement of the function, you have the ability of calling any number of autonomous functions. Any schema-level function can call autonomous functions that read and write tables with DML statements, like the INSERT, UPDATE, and DELETE statements. You can also call schema-functions that wrap Java libraries that delete external files.

You can confirm that the steps work by running the following query with or without the SQL*Plus formatting:

/*
 *  SQL*Plus formatting.
 */
SET PAGESIZE 99
 
COLUMN asin_number        FORMAT A11 HEADING "ASIN #"
COLUMN item_title         FORMAT A46 HEADING "ITEM TITLE"
COLUMN item_rating        FORMAT A6  HEADING "RATING"
COLUMN item_release_date  FORMAT A11 HEADING "RELEASE|DATE"
 
/*
 *  Query works only when item_import.csv file is present.
 */
SELECT   asin_number
,        item_title
,        item_rating
,        TO_CHAR(item_release_date,'DD-MON-YYYY') AS item_release_date
FROM     TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

It should return the following from SQL*Plus:

                                                                  RELEASE
ASIN #      ITEM TITLE                                     RATING DATE
----------- ---------------------------------------------- ------ -----------
B000W74EQC  Harry Potter and the Sorcerer's Stone          PG     11-DEC-2007
B000W746GK  Harry Potter and the Chamber of Secrets        PG     11-DEC-2007
B000W796OM  Harry Potter and the Prisoner of Azkaban       PG     11-DEC-2007
B000E6EK2Y  Harry Potter and the Goblet of Fire            PG-13  07-MAR-2006
B000W7F5SS  Harry Potter and the Order of the Phoenix      PG-13  11-DEC-2007
B002PMV9FG  Harry Potter and the Half-Blood Prince         PG     08-DEC-2009
B001UV4XHY  Harry Potter and the Deathly Hallows, Part 1   PG-13  15-APR-2011
B001UV4XIS  Harry Potter and the Deathly Hallows, Part 2   PG-13  11-NOV-2011

The creation of the schema-level function lets you recreate the ITEM_IMPORT view. The following view would encapsulate (or hide) the presence of the function, which hides all the infrastructure components developed before this section (see line 14 in the function):

1
2
3
CREATE OR REPLACE VIEW item_import AS
SELECT   *
FROM     TABLE(external_file_contents('ITEM_IMPORT_EXT_TABLE'));

Implementing a Managed Import Process

During any import the information from the import process is exposed and one or more items may fail during the import process. That means the source file and loading log files must be preserved immediately after reading the data successfully. This is done by loading the data source file and log, discard, and bad import files into database tables. Only the source and log files exist when all rows are well formed, but the log files are reused for any subsequent load and require human inspection to isolate a specific upload.

The best way to implement this requires creating individual tables to hold each of the four potential large objects. The ITEM_MASTER table holds a transactional primary key and a table name for the import table. The primary key of the ITEM_MASTER table is the base key for imports and the ITEM_DATA, ITEM_LOG, ITEM_DISCARD, and ITEM_BAD tables hold foreign keys that point back to the ITEM_MASTER table’s primary key. These tables also hold a character large object column (CLOB), which will hold the respective source data file or log, discard, or bad files.

The following create the tables for the logging framework:

CREATE TABLE import_master
( import_master_id  NUMBER CONSTRAINT pk_import_master PRIMARY KEY
, import_table      VARCHAR2(30));
 
-- Create sequence for import master.
CREATE SEQUENCE import_master_s;
 
-- Create import table.
CREATE TABLE import_data
( import_data_id    NUMBER CONSTRAINT pk_import_data PRIMARY KEY
, import_master_id  NUMBER
, import_data       CLOB
, CONSTRAINT fk_import_data FOREIGN KEY (import_data_id)
  REFERENCES import_master (import_master_id))
LOB (import_data) STORE AS BASICFILE item_import_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_data_s;
 
-- Create import table.
CREATE TABLE import_log
( import_log_id     NUMBER CONSTRAINT pk_import_log PRIMARY KEY
, import_master_id  NUMBER
, import_log        CLOB
, CONSTRAINT fk_import_log FOREIGN KEY (import_log_id)
  REFERENCES import_master (import_master_id))
LOB (import_log) STORE AS BASICFILE item_import_log_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_log_s;
 
-- Create import table.
CREATE TABLE import_discard
( import_discard_id  NUMBER CONSTRAINT pk_import_discard PRIMARY KEY
, import_master_id   NUMBER
, import_discard     CLOB
, CONSTRAINT fk_import_discard FOREIGN KEY (import_discard_id)
  REFERENCES import_master (import_master_id))
LOB (import_discard) STORE AS BASICFILE item_import_discard_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_discard_s;
 
-- Create import table.
CREATE TABLE import_bad
( import_bad_id     NUMBER CONSTRAINT pk_import_bad PRIMARY KEY
, import_master_id  NUMBER
, import_bad        CLOB
, CONSTRAINT fk_import_bad FOREIGN KEY (import_bad_id)
  REFERENCES import_master (import_master_id))
LOB (import_bad) STORE AS BASICFILE item_import_bad_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT    1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));
 
-- Create sequence for import master.
CREATE SEQUENCE import_bad_s;

The tables set the targets for uploading the source and log files. You should note that the table name is also the column name for the CLOB column, this becomes convenient when supporting a Native Dynamic SQL (NDS) statement in a single autonomous function. The LOAD_CLOB_FROM_FILE function supports reading the external source and log files and writing them their respective tables.

There is a DEADLOCK possibility with this type of architecture. It requires that the base row in the IMPORT_MASTER table is committed before attempting inserts into one of the dependent tables. A call to the function raises an error when the primary key column hasn’t been committed before hand.

You already set the access privileges for the DBMS_LOB package when you granted them to the UPLOAD_FILES and UPLOAD_LOGS virtual directories. This function only requires read permissions, which were granted to both virtual directories.

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
93
94
95
96
97
98
99
100
101
102
CREATE OR REPLACE FUNCTION load_clob_from_file
( pv_src_file_name  IN VARCHAR2
, pv_virtual_dir    IN VARCHAR2
, pv_table_name     IN VARCHAR2
, pv_column_name    IN VARCHAR2
, pv_foreign_key    IN NUMBER ) RETURN NUMBER IS
 
  -- Declare placeholder for sequence generated primary key.
  lv_primary_key  NUMBER;
 
  -- Declare default return value.
  lv_retval  NUMBER := 0;
 
  -- Declare local variables for DBMS_LOB.LOADCLOBFROMFILE procedure.
  des_clob    CLOB;
  src_clob    BFILE := BFILENAME(pv_virtual_dir,pv_src_file_name);
  des_offset  NUMBER := 1;
  src_offset  NUMBER := 1;
  ctx_lang    NUMBER := dbms_lob.default_lang_ctx;
  warning     NUMBER;
 
  -- Declare pre-reading size.
  src_clob_size  NUMBER;
 
  -- Declare variables for handling NDS sequence value.
  lv_sequence          VARCHAR2(30);
  lv_sequence_output   NUMBER;
  lv_sequence_tagline  VARCHAR2(10) := '_s.nextval';
 
  -- Define local variable for Native Dynamic SQL (NDS) Statement.
  stmt  VARCHAR2(2000);
 
  -- Declare the function as an autonomous transaction.
  PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
  -- Open file only when found.
  IF dbms_lob.fileexists(src_clob) = 1  AND NOT dbms_lob.isopen(src_clob) = 1 THEN
    src_clob_size := dbms_lob.getlength(src_clob);
    dbms_lob.OPEN(src_clob,dbms_lob.lob_readonly);
  END IF;
 
  -- Concatenate the sequence name with the tagline.
  lv_sequence := pv_table_name || lv_sequence_tagline;
 
  -- Assign the sequence through an anonymous block.
  stmt := 'BEGIN '
       || '  :output := '||lv_sequence||';'
       || 'END;';
 
  -- Run the statement to extract a sequence value through NDS.
  EXECUTE IMMEDIATE stmt USING IN OUT lv_sequence_output;
 
  --  Create a dynamic statement that works for all source and log files.
  -- ----------------------------------------------------------------------
  --  NOTE: This statement requires that the row holding the primary key
  --        has been committed because otherwise it raises the following
  --        error because it can't verify the integrity of the foreign
  --        key constraint.
  -- ----------------------------------------------------------------------
  --        DECLARE
  --        *
  --        ERROR at line 1:
  --        ORA-00060: deadlock detected while waiting for resource
  --        ORA-06512: at "IMPORT.LOAD_CLOB_FROM_FILE", line 50
  --        ORA-06512: at line 20
  -- ----------------------------------------------------------------------  
  stmt := 'INSERT INTO '||pv_table_name||' '||CHR(10)||
          'VALUES '||CHR(10)||
          '('||lv_sequence_output||CHR(10)||
          ','||pv_foreign_key||CHR(10)||
          ', empty_clob())'||CHR(10)||
          'RETURNING '||pv_column_name||' INTO :locator';
 
  -- Run dynamic statement.
  EXECUTE IMMEDIATE stmt USING OUT des_clob;
 
  -- Read and write file to CLOB, close source file and commit.
  dbms_lob.loadclobfromfile( dest_lob     => des_clob
                           , src_bfile    => src_clob
                           , amount       => dbms_lob.getlength(src_clob)
                           , dest_offset  => des_offset
                           , src_offset   => src_offset
                           , bfile_csid   => dbms_lob.default_csid
                           , lang_context => ctx_lang
                           , warning      => warning );
 
  -- Close open source file.
  dbms_lob.close(src_clob);
 
  -- Commit write and conditionally acknowledge it.
  IF src_clob_size = dbms_lob.getlength(des_clob) THEN
    COMMIT;
    lv_retval := 1;
  ELSE
    RAISE dbms_lob.operation_failed;
  END IF;
 
  RETURN lv_retval;  
END load_clob_from_file;
/

You can test this procedure against the data source file with the following script file:

-- Insert a sample row in the master table.
INSERT INTO import_master
VALUES (import_master_s.NEXTVAL,'ITEM_IMPORT_EXT_TABLE');
 
-- Record the row value to avoid deadlock on uncommitted master record.
COMMIT;
 
-- Test program for loading CLOB files.
DECLARE
 
  -- Declare testing variables.
  lv_file_name     VARCHAR2(255) := 'item_import.csv';
  lv_virtual_dir   VARCHAR2(255) := 'UPLOAD_FILES';
  lv_table_name    VARCHAR2(30)  := 'IMPORT_DATA';
  lv_column_name   VARCHAR2(30)  := 'IMPORT_DATA';
  lv_foreign_key   NUMBER;
 
BEGIN
 
  -- Assign the current value of the sequence to a local variable.
  lv_foreign_key := import_master_s.currval;
 
  -- Check if you can read and insert a CLOB column.
  IF load_clob_from_file(lv_file_name
                        ,lv_virtual_dir
                        ,lv_table_name
                        ,lv_table_name
                        ,lv_foreign_key) = 1 THEN
 
    -- Display a successful subordinate routine.
    dbms_output.put_line('Subordinate routine succeeds.');
  ELSE
    -- Display a failed subordinate routine.
    dbms_output.put_line('Subordinate routine fails.');
  END IF;
 
END load_clob_from_file;
/

You can test this procedure against the log file with the following script file:

DECLARE
 
  -- Declare testing variables.
  lv_file_name     VARCHAR2(255) := 'item_import_ext_table.log';
  lv_virtual_dir   VARCHAR2(255) := 'UPLOAD_LOGS';
  lv_table_name    VARCHAR2(30)  := 'IMPORT_LOG';
  lv_column_name   VARCHAR2(30)  := 'IMPORT_LOG';
  lv_foreign_key   NUMBER;
 
BEGIN
 
  -- Assign the current value of the sequence to a local variable.
  lv_foreign_key := import_master_s.currval;
 
  dbms_output.put_line('Foreign key ['||lv_foreign_key||']');
 
  -- Check if you can read and insert a CLOB column.
  IF load_clob_from_file(lv_file_name
                        ,lv_virtual_dir
                        ,lv_table_name
                        ,lv_table_name
                        ,lv_foreign_key) = 1 THEN
 
    -- Display a successful subordinate routine.
    dbms_output.put_line('Subordinate routine succeeds.');
  ELSE
    -- Display a failed subordinate routine.
    dbms_output.put_line('Subordinate routine fails.');
  END IF;
 
END;
/

You now have the ability to read and store the source and log files in CLOB columns. The next step is to write a master function that writes the master row and calls the LOAD_CLOB_FROM_FILE function for the source file and each of the log files. That’s what the CLEANUP_EXTERNAL_FILES function provides.

Unfortunately, the Java logic requires using the logical and operation, which is two ampersands (&&). This requires that you turn off substitution variables in SQL*Plus. You do that by disabling DEFINE, like this:

SET DEFINE OFF

You can compile this Java library file after you’ve disabled LOAD_CLOB_FROM_FILE:

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
CREATE OR REPLACE FUNCTION cleanup_external_files
( table_in           VARCHAR2
, data_directory_in  VARCHAR2
, log_directory_in   VARCHAR2 ) RETURN NUMBER IS
 
  -- Declare a local Attribute Data Type (ADT).
  TYPE list IS TABLE OF VARCHAR2(3);
 
  -- Declare a collection.
  lv_extension LIST := list('csv','log','bad','dis');
 
  -- Define a default return value.
  retval NUMBER := 0;
 
  -- Declare base target table name.
  lv_target_table  VARCHAR2(30) := 'IMPORT';
  lv_foreign_key   NUMBER;
 
  -- Decalre a cursor to find external tables.
  CURSOR check_source (cv_table_name VARCHAR2) IS
    SELECT   xt.file_name
    FROM    (SELECT   uxt.TABLE_NAME
             ,        ixt.column_value AS file_name
             FROM     user_external_tables uxt CROSS JOIN
             TABLE(list_files(get_directory_path(uxt.default_directory_name))) ixt) xt
    JOIN     user_external_locations xl ON xt.TABLE_NAME = xl.TABLE_NAME
    AND      xt.file_name = xl.location AND xt.TABLE_NAME = UPPER(cv_table_name);
 
  -- Declare a cursor to find files and compare for one input file name.
  CURSOR check_logs (cv_file_name VARCHAR2) IS
    SELECT   list.column_value
    FROM     TABLE(list_files(get_directory_path('UPLOAD_LOGS'))) list
    JOIN    (SELECT cv_file_name AS file_name FROM dual) FILTER
    ON       list.column_value = FILTER.file_name;
 
  -- Declare the function as autonomous.
  PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
  -- Master loop to check for source and log files.  
  FOR i IN check_source (table_in) LOOP
 
    -- Assign next sequence value to local variable.
    lv_foreign_key := import_master_s.NEXTVAL;
 
    -- Write the master record and commit it for the autonomous threads.
    INSERT INTO import_master
    VALUES (lv_foreign_key,'ITEM_IMPORT_EXT_TABLE');
    COMMIT;
 
    -- Process all file extensions.    
    FOR j IN 1..lv_extension.COUNT LOOP
 
      -- The source data file is confirmed by the CHECK_SOURCE cursor.
      IF lv_extension(j) = 'csv' THEN
 
        --  Load the source data file.
        -- ----------------------------------------------------------
        --  The RETVAL holds success or failure, this approach 
        --  suppresses an error when the file can't be loaded.
        --  It should only occur when there's no space available 
        --  in the target table.
        retval := load_clob_from_file(i.file_name
                                     ,data_directory_in
                                     ,lv_target_table||'_DATA'
                                     ,lv_target_table||'_DATA'
                                     ,lv_foreign_key);
                                     lv_foreign_key := lv_foreign_key + 1;
      ELSE
 
        -- Verify that log file exists before attempting to load it.
        FOR k IN check_logs (LOWER(table_in)||'.'||lv_extension(j)) LOOP
 
          --  Load the log, bad, or dis(card) file.
          -- ----------------------------------------------------------
          --  The RETVAL holds success or failure, as mentioned above.
          retval := load_clob_from_file(LOWER(table_in)||'.'||lv_extension(j)
                                       ,log_directory_in
                                       ,lv_target_table||'_'||lv_extension(j)
                                       ,lv_target_table||'_'||lv_extension(j)
                                       ,lv_foreign_key);
        END LOOP;
      END IF;
    END LOOP;
    retval := 1;
  END LOOP;
  RETURN retval;
END;
/

Deleting Files from Virtual Directories

After you’ve read the files through a query and uploaded the source and log files to the database, you need to cleanup the files. This can be done by using another Java library function, provided you granted read, write, and delete privileges to the internal Java permissions file.

The DeleteFile Java library deletes files from the file system. It doesn’t put them in the trash can for final delete, it removes them completely.

Now you can build the Java library that lets you delete a file. A quick caveat, this code includes an AND logical operator that is two ampersands (&&). SQL uses an ampersand (&) for substitution variables. You’ll need to suppress that behavior when you run this code.

You do that by issuing the following command to disable substitution variables in SQL*Plus:

1
SET DEFINE OFF

You create the DeleteFile library like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DeleteFile" AS
  // Java import statements
  import java.io.File;
  import java.security.AccessControlException;
 
  // Class definition.
  public class DeleteFile
  {
    // Define variable(s).
    private static File file;
 
    // Define copyTextFile() method.
    public static void deleteFile(String fileName) throws AccessControlException {
 
      // CREATE files FROM canonical file names.
      file = NEW File(fileName);
 
      // DELETE file(s).
      IF (file.isFile() && file.DELETE()) {}}}
/

You need a PL/SQL Wrapper to call the library, and here it is:

1
2
3
4
CREATE OR REPLACE PROCEDURE delete_file (dfile VARCHAR2) IS
LANGUAGE JAVA
NAME 'DeleteFile.deleteFile(java.lang.String)';
/

You can call this separately or embed it inside the UPLOAD_LOGS function, which saves re-writing the logic to find any source or log files.

This has provided you with an external table import framework. You can extend the framework by wrapping the query in an object table function. Such a function would afford you the opportunity to cleanup the source and log files after the query operation.

Written by maclochlainn

March 5th, 2012 at 12:19 am

How to use object types?

with 3 comments

A tale of Oracle SQL object types, their constructors, and how you use them. This demonstrates what you can and can’t do and gives brief explanations about why.

The following creates a base SAMPLE_OBJECT data type and a sample_table
collection of the base SAMPLE_OBJECT data type.

CREATE OR REPLACE TYPE sample_object IS OBJECT
(id       NUMBER
,name     VARCHAR2(30));
/
 
CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
/

If the base SAMPLE_OBJECT data type were a Java object, the default constructor of an empty call parameter list would allow you to construct an instance variable. This doesn’t work for an Oracle object type because the default constructor is a formal parameter list of the object attributes in the positional order of their appearance in the declaration statement.

The test case on this concept is:

1
2
3
4
5
6
DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object();
BEGIN
  NULL;
END;
/

Running the program raises the following exception, which points to the object instance constructor from line 2 above:

  lv_object_struct SAMPLE_OBJECT := sample_object();
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_OBJECT'
ORA-06550: line 2, column 20:
PL/SQL: Item ignored

Changing the instantiation call to the Oracle design default, two null values let you create
an instance of the SAMPLE_OBJECT type. The following shows that concept, which works when the base object type allows null values.

1
2
3
4
5
6
DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object(NULL, NULL);
BEGIN
  NULL;
END;
/

If you want to have a null parameter constructor for an object type, you must implement a type and type body with an overloaded no argument constructor, like this:

1
2
3
4
5
CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT);
/
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(NULL,NULL);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

Unlike Java, the addition of an overloaded constructor doesn’t drop the default constructor. You can also create a single parameter constructor that leverages the sequence like this:

1
2
3
4
5
6
CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT);
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,NULL);
  BEGIN
    SELF := sample_obj;
  END sample_object;
  CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,pv_name);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

You can test the final object type and body with this anonymous block of code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SET SERVEROUTPUT ON SIZE UNLIMITED
 
DECLARE
  lv_object_struct1 SAMPLE_OBJECT := sample_object();
  lv_object_struct2 SAMPLE_OBJECT := sample_object('User Name');
  lv_object_struct3 SAMPLE_OBJECT := sample_object(1001,'User Name');
BEGIN
  dbms_output.put_line('lv_object_struct1.id   ['||lv_object_struct1.id||']');
  dbms_output.put_line('lv_object_struct1.name ['||lv_object_struct1.name||']');
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  lv_object_struct2.name := 'Changed Name';
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  dbms_output.put_line('lv_object_struct3.id   ['||lv_object_struct3.id||']');
  dbms_output.put_line('lv_object_struct3.name ['||lv_object_struct3.name||']');
END;
/

It prints to console:

lv_object_struct1.id   [1]
lv_object_struct1.name []
lv_object_struct2.id   [2]
lv_object_struct2.name [User Name]
lv_object_struct2.id   [2]
lv_object_struct2.name [Changed Name]
lv_object_struct3.id   [1001]
lv_object_struct3.name [User Name]

Hope this helps those looking for a quick syntax example and explanation.

Written by maclochlainn

February 14th, 2012 at 8:14 pm

Function or Procedure?

with 7 comments

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to 1/2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value.
  lv_result   BINARY_DOUBLE;
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    lv_result := POWER(pv_number,pv_power);
  ELSE
    lv_result := POWER(pv_number,(1 / pv_power));
  END IF;
 
  RETURN lv_result;
END find_root_function;
/

You can test it with these to queries against the dual table:

SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual;
SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual;

The procedure does the same thing as the function. The difference is that the fourth parameter to the procedure returns the value rather than a formal return type like a function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE PROCEDURE find_root_procedure
( pv_number   IN     BINARY_DOUBLE
, pv_power    IN     BINARY_DOUBLE
, pv_inverse  IN     BINARY_INTEGER DEFAULT 0
, pv_return      OUT BINARY_DOUBLE ) IS
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    pv_return := POWER(pv_number,pv_power);
  ELSE
    dbms_output.put_line('here');
    pv_return := POWER(pv_number,(1 / pv_power));
  END IF;
 
END find_root_procedure;
/

You can test it inside an anonymous block PL/SQL program, like this:

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
DECLARE
 
  -- Declare input variables.
  lv_input   BINARY_DOUBLE;
  lv_power   BINARY_DOUBLE;
  lv_inverse BINARY_INTEGER;
  lv_output  BINARY_DOUBLE;
 
BEGIN
 
  -- Assign input values to variables.
  lv_input := '&1';
  lv_power := '&2';
  lv_inverse := '&3';
 
  -- Test raising to a power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
  -- Test raising to an inverse power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
END;
/

You can test it inside an anonymous block PL/SQL program, like the following example. For reference, the difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block for a Native Dynamic SQL (NDS) call. Inside a PL/SQL block you would use EXECUTE IMMEDIATE because it dispatches a call from the current running scope to a nested scope operation (see comment below).

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
-- SQL*Plus Test.
VARIABLE sv_input BINARY_DOUBLE
VARIABLE sv_power BINARY_DOUBLE
VARIABLE sv_inverse BINARY_DOUBLE
VARIABLE sv_output  BINARY_DOUBLE
 
-- Verify the null value of the session variable.
SELECT :sv_output AS ":sv_output" FROM dual;
 
BEGIN
 
  -- Prompt for local assignments and initialize output variable.
  :sv_input   := '&1';
  :sv_power   := '&2';
  :sv_inverse := '&3';
  :sv_output  := 0;
 
END;
/
 
-- Run the procedure in the SQL*Plus scope.
EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output);
 
-- Query the new value of the session variable.
SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual;

As usual, I hope this helps folks beyond the one who asked. Comments are always welcome.

Written by maclochlainn

January 31st, 2012 at 5:00 pm

Updating Table View Columns

with 2 comments

Answering a reader’s question: How can you sort data inside an Oracle table view column? This blog post shows you how to perform the trick, but for the record I’m not a fan of nested tables. A table view column is an Oracle specific user-defined type (UDT), and is nested table or varray of a scalar data type.

Oracle’s assigned a formal name to this type of UDT. It’s now labeled an Attribute Data Type (ADT). The ADT doesn’t allow you to update nested elements outside of PL/SQL program units.

This blog post reviews table view columns, and extends concepts from Oracle Database 11g & MySQL 5.6 Developer Handbook (by the way virtually everything in the book is relevant from MySQL 5.1 forward). It demonstrates how you can use PL/SQL user-defined functions (UDFs) to supplement the SQL semantics for updating nested tables, and then it shows how you can reshuffle (sort) data store the sorted data in table view columns.

Before you implement table view columns, you should answer two design questions and one relational modeling principal. You should also understand that this direction isn’t portable across database implementations. It currently supported fully by the Oracle database and mostly by PostgreSQL database. You can find how to join nested tables helpful in understanding the UPDATE statements used in this posting, and this earlier post on UPDATE and DELETE statements.

Design Questions:

  • Should you implement full object types with access methods in PL/SQL? The object type solution says there is no value in the nested data outside of the complete object. While choosing the table view column solution says that there is value to just implementing a nested list without element handling methods.
  • Should you embed the elements in an XML_TYPE? An XML solution supports hierarchical node structures more naturally, like when you only access child nodes through the parent node. While choosing the table view column solution says that you want to avoid the XML Software Development Kit and that the data set is small and more manageable in a table view column.

Design Principle:

  • Should you implement an ID-dependent relational modeling concept? An ID-dependent model replaces the primary and foreign keys with the relative position of parent and child elements. This is the design adopted when you choose a table view column, and it is more complex than single subject relational tables.

You should note that table view columns are inherently static at creation. You must also update the entire nested table view column when using Oracle SQL. Oracle SQL does let you modified attributes of object types in nested tables, as qualified in my new book (page 252).

Any attempt to modify a table view column element in SQL raises an ORA-25015 error. The error message states that (you) cannot perform DML on this nested TABLE VIEW COLUMN.

You can update the table view column value by replacing it with a new collection, and that’s done with a PL/SQL function. This type of function preserves the ordered list in the table view column by finding and replacing an element in the collection.

Unfortunately, developers who use nested tables typically design table view columns with an internal ordering scheme. That means the collection is ordered during insert or update. This type of design relies on the fact that you can’t change the order without re-writing the stored structure.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. All that aside, here’s how you ensure element updates while preserving element position:

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
CREATE OR REPLACE FUNCTION update_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2
, new_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
    ELSE
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := new_element_value;
    END IF;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  RETURN lv_element_collection;
END update_collection;
/

Then, you can use the user-defined function (UDF) inside a SQL UPDATE statement, like this:

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 = update_collection(e.street_address, 'Suite 525','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The UPDATE_COLLECTION function replaces Suite 525 with Suite 522, and preserves the sequence of elements in a new nested table. The UPDATE statement assigns the modified nested table to the table view column. You can find the code to create the employee table in Chapter 6 (pages 148-149), and the code to insert the default data in Chapter 8 (page 229) of Oracle Database 11g & MySQL 5.6.

The lv_counter variable could be replaced with a reference to the for loop’s iterator (i) because the counts of both collections are the same. I opted for the local variable to make the code easier to read.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. Along the same line of thought, you also have the ability of removing elements from a table view column with a similar PL/SQL function. You could write the function like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE FUNCTION delete_from_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
      lv_counter := lv_counter + 1;
    END IF;
  END LOOP;
 
  RETURN lv_element_collection;
END delete_from_collection;
/

Then, you can use the user-defined function (UDF) to delete an element from the collection inside a SQL UPDATE statement, like this:

1
2
3
4
5
6
UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = delete_from_collection(e.street_address,'Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

After understanding all that, let’s examine how you sort data in a nested table or varray of a scalar data type (the basis of a table view column). The easiest way is a BULK COLLECT INTO statement nested inside a function, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION sort_collection
( old_element_collection  STREET_LIST) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  -- Sort a collection alphabetically based on case sensitivity.
  SELECT   column_value BULK COLLECT INTO lv_element_collection
  FROM     TABLE(old_element_collection)
  ORDER BY column_value;
 
  RETURN lv_element_collection;
END sort_collection;
/

You could test it with this:

1
2
SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon')));

Then, you can use the user-defined function (UDF) to update a table view column like this:

1
2
3
4
5
6
UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = sort_collection(e.street_address)
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The funny thing about database solutions these days is that some Java developers don’t appreciate the simplicity of SQL and PL/SQL and would solve the problem with Java. Especially, if it was an case insensitive sort operation. That’s the hard way (easy way at the bottom), but I figured it should be thrown in because some folks think everything is generic if written in Java. Though, I thought making it proprietary would increase the irony and wrote it as a Java library for Oracle.

Here’s the Java library, which you can run from the SQL*Plus command line, SQL Developer, or that pricey Toad:

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
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class DemoSort {
    public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
      // Convert Oracle data type to Java data type.
      String[] unsorted = (String[])list.getArray();
 
      // Sort elements.
      Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER);
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
    return sorted; }}
/

Then, you write the PL/SQL wrapper like this:

1
2
3
4
CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

You could test the case insensitive sort with this:

1
2
SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon')));

Naturally, it ignores the fact you could do it like this without Java by using the UPPER function in the purely PL/SQL SORT_COLLECTION function shown earlier in this post:

12
13
14
15
    -- Sort a collection alphabetically based on case insensitive comparison.
    SELECT   column_value BULK COLLECT INTO lv_element_collection
    FROM     TABLE(old_element_collection)
    ORDER BY UPPER(column_value);

Anyway, it’s a bunch of thoughts about writing solutions for table view columns. Hope it helps those interested in nested tables.

Written by maclochlainn

December 20th, 2011 at 9:37 pm