Home » SQL & PL/SQL » SQL & PL/SQL » Error Linking to C DLL from PL/SQL (Local Oracle 12c - Windows 10)
Error Linking to C DLL from PL/SQL [message #665279] Mon, 28 August 2017 13:20 Go to next message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
I am getting the below error and I've put my setup below. Been searching for answers to get this to work, but no luck. Any suggestions? Thanks in advance.

Error report -
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "ECCDBA.GENERALIZED_BLACK_SCHOLES", line 1
ORA-06512: at line 19
06520. 00000 - "PL/SQL: Error loading external library"
*Cause: An error was detected by PL/SQL trying to load the external
library dynamically.
*Action: Check the stacked error (if any) for more details.
------------------------------------------------------------------------------------------------------------------
# tnsnames.ora Network Configuration File: D:\app\jgero\virtual\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ECCDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORAPLS_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = PLSPROC1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ECCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = eccdb)
)
)
------------------------------------------------------------------------------------------------------------------
# listener.ora Network Configuration File: D:\app\jgero\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\app\jgero\virtual\product\12.2.0\dbhome_1)
(PROGRAM = extproc)
)
)

LISTENER_PLS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = PLSPROC1521))
)
)
------------------------------------------------------------------------------------------------------------------
#
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
#
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the
# following values:
#
# * ONLY (maximum security)
#
# When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
#
# Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
# When EXTPROC_DLLS is not set, only the DLL(s) in $ORACLE_HOME/bin and
# ORACLE_HOME/lib can be loaded.
#
# * Colon-seperated list of the DLLs
#
# When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in
# $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
#
# Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY
# When EXTPROC_DLLS=ANY, DLL checking is disabled.
#
# Syntax: SET EXTPROC_DLLS=ANY
#
#
# To turn extproc tracing on, set TRACE_LEVEL=ON (default is OFF).
#
# Syntax: TRACE_LEVEL=ON
#
EXTPROC_DLLS=D:\\app\\jgero\\virtual\\product\\12.2.0\dbhome_1\bin\\eccoptionslibrary.dll
------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE LIBRARY OPTION_MODELS AS 'D:\app\jgero\virtual\product\12.2.0\dbhome_1\bin\eccoptionslibrary.dll';

