Search code examples
oraclestored-proceduresdynamiccustom-code

Call data-specific stored procedure from Oracle procedure

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?

Solution

  • 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.