Sample Deliverable

Oracle to SQL Server: the one-page summary

What an Infor O&R agency on Oracle receives before its database moves to SQL Server: what was found, what to expect, and the decisions to approve, for a fictional agency. The analysis only reads the Oracle database.

Illustrative sample. Sample City and every name, value and finding on this page are fictional.

Every count on this page is a round illustrative figure, not a result from any agency.

Conversion analysis summary: Sample City, Infor O&R

On Oracle, every O&R number column is a bare NUMBER, so the database alone cannot tell a record key from a fee amount. Every product column is checked against the O&R product metadata, which gives the right SQL Server type. The agency also receives the full mapping record: each column, the type used and why.

The analysis only reads the Oracle database; it never changes it. Nothing is converted until the agency has approved every decision below, and the result is then checked against this record.

What was checked

The scope of the check and what it found, at a glance.

Measure	Result	What it means
Columns checked	2,000	Every column in the O&R schemas of the Oracle database.
Product columns	1,800	Found in the O&R product metadata, so the metadata sets the SQL Server type.
Agency columns	200	Added by the agency or left from older releases; each needs an approved rule.
Text lengths	All match	Oracle NVARCHAR2(n) and SQL Server nvarchar(n) both count characters.
Exceptions	8 kinds	Listed below, each with the decision to approve before conversion.

Column types

Each Oracle type, the O&R metadata type that decides the target, and the SQL Server type used. Four rows share the Oracle type NUMBER; only the metadata tells them apart.

Oracle type	O&R type	SQL Server type	Columns
NUMBER	Integer	int	600
NUMBER	Short	smallint	30
NUMBER	Long	bigint	20
NUMBER	Currency	money	40
NUMBER(p,s)	Decimal	decimal(p,s)	20
FLOAT(126)	Float8	float	30
NVARCHAR2(n)	Varchar	nvarchar(n)	700
NCLOB	LongVarChar	nvarchar(max)	60
DATE	Timestamp	datetime	300

Exceptions and decisions

What will not convert cleanly on its own, why it matters, and the decision proposed for the agency to approve.

Exception	Found	Why it matters	Decision
Bare NUMBER keys	650 columns	Oracle stores O&R numbers without precision. A generic tool's default maps them to float(53), which is wrong for keys.	int, smallint or bigint, as the O&R metadata says.
Bare NUMBER amounts	40 columns	Fees and payments mapped to float can round.	money, as the O&R metadata says.
Agency columns	200 columns	Not in the product metadata, so no product type to follow.	A rule per column, approved by the agency, from the values actually stored.
Blank vs NULL	30 columns	Oracle stores an empty string as NULL; SQL Server keeps them apart, so filters and reports can change.	One rule per column, agreed and applied before conversion.
Case collisions	5 code tables	Oracle compares text case sensitively; the usual O&R SQL Server collation does not, so codes that differ only by case collide.	Merge or rename with the agency before the move.
Dates before 1753	10 values	SQL Server datetime cannot hold them; most are typing errors.	Correct at the source, or set to an agreed value from the list given to the agency.
Date defaults	Most tables	Oracle SYSDATE uses the database server clock; the O&R SQL Server default uses the agency time zone.	Keep the O&R default and confirm time-based reports.
Agency database code	15 objects	PL/SQL views, triggers and procedures do not run on SQL Server.	Rewrite in T-SQL or replace with a supported O&R feature, each reviewed by a person.

From the workbook: data lineage

The agency also receives a workbook that traces every column from its Oracle source to its SQL Server type, with the rule that set the type, the check, the decision and its status. This is an excerpt.

Excerpt of the data lineage sheet for Sample City: ten Oracle columns, each with its Oracle type, O&R type, whether the type was set by the product metadata or an agency rule, the SQL Server type, the check, the decision, and a status of Approved or Decision needed. — Excerpt of the conversion workbook. Illustrative data.

Sign-off

Nothing is converted until these approvals are recorded. The conversion is then checked against this record.

Role	Approves	Signed
Agency project lead	Exceptions and decisions above	Name, date
Agency database administrator	Agency column rules and data fixes	Name, date
eClas Consulting	Mapping record and conversion check	Name, date

Is your agency on Oracle? We'll help you prepare for the conversion. Reach out for an analysis, or to see what yours would look like.