MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

A/UX, NeXTSTEP, & OS X

with 5 comments

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates a robust development platform and gives you more testing options with the MySQL monitor (the client software).

Notwithstanding, some Windows users appear to malign Apple and the Mac OS X on compatibility, but they don’t understand that it’s a derivative of the Research Unix, through BSD (Berkeley Software Distribution). This Unix lineage chart illustrates it well:

Screen Shot 2014-04-18 at 3.49.39 PM

I’m probably loyal to Apple because in the early 1990′s I worked on Mac OS 6, Mac OS 7, A/UX, NeXTSTEP, and AIX/6000 (Version 3) while working at APL (American President Lines) in Oakland, California. Back then, my desktop was a pricey Macintosh Quadra 950 and today I work on a pricey Mac Pro desktop. The Mac Pro lets me use VMware virtualize development environments for Oracle Linux, Red Hat Enterprise Linux, Fedora, and as you might guess Windows 7/8. My question to those dyed in the wool Microsoft users is simple, why would you choose a single user OS like Windows over a multi-user OS like Mac OS X?

Written by maclochlainn

April 18th, 2014 at 4:28 pm

Best UTL_FILE Practice

with 3 comments

In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.

My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, the loadclobfromfile and loadblobfromfile procedures. By the way, Chapter 10 of the Oracle Database 12c PL/SQL Programming has several complete examples using the DBMS_LOB package. My third assumption is that you’d like the external file packaged as a whole, which means you want it returned as a CLOB or BLOB data type.

Here’s a generic function that achieves that and avoids the nonsense with closing the file in the exception handler, or worse yet, wrapping it in another PL/SQL schema-level 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
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 read_file_to_clob
( pv_location   VARCHAR2
, pv_file_name  VARCHAR2 ) RETURN CLOB IS 
 
  /* Declare local input variables. */
  lv_location      VARCHAR2(60);
  lv_file_name     VARCHAR2(40);
 
  /* Declare a file reference pointer and buffer. */
  lv_file    UTL_FILE.FILE_TYPE;  -- File reference
  lv_line    VARCHAR2(32767);     -- Reading buffer
 
  /* Declare local sizing variables. */
  lv_file_size  NUMBER;
  lv_line_size  NUMBER;
  lv_read_size  NUMBER :=0;
 
    /* Declare local file attribute data. */
  lv_file_exists  BOOLEAN := FALSE;
  lv_block_size   BINARY_INTEGER;
 
  /* Declare a control variable and return CLOB variable. */
  lv_enable  BOOLEAN := FALSE;
  lv_return  CLOB;
BEGIN
  /* Declare local input variables. */
  lv_location  := pv_location;
  lv_file_name := pv_file_name;
 
  /* Check for open file and close when open. */
  IF utl_file.is_open(lv_file) THEN
    utl_file.fclose(lv_file);
  END IF;
 
  /* Read the file attributes to get the physical size. */
  utl_file.fgetattr( location    => lv_location
                   , filename    => lv_file_name
                   , fexists     => lv_file_exists
                   , file_length => lv_file_size
                   , block_size  => lv_block_size ); 
 
  /* Open only files that exist. */
  IF lv_file_exists THEN
 
    /* Create a temporary CLOB in memory. */
    dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.CALL);
 
    /* Open the file for read-only of 32,767 byte lines. */
    lv_file := utl_file.fopen( location     => lv_location
                             , filename     => lv_file_name
                             , open_mode    => 'R'
                             , max_linesize => 32767);
 
 
    /* Read all lines of a text file. */
    WHILE (lv_read_size < lv_file_size) LOOP
      /* Read a line of text until the eof marker. */
      utl_file.get_line( file   => lv_file
                       , buffer => lv_line );
 
      /* Add the line terminator or 2 bytes to its length. */
      lv_line := NVL(lv_line,'')||CHR(10);
      lv_read_size := lv_read_size
                    + LENGTH(NVL(lv_line,CHR(10))) + 2;
 
      /* Write to an empty CLOB or append to an existing CLOB. */
      IF NOT lv_enable THEN
        /* Write to the temporary CLOB variable. */
        dbms_lob.WRITE( lv_return, LENGTH(lv_line), 1, lv_line);
 
        /* Set the control variable. */
        lv_enable := TRUE;
      ELSE
        /* Append to the temporary CLOB variable. */
        dbms_lob.writeappend( lv_return, LENGTH(lv_line),lv_line);
      END IF;
    END LOOP;
 
    /* Close the file. */
    utl_file.fclose(lv_file);
  END IF;
 
  /* This line is never reached. */
  RETURN lv_return;
