Re: PLSQL for MS Sql server devs?

An instance is Oracle running. An instance has a database. A database can have multiple schemas. (tablespaces, to add to the confusion, are logical storage and datafiles are physical storage with a datafile in a tablespace. A tablespace consists of one or more datafiles. A schema can have objects stored in one or more tablespaces. Schemas can even be in the same tablespace) A database even starts with mutiple schemas.

You connect to a database and then you reference an object by schema.object name. If however you are George logged in as George and are interested in George's tables then you do not have to specify the schema; it defaults to the George - bit it won't hurt.

You don't have to use temp tables in Oracle. The concurrency model in Oracle is much more robust and readers don't block other readers or writers. So a cursor is a very useful thing. Yes, you can return a cursor or a result set from a function (see documentation for ref cursors). Yes, in Oracle 8i there are global temporary tables but I have never had to use them.(nor felt the need).

Probably the best place to look is the online documentation the Application developer's guide. It is all in HTML and so pretty easy to navigate. Jim

.
"duanez" <duanez_at_hostrack.net> wrote in message news:3afd7be7.226851953_at_news.w-link.net...
> I am transitioning from MS Sql server's t-SQL to Oracle's PL SQL
> (incld Oracle Web). I'd like to find a book or faq that can help me
> with the different concepts, programming practices and concepts.
>
> For example, i get confused over what we mean by instance and schema.
> Is an Oracle Instance the equivalent to a MS Sql Server Database? or
> the Server itself? And when we spek of a schema in Oracle, do we mean
> that that is a database, which is a subset of the Instance? In MS SQL
> server we had the heirarchy go SERVER.DATABASE.OBJECT. In Oracle is it
>
> INSTANCE.SCHEMA.OBJECT?
>
> I installed Oracle 8i personal edition at home. The documentation
> refers sometimes to a 'database' but this seems interchangeable with
> the concept of an Instance. So can an Oracle Database Server have > 1
> instances each with > 1 schemas?
>
> other questions i have are more directly related to programming
> practices in PL SQL. In MS SQL server, we relied heavily on #temp
> tables whereas in PL SQL i noticed (from other people's code) there is
> great emphasis on cursors. How do you deal with no temp tables? Can a
> PL SQL function return a 'table' or a recordset datatype as a result?
> And then that rs be like table object and included in joins to other
> objects?
>
> If anyone has writen an FAQ or a Book with a title like "PL SQL for
> ex-MS SQL Server Developers" that'd be great. If not, i'd like help
> starting one.
> thanks
Received on Sat May 12 2001 - 15:13:24 CDT