Package and Package Body

Packages make the database a much better programming environment by introducing the concept of an interface, which allows encapsulation and abstraction. They allow you to effectively develop and maintain complex applications written in PL/SQL

In MS SQL Server and similar, which just have stored procedures, there is no concept of an external and internal API; all sprocs are at the same level, available to be called by any code. There’s no way for a developer to present a limited interface to the outside worlds, all the internal methods are open. Without rigorous controls on development procedures, this can (and does) lead to “spaghetti code”, where there’s a complex and poorly understood interactions between various stored procedures. It makes ongoing maintenance much more complex as there’s no limit to what side effects might occur when a stored procedure is changed.

Packages solve this problem by encapsulating related stored procedures into a single package, and allowing a limited external interface to be defined. This means that the majority of the stored procedures are only ever called within the package and are not exposed to the outside. The impact of changing these is limited to the package. (If you end up with a lot of public stored procedures, you might want to reconsider your interface design and application boundaries to reduce that).

Packages also allow you to limit the namespace, and the scope of variables and exceptions to within that package, meaning there are no side effects. You can develop packages independently of each other without worrying that two packages both have a sproc called “GetCustomer” or a global variable called updateFlag. The reduction in the number of places the code can possibly interact to just the public interfaces of each package greatly simplifies new development and maintenance of the system.

Essentially packages fulfill the role of Class Libraries in .NET or Java, and make the database a useful development environment for large scale software systems. Don’t let the application developers tell you they need to do all the work in Java and database stored procedures are just for SQL. PL/SQL is a powerful enough language to usefully manage all the logic for a complex system. (There are pros and cons of taking that approach, but it’s a valid direction to consider).

http://ask.sqlservercentral.com/questions/38084/package-and-package-body.html

Disable all constraints of a table

MARCH 17 2011, BY SIMON KRENGER

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

Disable all constraints of a table:

begin
 for cur in (select owner, constraint_name , table_name 
  from all_constraints
  where owner = 'SIMON' and
  TABLE_NAME = 'MY_TABLE') loop
   execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' 
   MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE ';
   end loop;
end;
/

Remember to enable the constraints again before ending maintenance or else you might end up with a logically inconsistent database:

begin
 for cur in (select owner, constraint_name , table_name 
  from all_constraints
  where owner = 'SIMON' and
  TABLE_NAME = 'MY_TABLE') loop
   execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||'
   MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';
   end loop;
end;

http://www.krenger.ch/blog/disable-all-constraints-of-a-table/