Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs

For the last several months, I have been working primarily with Oracle 8i and 10g databases.  Over the last 10 years, 95% of my work has been with MS SQL server.  The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.  You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.  Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

  1. Bind variables versus PL/SQL variables instead of just variables.  Oversimplifying, use “:” in place of “@” when you want a parameterized query.  This is the most awkward and annoying aspect of Oracle compared to MS SQL Server, IMO.  The bottom line is that you can’t very easily copy code from a stored procedure and run it in Toad or SQL Developer.  Nor can you directly assign values to bind variables — you get prompted for them.
  2. You can’t do a SELECT without an INTO inside of a PL/SQL block.  This is really puzzling.  Especially since you can’t have anything but bind variables outside of a PL/SQL block and you can’t programmatically assign values to bind variables.  All of this adds up to being forced to be prompted for bind variable values every time you run a parameterized query in the SQL Worksheet or in Toad.
  3. There’s no built-in Query Analyzer/SSMS Query tool.  There’s an extremely crippled but free version of Toad.  This what I use if I have to work with a pre-10g instance.  There’s Oracle SQL Developer which is what I use if I am working with a 10g instance.  They both are lacking compared to Query Analyzer/SSMS Query window.  You will press ctrl-E and ctrl-R repeatedly with no effect so many times it will make you sick.
  4. DATEADD doesn’t exist.  You have to add fractional days to dates instead.  DATEADD(hour, 1, D) would be D + 1/24.
  5. INT can’t be used for columns.  You use NUMBER instead.  This can be a real pain in ADO.NET because you have to explicitly convert all those NUMBERs to ints unless you want to treat them as decimals.
  6. No IDENTITY columns.  Instead of typing that one word you have to create a completely separate object called a SEQUENCE and you also have to write a trigger.
  7. You can’t have multiple databases on an Oracle instance.  There are just multiple schemas which are really owners.  Public synonyms are used to make objects from different schemas accessible without explicit schema specification. 
  8. The default date format does not include the time part.  You have to “alter session set NLS_DATE_FORMAT=’MM/DD/YYYY HH24:MI’;” to see the time part.
  9. You concatenate strings with the oh-so-intuitive || instead of the obscure + operation.  Alternatively you can use CONCAT.
  10. You can’t PRINT.  There’s a print-like statement, but it only works in SQL*PLUS.  You don’t ever really want to use SQL*PLUS unless you are a massochistic, command-line-loving freak from the past that still uses VI or EMACS.  Actually, there’s a web-based version of SQL*PLUS that’s usable but I like to stay in the SQL Worksheet.

Those are the top 10 — the things I really struggled to understand coming from the MS SQL side of things.  Here are some more that I found useful:

  • SP_TABLES = SELECT * FROM ALL_TABLES
  • sp_help = DESCRIBE
  • You can’t just SELECT without a FROM clause.  You always have to include FROM DUAL.  I still don’t know what “DUAL” is.
  • GETDATE() = SYSDATE
  • SUSER_SNAME() = USER
  • Getting just the date part of a date value: CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)) = trunc(SYSDATE)
  • Just use VARCHAR2 and don’t ask about VARCHAR.
  • You can’t return values from procedures using the RETURN statement.  Use an OUT param instead.
  • In version 8i and earlier, there’s a “rule-based” optimizer.  The rule-based optimizer can suck badly and you may need to force the join order on some of your queries.  You do that with “SELECT /*+ ordered */” (yes, it really is in the comment).
  • The Oracle compiler doesn’t do a good job of telling what’s wrong with your statement.  It can give obscure messages for missing commas and semi-colons.  Don’t forget that you must terminate each statement with a semi-colon.
  • There’s no built-in support for UUIDs.
  • Oracle does not distingish between an empty string and null (which really sucks)

Here are a couple of non-obvious things I learned about Oracle and .NET 2.0:

  • You can’t send multiple SQL statements in a batch using ADO.NET like you can with MSSQL Server
  • You can’t use the ODP.NET with the Enterprise Library
  • You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP
About these ads

