Skip to Main Content
  • Questions

  • Oracle database Libraries (External Procedures Using C)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Abdul Rahim.

Asked: August 06, 2017 - 10:23 pm UTC

Last updated: August 07, 2017 - 9:08 pm UTC

Version: 12.1.02.0

Viewed 1000+ times

You Asked

Dear Tom,

I have an Oracle 12c(12.1.0.2.0) installation in Cent OS Linux. I was learning the Oracle Libraries and I created a library to find the Reverse of a String and I have published the function as given below.

CREATE OR REPLACE FUNCTION reverse_string(text varchar2)
RETURN varchar2
AUTHID DEFINER
AS LANGUAGE C
LIBRARY lib_string_function_sample
NAME "reverseString"
parameters(text string);
/

From the Server I tested the functionality by taking the SQLPlus. It is working perfectly. I got the result.
But when I tried to invoke the same in SQLDevelepor I am getting the following error.

ORA-28595: Extproc agent : Invalid DLL Path
28595. 00000 - "Extproc agent : Invalid DLL Path"
*Cause: The path of DLL supplied for the extproc execution is invalid.
*Action: Check if the DLL path is set properly using the EXTPROC_DLLS
environment variable.

and Connor said...

Assuming a default setup, you'll need to make sure your library is in the right location.

As per the docs:

Set Up the Environment

When you use the default configuration for external procedures, Oracle Database spawns extproc directly. You need not make configuration changes for listener.ora and tnsnames.ora. Define the environment variables to be used by external procedures in the file extproc.ora (located at $ORACLE_HOME/hs/admin on UNIX operating systems and at ORACLE_HOME\hs\admin on Windows), using this syntax:

SET name=value (environment_variable_name value)
Set the EXTPROC_DLLS environment variable, which restricts the DLLs that extproc can load, to one of these values:

NULL; for example:

SET EXTPROC_DLLS=
This setting, the default, allows extproc to load only the DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib.

ONLY followed by a colon-separated list of DLLs; for example:

SET EXTPROC_DLLS=ONLY:DLL1:DLL2
This setting allows extproc to load only the DLLs named DLL1 and DLL2. This setting provides maximum security.

A colon-separated list of DLLs; for example:

SET EXTPROC_DLLS=DLL1:DLL2
This setting allows extproc to load the DLLs named DLL1 and DLL2 and the DLLs that are in directory $ORACLE_HOME/bin or $ORACLE_HOME/lib.

ANY; for example:

SET EXTPROC_DLLS=ANY
This setting allows extproc to load any DLL.


If you've gone away from the defaults (and hence edited any of the sqlnet files, then ensure you bounce the listener to pick up any changes you've made).

Although for a function such as reverse, perhaps a java routine will be easier to build/load into the database.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-28595: Extproc agent : Invalid DLL Path 28595. 00000 - "Extproc agent : Invalid DLL Path"

Abdul Rahim Rahim, August 07, 2017 - 10:06 pm UTC

Thank you So much...!! Very informative.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library