REF：

Example compiling an External Procedure using Pro*C Callback on Windows platform (文档 ID 1271017.1)

Modified Date Label修改日期Modified Date06-JAN-2011Document Type Label类型Document TypeSAMPLE CODE状态REVIEWED(EXTERNAL)优先级3转到底部

In this Document

---

Applies to:

Precompilers - Version: 11.2.0.1 and later   [Release: 11.2 and later ]

Microsoft Windows (32-bit)

Purpose

Provide sample steps to compile and test an External Procedure call using Pro*C code to make a database callback, on Windows.

Software Requirements/Prerequisites

• A supported C compiler for your database/platform.  (refer to Certified Compilers) Create Library privilege must be granted to the Oracle user the External Procedure will be used with.
• Access to the database file system to copy files to.
• A SQL*Plus connection to the database to issue PL/SQL statements and test the procedure.

This note was last tested using Microsoft Visual Studio 2010 C++  (VS2010) and database 11.2.0.1.0 on Windows XP (32 bit)

Configuring the Sample Code

The following steps assume Visual Studio 2010.  The same steps can be used for Visual Studio 2008, but the paths will need to be changed as appropriate.

Running the Sample Code

1. Create a working directory (c:\tempextproc for example), and place the following code in a file called  getempno.pc 

   /* getempno.pc */

   #include <sqlca.h>
   #include <oci.h>
   int __declspec(dllexport) get_empno(OCIExtProcContext *epctx)
   {
   int empno=0;
   EXEC SQL REGISTER CONNECT USING :epctx;
   EXEC SQL SELECT empno INTO :empno FROM emp where ename='KING';
   return(empno);
   }

2. Open a Visual Studio command prompt (Start > Programs > Microsoft Visual Studio 2008 > Visual Studio Tools > Visual Studio 2008 Command Prompt ,   and move the command prompt into that irectory.
3. Set ORACLE_HOME and MSVCDIR environment variables by issuing the following at a command prompt (adjust paths for your environment):

   set oracle_home=D:\oracle\product\11.2.0\dbhome_1

   set MSVCDIR="D:\Program Files\Microsoft Visual Studio 9.0\VC"

4. Precompile the .pc file using the following command (all on one line), which will result in the creation of getempno.c :

   proc iname=getempno.pc oname=getempno.c parse=full include=%ORACLE_HOME%\oci\include include=%ORACLE_HOME%\precomp\public include=%MSVCDir%\include

5. Compile getempno.c with the following command (all on one line), which will result in the creation of getempno.dll

   cl -I%ORACLE_HOME%\oci\include -I%MSVCDir%\include -I%ORACLE_HOME%\precomp\public -Zi getempno.c /LD /link %ORACLE_HOME%\oci\lib\msvc\oci.lib %ORACLE_HOME%\precomp\lib\orasql11.lib msvcrt.lib /nod:libcmt /DLL

6. In this case, we'll copy the dll to %ORACLE_HOME%\bin for simplicity

   copy getempno.dll %ORACLE_HOME%\BIN\getempno.dll

7. At this point, getempno.dll will have a dependency on MSVCR90.DLL which likely does not exist in the PATH, so it will need to be copied to %ORACLE_HOME%\bin, or the following error will result at runtime:

   ERROR at line 1:

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

   First, however, it will need to have the manifest incorporated into it or the following error will result at runtime:

   Microsoft Visual Studio C++ Runtime Library

   ==================================
   Runtime Error!
   Program: d:\oracle\product\11.2.0\dbhome_1\bin\extproc.exe
   R6034
   An application has made an attempt to load the C runtime library incorrectly.
   Please contact the application's support team for more information.

   To create the manifest, move the VS2010  command prompt into the MSVCRT redistributable folder and issue the following:

   cd %MSVCDIR%redist\x86\Microsoft.VC90.CRT

   mt.exe -manifest Microsoft.VC90.CRT.manifest -outputresource:msvcr90.dll;2

   for further information, refer to

8. After incorporating the manifest, copy MSVCR90.dll to %OracleHome%\bin

   copy msvcr90.dll %ORACLE_HOME%\bin\msvcr90.dll

9. Now, connect in SQL*Plus for example

   sqlplus scott/tiger

   and create the library, create the procedure wrapper, and test it by running the code below

   drop library empnolib;

   /
   create library empnolib as 'D:\oracle\product\11.2.0\dbhome_1\bin\getempno.dll';
   /
   create or replace function get_empno
   return binary_integer as
   external library empnolib
   name "get_empno"
   language C
   with context
   parameters(context);
   /
   select getempno from dual;

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Sample Code

See code in above instructions.

Sample Code Output

SQL> select get_empno from dual;

GET_EMPNO

----------

7839

SQL>