MacLochlainns Weblog

Over a year ago, I put out a blog page on how to query the contents of a table from an Oracle database through Excel. I meant to get back to provide much more about this. Specifically, I want to cover the XML mechanism used to accomplish the task. However, a blog reader augmented my page with a solution comment. I’m thrilled he did because it tells me to get a basic thing out rather than hang folks up halfway. My thanks go to Vernon.

Here’s a quick addendum to the original post with screen shots because folks tell me they’re very useful. It demonstrates how you write queries in Microsoft Excel against the Oracle database. While I plan a full tutorial on a different URL, this should help everybody in the meantime. This content is dependent on the generic mechanism, which I covered in this older post.

It starts with an alternative to Step #9 in the older blog page, and therefore, I’ve started the numbering as 9 there.

9. The Import Data dialog asks you how and where you want to import it. When you don’t want the contents of a simple table, click the Properties button.

10. Clicking the prior dialog’s Property button brings you to the Connection Properties dialog. The Usage tab is first for a reason but the Definition tab is where you need to go to enter a free form query. Click the Definition tab highlighted in yellow below.

11. In this dialog, the most important boxes are the Command type (as you may guess, Table is the default value) and Command text boxes. The first thing to do, is click on the Command type multiple select widget and choose SQL.

Now, you can write any query that you’d like against the selected Oracle schema. You may reference any tables or views for which your user (schema) has SELECT permissions. You don’t need to limit yourself to a single table because ANSI SQL:89 (comma delimited tables), and ANSI SQL:92 (INNER JOIN, et cetera) are both supported. Don’t forget that Oracle uses a different concatenation method than Microsoft Access or SQL Server, you can find that here. After you’ve entered your query, click the OK button to proceed.

12. When you clicked OK, you’ve instructed Microsoft Excel 2007 to change the connection, it raises this dialog box (click on it if you want to read it). You click Yes to continue and save your query.

13. You’re back to where you began, more or less, this it the Import Data dialog. This is Step #9 but this time it’ll run your query not return a simple table content. Click OK to run the query. Don’t be surprised if you get a message saying no password was provided. It just means you’ll be prompted to enter one. You should never store that password because it’s stored in plain text inside an XML file.

You can find those data source files in this directory:

C:\Document and Settings\<some_user>\My Documents\My Data Sources

Installed Zend Community Edition on Windows 7 64-bit. It worked easily. You just need to remember to install the JSDK 32-bit version for the Java Bridge. Clear notation about phpMyAdmin and MySQL being separate downloads has been added to the new Zend Community Edition Server (4.0.6), and it clearly does support Windows 7.

If you plan on installing MySQL and Oracle, I would recommend you install MySQL after you install Oracle and the Zend Community Server. However, it doesn’t matter because both ways work.

That completes my WAMP (Windows, Apache, MySQL, Perl, PHP, or Python) and OPAW (Oracle, Perl, PHP, or Python, Apache, Windows) installations. Actually, I’m not sure there is an OPAW acronym for a LAMP stack running Oracle on a Windows platform. OPAL is the acronym for a LAMP stack running an Oracle database, but I’ve never seen one before for Windows. Therefore, I created one.

My two cents worth …