EXCEPTION
  WHEN OTHERS THEN
    utl_file.fclose(lv_file);
    RAISE NO_DATA_FOUND;
END;
/

You can test the function with the following:

SET LONG 100000
SET PAGESIZE 999
 
SELECT read_file_to_clob('SomeVirtualDirectory','TextFile.txt') AS "Output"
FROM   dual;

If anybody has suggestions for improvements, please pass them along. As always, I hope this helps other developers.

Written by maclochlainn

April 16th, 2014 at 3:07 am

Using UTL_FILE Package

with 5 comments

Sometimes I’m surprised. Today, the surprise came when somebody pointed to a potential error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters. While it works with only three parameters when the strings are less than or equal to 1,024 (thanks Gary), it throws read errors when a string exceeds the default. You use the fourth parameter when your string exceeds the default length of 1,024.

DECLARE
*
ERROR at line 1:
ORA-29284: file READ error
ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at line 26

As mentioned, you fix the problem by using the fourth parameter like the call on lines 15 through 18 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE
  /* Declare local input variables. */
  lv_location      VARCHAR2(60) := 'C:\Data\Direct';
  lv_file_name     VARCHAR2(40) := 'TextFile.txt';
 
  /* Declare a file reference pointer and buffer. */
  lv_file     UTL_FILE.FILE_TYPE;  -- File reference
  lv_line     VARCHAR2(32767);     -- Reading buffer
 
BEGIN
 
  /* Check for open file and close when open. */
  IF utl_file.is_open(lv_file) THEN
    utl_file.fclose(lv_file);
  END IF;
 
  /* Open the file for read-only of 32,767 lines of text.
     The fourth parameter is required when you want to use
     the GET_LINE procedure to read a file line-by-line. */
  lv_file := utl_file.fopen( location     => lv_location
                           , filename     => lv_file_name
                           , open_mode    => 'R'
                           , max_linesize => 32767);
 
  /* Read all lines of a text file. */
  LOOP
    /* Read a line of text, when the eof marker is found 
       the get_line procedure raises a NO_DATA_FOUND 
       error, which is why there's no explicit loop. */
    utl_file.get_line( file   => lv_file
                     , buffer => lv_line );
 
    /* Print the line of text or a line return because
       UTL_FILE doesn't read line returns. */
    dbms_output.put_line(NVL(lv_line,CHR(10)));
 
  END LOOP;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    utl_file.fclose(lv_file);
END;
/

While the foregoing is traditional and uses a physical directory path from the local server’s operating system, you can use a virtual directory from Oracle Database 10g forward. This physical directory is also defined in the utl_file_dir parameter of the database server. A virtual directory hides the physical directory from the application software, which simplifies physical file maintenance when you need to move the files.

3
  lv_location      VARCHAR2(60) := 'DirectVirtualDirectory';

The preceding example works, and I’ll put one out converting the external to CLOB later in the week.

Written by maclochlainn

April 13th, 2014 at 11:46 pm

DBMS_JAVA Privilege Error?

without comments

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM     TABLE(list_files(get_directory_path('STAGE')))
               *
ERROR at line 2:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
java.security.AccessControlException: Directory permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read,write,delete');
  6  END;
  7  /

