Thursday, November 25, 2010

Test Oracle External Procedure on windows

Someone asked me about to use External Procedure on windows. I use it on Linux and used to write it(on Linux/Unix) ...read
So, i tested program from %ORACLE_HOME%\RDBMS\extproc PATH

I started to change make.bat file after installed MS SDK + VC

REM USAGE: just type MAKE
cl /GS- /DWIN64 /D_WIN64 /DSS_64BIT_SERVER /D_AMD64_=1 -I"C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\include" -I"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Include" /LD -Zi extern.c /link "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\lib\amd64\msvcrt.lib" /LIBPATH:"C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\lib\amd64" /LIBPATH:"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Lib\x64" /MACHINE:AMD64 /nod:libcmt /DLL

then compiled program
PS C:\oracle\product\10.2.0\db_1\RDBMS\extproc> .\make.bat
C:\oracle\product\10.2.0\db_1\RDBMS\extproc>REM USAGE: just type MAKE
C:\oracle\product\10.2.0\db_1\RDBMS\extproc>cl /GS- /DWIN64 /D_WIN64 /DSS_64BIT_SERVER /D_AMD64_=1 -I"C:\Program Files (
x86)\Microsoft Visual Studio 10.0\VC\include" -I"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Include" /LD -Zi e
xtern.c /link "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\lib\amd64\msvcrt.lib" /LIBPATH:"C:\Program Files (
x86)\Microsoft Visual Studio 10.0\VC\lib\amd64" /LIBPATH:"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Lib\x64" /
MACHINE:AMD64 /nod:libcmt /DLL
Microsoft (R) C/C++ Optimizing Compiler Version 16.00.30319.01 for x64
Copyright (C) Microsoft Corporation. All rights reserved.
extern.c
Microsoft (R) Incremental Linker Version 10.00.30319.01
Copyright (C) Microsoft Corporation. All rights reserved.
/out:extern.dll
/dll
/implib:extern.lib
/debug
"C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\lib\amd64\msvcrt.lib"
"/LIBPATH:C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\lib\amd64"
"/LIBPATH:C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Lib\x64"
/MACHINE:AMD64
/nod:libcmt
/DLL
extern.obj
I found "extern.dll" file, then copied "extern.dll" to C:\oracle\product\10.2.0\db_1\BIN PATH
PS C:\oracle\product\10.2.0\db_1\RDBMS\extproc> dir ..\..\BIN\extern.dll

Directory: C:\oracle\product\10.2.0\db_1\BIN

Mode LastWriteTime
Length Name
---- ------------- ------ ----
-a--- 11/25/2010 12:32 PM
19968 extern.dll
then modified listener.ora and tnsnames.ora files
PS C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN> cat .\listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME =
PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM =
extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = dbtest)(PORT = 1521))
(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

PS
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN> cat .\tnsnames.ora

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
then retstart LISTENER, and then test (i used "extern.sql" file to help but changed..)
>>>
drop library externProcedures;
create library externProcedures as 'C:\oracle\product\10.2.0\db_1\BIN\extern.dll';
>>>

SQL> set serveroutput on;
SQL> execute UseIt;
The maximum of 1 and 2 is 2

PL/SQL procedure successfully completed

No comments: