Xojo Documentation Xojo Documentation

Oracle Database (often just referred to as Oracle) is a powerful database server that is commonly used in large companies. It works on Windows and Linux. Both free and commercial versions are available. To use it, you need to ensure the OraclePlugin plugin file is in the Plugins folder (it is there by default). The plugin supports connecting to Oracle database from Windows, Mac and Linux.

You can connect to Oracle from Desktop, Web and Console projects, but not iOS projects.

For more information about Oracle Database, refer to their web site: www.Oracle.com

Connecting to Oracle

To connect to Oracle, you need to have an Oracle server installed on either your computer or an accessible server. Each computer that is to connect to the Oracle Database needs to have the Oracle OCS 9i (or later) client installed. You’ll need to know several things about this installation, including:

• The name of the database on the server
• The username to use to connect to the server
• The password to use to connect to the server

With this information, you can connect to the database on the server using the OracleDatabase class:

Dim db As New OracleDatabase
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
If db.Connect Then
// Use the database
End If

Unfortunately connecting to Oracle Database is rarely that simple. Oracle uses a TNSNAMES.ORA file to configure many of the connection settings. You will likely have to spend time configuring that file before Xojo will be able to connect to the database server.

Alternatively, you may find that you can specify some settings directly in the DatabaseName property. This syntax connects to a local instance of Oracle Database XE:

db = New OracleDatabase

db.DatabaseName = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.1.14)(PORT=1521))(CONNECT_DATA=(SID=XE)))"
db.UserName = "SYSTEM"
db.Password = "dbexample"
db.Debug = 1

If db.Connect Then
MessageBox("Connected to Oracle!")
Else
MessageBox("Error connecting to Oracle: " + db.ErrorMessage)
End If

Debugging the Connection

Since Oracle can be trickier to configure than some databases, it may help you to enable debug mode. Set the OracleDatabase.Debug property to True before you connect to log messages to the console:

db.Debug = 1

Creating a Table

This SQL creates the Team table used in previous examples:

CREATE TABLE Team (ID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR2(100), Coach VARCHAR2(100), City VARCHAR2(100));

Auto-Incrementing Primary Keys

Oracle does not allow you to create a primary key that auto-increments. But the equivalent functionality is available by using Sequences (similar to how PostgreSQL works). A Sequence is a database object that manages unique values for use by primary keys. You use the sequence when you create new rows in a table.

This SQL declares a sequence for the Team table with values starting at 1:

CREATE SEQUENCE TeamSeq START 1;

You use the Sequence in INSERT SQL statements like this:

INSERT INTO Team (ID, Name)
VALUES (TeamSeq.nextval, 'Seagulls');

The nextval and currval functions are used to access the next value in the sequence and the current value of the sequence respectively. Dual Table

In other databases, the FROM part of a SELECT statement is optional allowing you to write SQL like this to get the next sequence value:

SELECT TeamSeq.nextval;

Or SQL like this to perform a calculation:

SELECT 5*5;

Oracle does not support the optional FROM, but it does provide a “dummy” table, called DUAL, that you can use for this purpose:

SELECT TeamSeq.nextval FROM DUAL;
SELECT 5*5 FROM DUAL;

Example Projects

• Examples/Database/Oracle/OracleExample
• Examples/Database/Oracle/OraclePluginTest

See Also

OracleDatabase class; UserGuide:Framework, UserGuide:Database Overview, UserGuide:Database Operations topics