You would get this message when trying to delete an external file:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
c:\DATA\log\item_import.log READ) has NOT been GRANTED TO IMPORTER. The PL/SQL
TO GRANT this IS dbms_java.grant_permission( 'IMPORTER',
'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' )
ORA-06512: at "IMPORTER.DELETE_FILE", line 1
ORA-06512: at line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload\item_import.log'
  5                               ,'read,write,delete');
  6  END;
  7  /

As always, I hope this helps other developers.

Written by maclochlainn

March 29th, 2014 at 1:59 pm

Hidden DBMS_JAVA Nuance

without comments

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: importer : USER OR ROLE id does NOT exist
ORA-06512: at "SYS.DBMS_JAVA", line 705
ORA-06512: at line 2

The problem was simple, while not published, you must enter the user/schema name in uppercase text when calling DBMS_JAVA.GRANT_PERMISSION procedure. Here’s a proper example:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

If you change the IMPORTER string to lower or mixed case, you raise the aforementioned error. Quite an interesting tidbit that I missed from Oracle Database 10g until Oracle Database 12c. Hope this helps you avoid wondering what’s happening when your line 2 looks like this:

  2    DBMS_JAVA.GRANT_PERMISSION('Importer'

As always, I hope this helps others. If you like more on writing Java inside the Oracle database you can check this older post.

Written by maclochlainn

March 21st, 2014 at 12:44 am

Deprecated mysqli Functions

without comments

PHPDeprecation5_4

Having noticed the release of PHP 5.5.10 last week while I was speaking at UTOUG, I checked the deprecated mysqli functions web page. There weren’t any deprecated by 5.5. Unfortuantely, there were six mysqli functions deprecated in 5.3 and removed in 5.4. Unfortunately, many of my posted code examples use 5.2 or 5.3 where they would have worked. The deprecated mysqli functions are:

  • mysqli_bind_param
  • mysqli_bind_result
  • mysqli_client_encoding
  • mysqli_fetch
  • mysqli_param_count
  • mysqli_send_long_data

Unfortunately, that puts a lot of updates on my queue of things to do. I imagine it likewise imposes limits on those running commercially on PHP 5.3 or older that plan an upgrade.

It does remind me that I need to pay close attention to the deprecation of mysqli functions with each release. These are actually the largest change since PHP 5.0.

Written by maclochlainn

March 18th, 2014 at 12:28 pm

Posted in MySQL,mysqli,PHP

Tagged with , ,

Capture MySQL Foreign Keys

with one comment

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' '
               ,'FOREIGN KEY (',kcu.column_name,')',' '
               ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' 
               ,'(',kcu.referenced_column_name,');') AS script
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'foreign key'
ORDER BY tc.TABLE_NAME
,        kcu.column_name;

You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:

AND      tc.table_schema = 'your_mysql_database'
AND      tc.table_name = 'your_table_name'

Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.

The command_list table that stores the values is:

CREATE TABLE command_list
( command_list_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sql_command      VARCHAR(6)    NOT NULL
, sql_object       VARCHAR(10)   NOT NULL
, sql_constraint   VARCHAR(11)
, sql_statement    VARCHAR(768)  NOT NULL);

