Call data-specific stored procedure from Oracle procedure

Question

I have a situation where I have an Oracle procedure that is being called from at least 3 or 4 different places. I need to be able to be able to call custom-code depending on some data. The custom-code is customer-specific - so, customer A might want to do A-B-C where customer B might want to do 6-7-8 and customer C doesn't need to do anything extra. When customers D...Z come along, I don't want to have to modify my existing procedure.

I'd like to be able to enter the customer-specific procedure into a table. In this existing procedure, check that database table if a custom-code procedure exists and if so, execute it. Each of the customer-code procedures would have the same parameters.

For instance:

1. My application (3+ places) calls this "delete" procedure
2. In this delete procedure, look up the name of a child-procedure to call (if one exists at all)
3. If one exists, execute that delete procedure (passing the parameters in)

I know I can do this with building a string that contains the call to the stored procedure. But, I'd like to know if Oracle 10g has anything built in for doing this kind of thing?

Answer 1

Do each of your customers have their own database? If so the best option would be to use conditional compilation. This has the advantage of not requiring dynamic SQL. Have the main program always call the custom procedure, and use CC flags to vary the code it contains.

Otherwise, Oracle does have a Rule Engine but it is not really intended for our use.

Answer 2

Agree with APC's answer and just to expand on it, in this white paper if you look for "Component based installation" it describes a similar problem solved by using conditional compilation.

Answer 3

The final solution that we went with was to store the name of a procedure in a database table. We then build the SQL call and use an EXECUTE statement.

Answer 4

Your solution seems reasonable given the requirements, so I voted it up.

Another option would be to loop through the results from your table look-up and put calls to the procedures inside a big case statement. It would be more code, but it would have the advantage of making the dependency chain visible so you could more easily catch missing permissions and invalid procedures.