Last post Oct 20, 2012 08:34 PM by Lannie
Oct 19, 2012 11:20 PM|antonyliu2002|LINK
I am new to Oracle, but not new to SQL Server and .NET application development. I think it is a general understanding that we should try to avoid using cursors as much as possible at least with sql server, because it just doesn't perform that well.
But if we read any Oracle PL/SQL primer, I bet you will be reading about cursors after cursors.
So, is cursor simply the established practice of Oracle to get our data? Is oracle cursor internally different from that of SQL Server such that it doesn't cause any performance hit?
Any thoughts? Thank you.
Oct 20, 2012 03:12 AM|ggaurav|LINK
Cursor is a pointer to records in database. Records are retrieved into memory at the time the cursor is opened. In SQL server, we normally try to avoid using cursor because slow performance. But cursor in Oracle will play more important roles, even
has to be used sometimes. For example, When you want to return a record set through a stored procedure, the record set has to be put into a SYS_REFCUSOR type cursor as a output parameter in the stored procedure.
Mark answer if you feel it is appropiate.
Oct 20, 2012 10:54 AM|antonyliu2002|LINK
I've read a good informative debate here: http://sqlblog.com/blogs/adam_machanic/archive/2007/10/13/cursors-run-just-fine.aspx
It seems that oracle's cursors are different from the T-SQL cursor, such that it
may not have a performance hit as bad as that of T-SQL. But, the debate there still gives me an impression that a set based approach is still preferred over cursors if possible even with Oracle.
Are there any Oracle / SQL Server gurus here? How different are Oracle cursors and SQL Server cursors? Does the Oracle company encourage developers to use cursors since their documentation or tutorials talk a lot about cursors, whereas SQL Server documentations
don't. Why? Why? Why?
Oct 20, 2012 08:34 PM|Lannie|LINK
I use Oracle everyday.
I use ODP.NET everyday.
I use SELECT on TABLEs and SELECT on VIEWs in code behind form,
and stored procedures with REFCURSORs.
I do not see any difference in performance between the three.
The main difference is the preference of the coding team where they want the SQL statements to reside.
Oracle meets more of my needs than MSSQL, especially in the area of scientific analytics and advanced queries.