MacLochlainns Weblog

Here’s the Oracle Database 11g Express Edition step-by-step installation. I did the installation on Window 7 64-bit, and did run into a problem with the Windows Registry on a fresh installation (posted earlier on the blog).

When you download the software from Oracle, you’ll need an account and you’ll need to accept the license agreement.

1. You’re prompted to accept the license agreement and can’t proceed without doing it.

2. After accepting the license agreement you can download the software.

3. Unlike, the Oracle 10g XE MSI file, you’ve downloaded a compressed (OracleXE112_Win32.zip) file, and you’ll need to explode the zip file and write it to a directory. When you write it to a directory, it looks like the following and you double click on the setup.msi file.

It’s recommended that you right click on the setup.msi program and run it as the Administrator. If you’ve not disabled Microsoft User Access Controls (UAC), I’d do that first.

4. After launching the setup.msi file, you see the Install Shield wizard screen while the file loads, like this:

After launching the setup.msi file, and loading the MSI file, you see the first dialog box of the installation. Click the Next button to start the installation.

5. The second dialog box is the license for the installation. Click the I accept the terms in the license agreement. radio button and then the Next button.

6. The third dialog box lets you accept the default location or provide an override location. If you accept the default location, click the Next button to continue.

7. The fourth dialog box lets you specify the TNS, MTS, and HTTP Ports. The default values are shown in the following screen shot but I’ve change my TNS port to 1621, which you’ll see later. Click the Next button to continue.

8. The fifth dialog box lets you enter the password for the SYS and SYSTEM users. You must enter it twice and naturally they must match each other. Click the Next button to continue.

9. The sixth dialog box lets you see the configuration options you’ve chosen. Note that this installation is using a TNS port of 1621 rather than the default of 1521. Click the Next button to continue.

10. The seventh dialog box lets you see the progress bar, take a break it can run for two or three minutes. Click the Next button to continue.

11. The last dialog box lets you see that you’ve installed the product. Click the Finish button to complete the installation.

You’ve now installed Oracle Database 11g Express Edition. You can start the command line by typing sqlplus system/password in command shell or the Get Started option in your Program Menus, as shown below.

You should see this console when you launch Get Started or manually type http://localhost:8080/apex. There’s more to the web page but I scrunched it to fit into the blog. Alas, space is always at a premium.

Hope this helps folks.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

tnsping xe

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

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

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

sqlplus username/password@xe

Hope this helps a few people.

Sometimes I’m amazed at things that come up. A student wondered why the sequences were incrementing by two when they’re defined to increment by one. It turns out that they were using Oracle APEX to create SQL statements to build a table, constraints, and a auto-numbering sequence trigger. Before executing the code, they’d copy it into their re-runnable script that created their schema.

Here’s an example of code that was generated by APEX for a table:

-- Create the table.
CREATE TABLE onesy
( onesy_id NUMBER
, onesy_text VARCHAR2(20));
 
-- Add the primary key constraint.
ALTER TABLE onesy ADD CONSTRAINT onesy_seq PRIMARY KEY (onesy_id);
 
-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

This works in APEX because it doesn’t create forms that call onesy_seq.NEXTVAL but they did create that logic in their forms. The INSERT statement would look like:

INSERT INTO onesy VALUES (onesy_seq.NEXTVAL, 'One');

Therefore, the INSERT statement incremented the trigger by one and the database trigger incremented it by one. The result is that sequences two-step, which isn’t effective or the desired behavior.

After I explained the two-step problem, they asked if they could only call the trigger when the primary key value was null. While they could do that like this:

1
2
3
4
5
6
7
8
9

-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
WHEN (NEW.one_id IS NULL)
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

The problem is that this type of trigger doesn’t stop other possible problems. While it prevents two-stepping the sequence, it doesn’t prevent two other errors.

One possible error that isn’t managed in this scenario is the use of numeric literals beyond the next value of the sequence. It writes the row but eventually the sequence catches up to the higher value and a production insert would fail. It would raise the following exception.

