Ref Cursors

Question

Ref Cursors

ssc_san

SSCommitted

Points: 1573
More actions
June 24, 2011 at 9:16 am

#243270

Hi All,
I have a question regarding the ref cursors.
Why is it required to use a ref cursor to return a record set from Oracle Stored proc, and not required in Sql server stored proc?
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

ssc_san (6/24/2011)
Why is it required to use a ref cursor to return a record set from Oracle Stored proc, and not required in Sql server stored proc?

Because Oracle and SQL Server are different. Ref Cursor is the way Oracle does it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ssc_san SSCommitted Points: 1573 More actions · Answer 2

Thanks for the reply Paul.

Any specific reason, is it because of the difference in their architecture?

Thanks.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

ssc_san (6/24/2011)
Any specific reason, is it because of the difference in their architecture?

Exactly.

Oracle and SQL Server are two independent projects - both implement the features needed to comply with the same underlying Relational Theory but the closer to the surface of the product you get, the more different they are.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ssc_san SSCommitted Points: 1573 More actions · Answer 4

ssc_san

SSCommitted

Points: 1573

June 25, 2011 at 12:18 pm

#1344106

Thanks Paul.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

Good question. It has always seemed odd to me that SQL Server has two different ways of getting data out of procs - as output variables and as "result-sets". Now that procs support table-valued parameters (for input parameters) I expect that in future we will get support for returning tabular results that way as well. As far as I know the result-set style of output is a legacy of Sybase rather than a Microsoft-specific thing.

PaulB-TheOneAndOnly (6/25/2011)
both implement the features needed to comply with the same underlying Relational Theory

Now you are just being silly, Paul 😉

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

David Portas (6/29/2011)
PaulB-TheOneAndOnly (6/25/2011)
both implement the features needed to comply with the same underlying Relational Theory
Now you are just being silly, Paul 😉

Oh David, David... I know Englishmen are allowed by Royal Decree to say that kind of things therefore - no offense taken. Cheers to that 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 7

ssc_san (6/24/2011)
Thanks for the reply Paul.
Any specific reason, is it because of the difference in their architecture?
Thanks.

SQL Server stored procedures can return result sets directly. Oracle stored procedures can not. So to get a result set out of an Oracle stored procedure, you have to pass it as an output parameter, and the type of the parameter is REF CURSOR.

Every time I go back to Oracle, it's one of the things I miss: being able to return a result set from a stored procedure.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Bruce W Cassidy (7/6/2011)
ssc_san (6/24/2011)
Thanks for the reply Paul.
Any specific reason, is it because of the difference in their architecture?
Thanks.
SQL Server stored procedures can return result sets directly. Oracle stored procedures can not. So to get a result set out of an Oracle stored procedure, you have to pass it as an output parameter, and the type of the parameter is REF CURSOR.
Every time I go back to Oracle, it's one of the things I miss: being able to return a result set from a stored procedure.

Correct but, a REF CURSOR is not a parameter - it's a reference to a specific memory location.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

PaulB-TheOneAndOnly (7/8/2011)
Correct but, a REF CURSOR is not a parameter - it's a reference to a specific memory location.

Well, kinda. It's more a handle to a result set (in a specific memory location, admittedly), that you can pass around via parameters. Memory is just memory, it has no inherent type or structure.

Anyway, the point being that it's how you pass a result set back from a stored procedure in Oracle... via a ref cursor output parameter. Which means if you are working with the procedure in (say) Reporting Services, the two are functionally equivalent. But if you are working with it from the SQL level, you have to break into PL/SQL to be able to use the result of the procedure.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

Bruce W Cassidy (7/8/2011)
PaulB-TheOneAndOnly (7/8/2011)
Correct but, a REF CURSOR is not a parameter - it's a reference to a specific memory location.
... the point being that it's how you pass a result set back from a stored procedure in Oracle... via a ref cursor

Well... that was clear even to original poster 😀 that was never under discussion.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.