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
<a>轉到底部</a>
<b>In this Document</b>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#PURPOSE">Purpose</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#SWREQS">Software Requirements/Prerequisites</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#CONFIG">Configuring the Sample Code</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#RUNSTEPS">Running the Sample Code</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#ADDINFO">Caution</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#CODE">Sample Code</a>
<a href="https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1271017.1&print=Y&bugProductSource=#OUTPUT">Sample Code Output</a>
Precompilers - Version: 11.2.0.1 and later [Release: 11.2 and later ]
Microsoft Windows (32-bit)
Provide sample steps to compile and test an External Procedure call using Pro*C code to make a database callback, on Windows.
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)
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.
Create a working directory (c:\tempextproc for example), and place the following code in a file called getempno.pc
<code>/* 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); }</code>
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):
<code>set oracle_home=D:\oracle\product\11.2.0\dbhome_1 set MSVCDIR="D:\Program Files\Microsoft Visual Studio 9.0\VC"</code>
Precompile the .pc file using the following command (all on one line), which will result in the creation of getempno.c :
<code>proc iname=getempno.pc oname=getempno.c parse=full include=%ORACLE_HOME%\oci\include include=%ORACLE_HOME%\precomp\public include=%MSVCDir%\include</code>
Compile getempno.c with the following command (all on one line), which will result in the creation of getempno.dll
<code>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</code>
In this case, we'll copy the dll to %ORACLE_HOME%\bin for simplicity
<code>copy getempno.dll %ORACLE_HOME%\BIN\getempno.dll</code>
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-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:
<code>cd %MSVCDIR%redist\x86\Microsoft.VC90.CRT mt.exe -manifest Microsoft.VC90.CRT.manifest -outputresource:msvcr90.dll;2</code>
After incorporating the manifest, copy MSVCR90.dll to %OracleHome%\bin
<code>copy msvcr90.dll %ORACLE_HOME%\bin\msvcr90.dll</code>
Now, connect in SQL*Plus for example
<code>sqlplus scott/tiger</code>
and create the library, create the procedure wrapper, and test it by running the code below
<code>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;</code>
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.
See code in above instructions.
<code>SQL> select get_empno from dual; GET_EMPNO ---------- 7839 SQL> 本文轉自海天一鷗部落格園部落格，原文連結：http://www.cnblogs.com/sgsoft/archive/2011/01/25/1944663.html，如需轉載請自行聯系原作者</code>