Oracle

Open Dental is compatible with Oracle, but is not tested extensively. This might be useful for very large institutions or for users who are already familiar with Oracle. For everyone else, MySQL will be a better choice because it has been tested more. There are many peculiarities of Oracle which we had to address, and we might have missed something.

For programmers working on OD, it is essential that you understand the Oracle peculiarities and how to deal with them.

Important Oracle issues

* All queries must be valid in both MySQL and Oracle. Sometimes, that's impossible, and we must create two separate queries, one for MySQL, and one for Oracle. When writing the two different versions of your query, you have to test whether you are connected to an Oracle database or not. If in the main OpenDental project, then test: FormChooseDatabase.DBtype==DatabaseType.Oracle. But this won't be available in the OpenDentBusiness layer, so there, you can instead use DataConnection.DBtype==DatabaseType.Oracle.
* Creating tables or adding columns in unavoidably complex. Read the rules towards the bottom, and let us double check it when done.
* Always use AND and OR instead of && and ||.
*MySQL can use LIMIT at the end of a SELECT statement, while in Oracle you must instead use the "RowNum<= #" pseudo-column keyword as
part of the where clause to get the same effect. Be careful in Oracle, since the rownumber is calculated before sorting, a subquery must be used to perform the sorting before the rownumber is calculated when using ORDER-BY and limits in the same query.
* Don't use AS. Aliases work fine without that keyword.
* When inserting rows, all columns which are defined as "NOT NULL" must be specified in the insert SQL statement.
* When using SELECT-GROUP-BY statements in Oracle, one cannot specify columns in the SELECT clause which are not also present in the GROUP BY clause. So the GROUP BY must always explicitly include all columns, forcing the results to be grouped by all columns.
* In Oracle, use the || operator to concatenate strings instead of the CONCAT function which would limit you to only two strings.
* Cannot perform an UPDATE statement using multiple table names. Instead, use a correlated subquery, like this: UPDATE table2 SET table2.column1= (SELECT table1.column1 FROM table1 WHERE table1.colKey=table2.colKey).

More obscure Oracle issues

* We need to be more aware of saving decimal values with fractions which are below about 5 decimal places, because the Oracle .NET will throw an exception and abandon a query if a round-off error occurs.
* Cannot create text-based columns in a table which will allow blank text, because blank text is considered to be the same as NULL by Oracle.
* Must use ordinal values in union-order-by statements.
* Must be sure to disable command splitting on Oracle commands which are a single command containing more than one ";", including but not limited to
fetch blocks.
* Due to date-time restrictions in Oracle, we no longer enclose date output with single quotes when using POut date functions.
* Can only use simple column names in Oracle order-by statements. i.e. select * from patient order by patnum!=17; is valid in MySQL, but invalid in
Oracle. The problem is sometimes solved with aliasing. Or use the Oracle function DECODE(patnum,17,0,1), which means if val1=val2, then return val3, else return val4.
* The date string "0000-00-00" is invalid in Oracle, so it should not be used. However, the date "0001-01-01" is valid in both Oracle and MySQL. The POut.PDate function handles this automatically.
* Cannot perform an "update" statement using multiple table names. Must instead perform a select query to get a list of the relevant primary keys and
perform an the update as a second query (more updates may be necessary) containing the table in question and a list of the primary keys and
corresponding values. Or, alternately, use correlated subquery, like this: UPDATE table2 SET table2.column1= (SELECT table1.column1 FROM table1 WHERE table1.colKey=table2.colKey).
* Auto increment is not supported directly in Oracle. Instead, after creating an auto-increment column for MySQL, a sequence and trigger must be
constructed from the Database Maintenance tool which simulates MySQL auto-incrementation. To add a new sequence/trigger pair for a new auto-increment
column, add the table name and column name to the list given in the code for the database maintenance tool.
* Common words are sometimes not supported as table names or field names in database systems. For instance, Oracle does not allow "user" as a table
name nor does it allow "mode" as a field name. Thus it is best practice to use uncommon names, or perhaps add an extra base 64 character such as "_"
to the end of a common name, when creating or renaming a column or table name.
* Use "case when then" statements to handle creating temporary columns in SQL statements. This is necessary because Oracle has a problem creating
temporary columns from some expressions. i.e. select patnum,guarantor, CASE WHEN patnum=guarantor THEN 0 ELSE 1 END as aresame from patient where
rownum<=10 ORDER BY 3; There might be another option here which is to use the DECODE function in the ORDER BY or in the SELECT itself. DECODE is explained above.
* The date-time functions differ between MySQL and Oracle. For Oracle queries, we use our internal GetNowDateTime(), which gets the current server time, to then create an output string for queries. Be careful to store the returned date time string as to reuse the exact same time in a single query where NOW() or CURDATE() would be used multiple times.
* When creating tables or adding columns, always make two versions. Oracle should use INT for all number columns, whereas MySQL uses either tinyint or mediumint. Oracle int columns should be NOT NULL, but only if creating a new table. NOT NULL cannot be used when adding a column in Oracle. All text columns for both MySQL and Oracle should never use NOT NULL. TEXT columns never have a default. VARCHAR column defs always end with default '', especially the MySQL version. This prevents nulls.
* This command is used in place of SHOW TABLES: SELECT TABLE_NAME from USER_TABLES

Conversion from MySQL to Oracle

*Update opendental MySQL database to the most recent version.
*Backup the newly updated database.
*Clear sound text from the recently updated MySQL database by running the following MySQL command: update sigelementdef set sound='';
*Use the Oracle Database Configuration Assistant to create a new/blank database. It seems to work best if you use an upper-case name.
*Use a database conversion tool, such as Full Convert from Spectral Core, to create the initial schema and copy existing MySQL data into the new database, and be sure to use the following options in the process:
-Disable any optimizations (such as multi-record insert).
-Convert text types from the MySQL database into the varchar2 type for the target Oracle database.
-Note: you may want to supress errors so that extra data is not lost during the conversion. It may be best to try the conversion both ways.
*Run the conversion tool and note any errors.
*If you have trouble opening OD, you might have to make a manual entry in the computer table in the database for the current computer.
*Run the database maintenance tool from inside OpenDental by going to Tools->Database Maintenence. Note any errors.
*Begin using the program.