SQL Offline

Oracle

Oracle Client Driver: Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)
MDAC Driver: Microsoft Data Access Components

SQL Language: Oracle SQL Language Reference
Oracle XE: Oracle Database 11g Express Edition

The Microsoft Drivers (MDAC) for Oracle do not support Unicode.

Column Type

Oracle Column Type Information (Text).

When creating or modifying a table using OleDb Admin, you can specify a specific Oracle column type from a list.

oracle column types
Oracle provider types

Additional qualifiers can be added. For example,

datetime DEFAULT CURRENT_TIMESTAMP

will specify that a field defaults to the time the record was appended. The Oracle provider type will be passed directly to the CREATE TABLE command and retained in the .OL grid file.

When using Oracle, you are likely to use the NUMERIC data type where other providers have named numerics such as INT8, INT4, tinyint or bit. I1, I2, I4, I8, UI1, UI2, UI4, UI8, CY and BOOL will be mapped to the numeric data type. If the precision is not specified for a type that maps to NUMERIC type a precision will be chosen large enough to hold the number.

The Oracle Client may not persist authentication information (password and user id) when initialized even if the Pasword Settings are set to Always Save. You can edit the connection string directly to add them.

If Installed, the Oracle Client drivers are used by default. You can set PreferMSDrivers in the registry to prefer MDAC drivers. Tables in all schemas that the User ID has access are listed in the Data Source window by default. If you want to restrict listing tables to only teh default schema, you can set DefaultSchemaOnly to 1 in the resistry.

If you open multiple tables from a single session in the source window you may get an ORA-03127: no new operations allowed until the active operation ends error. You can uninitalize and reinitialize the data source.

Precision of Numeric Data Types

You may specify precision and scale using the [Advanced] button from the Column Properties dialog.

Default Values

When appending a new row, default values are not immediately available. The row will appear null, dash ( - ) or blank. Pick Reload Data/Reload Update to acquire the default values. If the default value is part of the key, pick Reload Data/Reload.

Oracle does not show existing default values in table properties. Default values can be created or altered, but existing expressions are not shown in the Advanced Column Properties dialog.

AutoNumber

A sequence and a trigger will be created if a column ISAUTOINCRIMENT.

CREATE TABLE "TAUTO" ("NKEY" NUMERIC(10) PRIMARY KEY, "STEXT" VARCHAR2(50))
CREATE SEQUENCE "TAUTO_ID_SEQ" START WITH 1
CREATE TRIGGER "TAUTO_ID_TRG" BEFORE INSERT ON "TAUTO" FOR EACH ROW BEGIN
IF :NEW."NKEY" IS NULL THEN
SELECT "TAUTO_ID_SEQ".NEXTVAL INTO :NEW."NKEY" FROM DUAL; END IF;
END;

As with Default Values, the generated key is not returned with the insert. Pick Reload Data to acquire the automatic value. Use Grid/Data/Sort to return order. Neither the trigger, nor the sequence will be replaced if they already exist. To reset the sequence and trigger SQL Execute DROP TRIGGER and DROP SEQUENCE.

START WITH will be set to begin above the highest existing value.

Oracle Tip: How to use default values with database columns

If you wish to use the column as an update key, but it is not the primary key or part of a unique constraint, please select the column as the update column in the data link dialog.

Identifier Case

By default, Table and Column and index name identifiers will be converted to upper case. Set SqlPreferCaseSettings off (0) in HKEY_CURRENT_USER\Software\Interscape\Offline\1.0\Settings to override this behavior.

Identifiers are converted by the database server, to uppercase if not quoted. If you have SqlPreferCaseSettings off (0) and SqlTableIdBracket on (1) the identifiers held in the grid must match exactly those in the database.

System Tables and Schema

oracle system tables

To list SYSTEM tables in the Data Window, and TABLES, VIEWS, SYNONYMS that are not part of the schema for the login, hold SHIFT when initializing the data source.

Sample Migration

Load Customers from Nwind.mdb, as in the Crash Course Tutorial. Replace the connection string with a link to a Oracle server database, for example Provider=MSDAORA.1;User ID= SYSDBA;Data Source=localhost. Change the size of Description from 536870910 to 255 (Oracle ORA-01754: a table may contain only one column of type LONG).

A Write Data command would create a table with:

CREATE TABLE "CATEGORIES" (
"CATEGORYID" NUMERIC(10) NOT NULL,
"CATEGORYNAME" VARCHAR2(15) NOT NULL,
"DESCRIPTION" VARCHAR2(255) NULL,
"PICTURE" LONG RAW NULL)
CREATE SEQUENCE "CATEGORIES_ID_SEQ" START WITH 9
CREATE TRIGGER "CATEGORIES_ID_TRG" BEFORE INSERT ON "CATEGORIES" FOR EACH ROW BEGIN
IF :NEW."CATEGORYID" IS NULL THEN
SELECT "CATEGORIES_ID_SEQ".NEXTVAL INTO :NEW."CATEGORYID" FROM DUAL;
END IF;
END;

and create keys and indexes using:

ALTER TABLE "CATEGORIES" ADD PRIMARY KEY ("CATEGORYID")
CREATE UNIQUE INDEX "CATEGORYNAME" ON "CATEGORIES" ("CATEGORYNAME")

to create the primary key and categoryname index. After creating the table, Write Data Preview (SQL) would produce

-- updating Categories (CATEGORYID, CATEGORYNAME, DESCRIPTION, PICTURE)
UPDATE "CATEGORIES" SET "DESCRIPTION"='Cheeses and Cream' WHERE "CATEGORYID"=4
-- update complete (1 of 8 rows had changes)

if Description were replaced with 'Cheeses and Cream' for Dairy (categoryid 4).