MacLochlainns Weblog

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.

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

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.

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

Oracle

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

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

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

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

MySQL

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

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

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

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

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

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

SQL Server

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

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

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

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

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

A couple students stumbled on the MySQL on-line documentation today. Specifically, they didn’t find instructions on how a non-root user could change their password. I’ve also added this as an explanation on the MySQL documentation for 12.5.1.6. SET PASSWORD Syntax page.

You reset your own password when you’re not the root user with this syntax:

mysql> SET PASSWORD = PASSWORD('new_password');

This is simple syntax but carries a risk if you walk away from your terminal because somebody can run it without needing to know the original user password. Oracle requires that you know the original password if you use this syntax:

SQL> PASSWORD
Changing password FOR PLSQL
OLD password:
NEW password:
Retype NEW password:
Password changed

If you think that you’re safe walking away from an Oracle SQL*Plus console, check this because it doesn’t require knowing the current password either.

SQL> SHOW USER
USER IS "PLSQL"
SQL> ALTER USER plsql IDENTIFIED BY plsql;
USER altered.

Don’t walk away, always lock your terminal or use the QUIT command to exit MySQL. This generally prevents somebody hijacking your password. You can check this post if you’re interested in changing the root password when you don’t have it to begin with. Hope this helps a few folks.

The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.

The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.

1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.

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

-- Conditionally delete objects in reverse dependency order.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_type IN ('TABLE','SEQUENCE','TYPE')
            AND      object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE'
                                    ,'CONTACT_BOOK','CONTACT_BOOK_S1')
            ORDER BY object_type, object_name DESC) LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
END;
/
 
-- Create a composite data type (structure) and collection.
CREATE TYPE telephone_type AS OBJECT
( country_code   VARCHAR2(3)
, prefix_code    VARCHAR2(5)
, phone_number   VARCHAR2(9));
/
 
CREATE TYPE telephone_table AS TABLE OF telephone_type;
/
 
-- Create a table with a nested composite table and sequence.
CREATE TABLE contact_book
( contact_book_id   NUMBER
, first_name        VARCHAR2(30)
, last_name         VARCHAR2(30)
, telephone_numbers TELEPHONE_TABLE )
NESTED TABLE telephone_numbers STORE AS telephone_struct;
 
CREATE SEQUENCE contact_book_s1;
 
-- Insert two rows of data.
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Harry','Potter'
,telephone_table(telephone_type('44','20','7299 2127')
                ,telephone_type('44','17','8926 8115')
                ,telephone_type('44','18','3344 5566')
                ,telephone_type('44','72','9878 5436')));
 
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Weasley','Ronald'
,telephone_table(telephone_type('40','021','407 47 46')
                ,telephone_type('44','19','4088 1062')
                ,telephone_type('44','21','4075 5066')));

2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the CARDINALITY function introduced in Oracle 10g. The second leverages a CROSS JOIN and traditional aggregation tools.

2(a). Using the CARDINALITY function is generally the simplest and best approach to this problem:

1
2
3
4

SELECT first_name
,      last_name
,      CARDINALITY(telephone_numbers) list
FROM   contact_book;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Weasley      Ronald            3

2(b). Using the traditional CROSS JOIN and GROUP BY clause:

1
2
3
4
5
6
7

SELECT   cj.first_name
,        cj.last_name
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Ronald       Weasley           3

3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a CROSS JOIN and GROUP BY clause, like this:

1
2
3
4
5
6
7
8
9

SELECT   cj.first_name
,        cj.last_name
,        cj.country_code
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name
,        cj.country_code;

This yield the following data set:

FIRST_NAME   LAST_NAME    COUNTRY_CODE   LIST
------------ ------------ ------------ ------
Harry        Potter       44                4
Ronald       Weasley      44                2
Ronald       Weasley      40                1

If I captured the basics of the question, great. If there are more questions, please let me know.

I’m back from vacation and will start blogging again. I’ve got a couple items folks have asked me to cover and I’ll try to work those in during the next couple of weeks.

My son went over to Beijing to study Chinese this summer. I picked him up at the Airport last Friday. When he was there he found a copy of my Oracle Database 11g PL/SQL Programming in Chinese. He told me about it via Skype. I asked him to bring home a copy since my royalty statements don’t show the book was translated into Chinese. It cost about $12 US (click on the image to see a larger copy). The statements indicate it has only been translated into Russian, Portuguese, and Tata’s Indian Press at this point.

