View and vote on the article here: Oracle 10g Express Edition with Linux, PHP & Java
Oracle 10g Express Edition with Linux, PHP & Java| Category | | | Summary | | A beginners guide to getting started with Oracle Express Edition 10g (10.2). |
| | Body | Introduction
Oracle 10g Express Edition (XE) is a slightly stripped down, lighter and restricted (database-size) release of Oracle's famous database server software. It's ideal for students, developers and organisations who have either less availability of resources or who require a smaller database solution. Oracle 10g is available as a comparatively small download (165 to 263 megabytes) for Microsoft Windows as well as Linux (x86) distributions. If you wish to run Oracle 10g on an alternative platform or architecture (such as HP-UX, Solaris (SPARC), AIX, OpenVMS, etc.), you will have to use the Standard or Enterprise version rather than XE.
When I originally wrote this article, 10g XE on Linux was the most ideal solution: my laptop was running Windows at the time, and could barely handle running Eclipse or Netbeans with Tomcat in the background, never mind a powerful database server! So, I decided to perform the installation on a AMD K6 box running at ~500Mhz with 305.54 MB RAM. Despite already having Apache 1.3.34, Tomcat 5.0.28, Squid, CUPS, an IRCd and two BitchX sessions running on this server, it handled Oracle 10g XE very well:
22:13:00 up 18 days, 19:59, 3 users, load average: 0.28, 0.86, 0.64
which isn't too bad considering the specifications. It should be noted that I was running CentOS 4, and that this article is best followed with CentOS or RHEL (Though, as I said - I may cover some problems or quirks which are no longer applicable in later versions of CentOS or Oracle 10g XE edition).
Installation
Installation packages are available from the Oracle 10g XE Download Page. You will need an Oracle Technology Network (OTN) account to proceed with the download, but registration is free. If your database will only use characters in the LATIN1 character set (English, etc), grab the Western-European version; otherwise get Universal for Unicode support (includes multilingual web interface).
Once downloaded, install the package using:
rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm
(For RedHat-based distros)
dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
(For Debian-based)
Hopefully the installation will complete error-free, but you may receive dependency errors which you should resolve using your distro's package manager, or perhaps a warning about RAM/swap space. This is usually the case on systems with less than 1GB of available RAM - to solve this, we can create more swap space with the following commands:
dd if=/dev/zero of=/swap2 bs=1M count=1000
mkswap /swap2
swapon /swap2
which will create a 1GB file called 'swap2' in /, prepare the file for use as swap space, and activate it. Try to install the package again; everything should run fine.
Configuration
The installation process will have created a script in /etc/init.d - this will allow you to control and configure Oracle XE. If you are unsure about the location of this script, Oracle leaves a post-installation note for you to follow.
To begin configuring your database server, run the following command:
/etc/init.d/oracle-xe configure
which will start the post-installation configuration process. If you have problems with this script, you may call:
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts
./oracle-xe configure
Now, let's configure the server:
Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]: [enter]
Specify a port that will be used for the database listener [1521]: [enter]
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: yourpassword
Confirm the password: yourpassword
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
Starting Oracle Net Listener:Done
Configuring Database:Done
Starting Oracle Database 10g Express Edition Instance:Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
This script will ask you for a port to use for the web-based interface, the listener interface (to accept connections to the database server) and a password for the SYS & SYSTEM (and FLOWS_020100) master accounts. Make sure you use a good password for this.
You may now login using the SYSTEM account and your password at http://127.0.0.1:8080/apex (or http://127.0.0.1:8080/htmldb, depending on your release). Although this interface can provide an easy way to perform functions, I would recommend working from the command-line as much as possible: it's the best way to learn and you will find in time that it will be quicker. Additionally, if you ever end-up working with any version of Oracle in a production environment - it's likely the SYSTEM account won't be available to you.
Now, let's leave this friendly interface for now. Go back to your CLI, and add the following to the end of /etc/profile or bashrc (or .profile/.bashrc in your user directory):
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export PATH=$ORACLE_HOME/bin:$PATH
In the above, 'XE' refers to the Server Instance Identifier, which is the name of the default server instance created by Oracle 10g XE. You will have to re-login for these lines to take effect (or run: . /etc/profile or source /etc/bashrc, replacing the specified filenames with the option you chose above).
Getting Started
As we want to get started straight away, create a DBA (Database Administrator) and normal user account for daily use. I haven't covered SQL*Plus just yet, so you should create these users back on the intuitive web-interface mentioned above for now until you have studied Oracle a little more. It's pretty straightforward, just remember you want one user with the DBA role and one without.
SQL*Plus
SQL*Plus is a command-line client for the Oracle database; It allows you to run SQL and PL/SQL (Procedural Language/Structured Query Language) commands on live servers. The command 'sqlplus' should be in your current path, if not (or if you have installed the Oracle 10g XE Client on a remote machine), add the following to ~/.profile or ~/.bashrc:
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
export $PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin
Once you've re-loaded that, you can connect to any server using the following syntax:
sqlplus username/password@your.server.com
SQL*Plus has a number of command line options which are displayed by running "sqlplus --help". PL/SQL can be a very large topic, so will not be covered in great detail here - but I will go through some differences between Oracle and other database servers, as well as provide examples.
Some notes about Oracle SQL & PL/SQL
- Aliases/Synonyms: Unlike MySQL and Postgre, Oracle supports aliases for any object in the database (tables, views, etc).
- Code pages: Oracle can automatically convert data to match the code page used by your client if it differs from the server.
- Auto-incrementing: Oracle does not have an AutoIncrement option for fields. MySQL has this, as does Postgre (through the 'SERIAL' datatype). This will need to be performed through PL/SQL.
- Stored Procedures: These can be written in PL/SQL, Java , etc.
- Schemas: Each user account in itself is a schema, tables are then created under this user/schema (similar to Postgre).
And finally, some common data types:
VARCHAR2 Variable-length string
CHAR Static length string/char
NUMBER(p,s) Number
DATE Date
In NUMBER(p,s), p represents Precision, and s is for Scale (p = length of the number, s = places after the decimal point, or before for negative values).
Examples
I will now guide you through doing some actual work with Oracle. The PHP and Java code worked when this was originally written - please contact me if you spot any typos or inaccuracies.
OK, let's fire up SQL*Plus (remember to specify user/pass@host if connecting remotely or to login with another account):
sqlplus int16h/mypass@localhost
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 9 21:45:50 2006
Copyright - 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
SQL>
Paste or type the following into the console exactly as it appears:
CREATE TABLE tutorials
(
title VARCHAR2(70),
author VARCHAR2(40),
ref_number NUMBER(7,0) CONSTRAINT pk_tutorials PRIMARY KEY,
submit_date DATE DEFAULT SYSDATE
)
/
As you can see, we are creating a table called 'tutorials' with four fields - our 'ref_number' field being made the primary key. If successful, you should see something like the following:
-> Table Created
->
-> SQL>
Well done! You have created your first Oracle table! While you're on a roll, let's create another to complement it. This time, we will have a CONSTRAINT on a field which will act as a foreign-key, referencing the 'ref_number' field in our 'tutorials' table:
CREATE TABLE tutorials_comments
(
ref_number NUMBER(7,0) CONSTRAINT fk_tutorials_com REFERENCES tutorials(ref_number),
username VARCHAR2(12),
user_comment VARCHAR2(150)
)
/
Again, you should see confirmation if this was successful.
Constraints are extremely useful, and when used with your own procedures, or functions from Oracle's built-in packages, they can greatly improve your applications by cutting down on the amount of code externally used (in PHP, Java, etc.) as well as improving security. For example, you could ensure that the value of ref_number is restricted to a number between 1230000 and 5000000 by adding the following to your CREATE TABLE statement:
CONSTRAINT check_ref CHECK (ref_number BETWEEN 1230000 and 5000000)
In our first statement, note the use of DEFAULT and SYSDATE. The SYSDATE function returns the current date, and DEFAULT instructs the database to insert the SYSDATE (when an INSERT is made) into the submit_date column. Of course, an INSERT or ALTER statement may override this default value. The expected format for the date is DD-MMM-YY or DD-MM-YYYY.
If you would like to review the table structures you have just created you may use the DESCRIBE(or DESC) command, followed by the table name.
Unlike some CLI-based applications, it is not possible to press the [up] key on your keyboard to browse your command history within SQL*Plus, though there are 3rd-party applications which support this. You will find it extremely irritating when you notice an error on a line of your SQL statement and being unable to edit it. So how do we get around this? Luckily, SQL*Plus allows you to load external text files containing SQL or PL/SQL statements, so you can write statements in your favourite text editor and load them by typing an @ followed by the name of the file; for example: "@create_tables.sql".
Ok, so now that we have some tables - we should insert a record:
INSERT INTO tutorials VALUES
(
'Intro to Oracle XE on Linux',
'Int16h',
1000022,
'09-MAR-2006'
)
/
So here we have a tutorial called "Intro to Oracle XE on Linux", written by "Int16h" with ID number 1000022 on the 9th of March, 2006.
Oracle and PHP
To connect to Oracle from PHP scripts, we will be using the oci8 extension (php_oci8.dll). If you installed PHP through your distro's package management system this extension will most-likely be available to you. To enable it, uncomment the following line in php.ini:
extension = php_oci8.dll
If you compiled PHP from source, you will have to reconfigure with the following option:
--with-oci8=$ORACLE_HOME
OCI (Oracle Call Interface) is the name of the driver we use to interact with Oracle. More information on the various options for php_oci8 is available on PHP's website.
Here is a very simple script which will make use of the driver:
<?php
$conn = ocilogon( "username", "password" );
$query = "SELECT title, author, ref_number, submit_date FROM tutorials";
$parseresults = ociparse($conn, $query);
ociexecute($parseresults);
$rows = ocifetchstatement($parseresults, $rs);
?>
<html><head><title>Simple PHP->OCI Script</title></head><body>
<center><table border=0 cellspacing='0' width='50%'><tr>
<td><b>Author</b></td><td><b>Title</b></td><td><b>Date</b></td></tr>
<tr></tr>
<?php
for ($iterate = 0; $iterate < $rows; $iterate++)
{
echo "<tr>";
echo "<td>" . $rs['author'][$iterate] . "</td>";
echo "<td>" . $rs['title'][$iterate] . "</td>";
echo "<td>" . $rs['submit_date'][$iterate] . "</td>";
echo "</tr>";
}
echo "</table><br /><b>$rows tutorials were found.</b></body></html>";
?>
If you run this, you should see the record we manually inserted earlier.
Following the code, you will see that we construct a connection string (DSN-like) and the query we wish to execute. This is executed on the server and we grab the results ready for iteration.
Oracle and Java
This is a quick example of connecting to an Oracle server from Java. Java is one of the most popular languages for interfacing with Oracle - the following is a quick guide on getting started with "normal" interfacing.
First of all, you will need to add the following to your CLASSPATH:
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/jdbc/libs/
Or if you are using an IDE such as Netbeans, just add ojdbc14.jar from that directory to your project. As with any SQL database connectivity in Java, you will need to import java.sql.* - and for Oracle, the Oracle JDBC driver:
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
To create a connection to the database, it should look something like this:
public Connection getConnection()
throws ClassNotFoundException, SQLException {
OracleDataSource myds = new OracleDataSource();
myds.setUser("username");
myds.setPassword("pasword");
myds.setURL("jdbc:oracle:thin:@your.server:1521/xe");
Connection conn = myds.getConnection();
return conn;
}
This defines a new OracleDataSource, and creates the connection using your username, password and jdbc URL. Rather than re-create the function which our PHP code produced, I will show you how to create the table which we manually entered earlier:
public void createTable(Connection conn) throws SQLException {
Statement stmt = null;
String query;
try {
query = "CREATE TABLE tutorials title VARCHAR2(70), author VARCHAR2(40),
ref_number NUMBER(7,0) CONTRAINT pk_tutorials PRIMARY KEY,
submit_date DATE DEFAULT SYSDATE";
stmt = conn.createStatement();
stmt.executeUpdate(query);
}
finally {
if(stmt != null){
stmt.close();
}
}
}
Easy as pie. I haven't went into great detail about the code here - as I assume you already know how to use PHP and Java, and as you can see... using Oracle from Java is more-or-less the same as using any SQL database server.
Useful Links
|
|
There are no replies to this post yet.
|