INSERT INTO onesy (onesy_text) VALUES ('Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

Another possible error can occur when you use a bulk insert operation. Assuming you’re inserting 500 rows at a go, you query the maximum value of the onesy_id column and then create an array of 500 numbers. Then, you perform the bulk INSERT statement. The next call to the trigger would raise another ORA-00001 unique constraint error.

Yes, you could lock the table before you perform the bulk operation. After the bulk operation you would drop and recreate the sequence with a new value equal to the maximum value in the column, and unlock the table. This limits concurrency of operation. You could treat these bulk operations as off-line transactions (batch processing) and it would work nicely.

You could also implement a policy that no bulk operations provide generated column values that link to a sequence. Beyond it’s impracticality to manage, that type of restriction does limit the benefit of bulk operations.

The students wanted a solution. So, here’s my take on a trigger that prevents collision with values above the next sequence value. It assumes that bulk operations will be performed as batch processing where you can disable this trigger.

This trigger disallows numeric literals, logs any attempts to use them, and stops processing when an INSERT statement tries to use anything other than the .NEXTVAL of the sequence. It will only work in an Oracle Database 11g database because the context of using a sequence_name.CURRVAL in a comparison isn’t supported in prior releases. The onesy table is renamed the one table in the example.

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

-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/

Since anonymous transaction triggers are tricky, it’s important to note that the message writing requires two commits. One before raising the exception when the .CURRVAL is in session scope and another in the exception handler before raising the error. If you forget those COMMIT statements, this is a sample of the error stack:

INSERT INTO one VALUES (one_s1.nextval,'Six')
                        *
ERROR at line 1:
ORA-06519: active autonomous TRANSACTION detected AND rolled back
ORA-06512: at "STUDENT.ONE_T1", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

The trigger raises the following type of exceptions for an offending INSERT statement. The first occurs when the sequence is valid in the session scope, like:

DECLARE
*
ERROR at line 1:
ORA-20001: NOT a SEQUENCE generated VALUE [1].
ORA-06512: at "STUDENT.ONE_T1", line 48
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'
ORA-06512: at line 15

The second occurs when the sequence isn’t valid in the session scope.

INSERT INTO one VALUES (401,'Nine')
            *
ERROR at line 1:
ORA-20002: ID provided BY calling scope IS NOT NEXT SEQUENCE VALUE [401][Nine].
ORA-06512: at "STUDENT.ONE_T1", line 24
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

A value that’s below the current high-watermark of the sequence raises a unique constraint, like this:

INSERT INTO one VALUES (1,'Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

The following is a script with all the necessary code components to test the example.

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP
    IF i.object_name = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';  
    ELSE
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;  
    END IF;
  END LOOP;
END;
/
 
-- Create a table ONE.
CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE one_s1;
 
-- Create a table MSG.
CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
-- Create or replace trigger.
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20);
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20);
/
 
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_index ONE_INDEX := one_index();
  lv_list  ONE_LIST := one_list('One','Two','Three','Four','Five');
BEGIN
 
  -- Initialize the collection of the ONE_INDEX collection.
  lv_index.EXTEND(lv_list.COUNT);
  FOR i IN lv_list.FIRST..lv_list.LAST LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
    lv_index(i) := i;
  END LOOP;
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (lv_index(i),lv_list(i));
 
END;
/
 
-- Testing INSERT statements until sequence value increments beyond bulk insert values.
INSERT INTO one VALUES (6,'Six');
INSERT INTO one VALUES (one_s1.nextval,'Seven');
INSERT INTO one VALUES (1,'Eight');
INSERT INTO one VALUES (401,'Nine');
INSERT INTO one VALUES (NULL,'Ten');
 
COLUMN msg_id   FORMAT 999 HEADING "Msg|ID"
COLUMN msg_text FORMAT A72 HEADING "Msg Text"
 
-- 
SELECT * FROM one;
SELECT * FROM msg;
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_list  ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five');
BEGIN
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (NULL,lv_list(i));
 
END;
/
 
-- Query tables.
SELECT * FROM one;
SELECT * FROM msg;

If I’ve fat fingered any typing or made logical errors, please let me know.

This was a day with a busy schedule because of the publisher’s seminar. It’s where the authors, like me, learn about Oracle products and market focus. Andy Mendolsohn went over the Oracle Database 11g R2, and other VPs presented BI/ERP solutions and the effect of Oracle’s Essbase Plus (previously known as Hyperion), Java’s position and direction, Solaris’ position and direction, and the Fusion Applications. A summary of the highlights I noted follows:

