Oracle is partially supported. Oracle should only be considered if you have already invested in an Oracle platform and do not wish to move to MySQL. There is no reason for an existing user to switch to Oracle, so no tool has been built for that purpose. We would treat that as a conversion that we would have to charge for. For new customers who wish to start out with Oracle, use this schema:
and then this data dump:
We assume that you have your own database manager who can make use of the above files to create a database or schema.
We targeted Oracle 11g, and we also tested to make sure that it would connect to 10g.
In our development environment, in order to get the Middle Tier to work, we had to install
although that will probably not be necessary for the compiled version.
Features that are not supported in Oracle
Reports - Most reports will have issues on Oracle, particularly GROUP BY statements.
User queries not supported due to security risk. Reports
The EHR module will not run on Oracle, mostly because of the very complex reports.
eClinicalWorks users cannot use Oracle
Guidelines for Writing Queries in Oracle
* 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, use DataConnection.DBtype to test which database is being used.
* Creating tables or adding columns in unavoidably complex. This is now handled internally by a set of helper functions.
* 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.
* When inserting rows, all columns which are defined as "NOT NULL" (in practice, this means all columns) 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.
* Because the GROUP BY syntax is ridiculously cluttered, always include a simpler MySQL version. There are known performance issues when MySQL groups by a large number of columns.
* CLOBs cannot be used in a GROUP BY clause. One solution is a subquery.
* In Oracle, use the || operator to concatenate strings instead of the CONCAT function which would limit you to only two strings.
* All literal strings must be surrounded by single quotes. Double quotes are not allowed. Double quotes may be placed inside single quotes. To escape a single quote inside a string, use two single quotes.
* 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.
* Text-based columns cannot be NOT NULL 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
* 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 usually solved like this: ORDER BY CASE WHEN PatNum=17 THEN 0 ELSE 1 END. Another option is to use the Oracle function DECODE(patnum,17,0,1), which means if val1=val2, then return val3, else return val4. However, that doesn't work in MySQL.
* 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=
* Auto increment is not supported directly in Oracle. Instead, the primary key must be calculated ahead of time when doing an insert. OpenDental handles this internally.
* 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.
* Column and alias names must start with a letter or number. No symbols or underscores are allowed. In some cases, we surround the column name or alias with a double quote as a workaround.
* 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 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 use our helper functions. NOT NULL cannot be used when adding a column in Oracle. For a few years, our required pattern was that all text columns for both MySQL and Oracle should never use NOT NULL (TEXT columns never have a default in MySQL anyway). But that pattern was forgotten and neglected. It may not have even been the best choice of patterns. This may be an issue in the future for (our very few) Oracle users.
* This command is used in place of SHOW TABLES: SELECT TABLE_NAME from USER_TABLES
* GROUP_CONCAT is not supported and requires a workaround.