October 2, 2018 at 7:34 am
Lynn Pettis - Monday, October 1, 2018 3:02 PMsgmunson - 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.
Yep, and I've found that with Oracle, abandoning existing methods is pretty common because the differences in ways to do things is rather stark in many ways. It is actually much easier to adapt to SQL Server than the other way around, and the other way around will frustrate the royal crap out of people that are heavy duty with SQL Server, because Oracle often prevents you from doing even rather simple things that are a piece of cake in SQL Server. Just having to SELECT things FROM DUAL is it's own special hell, from my perspective. That's in the category of some coder back in the day decided to be a jerk, an idiot, and a moron, all at the same time for months on end. And somehow convinced others that he/she had the right idea... Despite most simple queries running as is, Oracle is a very different beast under the covers. For most experienced SQL Server folks, Oracle is a sizable challenge at first. That's my 2 cents, and I'm stickin' to it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 2, 2018 at 11:09 am
sgmunson - Tuesday, October 2, 2018 7:34 AMLynn Pettis - Monday, October 1, 2018 3:02 PMsgmunson - 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.
Yep, and I've found that with Oracle, abandoning existing methods is pretty common because the differences in ways to do things is rather stark in many ways. It is actually much easier to adapt to SQL Server than the other way around, and the other way around will frustrate the royal crap out of people that are heavy duty with SQL Server, because Oracle often prevents you from doing even rather simple things that are a piece of cake in SQL Server. Just having to SELECT things FROM DUAL is it's own special hell, from my perspective. That's in the category of some coder back in the day decided to be a jerk, an idiot, and a moron, all at the same time for months on end. And somehow convinced others that he/she had the right idea... Despite most simple queries running as is, Oracle is a very different beast under the covers. For most experienced SQL Server folks, Oracle is a sizable challenge at first. That's my 2 cents, and I'm stickin' to it...
PL/SQL and T-SQL both have their strengths and weaknesses. Getting used to writing FROM DUAL was not a special hell, it was just learning the syntax for SQL in Oracle.
MS SQL Server now has its version of Oracles CREATE OR REPLACE for DDL statements like CREATE TABLE. That, for me, was a welcome addition. Oracle lets you us IN for a relation of values, something that was very useful on the project I was working. It is something I would like to see come to MS SQL Server as well. SQL Server doesn't have it at the moment so when something like that is need you have come up with an alternative solution.
You also have differences with other versions of SQL, like PostgreSQL. It doesn't have the TOP clause, so you have to use its version to achieve the same thing. Just a different dialect that requires some adjustment.
October 2, 2018 at 11:17 am
Yes, this is why I've often had to explain to my boss (who is most definitely not a SQL guy) that getting hung up on what the language standard says just isn't as relevant in the world of SQL. Sure it's nice if roughly the same concept works in multiple places, but ultimately each database engine is a very different beast and getting the most out of it requires accepting that you since you have made a platform choice, you optimize for the way it works.
October 2, 2018 at 11:27 am
sgmunson - Tuesday, October 2, 2018 7:34 AMLynn Pettis - Monday, October 1, 2018 3:02 PMsgmunson - 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.
Yep, and I've found that with Oracle, abandoning existing methods is pretty common because the differences in ways to do things is rather stark in many ways. It is actually much easier to adapt to SQL Server than the other way around, and the other way around will frustrate the royal crap out of people that are heavy duty with SQL Server, because Oracle often prevents you from doing even rather simple things that are a piece of cake in SQL Server. Just having to SELECT things FROM DUAL is it's own special hell, from my perspective. That's in the category of some coder back in the day decided to be a jerk, an idiot, and a moron, all at the same time for months on end. And somehow convinced others that he/she had the right idea... Despite most simple queries running as is, Oracle is a very different beast under the covers. For most experienced SQL Server folks, Oracle is a sizable challenge at first. That's my 2 cents, and I'm stickin' to it...
I would say the lack of any decent datetime functions in oracle is it's special hell....
October 2, 2018 at 11:44 am
ZZartin - Tuesday, October 2, 2018 11:27 AMsgmunson - Tuesday, October 2, 2018 7:34 AMLynn Pettis - Monday, October 1, 2018 3:02 PMsgmunson - 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.
Yep, and I've found that with Oracle, abandoning existing methods is pretty common because the differences in ways to do things is rather stark in many ways. It is actually much easier to adapt to SQL Server than the other way around, and the other way around will frustrate the royal crap out of people that are heavy duty with SQL Server, because Oracle often prevents you from doing even rather simple things that are a piece of cake in SQL Server. Just having to SELECT things FROM DUAL is it's own special hell, from my perspective. That's in the category of some coder back in the day decided to be a jerk, an idiot, and a moron, all at the same time for months on end. And somehow convinced others that he/she had the right idea... Despite most simple queries running as is, Oracle is a very different beast under the covers. For most experienced SQL Server folks, Oracle is a sizable challenge at first. That's my 2 cents, and I'm stickin' to it...
I would say the lack of any decent datetime functions in oracle is it's special hell....
But isn't SYSDATE + 1/24/60 an intuitive way to add a minute? 🙂
Sue
October 2, 2018 at 12:12 pm
Sue_H - Tuesday, October 2, 2018 11:44 AMBut isn't SYSDATE + 1/24/60 an intuitive way to add a minute? 🙂Sue
I don't know about Oracle, but 1/24/60 is 0 in T-SQL. I think you meant 1.0/24/60.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 2, 2018 at 12:27 pm
drew.allen - Tuesday, October 2, 2018 12:12 PMSue_H - Tuesday, October 2, 2018 11:44 AMBut isn't SYSDATE + 1/24/60 an intuitive way to add a minute? 🙂Sue
I don't know about Oracle, but 1/24/60 is 0 in T-SQL. I think you meant 1.0/24/60.
Drew
Sysdate isn't t-sql so I probably wasn't referring t-sql. Refer to:
Oracle date math manipulation
Sue
October 2, 2018 at 5:17 pm
andycadley - Tuesday, October 2, 2018 11:17 AMYes, this is why I've often had to explain to my boss (who is most definitely not a SQL guy) that getting hung up on what the language standard says just isn't as relevant in the world of SQL. Sure it's nice if roughly the same concept works in multiple places, but ultimately each database engine is a very different beast and getting the most out of it requires accepting that you since you have made a platform choice, you optimize for the way it works.
Fully agree.
June 8, 2022 at 11:19 pm
In the past I worked in operations in healthcare and we would get adhoc requests to add or update hundreds of records, sometimes thousands. Using OOP principles, I would create a dynamic sql script and a dbo of Oracle's record type (TABLE OF VARCHAR2(10) for instance, depending on field) and create a sort of a pseudo array. I could copy hundreds of values from a spreadsheet and use SQL Developer's replace to complete the list. What could take me tens of hours and thousands of lines of code became about 50 lines of code using a few variable declarations, cases/conditions and a loop, chopping it down to a couple hours. I never found anything on google doing it how I did it, but I did that sort of thing all the time (like writing a dynamic SQL query to query the data dictionary to make a script generator (SSMS has DROP/CREATE generators) which MAINTAINED order of definition which most think is impossible in SQL as group by usually mixes the order) as they didn't provide me with expensive software, not even toad. I hope this will spark some ideas for someone cuz it was much easier to work with than a cursor IMO and it seemed to perform very well while making my life much easier. The only alternatives I've found for SQL Server (including cursors) were MUCH MORE complex IMO. Short of C# / python... integration, any ideas about utilizing TSQL syntax to flex dynamic sql programmability?
June 9, 2022 at 2:42 pm
Two potential values of the Oracle-style ROWTYPE over an MSSQL user-defined table type:
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply