Infor O&R on Oracle
From Oracle to SQL Server, then to multi-tenant cloud.
Many long-running Infor O&R, IPS and Hansen agencies are on Oracle. Infor's route into multi-tenant cloud starts from SQL Server, so the database is converted first. This page shows what to expect: how the data types line up, where the conversion goes wrong, and what your agency receives.
Request a Conversion AnalysisThe Route
Four steps from Oracle to the cloud.
Readiness on Oracle
Bring the data up to Infor's migration rules while it is still on Oracle: orphan rows, blank and NULL values, codes that differ only by case.
Oracle to SQL Server
Convert the database with every product column typed from the Infor O&R product definition, and every agency column typed by a rule your team approves.
Verify
Check the mapping and the converted data before anything is uploaded, so nothing reaches Infor that has not been confirmed.
SaaS Migration to Infor
Run Infor's SaaS Migration extract from SQL Server and hand it to Infor for the move into the multi-tenant tenant.
Data Types
Oracle and SQL Server, side by side for O&R.
How a typical O&R database on Oracle lines up with the same database on SQL Server.
| Oracle | SQL Server | What it holds |
|---|---|---|
| NVARCHAR2(n) | nvarchar(n) | Text. Length is counted in characters on both. |
| VARCHAR2(n) | nvarchar(n) | O&R uses Unicode text on SQL Server. |
| NUMBER (no size) | int, bigint, smallint or money | Record keys, counts and fee amounts. The right type comes from the product definition, column by column. |
| NUMBER(p,s) | decimal(p,s) | Direct. |
| FLOAT(126) | float | Quantities, multipliers and measurements. |
| DATE | datetime | Oracle DATE carries the time to the second. |
| TIMESTAMP(n) | datetime | O&R stores date and time as datetime. |
| NCLOB, CLOB | nvarchar(max) | Comments and long text. Older databases may still use ntext. |
| BLOB, RAW | varbinary | Binary content. |
| Sequence (NEXTVAL) | Identity column | Reseeded above the highest key after the load. |
| Owner (CDR, CORE) | Schema (CDR, Core) | Same names, so tables match one for one. |
The same idea in each dialect
| Oracle says | SQL Server says |
|---|---|
| SYSDATE | GETDATE() |
| NVL(a, b) | ISNULL(a, b) or COALESCE |
| a || b | a + b or CONCAT |
| ROWNUM <= n | TOP n |
| SELECT ... FROM DUAL | SELECT ... (no FROM) |
| DECODE(...) | CASE WHEN ... |
| TO_CHAR, TO_DATE | FORMAT, CONVERT, CAST |
| PL/SQL procedure, trigger, package | T-SQL procedure, trigger |
| Database link | Linked server |
What Goes Wrong
Five traps in an O&R conversion.
Numbers with no size
On Oracle, O&R number columns carry no precision or scale, so the database alone cannot tell a record key from a fee amount. A generic conversion tool guesses, and its default guess is a floating point number. Every key and every amount has to be typed from the product definition instead.
Blank is not NULL
Oracle stores an empty string as NULL. SQL Server keeps them apart. Rules and formulas that test for blank or NULL can behave differently after the move unless the values are settled first.
Upper and lower case
Oracle compares text case sensitively. SQL Server usually does not. Codes that differ only by case can collide on load and have to be found before the move.
Date defaults and time zones
On Oracle, the date a record was added comes from the database server clock. On SQL Server, O&R applies the agency time zone. Dates before 1753 do not fit and are listed as exceptions.
Custom database code
Agency procedures, triggers, views and database links written for Oracle do not carry into multi-tenant cloud. Each one is reviewed and either retired or rebuilt with a supported cloud feature.
Deliverables
What your agency receives.
- A full mapping record: every column's Oracle type, its O&R type and its SQL Server type, and why.
- Exceptions listed with a decision for each: blank values, case collisions, early dates, agency columns.
- A list of agency database code with a disposition for each object.
- Counts and checks before and after the conversion, so the move can be signed off.
The analysis is run with the Orchestra, which checks every product column against the O&R product definition before anything moves. It fits into the wider cloud migration readiness review. See an example: sample Oracle to SQL Server conversion analysis.
Questions
Frequently asked questions.
Does Infor's multi-tenant cloud run on Oracle?
No. Infor's SaaS Migration route into multi-tenant cloud starts from a SQL Server database, so an Oracle agency converts to SQL Server first.
Can a standard Oracle to SQL Server tool do the conversion?
It can move the data, but its default type choices are wrong for O&R number columns. The mapping has to come from the O&R product definition and be checked before the load.
We are staying on-premises. Does this still apply?
Yes. The same type mapping applies to an agency moving from Oracle to SQL Server on-premises.
Does this work for legacy IPS and Hansen?
Yes. The practice spans the full product lineage, from legacy Hansen and on-premises IPS through Infor Operations and Regulations in the cloud.
Start the Conversation
Running O&R on Oracle?
Reach out for a comprehensive conversion analysis before planning the move.
Request a Conversion Analysis