Example compiling an External Procedure using Pro*C Callback on Windows platform (文档 ID 1271017.1)
Modified Date Label修改日期Modified Date06-JAN-2011
Document Type Label类型
Document TypeSAMPLE CODE
状态
REVIEWED(EXTERNAL)
优先级
3
转到底部
In this Document
Purpose
Software Requirements/Prerequisites
Configuring the Sample Code
Running the Sample Code
Caution
Sample Code
Sample Code Output
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 Note 43208.1 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
- 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);
} - 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.
- 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" - 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
- 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
- In this case, we'll copy the dll to %ORACLE_HOME%\bin for simplicity
copy getempno.dll %ORACLE_HOME%\BIN\getempno.dll
- 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 DLLFirst, 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;2for further information, refer to MSDN Documentation
- After incorporating the manifest, copy MSVCR90.dll to %OracleHome%\bin
copy msvcr90.dll %ORACLE_HOME%\bin\msvcr90.dll
- 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>