Re: Migrating an Application from SQL Server 2000 to Oracle 10g

On Jul 15, 3:38 pm, tcole6 <tco..._at_gmail.com> wrote:
> We have an application that was written using SQL Server 2000 as the
> RDBMS. We now have a need to convert this to Oracle. We know that we
> obviously need to hire an Oracle DBA, but we don't have one at this
> time...
>
> Our application uses queries with joins across databases. In SQL
> Server this is acheived like so:
>
> SELECT a.a, a.b, b.a, b.b from Database1.dbo.Table1 a LEFT JOIN
> Database2.dbo.Table1 b WHERE a.c = '123'
>
> We absolutely do not understand the how Oracle uses Schemas, but we
> are hoping to be able to create a structure where the above select
> statement would still function. In other words we need to create these
> databases with tables under the schema dbo. Otherwise we would need to
> rewrite lots of code.
>
> Are schemas tied to user accounts? Can anyone help me understand this,
> or is this information just too vague?
>
> Thanks in advance.

What you call a database is what Oracle calls a schema. Users and schemata are the same thing in Oracle. The ansi sql works in Oracle, I think (I don't use it). So if I'm reading your code correctly, you would have users dbo1 and dbo2, and an oraclish way to do a join would be:

select d1.a, d1.b, d2.a, d2.b
from dbo1 d1, dbo2 d2
where d1.c=d2.c
 and d1.c='123'
/

The d1 and d2 are aliases (like your a and b, I just didn't want the confusion with column names), you could use the schema names directly. You'd also have to grant privileges to select objects between schemata. No schema in the select means the current schema.

The things that will really burn you are differences in locking and concurrency, and the world-view of doing more stuff in the database rather than in apps. I strongly suggest you buy and study books by Tom Kyte - he includes good explanations as to how and why things are done differently between where you are and where you want to be. See http://asktom.oracle.com for book links.

jg

--
@home.com is bogus.
How CAPTCHA got trashed.  http://www.computerworld.com.au/index.php/id;489635775;fp;;fpid;