The Oracle

and how to talk to it


You know a thing or two about rdbms and sql - so you decide to have a look at the Rolls Royce among relational database systems : Oracle. And you have no clue - you don't even know where to start looking.
Been there done that, took notes :

Quick Start Guide

Getting Oracle rdbms
Oracle, no doubt to compete with gratis open source offerings, offers a free download of its database management system in the form of a personal edition, "Oracle Express Edition". You can get it at the Oracle website. Supported platforms : Windows and Linux (Oracle offers .rpm and .deb packages).
install oracle on Debian
Oracle prefers Linux as a platform - they offer their own Linux distribution (Unbreakable Linux, based on the RedHat distribution). But since I like Debian, I'll use that. You can also install on Windows, of course.
Here's a very clear and concise guide to install Oracle on Debian Linux.

some points of interest :

During the configuration, you'll set the following parameters :
create and manage databases
Tool : Oracle sql developer. Free download

Tool : Oracle Web interface (Application Express). This is installed automatically with your Oracle rdbms and allows you to do some initial administration from a browser. By default, it is only accessible from the server (no remote access) but this can easily be changed. Of course, the first time is going to be harder. See further for how to connect to the server.
Using your databases : application development
How do you create front-ends - applications that are capable of using the data stored in an Oracle database ?

Applications that need to connect to an Oracle database probably require the installation of an "Oracle Client" on the client system. The Oracle Instant Client looks like an elegant solution to that. Windows developers might want to also look at some ADODB connection strings for Oracle.
Using your databases : creating reports
Oracle BI Publisher, Free download : xml-based report tool that sits between the database and the application you use to represent the data. Compatible with other front-ends eg MS Office apps : Design a report template in MS Word, and use BI Publisher to get data, generate graphs, etc.
Oracle B.I. Publisher / XML Publisher is not a report tool as such, but a server that serves up xml files containing a subset of your data, which is to be used with Oracle XML Publisher Desktop, a (Windows or Linux) client application that uses the server xml as a data source and can use MS Office as output, i.e. your final report will be a word document, an excel worksheet etc.

Oracle Reports is a another tool for developing reports against data stored in an Oracle database. Oracle Reports consists of Oracle Reports Developer (a component of the Oracle Developer Suite) and Oracle Application Server Reports Services (a component of the Oracle Application Server). Also see Oracle Reports Tutorial.

Other than that : with a suitable Oracle ODBC driver, any any odbc-capable report tool will do :-)
interactive SQL
Oracle SQL Plus, Oracle SQL Developer (Free download) , or any 3th party interactive SQL tool with ODBC, JDBC or native Oracle support.
talk to an Oracle database using sql from a unix shell
Invoke SQL*Plus from a UNIX shell, with the command sqlplus. SQL*Plus then displays some information about the product, and prompts you for your user name and password for the Oracle system. (http://www.db.cs.ucdavis.edu/teaching/sqltutorial/tutorial.pdf (dead link ?).

Most of the oracle clients and front ends mentioned here require java VM and JDK (version 5) on the client system, and Sun Java seems to be the preferred one. For Ubuntu, refer to How to install Java in the Ubuntu documentation.

Oracle Tutorials

The Oracle website, and especially the 'Technology Network" is your friend : find the products section, and see what's find. Look for suitable tools, find free downloads, read the relevant tutorials and white-papers, and get your hands dirty. "Oracle By Example" is a good place to start, and on the product pages (see links throughout this paper) you'll always find links to tutorials, guides, and other documentation for the product in question.


Primer

Oracle Web Interface

To access the web interface on a remote server (without GUI), you can use a ssh tunnel. In a terminal on your Linux , run

ssh root@oracleserver -L 8081:localhost:8080

Then, in your web browser, go to localhost on the new port : http://localhost:8081/apex . You're presented with a login screen, where you log in as 'system'. You have now administrator access to the rdbms.

Unless you want to keep using the ssh tunnel in the future, you should now enable remote http access.

You can now unlock the 'hr' test user account so you can use the demo Human Resources database, or set up user accounts and create databases.

Oracle Application Express will let you do rdbms management (create and manage user accounts, create databases, ...) as well as create (web) applications, i.e. web pages with input forms and report pages.
Application Express home page Application Express Create Application

command line client

This is a command line interactive SQL tool. You might want to install this on the server so you can use it in an ssh session.

sql developer

sql developer is a quite advanced tool to manage databases (add or modify tables, create views, triggers, stored procedures, etc.) To install SQL Developer, just unpack the zip archive to a suitable folder (eg ~/bin), set permissions so sqldeveloper.sh is executable, and run [~/bin/sqldeveloper/]sqldeveloper.sh

When you run SQL Developer, you need to create a connection to a database, with a suitable user account, so these need to have been created in advance. For a first look, you can use the demo database and demo account (hr).
database connection
(As you can see, sql developer can also be used as a front-end to MySQL en MS SQL Server)

Next, you can alter the database, or create and run sql scripts in a GUI sql developer

report generators

TODO

Links : resources and references

Databases and SQL
Oracle
Koen Noens
November 2007