DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: difrence between oracle stored procedures and sqlserver stored procedure

  1. #1
    Join Date
    Apr 2001
    Posts
    46

    Exclamation

    Hello DBA gurus,
    very urgent......................
    what are the differences between sqlserver stored procedures and oracle stored procedures?

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    The main difference between Oracle stored procedures and MSSQL Server stored procedures is the use of PL/SQL in oracle stored scripts and special data types and environment variables.

    PL/SQL is an extension of the standard SQL (92) of ANSI, It can use packages to group procedures and functions together.
    You can read about it at:
    http://otn.oracle.com/docs/products/...a96624/toc.htm

    Special data types include LOB, REF Cursor etc.

    In MSSQL there are other implementations (taken from sybase)
    such as date manipulation (datepart function) etc.

    cheers,
    r.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    rotem_fo gave some good examples, but it even confused me, so I'll try to clarify.

    At the highest level, they are exactly the same. They are both used to store procedural code in the database.

    SQLServer uses T-SQL as its procedural language and Oracle Uses PL/SQL as its procedural language.

    The languages themselves have tons of differences. Now, I haven't written in T-SQL for about 5 years, and there have been several new releases since then, but at that time there were significant differences between the languages, and I imagine most of them still exist:
    - No structured error-handling in T-SQL
    - Much, much better temp table capabilities in T-SQL
    - yada yada yada


    However, you asked about the stored procedures, and not the languages themselves.

    At that level the biggest differences are:

    - In Oracle, you have packages. Packages allow you to group procedures into larger logical units. More than that, packages provide package-level variables and data-types. They have been called the 'poor-man's objects', and it's pretty true.
    - In SQLServer, a procedure can return a record-set almost like a function. In Oracle, the methodology used to be really abysmal - the multiple table-type params. Now you can use Ref Cursors, which are much, much better. However, SQLServer has had a cleaner interface since back when it was Sybase . You simply do a SELECT in the code and the result set (or as many result sets as there are SELECTs in the proc) is returned to the calling program.

    I'm sure there are more, but that's all that occurs to me now.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width