I’d vote for clearer guidance on these acronyms. After all, they’re only purpose appears to be how to market variants of LAMP. The variants that I’ve seen for LAMP (Linux) are: MAMP (Mac OS X), SCAMP (Santa Cruz Operation), SAMP (Solaris), OAMP (OpenBSD, and WAMP (Windows) for MySQL database versions. The key seems to be swapping the first letter. I’ve only seen OPAL (Linux) officially for a LAMP stack that uses an Oracle database on a Linux platform. While my OPAW leverages what I perceive as a possible pattern, it may be wrong. Does anybody know what the right way to label these is?

I finally got around to installing Oracle 11g on Windows 7 Professional Edition, 64-bit (click here for Oracle 11g Release 2 on Windows 7). The only catch was that the Oracle 11g (11.1.0.7) installer validates only against Windows 5.0, 5.1, 5.2 or 6.0. Windows 7 is actually Windows Code Base 6.1, as seen in this screen shot after successful installation:

With that knowledge, first you should download the software from Oracle’s web site. You should unzip the contents into another directory. I used a C:\Stage directory. Inside that you’ll find the database directory, and it should look like this:

1. Click on the setup icon to launch the installer. You should then see the following screen shot. Enter a password for the SYS and SYSTEM users (if you’re coming to Oracle from a MySQL background they’re like the root user in MySQL). When you’ve entered a matching password in both fields, the Install button becomes active. Click the Install button to proceed.

2. In this screen, you’re prompted for your Email and MetaLink Password. If this is a test database (a throw away instance without any real data), you can uncheck the box for automatic security updates. You should enter a password even for test databases. Click the Next button to proceed.

3. You’ll only see this failure if you’re installing Oracle 11g (11.1.0.7) on a Windows 7 operating system, which is actually version 6.1 according to their code control numbering. You simply check the Checking operating system requirements … and Checking service pack requirements … to override the prerequisite checks. Another error that you may encounter is related to networking. It is a warning and occurs when you’re operating system uses DHCP to get its IP address. You really should configure the operating system with a static IP address. You can set a static IP address in Windows 7 with these instructions.

4. At this point, you’ll get a Windows System Alert asking you to unblock the installer’s javaw.exe program. You must grant the access or forget about installing Oracle 11g. Click the Allow access button to proceed.

5. The dialog tells you what will be installed. Click the Install button to proceed.

6. This is the installation progress dialog. It’s running while the Oracle Installer lays down the operating system files for the database management system. It takes about 8 to 9 minutes, so stretch your legs if you want to take a break. When it’s 100% complete, click the Next button to continue.

7. After the installation, you’ll see the first of the Configuration Assistants, which is the Oracle Net Configuration Assistant (unfortunately, I didn’t capture that screen shot but I provided a temporary substitute until I re-install it). Provided everything happens successfully, it’ll move to the next step without your intervention.

8. This one is exactly where it belongs. It means you have approximately a 5 to 6 minute break while a sample database instance is cloned for you. The cloning process copies a sample compressed database from the installation staging area to your local operating system.

9. The last Configuration Assistant is the Oracle Configuration Manager Configuration. It’s a short process, it configures the cloned sample database against your installation names and passwords. When it completes it enables the Next button. Click the Next button to complete the installation.

10. You’ve finally reached almost the end of the installation. Click the Exit button on this dialog to proceed to the “are you sure” dialog box.

You’ve successfully installed Oracle 11g on Windows 7.

While helping in the forum, I noticed that folks don’t know that Java isn’t deployed with the Oracle 10g XE. However, it strikes me that you might be able to fudge it but I’m not sure that’s allowed in the EULA. If you want Java inside the database, why wouldn’t you install the licensed product?

There was an OTN article that listed three limitations but this wasn’t one in the article. Maybe I’ll run across the marketing note sometime in the furture or somebody will post the URL as a comment, which is always appreciated.

Anyway, the presence or lack of Java inside the database is pretty easy to test. You only need to do this:

SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 26 21:19:42 2009
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 - Production
 
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  public class HelloWorldSQL {
  3    public static String hello() {
  4      RETURN "Hello World."; }
  5  }
  6  /
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
*
ERROR at line 1:
ORA-29538: Java NOT installed

This is also true for Oracle Database 11g XE, as shown:

SQL*Plus: Release 11.2.0.2.0 Production ON Fri DEC 9 02:34:20 2011
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
  2  public class HelloWorldSQL {
  3    public static String hello() {
  4    RETURN "Hello World!"; }
  5  }
  6  /
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
*
ERROR at line 1:
ORA-29538: Java NOT installed

It also explains the lack of loadjava or dropjava from the $ORACLE_HOME/bin directory.

There are some subtle changes between Windows 7 and either Windows XP and Windows Vista. Since I use virtualization (with VMWare Fusion) extensively to test environments, I seem to go through this drill too often. By the way, I upgraded to VMWare Fusion 3 before testing the production releases of Windows 7.

The easiest Windows 7 installation uses DHCP. That’s what I did before patching the OS, installing virus protection software, and installing a few tools and program. Then, I change from a dynamic IP to static IP address and add a meaningful name to the C:\Windows\System32\Drivers\etc\hosts file. Here are the steps to set a static IP address on Windows 7:

1. Assuming that you’re in the Category view, you should navigate to the Control Panel, choose Network and Internet, and then click Network and Sharing Center. This is the window that you should see (click image to enlarge it):

Click the Local Area Connection to begin configuring your static IP address.

3. From the Local Area Connection Status window, click the Details button to see your existing connection details (most likely DHCP). If you’re running this in VMWare Fusion, the 172.16.153.129 is the first IP address allocated. You should note the default gateway and DNS server IP address, which should always be 172.16.153.2. Click the Close button when you’ve made note of those IP addresses for subsequent steps.

4. Back at the Local Area Connection Status window, click the Properties button. It will show you the Local Area Connection Properties dialog. Click on the Internet Protocol Version 4 (TCP/IPv4) in the item box of the dialog window.

5. Click the Use the following IP address radio button and enter the appropriate values for your static IP address. The default gateway and DNS server are generally different but are the same when you’re using NAT addressing inside VMWare.

You should be completed now. If you test the connection, Windows 7 raises and error but everything works after you reboot the operating system.

Needless to say, Windows 7 is here to stay. After all, not everybody can work on a Mac or generic Linux.

Three things about Windows bugs me from the start. They are the default power settings, User Access Controls (UAC), and that an Administrator account isn’t really an administrator unless you launch the program as such with a right click.

Power Settings

I don’t waste electricity and generally run Windows in a VMWare Fusion context. It seems to me that a desktop should assume operation unless a user configures it to suspend itself after a time limit. Windows 7 assumes the opposite, suspension is automatic in 30 minutes, unless you overwrite the settings.

You change it from the default by navigating to the Control Panel and choosing Hardware and Sound and then Power Options, or you put the interface into Icon mode. Icon mode lets you choose Power right away, eliminating a mouse click.

Click the Change plan settings link. You should then see the default values of Turn off the display set to 10 minutes; and Put the computer to sleep set to 30 minutes. If you’re using virtualization, like me, set them both to Never, as shown:

Click the Save changes button and it is now a worry of the past.

Disabling User Access Control

Windows 7 doesn’t do away with User Account Control (UAC), it enhances them. From my perspective, the user interface probably causes hesitation when you want to disable it. If you need more than my short version on disabling UAC, there’s an excellent post on the myriad ways to turn UAC off here. I’m not keen on the ads for that page and didn’t test them all.

The simplest way is to launch the Control Panel and choose System and Security. In that Window, click the Change User Account Control settings link to continue disabling UAC.

When you click that link, you’ll see this screen. Simply drag the gauge to the bottom to disable UAC, like this:

It hasn’t taken affect yet. Like every other change in the Windows Registry, you must reboot the operating system for this to take effect.

Running as the Administrator

Just because you’re account is an Administrator doesn’t mean you run programs as one. This is actually wise based on how easily the Windows OS can be infected. You must right click the executable to say Run as administrator or you can’t edit files like the C:\Windows\System32\Drivers\etc\hosts file.

If you do things first, you should have trouble with any of the setup issues for the Oracle Database 11g, or the OPAL stack with the Zend Community Server.

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

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

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

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

Finally, I’m breaking down and testing the production release. Two reasons, I’ll have students install it, and I want to test the Oracle and Zend Community Server stack. At least, the art work is very sharp. 😉

I’ll post findings as discovered. For reference, I’m installing both 32-bit and 64-bit versions.

It’s always interesting troubleshooting things that students run into when they work with Excel. For example, in Excel when is one not equal to one, or for that matter when is one less than one? If you use the CODE(cell_reference) function, you’d find both cells contain the same ASCII value of 49.

If you’re wondering why the one on the left is less than the one on the right, the one on the right is a text value and the one on the left is a number. If you use the TYPE(cell_reference) function, you’d find that a text field returns a 2 and a number (or date) cell returns a 1. All equality comparisons are identity comparisons in Microsoft Excel, which means they compare type and value. Inequality comparisons, compare type and return a value based on the type number and never compare value when the types differ. There are three other types: (1) A logical value is 4; (2) An error message is 16; and (3) An array is 64.

The answer is when one cell contains a number and the other cell contains a string (text value). That’s done here by putting an apostrophe before the number and right aligning the cell. You solve this by using the VALUE(cell_reference) function to ensure that you’re comparing values not unknown data types because there’s no implicit casting resolution in Microsoft Excel and all comparisons are identity operations.

I haven’t checked Open Office or Numbers, but I’ll do that and update this posting with findings.

I’d tweeted about the award last night but apparently the Oracle ACE Directory Server is down at the moment. At least, I got an AIM message to that effect. Fortunately, I’d left the browser open on my machine on that page. So, here’s the image you’d see on the server when it’s back up.

It’s nice to have received the award.