Error Linking to C DLL from PL/SQL [message #665279] |
Mon, 28 August 2017 13:20 |
|
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 |
|
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 #665286 is a reply to message #665285] |
Mon, 28 August 2017 15:12 |
|
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 #665288 is a reply to message #665287] |
Mon, 28 August 2017 15:53 |
|
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.
|
|
|