I thought it was an illegal copy, even though it was sold in a mainline store in Beijing as an original. Anyway I sent the scans of the book and ISBN to McGraw-Hill so they could sort it out. They told me it’s a legal copy, translation licensed, and that not all translations fees post to royalty statements before the translation. It’s only the fifth book, and I continue to learn about the process. Tsinghua University Press produces it. If you want a copy in Chinese, you’ll find it at their web site and my book is here. My son said they had an impressive collection of Oracle Press books on sale there too.

Somebody asked me to post some examples of Large Object syntax. I checked Chapter 9 of the Oracle Database SecureFiles and Large Object Developer’s Guide, and I agree an example or two would be helpful. I’ve provided CREATE and ALTER statements.

Here’s a sample CREATE TABLE statement for a CLOB, BLOB, and BFILE:

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 TABLE item
( item_id             NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_barcode        VARCHAR2(20)  CONSTRAINT nn_item_1  NOT NULL
, item_type           NUMBER        CONSTRAINT nn_item_2  NOT NULL
, item_title          VARCHAR2(60)  CONSTRAINT nn_item_3  NOT NULL
, item_subtitle       VARCHAR2(60) 
, item_desc           CLOB          CONSTRAINT nn_item_4  NOT NULL
, item_blob           BLOB
, item_photo          BFILE
, item_rating         VARCHAR2(8)   CONSTRAINT nn_item_5  NOT NULL
, item_rating_agency  VARCHAR2(4)   CONSTRAINT nn_item_6  NOT NULL
, item_release_date   DATE          CONSTRAINT nn_item_7  NOT NULL
, created_by          NUMBER        CONSTRAINT nn_item_8  NOT NULL
, creation_date       DATE          CONSTRAINT nn_item_9  NOT NULL
, last_updated_by     NUMBER        CONSTRAINT nn_item_10 NOT NULL
, last_update_date    DATE          CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1                      FOREIGN KEY(item_type)
  REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2                      FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3                      FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id))
  LOB (item_desc) STORE AS BASICFILE item_desc
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645))  
, LOB (item_blob) STORE AS item_blob
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645));

If you attempt to make the CHUNK size greater than 32,768, it will raise an ORA-22851: invalid CHUNK LOB storage option value error.

Here’s a sample ALTER TABLE statement that adds a new large object column to a table. You can’t modify an existing large object column. You raise an ORA-22859: invalid modification of columns error when you try it. If you need to make modifications, check this post form last year on re-organizing LOB indexes.

1
2
3
4
5
6
7
8

ALTER TABLE item ADD (another CLOB)
LOB (another) STORE AS BASICFILE item_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));

This all makes sense, but hold on there’s a potential problem when you add a large object column to a table. I’m not sure it’s a bug but I’ll probably open a TAR on it later in the week (arghhhh! I once worked in Oracle Support. :-)). Anyway, here’s what I found:

If you drop and recreate the table, you can encounter an ORA-01430 error. It appears that the large object is really hanging out in the catalog. As soon as you try to re-add it, you get the error.

If you describe the table after recreating it, you’ll see the following:

SQL> DESCRIBE item
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ITEM_ID                                   NOT NULL NUMBER
 ITEM_BARCODE                              NOT NULL VARCHAR2(20)
 ITEM_TYPE                                 NOT NULL NUMBER
 ITEM_TITLE                                NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE                                      VARCHAR2(60)
 ITEM_DESC                                 NOT NULL CLOB
 ITEM_BLOB                                          BLOB
 ITEM_PHOTO                                         BINARY FILE LOB
 ITEM_RATING                               NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY                        NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE                         NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE

Also, if you run the following query with the DBMS_METADATA package, the column doesn’t exist in the table defintion:

SELECT dbms_metadata.get_ddl('TABLE','ITEM') FROM dual;

If try to run the ALTER statement to add the column that doesn’t appear to exist, you’ll get the following message in Oracle 11.1.0.7.0:

ALTER TABLE item ADD (another CLOB)
                      *
ERROR at line 1:
ORA-01430: COLUMN being added already EXISTS IN TABLE

If you rerun the DBMS_METADATA.GET_DDL query, it’ll be there in the table definition. Also, in its non-existence but existent state (ghost-state), there’s nothing in the DBA_LOB, or DBA_SEGMENT views. You can find a query to check large object segments and indexes here. My questions is where has it gone, why is it there, and did I do something wrong because this looks like basic functionality.

If you know the mystery or the existence of a bug, please post a comment.