create or replace PROCEDURE GENERALIZED_BLACK_SCHOLES(PC_FLAG IN CHAR,
MKT_PRICE IN DOUBLE PRECISION,
STRIKE_PRICE IN DOUBLE PRECISION,
TIME_TO_MATURITY IN DOUBLE PRECISION,
RISK_FREE_RATE IN DOUBLE PRECISION,
COST_OF_CARRY IN DOUBLE PRECISION,
VOLATILITY IN DOUBLE PRECISION,
MTM IN OUT DOUBLE PRECISION)
AS LANGUAGE C
LIBRARY OPTION_MODELS
NAME "GBlackScholes";
------------------------------------------------------------------------------------------------------------------
DECLARE
TEMP2 NUMBER := 0;
BEGIN
FOR D IN 1..100000 LOOP
GENERALIZED_BLACK_SCHOLES('C', 100, 75, 400, 0.02, 0, 0.55, TEMP2);
GENERALIZED_BLACK_SCHOLES('P', 100, 75, 400, 0.02, 0, 0.55, TEMP2);
END LOOP;
END;
/
Re: Error Linking to C DLL from PL/SQL [message #665280 is a reply to message #665279] Mon, 28 August 2017 13:29 Go to previous messageGo to next message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
ADDITIONAL INFO: I Noticed a typo in the extproc.ora -- changed it to:

EXTPROC_DLLS=D:\\app\\jgero\\virtual\\product\\12.2.0\\dbhome_1\\bin\\eccoptionslibrary.dll

but same error

DECLARE
TEMP2 NUMBER := 0;
BEGIN
FOR D IN 1..100000 LOOP
GENERALIZED_BLACK_SCHOLES('C', 100, 75, 400, 0.02, 0, 0.55, TEMP2);
GENERALIZED_BLACK_SCHOLES('P', 100, 75, 400, 0.02, 0, 0.55, TEMP2);
END LOOP;
END;
Error report -
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL
ORA-06512: at "ECCDBA.GENERALIZED_BLACK_SCHOLES", line 1
ORA-06512: at line 5
06520. 00000 - "PL/SQL: Error loading external library"
*Cause: An error was detected by PL/SQL trying to load the external
library dynamically.
*Action: Check the stacked error (if any) for more details.
Re: Error Linking to C DLL from PL/SQL [message #665282 is a reply to message #665280] Mon, 28 August 2017 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What problem are you trying to solve?

http://lmgtfy.com/?q=oracle+external+procedure+dll+example
Re: Error Linking to C DLL from PL/SQL [message #665283 is a reply to message #665282] Mon, 28 August 2017 13:55 Go to previous messageGo to next message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
Thanks for the great suggestion. Already tried that and have been through plenty of links making the suggested changes to the various config files with no luck.



Re: Error Linking to C DLL from PL/SQL [message #665284 is a reply to message #665283] Mon, 28 August 2017 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, external DLL contain compiled (C, C++, Java) code; not PL/SQL.

What problem are you trying to solve?
Why do you believe that external DLL is part of the solution?
Re: Error Linking to C DLL from PL/SQL [message #665285 is a reply to message #665284] Mon, 28 August 2017 15:07 Go to previous messageGo to next message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
The problem I am trying to solve is that when I try to get PL/SQL to call an external C DLL it returns:

ORA-06520: PL/SQL: Error loading external library
ORA-06522: Unable to load DLL

(1) I have created the C DLL with VS2017 and added to the BIN directory in my Oracle Home

(2) Added the entries I have seen on several searches to my tnsnames, listener, and extproc config files -- see my initial post

(3) Restarted the machine

(4) In SQL/PLSQL I create the library -- see my initial post

(5) In SQL/PLSQL I create a procedure that calls the C DLL -- see my initial post

(6) I created a PL/SQL block that calls the PL/SQL in #5 that returns the error that the DLL can't be loaded

Any thoughts?
Re: Error Linking to C DLL from PL/SQL [message #665286 is a reply to message #665285] Mon, 28 August 2017 15:12 Go to previous messageGo to next message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
Here's the C code too:

// ECCOptionsLibrary.cpp : Defines the exported functions for the DLL application.
//

#include "stdafx.h"
#include <math.h>

// The Normal Distribution Function
double NormDist(double X)
{
// ND = 1 / Sqr(2 * Pi) * Exp(-X ^ 2 / 2) 3.1415926535897932384626433832795
return 1.0 / sqrt(2 * 3.1415926535897932384626433832795) * exp(pow(-X, 2.0) / 2.0);
}

//Cumulative Normal Distribution Function
double CumNormDist (double X)
{
double L;
double K;
double CND;

L = fabs(X);
K = 1.0 / (1.0 + 0.2316419 * L);

CND = 1.0 - 1.0 / sqrt(2 * 3.1415926535897932384626433832795) * exp(pow(-L, 2.0) / 2.0) * (0.31938153 * K + -0.356563782 * pow(K, 2.0) + 1.781477937 * pow(K, 3.0) + -1.821255978 * pow(K, 4.0) + 1.330274429 * pow(K, 5.0));

if (X < 0.0)
CND = 1 - CND;

return CND;
}

void __declspec(dllexport) GBlackScholes(char PutCallFlag, double MarketPrice, double StrikePrice, double TimeToMaturity, double RiskFreeRate, double CostOfCarry, double Volatility, double MTM)
{
double TimeToMaturityYears;
double D1;
double D2;

//S -- MarketPrice
//X -- StrikePrice
//b -- CostofCarry
//v -- Volatility
//T -- TimeToMaturity
// D1 = (log(MarketPrice / StrikePrice) + (CostOfCarry + Volatility ^ 2 / 2) * TimeToMaturity) / (Volatility * sqrt(TimeToMaturity));

TimeToMaturityYears = TimeToMaturity / 365.0;

D1 = (log(MarketPrice / StrikePrice) + (CostOfCarry + pow(Volatility, 2.0) / 2.0) * TimeToMaturityYears) / (Volatility * sqrt(TimeToMaturityYears));
D2 = D1 - Volatility * sqrt(TimeToMaturityYears);

if (PutCallFlag = 'C')
{
MTM = MarketPrice * exp((CostOfCarry - RiskFreeRate) * TimeToMaturityYears) * CumNormDist(D1) - StrikePrice * exp(-RiskFreeRate * TimeToMaturityYears) * CumNormDist(D2);
}
else
{
MTM = StrikePrice * exp(-RiskFreeRate * TimeToMaturityYears) * CumNormDist(-D2) - MarketPrice * exp((CostOfCarry - RiskFreeRate) * TimeToMaturityYears) * CumNormDist(-D1);
}
}
Re: Error Linking to C DLL from PL/SQL [message #665287 is a reply to message #665285] Mon, 28 August 2017 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>EXTPROC_DLLS=D:\\app\\jgero\\virtual\\product\\12.2.0\\dbhome_1\\bin\\eccoptionslibrary.dll
above looks strange (wrong?) to me.
Since when are double back slashes ("\\") used between folder names?
Does the Oracle software have necessary OS file permissions down the whole fully qualified pathname?

I suspect that Oracle is the victim; not the culprit?

Can you reproduce any external DDL sample code & successfully run it.

Do you have error of commission? (Did something wrong)
Do you have error of omission? (Did NOT do something that was required)
Re: Error Linking to C DLL from PL/SQL [message #665288 is a reply to message #665287] Mon, 28 August 2017 15:53 Go to previous message
jhgerold
Messages: 6
Registered: August 2017
Junior Member
Tried changing to single slashes, same error--in one of my searches they had double slashes for EXTPROC_DLLS.

Does the Oracle software have necessary OS file permissions down the whole fully qualified pathname?
How can I check this?

Can you reproduce any external DDL sample code & successfully run it--I'm going to try this next.


Previous Topic: Cursor within a cursor
Next Topic: Oracle lookup function in loop returns rowtype how to get fields from rowtype
Goto Forum:
  


Current Time: Thu Mar 28 04:40:14 CDT 2024