Fog Creek Software
Discussion Board




Migrating to Oracle

Does anybody have any links to resources / books that will help us in the transition to supporting Oracle as the backend to our system?

We currently use Visual FoxPro 7 with ODBC to connect to SQL Server 2000 and VFP File Databases as the backends, but of course some clients want us to support Oracle. So anything that covers the sort of differences and issues to expect when going from SQL 2000 to Oracle would be ideal.

Thanks

Chris.

Chris
Monday, July 14, 2003

It depends on your system and how you have architected the current database. There is a great mailing list Oracle-L@fatcity.com with smart Oracle talent.

Tom Vu 
Monday, July 14, 2003

TOAD. Buy it. Now.

Does 9i support inner join syntax yet? Or is it still * stuff?

If you need full text searching, well... be prepared...

Get a VMWare partition, make a copy of it - practice installing Oracle.

99% of the time the answer is in the .ora files.

There's more... [grin]

Philo

Philo
Monday, July 14, 2003

Start by taking 3 bottles of Tylenol...

Pyreal
Monday, July 14, 2003

TOAD (http://www.toadsoft.com) is an extremely useful tool, on the lines of SQL Manager (there is no GUI Oracle Manager).
Oracle9 supports even the standard JOIN ANSI syntax. DROP COLUMN is supported since 8i as is the BLOB data type. Still a pain to rename a column though.

I don’t think you need to practice ORACLE installation that much. Usually our ORACLE customers have their own fine tuned ORACLE servers.

To compare SQL2000 and Oracle is like comparing the Mini Cooper and BMW740i. They are both cute beamers but on very different levels. Expect to spend a lot more time maintaining and tuning Oracle.

coresi
Monday, July 14, 2003

Philo : oracle 9i supports standard SQL92 ANSI standard join syntax.

Chris :

I have been heavily involved with Oracle work for the last 6 months, coming in with 0 Oracle experience before that.

Initially you will need to search for web info, buy a few books and make a few mistakes, as is always the way.

The best resources by far for 'why doesn't it work' questions are oracle metalink and technet.

technet.oracle.com
metalink.oracle.com

Simply go to the discussion forums and find somone who has already asked your question (somone's always already asked).

Technet requires a free signup i believe and metalink requires your organisation has a support agreement with oracle.

Braid_ged

braid_ged
Monday, July 14, 2003

Thanks for all the input guys. At least I now have an idea of where to start.

And thanks for the links to technet.oracle.com, I was getting so confused by the documentation that came in the Oracle CD Pack (21 CDs).

Chris
Tuesday, July 15, 2003

"I don’t think you need to practice ORACLE installation that much. Usually our ORACLE customers have their own fine tuned ORACLE servers"

Hello? Development and test servers? ;-)
I was simply making the point to be prepared to slick your machine a few times while you're installing.

Oh - you'll be asked "what type of database is this" when you install - the ONLY difference between OLTP, Data Warehouse, and "mixed" (or general or whatever) is the block size, and it's not that big a deal - pick the middle one and forget about it.

Finally - hopefully it's improved, but recognize that Oracle's native platform is Unix, and the docs have often been ported from unix, tho not perfectly. If someone isn't working right even though you're doing it just like the docs, double-check in usenet.

Philo

Philo
Tuesday, July 15, 2003

One thing to be aware of is that in Oracle you don't use a stored procedure to return a cursor, which I gather is the way SQL-Server people tend to do things.

Some other Gotchas:

* There is no autonumber/id field - you have to do it manually
* You can't query the table that is being triggered in a trigger
* Don't use Oracle for any string manipulation. It is slooow (a real trap for VFP people).
* serious tuning becomes important far sooner than it would with a VFP database
* make sure that if you use a varchar2 column for keys you fill it completely (don't permit leading blanks).  VFP loses the record if you do (probably the same in SQL-server?)
* Use the Microsoft ODBC drivers not the Oracle ones

I also agree that metalink support is good

(Feel free to contact me if you have any specific questions: I've been using VFP6 and Oracle for years)

LesC
Tuesday, July 15, 2003

* There is no autonumber/id field - you have to do it manually

I assume you mean by sequences.  Not too manual, still atomic fetch and update.  Very handy, and I'm rather more fond of them than auto increment fields...

Andrew Hurst
Tuesday, July 15, 2003

best site for Oracle help:

http://asktom.oracle.com/

will s
Tuesday, July 15, 2003

The biggest problem we had in moving from MDB and SQL Server to supporting Oracle as well is that Oracle is case sensitive and the others are not. Seems like a trivial problem, but it wasn't for us.

pdq
Tuesday, July 15, 2003

LesC: Thanks for all those tips, and the offer to allow me to email questions.

pdq: When you says "Case Sensitive" do you mean that "SELECT cCustId FROM Customers" is different to "SELECT Ccustid FROM cUSTOMERS"? This could be a major pain.

And thanks again for everbody elses tips.

Chris
Tuesday, July 15, 2003

To the Oracle optimiser "SELECT name FROM customers" and "SELECT NAME FROM CUSTOMERS" are different queries. It can't use any cached results from the first for the second.

LesC
Tuesday, July 15, 2003

We've been running 8i, so this may be different with 9. I think it's table/field/username, etc are case sensitive. We found it not just for the optimizer, but for any SQL string sent to the database.

pdq
Tuesday, July 15, 2003

Also, schemas - objects you create (tables, sequences, views, etc) belong to the user that creates them. Other users have to precede the object name with the schema name, or you have to use global synonyms.

Best to establish your schemas before starting, then get everyone to use the schemas.

Philo

Philo
Tuesday, July 15, 2003

I have seen Toad do incredibly stupid things. But then that's true of many tools. Buyer's beware.

Li-fan Chen
Wednesday, July 16, 2003

Li-fan Chen: stupid things like…

Philo: schema/owner/namespace naming conventions are used by both MSSQL and ORACLE with some differences. A search for "Database Object Identifiers" on MSDN will shed some light on this. Also “Migrating Oracle Databases to Microsoft SQL Server 7.0” on the MSDN has some good points. It is always a good idea to fully qualify the namespace objects.


I believe Oracle converts automatically all object names to uppercase. What causes more problems is data case sensitivity:

select * from clients where name =’JOE’ is not the same as
select * from clients where name =’Joe’

All kind of tricks are employed to solve this matter. Recent Oracle versions offer Index Functions to speed things up by avoiding a full table scan when using
select * from clients where upper(name) = ‘JOE’

19th floor
Wednesday, July 16, 2003

*  Recent Topics

*  Fog Creek Home