Post reply

Stored Procedure to run on SQL Server and Oracle

  • July 24, 2009 at 4:01 am

    #97862

    We have a requirement to write a stored procedure which works both on SQL Server and Oracle with out any modifications. not maintaining different copies of the Stored procedures

    Stored procedures contains all DML Queries, String and date functions, temp tables, calls to other stored procedure / functions, exception / error flows [on error goto statements, raise error statements etc].

    I heard that native SQL can be used but its a slows down the performance drastically.

    Any pointers on this will really helps.

    Thanks in advance

    Raju P. S

  • July 24, 2009 at 6:07 am

    #1029584

    If you use standard SQL you have a chance to make it, but I don't think it's neither easy nor fast.

    Try explaining your needs deeper, maybe we can suggest something more specific.

    Regards

    Gianluca

    -- Gianluca Sartori

  • July 25, 2009 at 4:58 am

    #1030132

    i currently have to support both SQL and Oracle at the shop that I work.

    because even little things like naming conventions for parameters(SQL requires parameters to start with @, Oracle does not allow it), and built in things like ISNULL(SQL) vs NVL(Oracle's equivalent)

    the more advanced stuff you identified, like conversions,#temp tables (use CTE instead) error handling, returning recordsets are all radically different.

    every one of your procedures, functions, etc will be different. I cannot think of a single procedure I have that are identical, except for the DML part of the statements if there are no parameters.

    i know it's someones wish list, but I'm absolutely sure it is not possible to have Identical code for your procs.

    even you table definitions will be different, because the datatypes are not named the same, and an identity() column in SQL requires a sequence and a trigger in order to duplicate the functionality.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 29, 2009 at 3:43 pm

    #1032049

    This is a bad plan and is doomed to failure.

    Instead, you should use your stored procedures to hide the differences between SQL Server and Oracle from all of the higher layers of your app. That has a reasonable chance of sucess.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 30, 2009 at 6:54 am

    #1032351

    I agree with Lowell and RBerryYoung.

    What you want to do is like asking for a single paragraph to be written in English and German that means exactly the same in both languages.

    Each language has its own syntax. German uses different words to English for the same thing, and a word with the same spelling in German and English means something different. It is the same with T-SQL and PL/SQL.

    You should aim for the call interface to the stored proc to be as similar as possible between SQL and Oracle, but the code within the stored procedure will be different. Even the call interface may have its differences, because not all data types are available in both SQL and Oracle.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • July 30, 2009 at 10:40 am

    #1032526

    EdVassie (7/30/2009)

    ...

    What you want to do is like asking for a single paragraph to be written in English and German that means exactly the same in both languages...

    Well, maybe not as bad as English vs. German. But worse than Spanish vs. Portuguese. Hmm...

    I'd say more like Spanish vs. Italian. Yeah, that works, they're eerily similar to non-speakers, but definitely different enough that you couldn't do any serious communications if you were just limited to words that were the same in both languages. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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