View Single Post
  #2 (permalink)  
Old June 7th, 2006, 05:15 PM
shogre shogre is offline
Registered User
 
Join Date: Jun 2006
Location: Rouen, France, France.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I used Sql Server for a few month, and i can understand your intention.
Transac SQL and PL/SQL have a major difference about the execution context. if a transac stored proc could be considered like a "group of sql orders", a pl one should be seen similar to a java external function that is just "inside" the database.
You can notice that when trying for example to execute a ddl statement. In pl you should use "execute immediate" function to "submit" it to the sql engine whereas in transac you can even ask for a restoration by simpling writing the order.

I'm probably not very easy to understand, but i mean you shouldn't think the same way you did with msql.

A pl stored proc (or function) was not natively made to return a resulset. It is now possible, but i am not very "up to date" with pl/sql features. You should look after returning "ref cursor" or "pl table" and return casting with table() function. ( I hope a pl/sql expert will answer you and will be more accurate than i am)

In Oracle, the views are really powerful. They are very used, and you should use a stored proc only when you want to "totally" create a resulset or apply an heavy process to data before getting them.
these occasions should be rare in my opinion....

Hope this help
-----------------------------------
Shogre
Oracle DBA / Apave NO France