Since SQL Server doesn't have packages, what do programmers do to get around it?

Question

I have a SQL Server database that has a huge proliferation of stored procedures. Large numbers of stored procedures are not a problem in my Oracle databases because of the Oracle "package" feature.

What do programmers do to get around the lack of a "package" feature like that of Oracle?

Answer 1

1. Like people have said, Schema's are a more logical and ANSI compliant way to organize database tables and procedures.

2. Software engineering best practices are that we should never make a change directly on any server. Since all database sprocs are scripted and under configuration control, we can arrange those scripts into any folder structure we want.

(outdated info sourced from AskTom has been removed)

Answer 2

It is somewhat funny to see how emotional one can get over such a dry subject. The fact that Oracle has a feature that SQL Server does not seems to generate all kinds of reactions towards the disputable characteristics of this feature.

For starters, the question was in the style: there is this feature in Oracle that is being missed in SQL Server and what is the recommended approach.

No need to get emotional about it.

For those who do not like the packaging feature in Oracle, -for whatever the reason-, they still can go about it the same way one can do with SQL Server.

Getting more into the detail, there could be a follow-up question in the style: when modifying a function or procedure within a package, the entire package is invalidated and this "sucks", what would be the recommended way to avoid the sucking aspect.

Personally, I have never seen anyone complaining about not being able to modify a statically linked library in an executable without having to relink it.

Answer 3

One additional feature of packages that was not mentioned is the ability to 'wrap' the body. The header is always public and can be viewed by anyone with permissions to execute the package. But that also allows them to view the code in the body. You can wrap the body, encrypting it, and prevent anyone from seeing what the code is actually doing. Its a nice feature where security is a big issue.

Answer 4

3) 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.

I understand the frustration of this statement, but I would not call id "unacceptable". In a true production environment, changes should never be tested in production. Updates should be moved from a test environment to production in a scheduled and orderly manner. In a 24/7 system, then redundant production environment should handle down time while servers are updated. Not only does the package have to be taken off line, but the new package, if not compiled, will fail when placed back on line. There is a DBA element required for Oracle databases. However, I do miss the Oracle packages.

Answer 5

I've worked with both SQL Server and Oracle so have seen the good and bad of both. As the above comments have beena bit heated I'll try and keep this as neutral as possible...

So, what's an Oracle Package? Think of it like a database class

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.

I found packages to be really useful for a number of reasons:

1. You've got the concept of a public interface that can be provided to other developers
2. Packages can mirror your compiled classes. My Orders.Save() C# method will call my Oracle Orders.SaveLineItem method to save each line item and an Oracle SaveOrder method to save the order summary details.
3. My procs are grouped together in a nice, logical way inside the packages

Personally, I would be love MS to implement some kind of package functionality as I think it makes for a cleaner database.

Answer 6

I would thank my lucky stars that SQL Server doesn't have packages. Oracle packages suck.

Hmm, we need a way to take all these procedures and put them in one place. I know! Let's make developers create and maintain two files for each package. They will love us forever!

As long as MS never implements packages like Oracle did, it'll be a win in my book.

EDIT for commenters:

Oracle Packages are simply a way to organize your stored procedures into, well, packages so that you don't have 100 stored procedures sitting around, but maybe 5 packages. They're not stackable like packages in Java or C# code. All packages are at the same level.

A package requires two files: the headers file and the body file. This creates frustration when adding new procedures to an existing package, because you cannot add the body without adding the header, even though it contains the exact same information as is in the body.

For example, here is a snippet from the header file of one of my packages:

    PROCEDURE bulk_approve_events
(
    i_last_updated_by IN VARCHAR2,
    o_event OUT NUMBER
);

And here's the corresponding procedure in the body:

    PROCEDURE bulk_approve_events
(
    i_last_updated_by IN VARCHAR2,
    o_event OUT NUMBER
) IS
...
BEGIN
...
END;

No difference. The header file is useless and is simply another hurdle for the developer to step over when developing with packages. On my project, we have a convention that all the commented documentation for each procedure goes in the header, along with the details of when it was added and by whom, but that could just as easily be included in the body.

Answer 7

While SQL Server has nothing to offer by way of the "cool features" of encapsulation and package state like you are used to, you can organize your stored procedures into schemas.

In enterprise manager, these procs are still all listed together which makes for a HUGE treelist if you have hundreds of procs. I too miss the organization and cool features of Oracle packages. However, all platforms have their strengths.

NOTE: Writing stored procedures in the .NET language DOES give you encapsulation and state. It still does not however separate them in the EM treeview in any special way.

Answer 8

Schemas may be used to organize stored procedures and other objects. Personally, I prefer to use schemas when they organize objects by functional area, and where those funcational areas correspond to security boundaries. An example of this is found in the AdventureWorks sample databse, which has schemas like "HumanResources" and "Sales". The theory being that a given user may need access to objects in "HumanResources", but may not need access to "Sales" information.

An alternative is to use a naming convention and enforce it, as James says above. I'll add that SQL Server Management Studio has a filter button that can be used to filter the list of objects displayed. For instance, one can click on the "Stored Procedures" folder and filter on Name contains "Add".

On my current project, I have pulled a number of SQL queries out of SSIS packages and into stored procedures. In order to distinguish between these stored procedures and those that should be of general use, I have prefixed the names with "ssis". It would certainly have been more pleasant if I could have created something similar to a namespace in C# or C++, and created "SSIS.SelectUserLookupData" instead of "ssis_SelectUserLookupData". It would be even nicer if these namespaces could be nested.

If this is one of the featues of Packages in Oracle, then perhaps someone would let me know.

Answer 9

Come up with a good naming convention, use it, and enforce it.