Oracle vs. MS-SQL dialects 
Author Message
 Oracle vs. MS-SQL dialects

I have about 900 lines of stored procedure code that I wrote in MS-SQL that
I need to transfer to run on an Oracle database.

I know there are some things I have to change, in particular:

parameter passing, which have to be explicitly passed as in out or in out

all my SELECT ... FROM sometab INNER JOIN someothertab ON
somefield=someotherfield WHERE ...
will have to have the JOIN info put into the WHERE clause using + operators
for outer joins etc.

What would replace




would it be

DEFINE val=1
select * from tab where sexval=&val
UNDEFINE val

or am I missing something?

Thanks,

Jon



Sun, 23 May 2004 23:51:37 GMT
 Oracle vs. MS-SQL dialects

What version of Oracle?  I believe 9i supports the inner and outer join
syntax.
Jim


Quote:
> I have about 900 lines of stored procedure code that I wrote in MS-SQL
that
> I need to transfer to run on an Oracle database.

> I know there are some things I have to change, in particular:

> parameter passing, which have to be explicitly passed as in out or in out

> all my SELECT ... FROM sometab INNER JOIN someothertab ON
> somefield=someotherfield WHERE ...
> will have to have the JOIN info put into the WHERE clause using +
operators
> for outer joins etc.

> What would replace




> would it be

> DEFINE val=1
> select * from tab where sexval=&val
> UNDEFINE val

> or am I missing something?

> Thanks,

> Jon



Mon, 24 May 2004 00:28:25 GMT
 Oracle vs. MS-SQL dialects
8.1.6, because I had to host it on a Netware server.


Quote:
> What version of Oracle?  I believe 9i supports the inner and outer join
> syntax.
> Jim



> > I have about 900 lines of stored procedure code that I wrote in MS-SQL
> that
> > I need to transfer to run on an Oracle database.

> > I know there are some things I have to change, in particular:

> > parameter passing, which have to be explicitly passed as in out or in
out

> > all my SELECT ... FROM sometab INNER JOIN someothertab ON
> > somefield=someotherfield WHERE ...
> > will have to have the JOIN info put into the WHERE clause using +
> operators
> > for outer joins etc.

> > What would replace




> > would it be

> > DEFINE val=1
> > select * from tab where sexval=&val
> > UNDEFINE val

> > or am I missing something?

> > Thanks,

> > Jon



Mon, 24 May 2004 02:12:37 GMT
 Oracle vs. MS-SQL dialects
You are basically going from transact sql to pl/sql which is Pascalish.
In triggers you do have the before and after values and don't have to select
them out of the table you just changed.
In general in Oracle you should use packages to group related functions and
procedures togther.
You would use ref cursors for returned result sets, and sequences for
autonumber.

If it were me I would try to group the procedures logically and then start
rewriting them one group at a time.

Jim


Quote:
> 8.1.6, because I had to host it on a Netware server.



> > What version of Oracle?  I believe 9i supports the inner and outer join
> > syntax.
> > Jim



> > > I have about 900 lines of stored procedure code that I wrote in MS-SQL
> > that
> > > I need to transfer to run on an Oracle database.

> > > I know there are some things I have to change, in particular:

> > > parameter passing, which have to be explicitly passed as in out or in
> out

> > > all my SELECT ... FROM sometab INNER JOIN someothertab ON
> > > somefield=someotherfield WHERE ...
> > > will have to have the JOIN info put into the WHERE clause using +
> > operators
> > > for outer joins etc.

> > > What would replace




> > > would it be

> > > DEFINE val=1
> > > select * from tab where sexval=&val
> > > UNDEFINE val

> > > or am I missing something?

> > > Thanks,

> > > Jon



Mon, 24 May 2004 02:50:25 GMT
 Oracle vs. MS-SQL dialects
Thanks Jim,

Packages I will definitely check out. Basically what I am doing is taking a
set of non-normalised flat files and trying to create a normalised database
out of them. I therefore have several routines that deal with different
parts of the "row" in the raw data that are all called by a main procedure
that handles the translation of one imported file. There are a few similar
main procedures that deal with slightly different source files.

Fortunately I have no triggers. I have a lot of temporary tables used to
celan up the data before import. Looks like these are
create global temporary table x instead of create table #x. I expect I can
still do stuff the same way. It's possible that ref cursors might be more
efficient in some cases than temporary tables, but this is a one time thing
(plus small monthly additions), so unless I start running into weird
problems I'll probably not worry about changing those.

Sequences I knew about but had temporarily forgotten.

I currently have things set up so that the simplest, nested procedures are
first in the file (for obvious reasons), so I'll probably start there.

Thanks again,

Jon


Quote:
> You are basically going from transact sql to pl/sql which is Pascalish.
> In triggers you do have the before and after values and don't have to
select
> them out of the table you just changed.
> In general in Oracle you should use packages to group related functions
and
> procedures togther.
> You would use ref cursors for returned result sets, and sequences for
> autonumber.

> If it were me I would try to group the procedures logically and then start
> rewriting them one group at a time.

> Jim



Mon, 24 May 2004 03:26:42 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. ORACLE vs INFORMIX vs MS SQL SERVER

2. Oracle vs Sybase vs MS SQL Server

3. MS SQL Server vs Oracle vs DB2 (&Sybase too)

4. HELP: Oracle 8i vs 9i vs MS SQL 2000

5. ORACLE vs INFORMIX vs MS SQL SERVER

6. MS SQL Server vs Oracle vs DB2 (&Sybase too)

7. Oracle vs Sybase vs MS SQL Server

8. ORACLE vs INFORMIX vs MS SQL SERVER

9. ORACLE vs INFORMIX vs MS SQL SERVER

10. ORACLE vs INFORMIX vs MS SQL SERVER

11. MS SQL Server vs Oracle vs DB2 (&Sybase too)

12. SYBASE vs ORACLE vs MS-SQL SERVER


 
Powered by phpBB® Forum Software