Moving History from MS SQL to Oracle

Ignition
1

One of our clients wants us to migrate their Gateway, which I am totally up for doing.
However, as part of it, we need to move all of our SQL tables…including the history, from MS SQL Server, to Oracle Server.

Now, there are automatic tools for copying a DB, but will this screw up the tables for how Ignition reads them? I mean, are the schemas for the table data going to stay in line?
Or is there some tool in Ignition that does that kind of conversion?
I feel like either there isn’t going to be any problem, or there are going to be huge problems…

Any thoughts here?

2

I’d worry more about quoted identifiers in your queries. Square brackets are MS-only. Oracle follows the standard (double-quotes for idenfiers, single quotes for strings). MS can be told to accept the standard (ANSI quotes), so you can start fixing them before you migrate.

3

Also watch out for case sensitivity.

F.e. if you SELECT MyColumn in MsSQL, you get back a dataset with a MyColumn column name

But if you do the same in Postgres (and I believe it’s the same in Oracle), you get back a dataset with a lowercase mycolumn column name. This is not always a problem, but if you later use that dataset for processing, it might cause bugs.

In Oracle, you will need to SELECT "MyColumn" to get it back as MyColumn in the dataset.

4

Yeah, MySQL and SQL Server are case-insensitive, case-preserving when identifiers are unquoted. PostgreSQL is case-sensitive, and lower-cases any unquoted identifiers. Oracle is also case-sensitive, and upper-cases unquoted identifiers. Oracle is the only SQL standard compliant implementation in this regard.

5

MySQL is case sensitive on file systems that are case sensitive, at least for table names. On Linux, as an example. Column names are not case sensitive.

1 Like
6

Thanks for much good advice, folks!

I have managed to get most things running, but doing simple Oracle table reads seem impossible. I can only SELECT everything from the Oracle. “Why” is completely unclear. If I use this:
SELECT * FROM FAI_FUEL_HIST.DWA_EVENT2 everything is fine
If I try to just pull 2 columns, like this:
SELECT ‘EventNum’, ‘SiteName’ FROM FAI_FUEL_HIST.DWA_EVENT2
It retrieves those 2 columns, but with all data overwritten as “EventNum” and “SiteName”.

Adding a WHERE clause that should be good, returns 0 rows every time.
Trying to use ORDER BY does nothing.

If anybody has seen this bizarre behavior with Oracle, let me know.

7

Use double quotes - the single quotes are telling Oracle you want to retrieve the literal strings EventNum and SiteName

1 Like
8

That worked, although I could have sworn I tried the double quotes yesterday before I tried the single quotes. Not sure what happened there.
thanks!

1 Like