59 Responses to “Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs”

  1. dgagnon Says:

    I understand your problem, but for me it’s the opposite. How can I assign a single value for a query into a variable. This was quite easy with Oracles 9i.

    DECLARE myVar VARCGAR32(32);
    BEGIN
    SELECT COL INTO myVar FROM TABLE WHERE COND = 1;
    DBMS_OUTPUT(‘Result : ‘ + myVar);
    END:

    The damn MS SQL Server 2005 can’t do something as basic as that. It insist on an error that says that myVar is not a valid type of cursor. Of course I know, it is a variable.

    I think Oracles is much more better on every aspect then Ms SQL Server 2005 …

    • Michael Bator Says:

      Declare @myVar int

      Select @myVar = someID from theTable

      Select @myVar
      Or use print if you just want text, totally handy for checking concatenated strings
      Print cast(@myVar as varchar)

  2. jopincar Says:

    Actually, it’s quite simple to do what you want in SQL server:

    DECLARE @myVar varchar(32)
    SELECT @myvar = COL FROM TABLE WHERE COND = 1

    If you think Oracle is much better on every aspect then I don’t think that you really have taken the time to learn how to use SQL Server 2005 to it’s fullest.

  3. Alastair Says:

    “You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP”

    Are you sure? You can copy the entry from TNSNAMES (in order to get the syntax and parentheses right) and use that as the Data source argument rather than the alias.

    The to_date() in your example “to_date(trunc(SYSDATE))” is redundant, just use trunc(sysdate). You can use trunc(sysdate,’MM’) to round to the start of the month and so on.

    PRINT – Is this the same as dbms_output.put_line?

    A lot of the variations are just a case of what you are used to. The NULL/Null string being a perennial favourite. There is a long running thread in the oracle forums about this one. They make the point that NULL can cover about 15 subtly different concepts and that different manufacturers collapse different subsets into this. In practice, I have never found that the lack of empty strings in Oracle causes a problem.

  4. jopincar Says:

    The last time I checked, I did exactly what you say (copy from tnsnames.ora) and it did not work. The same connection string with ODP did work. Perhaps they’ve fixed it. Have you tried it? How about with no tnsnames.ora at all?

    Yeah, the to_date is redundant. Trunc naturally returns a number in my mind.

    I can put PRINT absolutely anywhere in my T-SQL: Stored procedure or batch. Try opening a SQL Worksheet in oracle developer and executing dbms_output.put_line. It doesn’t work.

    Re null vs empty string not causing a problem. When you are writing non-SQL code, the forced nullability of strings is nothing but annoying. On sql server, we never allowed nullable string columns. We’d make them all not null default ”. Sure, you can make your life needlessly more complicated, but I haven’t heard a single good argument for having nullable strings. In code, I can compare empty string, test it’s length, etc. without having a special case to handle NULL or convert it something that won’t throw an exception.

  5. Jack Schneider Says:

    Great write up. I’m finding I have run into exactly the same problems with Oracle as you. God its painful after the luxury of SQL Server 2005! :)

  6. Trakal Says:

    Hello,
    I don’t write the comment for this article but i just want to know a function of SQL Server for Oracle.
    for exemple,
    CREATE PROCEDURE [dbo].[test]
    ( @username[nvarchar] (64) = NULL)
    AS
    IF@username IS NULL
    BEGIN
    SELECT DISTINCT @username= SYSTEM_USER
    END
    ….
    This is just a extrait of my procedure. It have a parameter @username and the procedure will verify if this parameter is null or not. If the parameter is null, the procedure will assign the name of the user who connect to the database.
    My question is that how can i do that with Oracle without using the select … into ….
    Thanks before hand
    and sorry for my poor english.
    Trakal

  7. gayathri Says:

    Hi , Can you tell me the MSSQL equivalent for DBMS OUTPUT.PUTLINE

    Thanks

  8. jopincar Says:

    Gayathri, I believe all you need is PRINT. Keep in mind that print only take one varchar argument, so if you needed to print a mix of types, you would need to assign them to a varchar variable first with convert before PRINTing them, EG:

    DECLARE @count int
    DECLARE @msg varchar(255)
    SELECT @count = 1
    SELECT @msg = “Count = ” + CONVERT(varchar, @Count)
    PRINT @msg

  9. gayathri Says:

    Hi,

    Thanks a ton for your response. Iam new to SQL server and since i have my deadline approaching i am trying to learn as create procedure VBU
    @lv_bunit varchar,@return_val INT output

    as
    begin
    select @return_val = 1 from PS_BUS_UNIT_TBL_AP where BUSINESS_UNIT = @lv_bunit;
    if @return_val = 1
    return 1;
    else
    return 0;
    end;


    Query for executing the proc:
    declare @return_val INT
    exec @return_val = VBU ‘AUS01′
    if @return_val = 1
    print ‘Business Unit is present';
    else
    print ‘Business Unit is not found';

    It will be really great if you could help me in this

    Thanks

  10. gayathri Says:

    oops there were few lines which were missed in teh above post. sorry abt that .
    Actually when i execute the above procedure it says the procedure is expecting a @return_val which was not supplied and then it prints “Business Unit not found along with that ‘
    Not sure what am i missing here .
    Please advice.

    Thanks

  11. Joshua Masek Says:

    I had trouble with connection strings to using microsoft’s oracle .net provider. I discovered it had something to do with the length. Either the length of the whole connection string or a part of it, i forget. (I think I had to shorten a long computer / domain name down to an ip address to get a valid length.)

    I wish Oracle would have left it up to the users to decide if they wanted null strings or not. If I want NULL to mean the value is undefined, and “” to mean it is defined as empty, that should be my choice. We write database independent code, and I its unfortunate that I can’t have an empty string returned from Oracle. This forces me to change my code when switching to Oracle, changing my code to check for nulls. Really though, I bet Oracle would have changed it by now except it would potentially break so much code that it wouldn’t be worth it, so we are stuck with it. Oracle can be frusterating, but I can’t complain, the extra hours we charge for programming against Oracle bring in more profit.

  12. sanjay Says:

    What is the difference between dbms.output.put_line() and
    dbms.output.new_line() ??

  13. Don Says:

    We use both Oracle 9i/10g and SQL Server 2000/2005 in our shop. I always know who’s working with Oracle by the grumbling!

  14. Dean Bullen Says:

    I think the truth is it just depends what you’re used to. I worked with Oracle for 8 years, just switched to SQL Server 2005, and I long to get Oracle back! But this blog raised some good points, and made me realise, it’s just what you’re used to.
    On “You can’t return values from procedures using the RETURN statement. Use an OUT param instead.”, in fact you would usually use a function, rather than a procedure.
    On the empty string debate, it drives me crazy that ” and null are 2 different things! Why would I want to make a column mandatory but default it to an empty string?!
    One other thing I really miss – tell me if I am wrong, but it seems like there’s no equivolent of packages – logical groupings of procedures (and functions)? Anyone recommend anything I could use instead to reduce the list of 200 procedures I have?
    Oh, and the lack of the “start with/connect by” clause in SQL Server! I’ve been in tears about it! Anyone got any tips on quickest way to produce that? (I’ve been doing stored procedures with temp tables and cursors…
    Also is it possible to call functions (I guess they’d be procedures) from in SQL with SQL Server? e.g. SELECT name, myProc(DOB) As age FROM myTable ?
    And the Cursor For Loop in Oracle is REALLY handy, rather than having to save @@FETCH_STATUS in a variable…
    I’m sure there are more things, these are just off the top of my head. I could really use someone writing a “SQL Server for Oracle Developers” book!
    Dean

  15. jopincar Says:

    Dean, there are functions in SQL server and you can call them from other functions or stored procedures. Take a look at the docs — it’s pretty straightforward.

  16. jopincar Says:

    Dean, re “Why would I want to make a column mandatory but default it to an empty string?!” That’s easy to answer. Because I don’t want to screw around with null vs empty string in my non-sql code (EG java or c#) and I don’t want to have to use IS NULL or IS NOT NULL or COALESCE in my sql code whenever I’m working with that column.

  17. Larry Bailey Says:

    Dean, regarding the absence of packages in SQL Server: I recommend a good naming convention. Depending on the culture (staff, prevailing languages) I’ve used a few different ones over the years, but it usually boils down to something like:

    p_entity_function

    so I end up with

    p_book_create
    p_book_get
    p_book_set
    p_publisher_create
    p_publisher_get

    and so on. I tend to prefer using delimiters rather than camelCase, the prefix-noun-verb structure, and prefixes of consistent length (simplifying string manipulation tricks), but the details are up to you and your team, and whatever best meets your needs. You like seeing all of the “create” functions together? then use the prefix-verb-noun order. Need more granularity? add an adjective to the equation: prefix-noun-adjective-verb.

    I’ve found that if I use consistent prefixes (like “p_” for procs, “v_” for views, “t_” for tables, and so on), consistent name structure (like prefix-noun-verb), and consistent delimiters, then I can play some pretty cool games with the system views. It becomes almost trivial to write stored procedures that can generate optimized db wrapper classes in Java, C++, VB, or whatever. It’s pretty handy during the initial development cycle to be able to change your schema and then regen your whole db access layer…

  18. Phil Singer Says:

    Nice site, and I especially like this post. You may be interested in knowing that I was looking for the answer to a SQL Server question, and Google returned this post on it’s first page. Unfortunately, the answer is not to be found — but you may know it. I’ve worked very little with SQL Server, some with DB2, and lot with Oracle. We have an app, and the question is whether or not it is even possible to migrate it to SQL Server.

    In a spirit of trying to be helpful, I wish to ask my question, then comment on a few of your points (will put them in a separate reply).

    My question: In the SQL Server 2000 docs, MS indicated that support for embedded SQL was going to be dropped in a future release. The subject is not mentioned at all in the SQL Server 2005 docs. Has it, in fact been dropped? Or merely obfuscated?

  19. Phil Singer Says:

    Now for the replies to your post (as promised):

    A) You have missed the biggest difference of all: how Oracle’s concurrency technique differs from SQL Servers’. I don’t have space to get into the details ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:27330770500351 is a good place to start), but I will list a couple of implications:

    A1) In Oracle, you want to commit as seldom as possible, certainly at the end of every “logical unit of work”, but you want to take as large a view of what the “logical unit of work” is as possible.

    A2) Conversely, committing too often will really slow your application down.

    A3) Which means, you don’t (or shouldn’t) want to write procedural code to do DML if it is possible to write SQL to do it. As an example, I once took a procedual program which was trying to update 45 columns in 34 tables, and not being too complicated, just looping through a FETCH/UPDATE/COMMIT on a row by row basis. Just replacing the (really simple) procedural code with straight UPDATES (and committing once, at the end) dropped the run time from 3 hours to 7 minutes.

    A4) Since updating a row will _not_ block other sessions from reading the row, if you really need to do so, you must jump though a few hoops to do it.

    B)

  20. Phil Singer Says:

    Another thing: I really don’t understand your gripes about PL/SQL and bind variables. And I really would, because I would like to know what the problem is. Should add that I am over on the other side of the coding world from you: lots of *nix development experience, fair amount of Perl and PHP, some interaction with ASP people, some J2EE, absolutely no .NET.

    But, looking at the MS SQL Server SQL Reference, it appears that T-SQL and MS SQL are almost one and the same. If this is true, then that could what is happening. Oracle’s SQL is very distinct from PL/SQL. Separate engines.

    One last comment: You had gripped about having to include an INTO clause with every SELECT in PL/SQL. This is not quite correct: (using the SCOTT/TIGER tables)

    begin
    for c1 in (select * from emp where deptno = 20)
    loop
    dbms_output.put_line(c1.ename||’ ‘||c1.job);
    end loop;
    end;
    /
    SQL> /
    SMITH CLERK
    JONES MANAGER
    SCOTT ANALYST
    ADAMS CLERK
    FORD ANALYST

    with not an INTO in sight.

    I do hope you know the answer to the support for embedded SQL in SQL Server 2005, and if want an Oracle viewpoint for any of your differences, I will be glad to provide one.

  21. jopincar Says:

    Phil,

    Regarding concurrency — SQL Server 2005 introduced snapshot isolation which makes your advice for Oracle equally applicable to SQL Server 2005. Contention between readers (without the NOLOCK hint) and writers most definitely can cause blocking problems in ealier versions of SQL Server. There are several techniques for addressing this but they would really deserve a post of their own.

  22. jopincar Says:

    Phil,

    What you say in A3 “you don’t (or shouldn’t) want to write procedural code to do DML if it is possible to write SQL to do it” is true in general and is not a difference between SQL server and Oracle, IMO.

  23. jopincar Says:

    Phil,

    Re support for Embedded SQL and DB-Libarary calls, the answer is spelled out in http://msdn2.microsoft.com/en-us/library/ms143729.aspx. Support is there in 2005 but it will be dropped in the next version.

    I’ve never used either and I’m not sure why you’d want to at this point. Doesn’t using Enbedded SQL and DB Lib contradict your point about transactions?

  24. jopincar Says:

    Phil,

    Last response :) Re bind variables, perhaps you’ve been using Oracle for so long you just accept it, but “Oracle’s SQL is very distinct from PL/SQL. Separate engines.” is exactly my gripe. As a consumer of the product, I don’t care if they are seperate engines, the language(s) should be consistent. They don’t have to be different, they just are.

    Here’s an example. In T-SQL, I can use these statements unaltered in either an ad hoc query or a stored procedure:

    DECLARE @SSN varchar(9)
    SELECT LastName, FirstName FROM Person WHERE SSN = @SSN

    In the Oracle world, I would have to have two very different statements to achieve the same thing in “plain old” SQL versus PL/SQL. On the SQL Server side, I frequently paste code directly for a stored procedure into a query analyzer window and vice versa. In Orace, you can’t do that due to the select into and bind variable issues

  25. Phil Singer Says:

    John, thanks for the info about embedded SQL in SQL Server 2005. As to why the question even comes up …

    We have one of those dreaded 3rd party apps., actually, a 3rd party conversion of an obsolete O/S. The 3rd party chose to emulate the obsolete database (a hierarchical database) at the lowest level possible: in ‘c’.

    I think there actually is a place for embedded sql, and the place is where subroutines (at this level, they can’t really be called ‘methods’) must run as quickly as possible. And the transaction control should be placed in the main program, meaning, never commit in a subroutine (or a stored procedure, if there is any chance of calling it from some other place).

  26. Phil Singer Says:

    Regarding your last response (and I do appreciate your willingness to discuss this; most SQL Server/Oracle discussions seem to quickly devolve in a brutal flame war), I don’t want to get into a discussion of IDE’s. partly because even my co-workers regard me as a hopeless case (although I am willing to use SQL Developer and Kate on occasion, when they are clearly superior to vi). However, I do have a very serious question for you: in SQL Server, is

    SELECT LastName, FirstName FROM Person WHERE SSN = @SSN

    different from

    SELECT LastName, FirstName FROM Person WHERE SSN = ‘123456789’ ?

    (where 123456789 is the ssn in question)?

    Because, in Oracle

    SELECT LastName, FirstName FROM Person WHERE SSN = :SSN

    is very different from

    SELECT LastName, FirstName FROM Person WHERE SSN = ‘123456789’

  27. jopincar Says:

    Yes, one will have a parameterized query plan and the other will not.

  28. Michael Collins Says:

    I am kind of a newb to procedural sql stuff, and I am really an analyst, not so much a dba, but I learned plsql to write a lengthy market basket program, but now I am in the process of converting that code over to tsql as I know have my own sql server to play with. I will say that tsql is simplier than plsql but, and this is maybe because I am very new to tsql, but maybe you could address some of these issues like,
    When I had originally imagined procedural sql I thought how cool it would be to have a table variable, and there is not really one in plsql, although I did find out that you can essentially make a table of records, but in tsql, that is all there is. I s it too much to ask, for some array / list data type that can be reffered to with an index value? Is there some work around to this, ie is it hard to make a user defined one in tsql? I have read some stuff about clr / .net assemblies, and this I refuse to do, so something more simple like in plsql. I did one section of my program with tables instead of plsql tables and I felt like I had to be more “clever” with writing out a query that could be written out, although ugly, more quickly with if, for, and array indexes. I say ugly, but sometimes i like for count in 1..var.count rather than select whositz form whatnots where something in (sub….
    Also, I would be interested to know the performance difference between plsql and tsql, because it seems like, (now im not a dba / computer science person), but not having supper specificities like varray vs tables and only having while and not for (which it seems like the compiler could not pre-allocate resources for a loop) would hamper things down. Is this about right?
    Last thing.. Is there a way to redefine scope within a procedure? Like, i have a plsql package, and there is no question between scope, but if I have a loop inside of a procedure, I have to explicitly delete the table variable, and even then an identity column will not reset! This is all in lieu of having global and local variables in packages and doing the whole <> thing.
    I should mention that sql server is infinitely more attractive to me because of analysis services (and all of these problems seem very trivial while I am learning MDX), but I would really like to work out some of these issues–Thanks for your help

  29. jopincar Says:

    Michael,

    Honestly, if you want to code solely using the server’s native language (t-sql or pl/sql) and you refuse to use CLR stored procedures, then you should go back to Oracle.

    I would argue that limiting yourself to coding stricly on the server using pl/sql or t-sql is a serious mistake.

    That being said, I don’t have experence with CLR stored procs or the advanced procedure programming aspects of pl/sql so I really can’t give you good answers.

    I agree wholeheartedly that a native composite type would be wonderful in t-sql, particularly for passing parameters. I’ve been forced to use temp tables and delimited strings in the past to work around this. But I think that MS has (not unwisely) taken then approach that if you want to do procedural things, use CLR or do them off the server.

    As far as scoping, I don’t believe you have any control over the scope of variable in t-sql.

    Good luck with your conversion. I really would give CLR stored procs another look.

  30. Michael Collins Says:

    Yeah, I am intermediate at Python, so maybe I could transfer some of that knowledge and pick up some vb / clr. By the way, the thing about the identity column not reseting in a table variable is straight up broken–I wrestled with that for like 2 hours today. I hope they fix that sometime in the near future–

  31. jopincar Says:

    BTW — it really sucks not being able to edit your comments. All those spelling mistakes!

  32. Bill Holmes Says:

    DUAL is a table owned by SYS:
    OWNER TABLE_NAME
    —————————— ——————————
    SYS DUAL

    Here is its structure:

    Name Null? Type
    —————————————– ——– —————————-
    DUMMY VARCHAR2(1)

    Nere are the contents:

    D

    X

  33. Bill Holmes Says:

    Oracle’s RULE-based optimizer exists in Oracle 9i and earlier. In Oracle 10g and later it is deprecated and no longer supported.

  34. Bill Holmes Says:

    An overly simple description of Oracle in the Unix world: an Oracle instance is a shared memory segment plus a collection of separate background (and server) processes that access it. An Oracle database is a collection of files, e.g. database files, redo log files, control files, etc. mounted and opened by one or more Oracle instances.

    On Windows oracle.exe (which is a collection of background processes, etc.) is implemented as a collection of threads.

  35. Bill Holmes Says:

    Only PL/SQL functions return values to callers with the RETURN statement.

  36. jopincar Says:

    Bill, re DUAL, I was asking why they called it DUAL.

  37. Patrick McShea Says:

    http://en.wikipedia.org/wiki/DUAL_table

    “The DUAL table was created by Chuck Weiss of Oracle corporation to provide a table for joining in internal views:

    I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one. [1]

    It may not be obvious from the above, but the original DUAL table had two rows in it (hence its name). Nowadays it only has one row.

    The single column in the DUAL table is named “DUMMY” and has a value of “X”.”

  38. jopincar Says:

    Thanks Patrick. That’s a nice bit of trivia.

  39. Bill Holmes Says:

    Thanks for the follow-up on DUAL.

  40. Alvaro Lozada Says:

    Hey SSMS developers, please do not be martyrs, for one single time… be honest. Oracle has many years of experience and is the number one in both relational and datawarehouse market, “the experience is not improvised” or like we use to say in spanish “La experiencia no se improvisa”. Let’s see can t-sql surpass PL/SQL? Does T-sql manage packages?, Does T-sql manage even a simple function? Does T-sql manage overloaded operators or methods? (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=820622&SiteID=17) …no more on this. Sorry man… T-sql could be more simple… but PL/SQL is more powerfull… is the same story when you need to choose between coding a program in VB or in C. In the other hand… SQL from Oracle (different from PL/SQL) has a lot of stuff that prevents you from reinventing the wheel; come on… How many of you boys wants to make a coil a dial a wood box to do a phone when you can get in the shop for a few dollars… Does MSSQL support Analytic Functions?(http://www.akadia.com/services/ora_analytic_functions.html – ) and 11g does better!! Does support Flashback Technology? well… (sighs) MSSQL is not a bad product… may be the best choice if you are triying to standarize to MS plataform… and if it fits your needs (i mean need$), but do not try to be David knocking out Golliat… he was held by Jeovah … I hope you too… but in your life. Final comment Jhon good blog… i’think you need to work more whit Oracle

  41. jopincar Says:

    Alvaro, Oracle costs alot more than SQL Server so I’m not getting your “need$” comment.

    The key for me is that I’m not interested in coding on my database platform. I want my database to be a database.

    Even so, SQL Server has CLR integration now. It’s had functions since 7.0 which came out (in 98 I think). So you’re a little behind the times.

    • Observer Says:

      Then go forth, use MS SQL and be happy, be very happy.
      – It will cost you much less
      – You can use it as a persistent storage, and use something like Java to code all your logic
      – you can use the database language as a query scripting language
      and that will make you happy.

      But stop trying to turn Oracle into MSSQL.

      PL/SQL is s full and powerful language to write serious database based applications and in this, it really is excellent !

  42. Alvaro Lozada Says:

    Hello Jhon… first i apologize if my comments are a little strong … i don’t want to harm anybody. When i say need$ i meant that every customer has its own budget and of course it is adjusted to the IT needs the customer proposes to satisfy and the goals it want to achieve. Then the budget is a limitation for SMB’s… that a main reason for they to choose MS SQLserver. Oracle is taking strategies for being more attractive for SMB’s (take a look to http://www.oracle.com/global/in/pressroom/OracleGainsOver300NewSMB.html) but it is not the main target of Database and Datawarehouse product, VLDB’s customers have very high need$$$$ and then huge budget … then we have the hole picture (Note that i put more dollar signs then i mean more expensive needs in that customers).

    I do not want to do any comment about where is the appropiate place to code… there are many different opinions involving portability, escalability, performance and so on, very long story. Again a matter of choice … but a reflection must be done again: It is true that “the more you code” … the “better profesional” you are? (see a sample in http://blogs.msdn.com/sqltips/archive/2006/05/15/598372.aspx). There Oracle has a huge advance… they can offer more built-in functions, thus developers have to do less and in less time (ROI and TCO involved here?)

    About your final comment… well i do know about CLR integration but my comment was addresing to “T-SQL vs. PL/SQL” comparison. CLR is not T-SQL… please correct me if i am wrong.

  43. sobia Says:

    what is the replace ment of SP_Colum in Oracle

  44. Miguel Pereira Says:

    Just a couple of comments from a guys that has about almost two decades working with MSSQL products, a few on mySQL and a few months on Oracle 10g/11g.

    1. Please, please please…taunting a nice web based interface is nice and everything, but give us, DBAs a solid native GUI (Windows & KDE as well…)

    2. If I can get away with writing 2 lines of code to achieve a result, why would I want to do so in 4 lines instead? (tsql vs pl/sql). Somebody wants to prove me wrong: I’ll gladly hand over about 20 or so SQL stored procedures to be migrated. At the end of the day we can compare # of statements.

    3. How in heaven’s can I see an equivalent of an execution plan in Oracle? How can I tell what the engine itself is doing with my queries?

    4. C’mon! No auto-increment (identity) attribute? That’s the bread and butter of 99% of the work we do on RDBMS’

    Bottom line: in my personal opinion, MSSQL is friendlier and the learning curve from ‘nada’ to ‘i-am-pretty-good-at-it’ favors MS’ products.

    From a support DBA standpoint, I wish MS would take a look at ASM & RAC from Oracle. Very handy and definitely makes for a cheaper path to clustering vs MS Clustering -bloated, expensive and finnicky.

    Cheers!

    • Observer Says:

      If you are going to work in Oracle, work in Toad, anything else gives a skewed perception of Oracle!

      Interesting, we reduced some serious database applications by 37% (in size) converting it from T-SQL to PL/SQL. So .. ?

      Your ignorance of data modelling is scary. If your tables are logically related because they are subsets of a superset – in other words, they inherit from the same common entity, they need to take their identities from the same number doman (in oracle : sequences). So pay more attention to proper data modelling theory!

      • jopincar Says:

        Observer, the pain of the sequence is not offset by that very unusual circumstance, which frankly, can be handled other ways just as well.

  45. Dynamic Web Dev Says:

    John,

    Thank you tremendously for this blog. This is exactly the information I need to know.

    I’ve worked both in MSSQL and Oracle as back-ends to web applications. I’m much stronger on the SQL side than Oracle (10+ years with SQL vs. 2 with Oracle) and I’ve never created a database in Oracle, just manipulated the data…

    I’m working on a web project that was originally going to be developed using SQL and .NET. At the last minute my requirements changed to Oracle and ColdFusion, throwing me for quite a loop. Mind you I am the Business Analyst, DBA, Designer, and Programmer of this website…

    Oracle is baffling to me. It would be wonderful if I could script the entire database with SQL2005 and copy that code right into Oracle, but alas…

    I cannot…

    :(

    And why, oh why no simple IDE like beautiful SQL 2005?

    So, thank you again for pointing out the equivalents. It will really help me out!

    :)

    Create a Great Day!

  46. Steve Jones Says:

    It’s always nice to see an Oracle PL/SQL vs SQL Server T-SQL debate. Coming from an Oracle background, it’s gotta be PL/SQL for me.

    Anyway, I would like T-SQL to have anchoring so you can anchor a variable datatype back to a table column datatype. In Oracle, myVariable myTable.myColumn%type;

    It would also be nice if you could have %ROWTYPE for fetching rows of data into from SQL instead of having to declare all the variables.

    As said earlier, T-SQL needs packages so we can group Stored Procedures and Functions in a logical manner. Yes, I know you should have a nice naming convention but it’s just better to put them together.

    The other thing everyone says don’t use Cursors in SQL Server as they are so slow, but for me they run prettty darn fast and never had a problem. If they are that bad, Microsoft should fix them to make faster as lots of us developer like them.

    Ultimately, they are both pretty good, do a job and we will all have our favourites but PL/SQL is just beautiful to me, I would write it (and do) for fun!!

    Steve
    PS SQL Server Profiler is very, very good though (and a life saver).

  47. Rafi Says:

    Is it possible to substitute ” ” (a single space) instead of NULL or empty string? If so, a project could standardize on that meaning “empty string” and then all string comparisons would work without requiring the NULL comparisons, right? Maybe it’s kludgy, but it could work.

  48. Roy Says:

    Sorry guys, but I’m coming the other way (oracle to MSSQL) and I’m getting very tired of all the extra syntax and not short cuts for things like joins. So it maybe about what you already know. My previous life had Progress, Sybase, informix

  49. nitin ramani Says:

    my onother question is we use any alogorithem in database for automated th database and please help me how i automated my admin in website …?????
    like compare,insert,delete and all that

  50. svdba Says:

    For Oracle DBA who miss SQL*Plus:

    There is a free tool “SQLS*Plus” (on http://www.memfix.com ) which is like SQL*Plus for SQL Server. It works with SQL Server 2000, 2005 and 2008. Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, HTML output, etc – ways better than isql, osql or sqlcmd

  51. Lakshmi Says:

    Hi Team,

    Please do let me know if we have any tool which converts PL SQL procedure code to MS SQL procedure code.

    I have 1800 lines of PLSQL code which needs to be migrated to MS SQL.Please suggest me the best ways.

    Thanks

    Lakshmi

    ——————————————————————————–

  52. EMB Says:

    Wow what an interesting debate that happened a few years ago. I went from Oracle to TSQL and its fairly simple, although I do miss Oracle quite a bit with the packages, Prompt that lets a developer input the data, and especially the Decode function..

  53. Mike Says:

    I have done a little admin work with MS SQL for a Sr. guy in the past. So I decided to go back to school and learn more. But my instructor kind of through me for a loop because he wants each person in the class to decide if they are going to do the class in T SQL or PL SQL. So I have a few questions for those of you that have used both:

    1. Which one is easier to learn
    2. What do you find is more used in the work force today
    3. Do you think knowing T SQL and then learning PL SQL is easier/better
    4. Do you think knowing PL SQL and then learning T SQL is easier/better

    I am planning on doing straight contract work when I am finished with school. I have already taken C, Java, HTML/CSS and will be starting VB.net this semester and then moving over to C# or C++ next semester. I also have experience in both Linux, UNIX, and MS Servers. Any help would be appreciated.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: