Category Archives: Oracle

Articles based on my experience with Oracle’s products.

Oracle Applications Comedy Errors

Fair’s fair, any application the size and complexity of Oracle Applications will have some bugs. But applications seems to do far better than most.

This section refers to the server size of Applications Comedy Errors. There’s another page for the client side.

  • To install a new application module, it seems that you have to add the original version you have plus all patches. Not just for your new module, but for all of them. (People that implement Application’s are not expected to make mistakes.)
  • The recommended sizes for your database extents are useful. However, if you follow the advice, some parts of the system won’t actually install.
  • Oracle helpfully supply a program that goes through the thousands of files that it installs and checks that they’re the right ones. We’ve found at least a dozen files that it claims shouldn’t be there but in fact should be.
  • After applying a patch, the shell script that starts the Concurrent Managers just vanished.
  • One of the bugs that we found was because Oracle had ‘forgotten’ to port a number of forms from one environment to the one we were using. The word ‘forgotten,’ despite being inverted commas, is the actual word used by Oracle support.

Oracle Server Comedy Errors

Oracle, the company, was founded back in the late seventies as a company that produced an SQL database. Its entire reputation has been built on this product and it is, therefore, no surprise that it is very good.

That’s not to say that there have been no problems…

  • To perform dodgy ‘system’ operations on a database, you need access to a user called ‘internal.’ In theory, you need to be in the UNIX ‘dba’ group to be able to connect. That is unless you belong to rather a lot of groups, in which case it doesn’t seem to work. (We’ve not actually called this through, so there may be a simple explanation.) (7.1)
  • When creating a new database, you have to run a file called ‘catalog.sql’ to finish the job. At the end of this file is the following message: ‘THIS IS THE END OF THIS FILE – IF I AM NOT HERE THEN RCS HAS TRUNCATED FILE‘ (7.3)
  • I’m not entirely sure that this counts, but an installation program that requires more resources than that product that it’s installing demands at least an honorable mention. (8i)

Oracle Support Comedy Errors

Don’t get me wrong. Oracle support analysts have a difficult job. Their products are large, complex beasts and you don’t necessarily expect an immediate response. These, however, I did not expect…

  • Having spent over a day with hourly contact with support, they ring back returning a voice-mail message left before managing speaking to a ‘real’ person. Twenty-seven hours apparently wasn’t enough for a correct response, either.
  • Two weeks of problems lead to a call to our sales manager. Obviously concerned, he went away to research the problem. He came back a day or two later. “This problem you’re having with you SCSI CD-ROM…,” he began. Good effort, wrong customer.
  • They asked us to download a patch from their web site, a mere 36Megs. This looked remarkably similar to a patch we’d received the previous week for another problem. Next they insisted that we needed the next version of the SmartClient software. They even sent someone out to install it for us. Naturally, this solution didn’t work. They ummed and arred for a while before declaring it a ‘bug’ and sending it off to the states to be fixed. This is the last I heard of this problem.

Output

Introduction

PL/SQL is good for many things. It has a structured, Ada-like syntax and can have SQL statements seamlessly embedded in it. Although not compiled right down to machine code, it’s even fairly quick.

But one thing that most people initially have difficulty doing is displaying information on the screen. The first thing I did was to enter what I wanted into a temporary table and use SQL*Plus to select from it. This works well for reports at the like, but it doesn’t work at all if you want to display something independently of a transaction or halfway through a function.

I was frustrated that there wasn’t a better way of feeding back information to end users. I was wrong to be frustrated: there is a better way.

Hello world!

The best example I can think of doing is the world famous “Hello, World!” program, so here goes:

CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
     DBMS_OUTPUT.PUT_LINE ('Hello, world!');
END hello_world;
/

In SQL*Plus you can run that procedure by typing “exec hello_world” and pressing return.

You may be surprised to see that it says “PL/SQL procedure successfully completed.” and nothing else; there’s no “hello world” text. Surely it’s lying, it hasn’t successfully executed anything, has it?!

As with most things Oracle, things are not quite as simple as they first appear. In this case you need to tell SQL*Plus that you’re going to use the DBMS_OUTPUT package. To do that, you need the following command:

set serveroutput on

You might want to put this in your “login.sql” file so that it gets executed every time you log in. You can also add the “size” parameter to the end to indicate how much output you’ll be producing. The most you’re allowed is around 1Mb so I usually type “set serveroutput on size 1000000”.

If you try executing the procedure now, you’ll find that it works correctly.

A longer example

For our second and final example, we’ll use one of the tables that Oracle installs by default. It’s called EMP and should be in the SCOTT schema (password TIGER). The important fact here is that there are a number of different columns types, NUMBER’s, VARCHAR2’s and DATE’s.

On to the example:

CREATE OR REPLACE PROCEDURE example2 AS
     CURSOR emp_cur IS
         SELECT e.empno, e.ename, e.sal, e.hiredate
         FROM   emp e
         WHERE  e.sal > 2500;
    BEGIN
     FOR current_employee IN emp_cur LOOP
         DBMS_OUTPUT.PUT_LINE ('-------------------');     -- string literal
         DBMS_OUTPUT.PUT_LINE (current_employee.empno);    -- number(4)
         DBMS_OUTPUT.PUT_LINE (current_employee.ename);    -- varchar2(10)
         DBMS_OUTPUT.PUT_LINE (current_employee.sal);      -- number(7,2)
         DBMS_OUTPUT.PUT_LINE (current_employee.hiredate); -- date
         DBMS_OUTPUT.PUT_LINE ('Year: ' ||
                               TO_CHAR(current_employee.hiredate, 'YYYY'));
     END LOOP;
 END;
 /

This code should be pretty much self-explanatory. We first start a loop over a table. Inside the loop we output the content of it. The first PUT_LINE statement shows that you can output a string literal (we know this as ‘Hello world’ is one too). The second shows that you can output numbers without first converting them to a string — the function is overloaded and will accept most standard types.

Following that theme, the third, fourth and fifth lines output a string, a different number and a date, in all cases using the default format string. The last PUT_LINE statement shows that you can output expressions, but note that it all needs to be of the same type (so in this case I convert a date to a string so I can concatenate it to another string).

On my machine, the output is as follows:

SQL> set serveroutput on
SQL> exec example2
 -------------------
 7566
JONES
2975
02-APR-81
Year: 1981
-------------------
7698
 BLAKE
2850
01-MAY-81
 Year: 1981
-------------------
7788
SCOTT
3000
19-APR-87
 Year: 1987
-------------------
7839
KING 5000
17-NOV-81
Year: 1981
-------------------
7902
 FORD
3000
03-DEC-81
Year: 1981
PL/SQL procedure successfully completed.
SQL>

Conclusion

As we’ve come to expect from Oracle over the years, simply outputting a little text to the screen isn’t quite as easy as you might wish for.

And it’s not without it’s problems. If you want to send more than a megabyte to the screen, you’re still going to have difficulty and all the playing around with “set serveroutput” is just plain tedious.

However, the important point is that you can do it and, programatically, it’s (basically) just a single command.

If you want more information on this kind of thing, have a look in chapter 6 of Oracle Builtin Packages, a whole book dedicated to this kind of thing.

Installing Oracle 8i R2


Introduction

Everyone will be very pleased to hear that Oracle’s third attempt at producing a usable database product on Linux has largely been successful. The first two usually worked but only after much aggravation. Forget all the extras that 8.1.6 provides, you can get the thing installed with much less grief!

Of course, I wouldn’t go so far as to say that it was simple and straight-forward all the time. It is Oracle that we’re talking about here.

I’ll start by describing how I got Oracle installed on my box and finish off with some questions and answers, much in the same format as the HOWTO. It’s probably worth having a look at the HOWTO still as many of the problems are similar and the solutions given there may give you some idea of where to start looking.

My machine

First, some news on my ‘server’ configuration: I still have the same Celeron 466 with 128Mb of memory. On the software side I’ve upgraded to Mandrake 7.1 (if I’d been running a production Oracle server I wouldn’t have taken the risk). I didn’t remove my old installation of Oracle before starting on the new one and I didn’t attempt to perform an upgrade.

I did remove JRE (Blackdown 1.1.6v5) and my installation of JDK (1.2.2) from my path. Oracle now comes with its own JRE, so even having the risk of it using the wrong one made me paranoid.

The last thing to note is that this time I downloaded my copy rather than using a CD. This seems to be what most other people do, so my tale here should be closer to ‘real life.’

My successful install

The process was as follows:

  1. Download Oracle 8i R2
  2. Extract the archive
  3. Create the required users and groups
  4. Make sure X is set up correctly
  5. Start the installer
  6. Quick tests

Firstly, the download. It’s big, nearly 300Mb. Don’t attempt it without something like Gozilla or wget even if you’re on a fast corporate connection.

