Re: External SQL functions exported by DLLs

On Fri, 11 Mar 2005 14:49:32 +0100, Peter Sullvan wrote:

Thanks for jumping across to this forum.

You are attempting to run what Oracle refers to as an External Procedure. First time around is not for the faint of heart, but it works well once you catch on.

Conceptually, your DLL is in an available secure directory, and is called by SQLNet (aka Net8 & Oracle Networking). The call is made from the database by storing a PL/SQL wrapper that points to the DLL. That wrapper can then be usually be called in any SQL clause or in PL/SQL.

There are conditions where the call may not be valid, enforced to preserve Oracle's integrity or locking mechanisms.

There are a few things to think about:

• Oracle heavily enforces the security, so setting up the Listener.ora to do the call out is usually either a matter of following the pattern from the metalink notes or trial and error
• The DLL is normally a precompiled (Pro*C) routine. Makefiles are available in the %ORACLE_HOME%/proc subdirectory, as is an example of the extproc source.
• the PL/SQL wrapper, being a PL/SQL stored procedure or function, follows database security considerations

Oracle provides an online samples of both parts under Oracle8i section at http://www.oracle.com/technology/sample_code/tech/pl_sql/index.html

In addition, a chapter of the "Oracle Application Developer's Guide - Fundementals" (chapter 10 for Oracle9iR2) is devoted to this topic.

HTH/FGB Received on Fri Mar 11 2005 - 09:54:40 CST