This document supplements the information in the Applications Development chapter of the Getting Started for Windows NT manual. It explains how to run the sample and gives some trouble-shooting advice.

Running the Sample

1. Install Microsoft Visual C++ 5.0.

2. Run the MAKE.BAT file in the RDBMS80\EXTPROC directory. This creates the EXTERN.DLL.

3. Run SVRMGR30 and run the EXTERN.SQL script. It prompts for the database's internal password.

D:\ORANT\RDBMS80\EXTPROC\>svrrmgr30 @extern.sql

This SQL script does the rest, including calling the PLSQL function "UseIt" that makes a call to the C function contained in the EXTERN.DLL.

Trouble-Shooting

PROBLEM:

You get the following error running the EXTERN.SQL script:

ORA-28575: unable to open RPC connection to external procedure agent

ORA-06512: at "SAMPLE.PLS_MAX", line 0

ORA-06512: at "SAMPLE.USEIT", line 8

ORA-06512: at line 2

SOLUTION:

This is most likely a SQL*Net configuration problem. Make sure that your listener service is started. Make sure that your TNSNAMES.ORA and LISTENER.ORA are properly updated. If you make changes to your LISTENER.ORA, be sure to stop and re-start your listener. The correct entry in your TNSNAMES.ORA is:

extproc_connection_data.world =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = IPC)

(KEY = ORCL)

)

(CONNECT_DATA = (SID = extproc)

)

)

The correct entry in your LISTENER.ORA should look like this:

LISTENER =

(ADDRESS_LIST =

(ADDRESS=

(PROTOCOL= IPC)

(KEY= oracle.world)

)

(ADDRESS=

(PROTOCOL= IPC)

(KEY= ORCL)

)

(ADDRESS=

(COMMUNITY= NMP.world)

(PROTOCOL= NMP)

(SERVER= YourServer)

(PIPE= ORAPIPE)

)

(ADDRESS=

(PROTOCOL= TCP)

(Host= YourServer)

(Port= 1521)

)

(ADDRESS=

(PROTOCOL= TCP)

(Host= YourServer)

(Port= 1526)

)

(ADDRESS=

(PROTOCOL= TCP)

(Host= 127.0.0.1)

(Port= 1521)

)

(ADDRESS=

(PROTOCOL= SPX)

(Service= YourServer_lsnr)

)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = 0

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = YourServer)

(SID_NAME = ORCL)

)

(SID_DESC =

(SID_NAME = extproc)

(PROGRAM=extproc)

)

)

PASSWORDS_LISTENER = (oracle)

PROBLEM:

When you build your DLL, you get the following missing exports:

ociepacm

ocieperr

ociepgoe

ociepmsg

SOLUTION:

Make sure you link with PLS803.LIB in the ORANT\OCI80\LIB\ directory. You will also need to link with the ORA803.LIB as well.

PROBLEM:

When you execute your external procedure, you get an Application Error in EXTPROC.EXE.

SOLUTION:

Your function prototypes are not correct, you are returning invalid data, or you have messed up the stack in you function. Try using DebugBreak(); to step into your DLL code to check the make sure you are not messing things up.

PROBLEM:

When you execute your external procedure, you get the following error

ORA-28576: lost RPC connection to external procedure agent

ORA-06512: at "SAMPLE.PLS_MAX", line 0

ORA-06512: at "SAMPLE.USEIT", line 8

ORA-06512: at line 2

SOLUTION:

This is a time-out problem. Perhaps while you were debugging your external function, you did not let your function return in time. In that case it is harmless. However, it could be an indication that you are taking too long in your external callout code.

PROBLEM:

You are having performance problems.

SOLUTION:

Performance is dramatically improved on future calls to external procedures in the same session. So, try to make as many of your external procedure calls as possible before disconnecting. Future releases will decrease this initial performance hit.

PROBLEM:

You want to limit the OS permissions of the DLL's that contain the external procedure.

SOLUTION:

Create a new listener service with its own LISTENER.ORA and have that service run under a user with limited permissions.

PROBLEM:

You are getting the following error when trying to execute the external procedure:

ORA-06520: PL/SQL: Error loading external library

ORA-06522: Unable to load DLL

ORA-06512: at "SAMPLE.PLS_MAX", line 0

ORA-06512: at "SAMPLE.USEIT", line 8

ORA-06512: at line 2

SOLUTION:

Make sure that the full path name to your extern.dll is correct and that the DLL exists.