Re: External Procedures: Embedded SQL and OCI callbacks both possible?

Sorry Stewart, I don't have an answer to your problem, I just want to defend what you are trying to do from a professional standpoint.

From what I understand, the bottom line of what you are trying to do is along the lines of being able to call a "pretty complicated Pro*C routine" from PL/SQL. As I see it you have three bad options:

1. Make a copy of the Pro*C algorithm in PL/SQL and call it directly. This has the huge drawback of duplicating code, the biggest no-no in software engineering. It also has the drawback that you'll (obviously) need to create the PL/SQL and you'll need to test the new code.
2. Replace the current Pro*C routine with PL/SQL. This is better than the previous approach in that you're not duplicating code. However, compared to the previous approach you'll have to do total regression testing of all code that uses the old Pro*C routine. This may not be feasible, especially if it's in production. But if you're far away from production and you have a bunch of unit tests this may be the way to go.
3. Wrap the Pro*C routine into a shared library and call it as an external procedure. What you're trying to do. Not the best solution from a holistically architectural standpoint but you'll save a lot of time in development and testing if you can get it to work.

As a professional software developer I certainly understand why you are interested in the third approach.

Tom

Ps. In the long run, I recommend you place all database code into PL/SQL packages and create well defined interfaces (public procedure/functions) to access the code. This has the benefit that you can call the code from a number of different types of clients: OCI, JDBC, ODBC etc.

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote:
> I agree entirely with Sybrand. Unless I've misunderstood, you're using the
> database to go out to an external procedure, which will then connect back to
> the database to work out its return, then pass that return back to the
> database calling routine.
>
> IMHO, this is not a sensible way to go about things.
Received on Sun Jul 21 2002 - 12:42:21 CDT