The Oracle Open Server Plug-in
==============================

Registration
------------

The Oracle Open Server plug-in requires a plug-in specific registration key
for use with a registered Open Server.

System requirements 
-------------------

The Oracle plug-in requires the client environment for Oracle 8.1.6 or later
to be installed on the system on which the Open server will be run.

Unix requirements:

The Oracle plug-in needs to load a number of Oracle libraries. To be able to
do this you must include the location of the Oracle libs directory in the
Unix environment variable 'LD_LIBRARY_PATH'.

setenv LD_LIBRARY_PATH "${LD_LIBRARY_PATH}:$ORACLE_HOME/lib"

Limitations
-----------

The Open Server supports data access only, it doesn't directly support data
definition. You can work around this by using the command 'dbms
query("execute", "<your statement>")' to send the statements directly to the
Oracle server with out processing.

The functions "describe databases", "describe tables", and "describe columns"
haven't been implemented.

Special Settings
----------------

There is an environment setting in the open server's environment file, number
1503, to set the default connection options that the open server will use
with Oracle.  Set this to the following string:
"(ADDRESS=(PROTOCOL=TCP)(Host=<host-name>)(Port=< port-number>))" where
"<host-name>" is the host name or IP address of the machine on which oracle
is running and "<port-number>" is the TCP port on which it is published. By
default the oracle publishes on port 1521. If the Open Server and the Oracle
server are running on the same machine then you can set "<host-name>" to
"localhost".

It is recommended that the Open Server and the Oracle server run on the same
machine to limit network traffic and improve performance.

Oracle Specific considerations

When a client opens a session with an Open Server running the Oracle Open
Server plug-in, the session is initialized but no connection is made to the
Oracle data server. A connection to the Oracle data server is made when a
database is opened. When opening a database the database name will be
interpreted as an "ORACLE_SID"  (Oracle System Identifier) and used in the
connection string to connect to the Oracle data server.

If your application was written for PrimeBase and not Oracle you will
probably need to make some changes. Here are some of the things to watch for:

1) Reserved words. A lot of names that you can use in PrimeBase are not
allowed in Oracle such as having columns called "Number" or "order".

2 Oracle doesn't allow you to create defaults on serial counters. This can be
worked around by using triggers.

3 Oracle doesn't have case insensitive columns. To do a case insensitive
search you must force everything to uppercase before doing the comparison.
You can talk to your Oracle expert to find the best way to handle this.

4) By default in Oracle a column of type NUMBER with no scale or precision is
a float. But the Oracle plug-in converts Oracle data of type NUMBER with no
scale or precision to an integer. If this is not done then when the user
executes "select count(*) from foo" the Oracle plug-in sees a result set
returned with one column of type NUMBER with no scale or precision and your
select count(*) would produce a result set with one column of type float,
which is probably not what you would expect. If you want NUMBERs with no
scale or precision to be treated as floats then this behavior can be turned
on and off with the commands. "SET NUMBER_IS_FLOAT()" and "SET
NUMBER_IS_INT()".

5) The % operator (as MOD) is not supported by Oracle.  PrimeBase, however,
supports the MOD() function which is also supported in Oracle.

6) Data concatenation on the server. You cannot use the '+' operator in SQL
statements to concatenate strings in Oracle. Instead you must use the
CONCAT() function which is supported by both Oracle and PrimeBase. For
example the following will work in PrimeBase but not Oracle: "select c1 from
foo where c1 = c2 + c3;" where the following statement will work in both
PrimeBase and Oracle: "select c1 from foo where c1 = CONCAT(c2,c3);"

7) Oracle doesn't support LOCATE() function.  Use 'LIKE' instead.

8) Oracle doesn't have a Boolean data type. By default the Oracle plug-in
will return the Oracle type 'NUMBER(1)' which is a 1 digit number as a
Boolean. This behavior can be controlled with the custom commands "SET
NUMBER1_IS_BOOL()" and "SET NUMBER1_IS_NUMBER()" as described below.

9) Oracle does not support '*' as the wildcard character.  However, PrimeBase
supports '%' which is also supported by Oracle.

10) When more than 1 table is specified in a select Oracle requires that you
explicitly

10) Oracle is very particular when it comes to SQL.  If more than one table
is used in a select/update/insert/delete statement, each column or * (all
columns) should be defined with the table, column method.  Otherwise, you
will get a "Column Ambiguously Defined" error message.

11) Oracle doesn't like unary Boolean tests in 'where' clauses. The following
will result in an error: "select * from foo where c1;" Replace the statement
with the following: "select * from foo where c1 = $TRUE;"

12)  "select MAX(c1) from foo'" will always return a row on Oracle even if
the table is empty. In this case $NULL is the value returned.


NOTE: When defining the Oracle scheme use CLOB and BLOB types and not "Long"
and "Long Raw".

Custom plug-in commands
-----------------------

The Oracle Plug-in supports the following custom commands that you can use to
work around some of the differences between PrimeBase syntax and Oracle
syntax.

DBMS QUERY("execute", "<Oracle command>");

Executes the string "<Oracle command>" in the Oracle server. The string is
not parsed by the PrimeBase Virtual Machine so it can contain Oracle specific
syntax. The command returns no data.

Example: DBMS QUERY("execute", "CREATE PUBLIC SYNONYM Employees for
common.Employees");

---

DBMS QUERY("select", "<Oracle select>") [INTO <cursor> [FOR EXTRACT]];

Executes the string "<Oracle select>" in the Oracle server. The string is not
parsed by the PrimeBase Virtual Machine so it can contain Oracle specific
syntax. The command returns cursor as if a normal select had been executed.

Example: DBMS QUERY("execute", "select * from foo where UPPER(foo.name) =
'PRIMEBASE'");

This example shows one way to work around the lack of case insensitive
columns in Oracle.

---

DBMS QUERY("update", "<Oracle update or insert>") ;

Executes the string "<Oracle update or insert>" in the Oracle server. The
string is not parsed by the PrimeBase Virtual Machine so it can contain
Oracle specific syntax. The command sets the $rowseffected flag.

Example: DBMS QUERY("execute", "update foo set quality = "VeryGood" where
UPPER(foo.name) = 'PRIMEBASE'");

---

SET NUMBER_IS_FLOAT();

Oracle data type NUMBER with no scale or precision will be treated as a
float.

---

SET NUMBER_IS_INT();

Oracle data type NUMBER with no scale or precision will be treated as an int.

---

SET NUMBER1_IS_BOOL();

Oracle data type NUMBER with precision 1 (a 1 digit number) will be treated
as a Bool. The following conversion will take place:

Oracle value: PrimeBase Value
 ----------------------------- 
0 			: $FALSE 
1 			: $TRUE 
>1         	: $MAYBE

This is the default setting.

---

SET NUMBER1_IS_NUMBER();

Oracle data type NUMBER with precision 1 (a 1 digit number) will be treated
as an integer.

---

SET SCHEMA("<name>");

By default the schema name is assumed to be the same as the user's login name
when not specified in a query. You can use this command to set the default
schema name to something other than the user's login name. For example if you
logged in as 'bob' and you executed the following select: "select * from foo"
it would be the equivalent of executing: "select * from bob.foo". If you
execute the command "SET SCHEMA('Sally');" then "select * from foo" would
default to "select * from Sally.foo".


DEBUG SETTINGS
--------------

The following can be used to turn on/off tracing on the open server. When
tracing is turned on the statements sent to the open server to be executed
will be displayed on the Open Server's console.

set tracing_on()

set tracing_off()