This is the storedForeignKeys procedure:

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
CREATE PROCEDURE storeForeignKeys
( pv_schema_name  VARCHAR(64)
, pv_table_name   VARCHAR(64))
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE sql_stmt                    VARCHAR(1024);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key table, it uses null replacement
     because the procedure supports null parameters. When you use null 
     parameters, you get all foreign key values. */
  DECLARE foreign_key_table CURSOR FOR
    SELECT   tc.table_schema
    ,        tc.table_name
    ,        tc.constraint_name
    FROM     information_schema.table_constraints tc
    WHERE    tc.table_schema = IFNULL(lv_schema_name, tc.table_schema)
    AND      tc.table_name = IFNULL(lv_table_name, tc.table_name)
    AND      tc.constraint_type = 'FOREIGN KEY'
    ORDER BY tc.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
 
  /* Open a local cursor. */  
  OPEN foreign_key_table;
  cursor_foreign_key_table: LOOP
 
    /* Fetch a row into the local variables. */
    FETCH foreign_key_table
    INTO  lv_schema_name
    ,     lv_table_name
    ,     lv_constraint_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF;
 
    /* The nested calls to the columnList function returns the list of columns
       in the foreign key. Surrogate primary to foreign keys can be resolved 
       with a simply query but natural primary to foreign key relationships
       require the list of columns involved in the primary and foreign key.
       The columnList function returns the list of foreign key columns in 
       the dependent table and the list of referenced columns (or the primary
       key columns) in the independent table. */
    SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' '
                          ,'ADD CONSTRAINT ',lv_constraint_name,' '
                          ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name));
 
    /* Record the SQL statements. */
    INSERT INTO command_list
    ( sql_command
    , sql_object
    , sql_constraint
    , sql_statement )
    VALUES
    ('ALTER'
    ,'TABLE'
    ,'FOREIGN KEY'
    , sql_stmt );
 
  END LOOP cursor_foreign_key_table;
  CLOSE foreign_key_table;  
 
END;
$$

This is the columnList 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
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
CREATE FUNCTION columnList
( pv_schema_name      VARCHAR(64)
, pv_table_name       VARCHAR(64)
, pv_constraint_name  VARCHAR(64)) RETURNS VARCHAR(512)
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE lv_column_count             INT UNSIGNED;
  DECLARE lv_column_name              VARCHAR(64);
  DECLARE lv_column_list              VARCHAR(512);
  DECLARE lv_column_ref_list          VARCHAR(64);
  DECLARE lv_referenced_table_schema  VARCHAR(64);
  DECLARE lv_referenced_table_name    VARCHAR(64);
  DECLARE lv_referenced_column_name   VARCHAR(64);
  DECLARE lv_return_string            VARCHAR(768);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key column. */
  DECLARE foreign_key_column CURSOR FOR
    SELECT   kcu.column_name
    ,        kcu.referenced_table_schema
    ,        kcu.referenced_table_name
    ,        kcu.referenced_column_name
    FROM     information_schema.key_column_usage kcu
    WHERE    kcu.referenced_table_schema = lv_schema_name
    AND      kcu.table_name = lv_table_name
    AND      kcu.constraint_name = lv_constraint_name
    ORDER BY kcu.column_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
  SET lv_constraint_name := pv_constraint_name;
 
  /* Set the first column value. */
  SET lv_column_count := 1;
 
  /* Open the nested cursor. */
  OPEN  foreign_key_column;
  cursor_foreign_key_column: LOOP
 
    /* Fetch a row into the local variables. */    
    FETCH foreign_key_column
    INTO  lv_column_name
    ,     lv_referenced_table_schema
    ,     lv_referenced_table_name
    ,     lv_referenced_column_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF;
 
    /* Initialize the column list or add to it. */
    IF lv_column_count = 1 THEN
      SET lv_column_list := lv_column_name;
      SET lv_column_ref_list := lv_referenced_column_name;
 
      /* Increment the counter value. */
      SET lv_column_count := lv_column_count + 1;
    ELSE
      SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name);
      SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name);
    END IF;
 
  END LOOP cursor_foreign_key_column;
  CLOSE foreign_key_column;
 
  /* Set the return string to a list of columns. */
  SET lv_return_string :=
        CONCAT(lv_column_list,')',' '
              ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' 
              ,'(',lv_column_ref_list,');');
 
  RETURN lv_return_string;
END;
$$

You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):

