September 27, 2018 at 11:38 pm
It seems that SQL*Server does not have equivalent of Oracle %ROWTYPE ?
Unless I am looking at old answers on the web ?
Is this still the case ?
This was handy in Oracle as often a column data type change (e.g making a column wider) would then only then require a package recompile rather than a package change.
September 28, 2018 at 12:17 am
No it doesn't. Possibly because cursors are relatively lightweight objects in Oracle whereas they're very expensive in SQL Server,
September 28, 2018 at 9:34 am
Perhaps more importantly, what would you do with the information if you could actually get it?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2018 at 9:45 am
sgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?
It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
September 28, 2018 at 9:51 am
Lynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
For a quick example of how it, %rowtype, and a column version ,%type, could be used: http://psoug.org/snippet/Oracle-PL-SQL-TYPE-vs-ROWTYPE--Whats-the-difference_700.htm.
September 28, 2018 at 9:52 am
Lynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2018 at 9:58 am
sgmunson - Friday, September 28, 2018 9:52 AMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
You are missing the point, these constructs don't require using dynamic SQL. You can use them to define a a row based on what the row in the table is defined. If you change any of the data types in that table all that is needed in the procedure(s) where %ROWTYPE is used for that table is to recompile it. No code changes are needed. The same using %TYPE when declaring parameters in a procedure or function. This is one of things I miss from working with Oracle for a year.
September 28, 2018 at 10:13 am
Lynn Pettis - Friday, September 28, 2018 9:58 AMsgmunson - Friday, September 28, 2018 9:52 AMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
You are missing the point, these constructs don't require using dynamic SQL. You can use them to define a a row based on what the row in the table is defined. If you change any of the data types in that table all that is needed in the procedure(s) where %ROWTYPE is used for that table is to recompile it. No code changes are needed. The same using %TYPE when declaring parameters in a procedure or function. This is one of things I miss from working with Oracle for a year.
Maybe I'm reading it wrong but that seems like more of a QOL improvement to save typing? I mean if the data type or table structure changes and whatever is using it relies something specific about that it still requires a code change.
September 28, 2018 at 10:30 am
ZZartin - Friday, September 28, 2018 10:13 AMLynn Pettis - Friday, September 28, 2018 9:58 AMsgmunson - Friday, September 28, 2018 9:52 AMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
You are missing the point, these constructs don't require using dynamic SQL. You can use them to define a a row based on what the row in the table is defined. If you change any of the data types in that table all that is needed in the procedure(s) where %ROWTYPE is used for that table is to recompile it. No code changes are needed. The same using %TYPE when declaring parameters in a procedure or function. This is one of things I miss from working with Oracle for a year.
Maybe I'm reading it wrong but that seems like more of a QOL improvement to save typing? I mean if the data type or table structure changes and whatever is using it relies something specific about that it still requires a code change.
Depends on the type of change being done. If it is a major change of data type, character to integer for instance, yes, there could very well be code changes required. But you just have to work the code changes, not change any of the declarations as that is already handled. I found it a handy feature where used appropriately.
September 28, 2018 at 12:09 pm
Lynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
Maybe I need some sleep, but this sounds a lot like a user-defined table type
September 28, 2018 at 12:47 pm
DesNorton - Friday, September 28, 2018 12:09 PMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
Maybe I need some sleep, but this sounds a lot like a user-defined table type
No, it isn't a table type.
October 1, 2018 at 8:01 am
Lynn Pettis - Friday, September 28, 2018 9:58 AMsgmunson - Friday, September 28, 2018 9:52 AMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
You are missing the point, these constructs don't require using dynamic SQL. You can use them to define a a row based on what the row in the table is defined. If you change any of the data types in that table all that is needed in the procedure(s) where %ROWTYPE is used for that table is to recompile it. No code changes are needed. The same using %TYPE when declaring parameters in a procedure or function. This is one of things I miss from working with Oracle for a year.
Well, I wasn't looking at this from the Oracle point of view. If one is asking if SQL Server has an equivalent, then the MOST important question I'm going to ask, is WHY do they want to know, and what would they do if they had the information. People that are potential newbies to SQL Server are likely to be in a very similar boat to people new to Oracle, and might not realize that the same query that runs great in Oracle might not run well at all in SQL Server in any of a number of cases, for what could easily be as many different reasons (or more) as there are cases. Once you get to the real reason for the inquiry, you can generally provide better guidance on what choice that person makes. If one has to work with SQL Server instead of Oracle, then one cannot afford to take an Oracle point of view. One must, however painfully, find a SQL Server solution, and until one has the right mindset for a SQL server solution, a good one might well elude you, simply because one is not familiar enough with SQL Server to know how to accomplish their task quickly, easily, or in a performant manner.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 1, 2018 at 9:32 am
Wow!! this kinda got ugly. Lynn was trying to explain how %ROWTYPE% works. I think the OP is used to using Oracle and was just asking if there was a SQL equivalent. So, in Oracle, instead of declaring 25 different variables, he could use %RowType and he could use the 25 variables by using RowType. (I forget syntax, but that's the gist). In SQL, he will need to declare all 25 variables.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 1, 2018 at 9:48 am
Well the SQL server equivalent is probably to use a Custom Table Type and select a single row into it. It's not exactly equivalent but it's a close approximation.
Now whether that is the best solution to the specific problem or not is a whole different thing. As I say, cursors are a lot more light weight in Oracle and it's a common place to see this kind of requirement. The solution in SQL Server might well be a totally different process than the approach you'd take in Oracle.
October 1, 2018 at 3:02 pm
sgmunson - Monday, October 1, 2018 8:01 AMLynn Pettis - Friday, September 28, 2018 9:58 AMsgmunson - Friday, September 28, 2018 9:52 AMLynn Pettis - Friday, September 28, 2018 9:45 AMsgmunson - Friday, September 28, 2018 9:34 AMPerhaps more importantly, what would you do with the information if you could actually get it?It actually is a neat concept as it allows you to define variables that correspond to the data types of the columns the variables will hold data from. I thought it a good way to keep data types the same while writing Oracle procedures for manipulating data passed from a web-based application to the Oracle database. Made maintenance of the procedures easier.
As the OP didn't say WHY they were asking, I couldn't be sure what the usage would be. However, it's not that challenging to build some dynamic SQL that gets created based on sys.tables and sys.columns, or for that matter, even the INFORMATION_SCHEMA equivalents that could be pre-pended ahead of whatever other SQL you needed. Also, there's always SELECT * INTO #TestTempTable FROM YourTable WHERE 1 = 0; ... which can prep a temp table to hold derived values.
You are missing the point, these constructs don't require using dynamic SQL. You can use them to define a a row based on what the row in the table is defined. If you change any of the data types in that table all that is needed in the procedure(s) where %ROWTYPE is used for that table is to recompile it. No code changes are needed. The same using %TYPE when declaring parameters in a procedure or function. This is one of things I miss from working with Oracle for a year.
Well, I wasn't looking at this from the Oracle point of view. If one is asking if SQL Server has an equivalent, then the MOST important question I'm going to ask, is WHY do they want to know, and what would they do if they had the information. People that are potential newbies to SQL Server are likely to be in a very similar boat to people new to Oracle, and might not realize that the same query that runs great in Oracle might not run well at all in SQL Server in any of a number of cases, for what could easily be as many different reasons (or more) as there are cases. Once you get to the real reason for the inquiry, you can generally provide better guidance on what choice that person makes. If one has to work with SQL Server instead of Oracle, then one cannot afford to take an Oracle point of view. One must, however painfully, find a SQL Server solution, and until one has the right mindset for a SQL server solution, a good one might well elude you, simply because one is not familiar enough with SQL Server to know how to accomplish their task quickly, easily, or in a performant manner.
They are trying to do things in a manner that they are familiar. The same way I did the Oracle development while working for Honeywell. I wrote what I needed in T-SQL then figured out how to port it to Oracle and PL/SQL. Some things translated easily, some needed additional work.
Sometimes it works well, other times you have to abandon what you are used to and learn something new.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply