Packages (procedures/functions)

I was recently asked to compare differences in T-SQL and PL/SQL packages. At least that's what I understood. Since, T-SQL does not support anything virtually resembling PL/SQL packages it may seem pointless. I disagree, however. I did some research and found, rather interesting (at least socially), discussion on StackOverflow. There is some valid points made there but mostly all voices against PL/SQL packages come from people who seem to be very uneducated developers. It's also pretty old topic so maybe Oracle was so much different back then? No, it wasn't ;)

So, instead of just finishing with - SQL Server doesn't support packages, which, by itself is both true and something SQL Server can be hated on, I decided to elaborate a bit on PL/SQL packages and why these are so much better then, yet another, bollocks workaround T-SQL developers are forced to make.

So, what's the package? I'm going to directly quote gadsUK here:

"The Package has two elements: a header file and a body file. The header file is your public interface, and contains the signature (name, params and return type if applicable) of all the stored procedures or functions (in Oracle a function returns a value, a stored proc doesn't) that are directly callable. The package body must implement all the procedure signatures in the package header file.
The body element of the package contains all the stored procs and logic that actually do the work. You may have a Save procedure declared in the package header that calls an insert or update proc that exists in the body. The developer can only see the "Save" proc. It's important to keep in mind that the package body can also implement procs or functions not declared in the package header, they're just not accessible outside of the package itself."

So, unlike some seems to be suggesting, it's not only used for grouping code. It's more of a single entity, a class. Remember, you can keep package variables and constants there as well so it's possible to share it between sessions or use it as a Singleton if need be. It's all well documented. It's not impossible to mimic such behaviour in SQL Server but you would have to code it manually, probably using temporary tables.

Some say, PL/SQL packages have this huge installation drawback. Let me quote gunnar here:

"The best argument against oracle packages is that based on experience and research on the Ask Tom site, one can't update a package without taking it off line. This is unacceptable. With SQL Server, we can update stored procedures on the fly, without interrupting production operation."

This is absolute bollocks as it has been in 2012 and 2014 (when the comment has been made). I don't know if Gunnar never used Oracle or still had been using some pre 10 version. I suppose, the issue he is reffering to is inability to compile packages in certain situations. Those situations are changing with Oracle version and... the same happens for regular procedures and function. Hell... to most of the database objects really! What they're talking about is the fact that you can't compile the package when it's being used. That means - if there are any sessions running any procedures/functions from the package. The same applies to regular procedures and functions as well. And also, you can't run any DDLs on currently used objects. That alone means that this opinion doesn't have any sense.

How did it change with Oracle versions? Well, in 10i, you couldn't compile a package if it was referenced in anything that was being executed at the time of compilation. In 11g, it's not possible only if the particular package is active. I think in 12-something or some next version it'll be possible to compile package at any time (obviously only new sessions/executions will see new package) so the issue is disappearing anyway.

Also shout-out to belgariontheking for most pointless and most uneducated opinion on the topic ever. You should switch to some other job, bro. You're clearly not fit for the Oracle. Maybe find SQL Server position somewhere :P

 Best