• The Oracle Database 11g R2 presentation explained the idea of quarterly patch set updates (PSUs), advanced compression, the new Oracle Database Firewall, OCFS (Oracle Clustered File System), and the Exadata Server. Three quarterly PSUs, then a point release is a change but a welcome one.
• Advanced compression qualified three subgroups: (a) A 3 times compression for OLTP systems; (b) A 10 times compression for data warehousing, and (c) a 15 to 50 times compression for archive data. If we apply this with the capital cost figure of 40% for storage as valid, compression may substantially reduce costs.
• They shared Gartner statistics that 82% of Fortune 500 companies use Oracle BI/ERP solutions was very interesting. The idea that the largest customer deploys an Oracle BI/ERP solution to 1 million users turns the demarcation between traditional data warehouse explorers and farmers into a historical footnote. They positioned Oracle BI EE Plus as targeted to the development of dashboard and BI Publisher (formally XML Publisher). Oracle Essbase Plus for Model-OLAP (Online Analytical Processing – focused on resolving the discrepancies between R-OLAP (Relational-OLAP) and M-OLAP (Model-OLAP). Oracle Scorecard and Strategy Management tools are reserved to planning Long-Range Objectivs (LROs), Short-Range Objectives (SROs), and Key Indicators (KPIs). The last requires a carefully modeling of the objective needs of the business and data model capability.
• The Exadata server now comes in two types, the X2-2 and X2-8. The first may be a quarter, half, or full Exadata server while the latter is always a full Exadata server. The X2- has 128 cores, 2 TB of memory, a Linux/Solaris 10 GB Ethernet connection, 14 storage servers with 6 core CPU in each storage server.
• Oracle’s commitment to Java is very solid but a question on NetBeans leaves one to believe that it isn’t part of Oracle’s solution space and it may not see much if any evolutionary change. They said that NetBeans would be supported to help external Open Source developers. My guess is that we should plan to migrate NetBeans or bear the cost of owning solutions with a higher integration cost.
• Solaris has come through the merger with new strength and a commitment to four goals that I caught: availability, scalability, and efficiency, and security. They also committed to make Solaris fully virtualizable, which will more effectively support private clouds. You may also note that Oracle has improved efficiency of MySQL 5.5 on the Solaris platform.
• Fusion Middleware Applications focus on: (a) Best Practice – leveraging the best in class of applications from the Oracle eBusiness Suite, PeopleSoft, JD Edwards, and Siebel; (b) Re-inventing User Experience – A role-based user interface, embedded decision support, and pervasive collaboration; (c) Cloud – Support for both private and public clouds; (d) Platform – Standard-based, configurable, adaptive, and secure Fusion middleware. Customers will have three options for the future. They may continue the current path and upgrade eBusiness Suite to eBusiness Suite, et cetera. Alternatively, they can adopt a co-existence strategy that lets them mix and match components form multiple suites. Lastly, they may embrace the complete suite as a whole.

After the Publisher’s Seminar, we went to the Nikko for Oracle Develop. On the way, we had to cross Powell Street. I snapped a photo of John Harper, co-author of Oracle Database 11g Programming Workbook, with the Cable Car. The Nikko’s door is on the right, and the sessions were on the third floor.

After one session, we went back to the Moscone South Exhibition hall to snag a copy of Guy Harrison’s Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. It’s the fresh version of his prior SQL Tuning books. I’d already bought one two months ago, but I couldn’t resist a signed copy for the office when it was free. I recommend the book as worthwhile and have promised Guy to write a review when I get home.

At the conclusion of the day, John and I went to the Oracle Press party to meet other authors. Ric Niemiec, founder of TUSC and author of Oracle Database 10g Performance Tuning: Tips and Techniques, and we took a photo. He’s also mentioned that the next edition covering Oracle Database 11g will include coverage of the Exadata Server. It’s awesome that he gets to write after the terminal release of the database.

Somebody posted a comment inquiring about the release of Oracle 11g XE. They felt it had been delayed. As far as I know, the last word on that came from Andy Mendelsohn last October, as published in this InfoWorld article.

The Oracle Database 11g XE is released and the download link is here.

When Andy Mendelsohn originally announced Oracle 11g XE in an interview with the NY Times, he clearly said it would be based on the terminal release of Oracle 11g. I also suspect it’ll include APEX 4.0, which was recently released. Maybe we’ll see the release at or near Oracle Open World 2010.

I don’t endorse the negative remarks in the InfoWorld article about why Oracle 11g XE isn’t released. While I have no direct knowledge of the forthcoming release, I think there’s an alternative explanation for any delay. Based on my involvement with the Oracle 10g XE release, I believe Oracle wants a rock solid starter version. At least, that was a major concern when they released Oracle 10g XE.