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 :

• you need lots of swap space !! 1 GB minimum.
• The installation requires at least 1.5 GB free on /usr/lib .
• add "deb http://oss.oracle.com/debian/ unstable main non-free" to your sources list and install with apt-get
• after installation, run "/etc/init.d/oracle-xe configure" to configure the rdbms
• configuration sets up a web interface for management. it defaults to "http://127.0.0.1:8080/apex"
• you use this web interface for initial configuration - preferably tunneled through an SSH connection (explained in the howto)
• remote SQL connections are disabled by default - use the web interface to enable them.

During the configuration, you'll set the following parameters :

• Specify the HTTP port that will be used for Oracle Application Express [8080]:
• Specify a port that will be used for the database listener [1521]:
• Specify a password to be used for database accounts. Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after initial configuration.
• Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]

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 ?

• Oracle Application Express - Web Applications Development Tool. Free download
• Oracle JDeveloper - Java Web Application Development Tool. Free download
• Zend Core - Dynamic web pages and web applications with php. Free download
• Oracle Forms - a Rapid Application Development environment. Part of the Oracle Developer Suite.

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.

command line client

• add "deb http://oss.oracle.com/debian/ unstable main non-free" to your sources list and install with apt-get
• apt-get install oracle-xe-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).

(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

report generators

• Open Office Base, Calc and Writer as database frontends for Oracle

Links : resources and references