Secondly the extraction. You’ll find that it comes in a standard tar archive compressed with GNU Zip. This command should get all the files out:

tar zxvf oracle8161.tar.gz

When you extract it, remember that the files coming out are slightly bigger (301807K on my machine). So you need over 500Mb of disk space before you even start the installation!

Before you actually start the installation, you’ll need to switch to “root” for a couple of commands. Start by creating a group called “dba” and a user “oracle”. Your new user should be in the new group. Log in as your new “oracle” user and make sure your X Windows system is working properly. (If you can fire up a new ‘xterm’ you’re fine.) The Oracle installer, as before, works only in a graphical environment.

Go to where you extracted the software archive. You’ll find a directory has been created (“Oracle8iR2”). Move into it and you’re ready to start the installation!

(A quick note: in the same directory there’s “index.htm” which is the root page for all the Oracle installation document. This seems to be improved over earlier releases and is worth a read.)

Type:

./runInstaller

A splash screen should appear, followed by a Windows-style Wizard/Installer. I find the default options for almost everything to be fine. Broadly speaking, and assuming some common-sense is used, just clicking “Next” continually should result in a working installation. In slightly more detail…

(Note that there are a few points where the installer asks you to log in as “root” to run some shell scripts. To simplify the text below, I’ve missed these steps out. Simply do as it says and click “Retry” once it’s done.)

  1. Welcome screen. Click “Next.”
  2. File Locations screen. The top box should be correct; it displays the location of the archive containing all the software about to be installed. The second box is the “base” of you Oracle installation. I chose “/home/ora816” but this is not recommended. Have a read of the OFA (Oracle Flexible Architecture) documentation.
  3. Available Products screen. If you’re installing a server, select “Oracle 8i Enterprise Edition 8.1.6.1.0”; otherwise select “Oracle 8i Client 8.1.6.1.0”. I’m assuming that you’re building a server and click the first option.
  4. Installation Types screen. Do you want a “Typical”, “Minimal” or “Custom” installation. Unless you really know what you’re doing, pick “Typical”.
  5. Upgrading or Migrating an Existing Database screen. If you have a previous installation, Oracle will ask whether you want to upgrade your database to the new 8.1.6 format. I didn’t. I’d recommend doing this yourself once the installation process is complete even if you do.
  6. Database Identification screen. Here Oracle asks you for a Global Database Name and a SID. As before, this is something your DBA probably has an opinion on. If you’re the DBA and you don’t know what it’s asking for, enter “dev1” for both.
  7. Database File Location screen. Now Oracle knows what you want to call your database, it asks you where you want to put all the files that make up the database. Think back to your reading of the OFA documentation for this.
  8. Summary screen. Oracle now tells you what it’s planning on installing. Click “Install” if you’re sure, or go to the “Previous” screen an juggle the options around.
  9. Configuration Tools. First Oracle runs the Net8 Configuration Assistant and then runs the Database Configuration Assistant. Basically, it sets up your networking and creates the database you asked for. No user intervention is required. (Note: the SYS account password is “change_on_install” and the SYSTEM password is “manager”. You should change both using the SQL*Plus “password” command as soon as possible.)
  10. End of Installation. That’s it, you have a complete installation!

If you want to install Oracle Programmer (Pro*C, etc), you need to follow the same process as before: go back through the installation process, but this time following the “Oracle Client” route. The rest of the process is similar to the above and very straight-forward. The new installer even asks you if you want to start again once your database has been created.

And if you want to set up a network connection to another machine, the process is exactly the same as for Oracle 8i (and is covered in the main HOWTO).

Questions and answers

Java problems?

As before, many of the problems come from your choice of Java Virtual Machine. R2 actually comes with a runtime environment this time (JRE 1.1.8), which does make things easier. However I have heard reports that older versions sometimes work better. The older version is normally Blackdown’s 1.1.6v5 release, the same Oracle used to recommend with their 8.1.5 release.

Memory requirements

One thing that is exactly the same is the amount of memory required. I don’t remember seeing a figure in their old documentation, but they say you need 400Mb this time, either real or virtual, for 8.1.6. I have 384Mb in total on my machine and it was fine. The default database configuration seems to use more memory but, as always, you can change that.

Running Redhat 7 or another glibc 2.2 based distribution

Short answer: add “export LD_ASSUME_KERNEL=2.2.5” to your profile and then type “. /usr/i386-glib21-linux/bin/i386-glibc21-linux-env.sh”.

Long answer: look at my page on the subject.