Developers that are familiar with using MS SQL Server eventually get the opportunity to work on a project that is Oracle based. This opportunity sometimes becomes an extremely frustrating time due to the philosophical differences in the approach to database development. I believe it is hard for most developers to transition between the two database platforms, not because one is necessarily better or worse, just because it is too hard for many people to change the way they solve problems once proven methodologies have been established.

These are a few questions that are just about guaranteed to come up during the initial stages of the project:

How do I get a record set from Oracle using a stored procedure?

Using a REF_CURSOR you can return a record set/cursor from a stored procedure.

Why don’t stored procedure work the same as in SQL Server?

In most cases you just use straight SQL and do not need a stored procedure. In Oracle, using cursors and adhoc SQL is the way. In SQL Server, using cursors and adhoc SQL is to be avoided (generally speaking, there are no absolutes).  Why you need to use a stored procedure for a SQL statement in Oracle must be fully explored. I am not saying you don’t need one, but the reason is not because that is the way you do it in SQL Server.

Please do not even start coding until you have read the following documents:

• Oracle Concepts
• 2 Day Developer’s Guide