CREATE TABLE parent
( first_name  VARCHAR(20)  NOT NULL DEFAULT ''
, last_name   VARCHAR(20)  NOT NULL DEFAULT ''
, PRIMARY KEY (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE child
( child_name  VARCHAR(20)  NOT NULL
, first_name  VARCHAR(20)  DEFAULT NULL
, last_name   VARCHAR(20)  DEFAULT NULL
, PRIMARY KEY (child_name)
, KEY fk_parent(first_name, last_name)
, CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name)
  REFERENCES parent (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You call the storeForeignKeys procedure for the child table with this syntax:

CALL storeForeignKeys('studentdb', 'child');

You call the storeForeignKeys procedure for all tables in a schema with this syntax:

CALL storeForeignKeys('studentdb', null);

While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:

CALL storeForeignKeys(null, null);

You can export the command sequence with the following command to a script file:

SELECT sql_statement
INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM command_list;

While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.

Written by maclochlainn

March 17th, 2014 at 11:27 pm

Hobbyist at Heart

without comments

Fifteen years ago when I dove into JavaScript friends called me a hobbyist and said I was wasting my time. They had some basis in what they said because most of my work is server side with the database or operating system. However, they haven’t said that I wasted my time with the advent of NoSQL solutions, like Cassandra and MongoDB; or frameworks like node.js. Now writing JavaScript is mainstream and an invaluable skill set, and they’re trying to catch up. While David Flanagan’s JavaScript: The Definitive Guide is comprehensive, I recommend my students start with David McFarland’s JavaScript & jQuery: The Missing Manual. I also strongly suggest they use jQuery first and write JavaScript when jQuery isn’t suitable.

CSS3: The Missing ManualI really liked David McFarland’s CSS: The Missing Manual when it came out in 2009, and I’ve recommended it for several years. However, he’s got a new version – CSS3: The Missing Manual that came out in late January 2014. It’s an improvement over his first volume and I’d recommend you upgrade if you’re writing, modifying, or maintaining Cascading Style Sheet or if you just want to learn more about CSS.

Fortunately for me, CSS3: The Missing Manual is available through iTunes for Apple users, Naturally, it’s also available on Safari and Kindle formats. As an Apple user, I opted for the iBook format for my iPad Air. Unfortunately, it’s $27.99 as an iBook compared to $15.49 on Kindle, and that almost makes me opt to use the Kindle App. ;-)

Written by maclochlainn

March 16th, 2014 at 4:20 pm

Posted in CSS,CSS3,JavaScript,jQuery,JSON

Tagged with , , , ,

Speaking at UTOUG 2014

with one comment

Oracle Database 12c PL./SQL ProgrammingI’m off shortly to attend and speak at the Utah Oracle User Group’s Spring Training Days 2014, which is held at Salt Lake Community College – Larry H. Miller Campus. My presentation is on writing PL/SQL for Oracle Database 12c. We’ll be covering how you can best anchor PL/SQL cursors to include or exclude hidden columns, and how to work with identifying columns in the scope of multiple table inserts.

I have copy of Oracle Database 12c PL/SQL Programming to give away in my session and John Harper’s session. While the book is also available on Kindle, Mac OS X users can get it as a natively as an iBook on the Apple Store. I’ve got a copy on my iPad and it looks great. What I especially like is the search feature.

Written by maclochlainn

March 11th, 2014 at 1:03 pm

Posted in Oracle,Oracle 12c,UTOUG

Tagged with ,

Word 2010 – Add Template

without comments

You can add a Word Template without a problem until you get to Word 2010 because they’re hidden under the Developer ribbon, which is disabled by default. It’s funny something so widely used would be placed on a ribbon that is disabled by default.

It didn’t come up in the first few Google search pages. I figured that I’d blog it because it took me more than a couple minutes.

You can enable the ribbon with the following steps:

  1. Click the File menu choice as shown in the image.

WordTemplate01

  1. Click the Options menu item as shown in the image.

WordTemplate02

  1. Click the Customize Ribbon menu item from the left column.

WordTemplate03

  1. After clicking the Customize Ribbon menu item, the dialog on the right changes as shown below.

WordTemplate04

  1. Click the Developer checkbox to enable the Developer Ribbon that lets you assign a macro template.

WordTemplate05

  1. AFter enabling the Developer Ribbon, it now appears whenever you open or launch Word 2010.

WordTemplate06

Written by maclochlainn

March 9th, 2014 at 3:35 pm