This is also based on on one of my postings in OTN. Here, I will demonstrate, How to call kernel32.dll from PL/SQL.
By the way, I have copied kernel32.dll from C:\WINDOWS\system32 to C:\oracle\product\10.2.0\db_3\bin
My listener.ora setup
By the way, I have copied kernel32.dll from C:\WINDOWS\system32 to C:\oracle\product\10.2.0\db_3\bin
My listener.ora setup
SID_LIST_LISTENEREXTPROC =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(ENV = "EXTPROC_DLLS=ONLY:C:\oracle\product\10.2.0\db_3\bin\kernel32.dll")
(SID_NAME = extproc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_3)
)
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
A little verification.LSNRCTL> stop LISTENEREXTPROC
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
The command completed successfully
LSNRCTL> start LISTENEREXTPROC
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_3\network\admin\listener.o
a
Log messages written to C:\oracle\product\10.2.0\db_3\network\log\listenerextpr
c.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\extprocipc
))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
STATUS of the LISTENER
------------------------
Alias LISTENEREXTPROC
Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Prod
ction
Start Date 21-JAN-2011 17:32:26
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_3\network\admin\listener.
ra
Listener Log File C:\oracle\product\10.2.0\db_3\network\log\listenerext
roc.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\extprocipc)))
Services Summary...
Service "extproc" has 1 instance(s).
Instance "extproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit
C:\Documents and Settings\Administrator>C:\>tnsping EXTPROC_CONNECTION_DATA
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-JAN-2
011 21:53:10
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)
(KEY = EXTPROC1))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))
OK (70 msec)
C:\>Now, the actual codes.SQL> CREATE OR REPLACE LIBRARY kernel32 AS 'C:\oracle\product\10.2.0\db_3\bin\kernel32.dll'; 2 / Library created. SQL> CREATE OR REPLACE FUNCTION CreateFile (FileName VARCHAR2 --1 2 ,p_DesiredAccess BINARY_INTEGER --2 3 ,p_ShareMode BINARY_INTEGER --3 4 ,p_SecurityAttributes BINARY_INTEGER --4 5 ,p_CreationDisposition BINARY_INTEGER --5 6 ,p_FlagsAndAttributes BINARY_INTEGER --6 7 ,p_TemplateFile BINARY_INTEGER )--7 8 Return BINARY_INTEGER 9 IS EXTERNAL 10 LIBRARY kernel32 Name "CreateFileA" 11 PARAMETERS (FileName STRING 12 ,p_DesiredAccess long 13 ,p_ShareMode long 14 ,p_SecurityAttributes long 15 ,p_CreationDisposition long 16 ,p_FlagsAndAttributes long 17 ,p_TemplateFile long 18 ,return long ); 19 / Function created. SQL> /* This is for closing the handle after use. */ SQL> CREATE OR REPLACE FUNCTION CloseFile (p_FileHandle BINARY_INTEGER) 2 Return BINARY_INTEGER 3 IS EXTERNAL 4 LIBRARY kernel32 Name "CloseHandle" 5 PARAMETERS (p_FileHandle long, return long); 6 / Function created. SQL> /* This is the main function for getting size */ SQL> CREATE OR REPLACE FUNCTION GetSize (p_FileHandle BINARY_INTEGER, 2 p_FileSizeHigh IN OUT BINARY_INTEGER) 3 RETURN BINARY_INTEGER 4 IS EXTERNAL 5 LIBRARY kernel32 NAME "GetFileSize" 6 PARAMETERS (p_FileHandle long, p_FileSizeHigh long, return long ); 7 / Function created. SQL> set serverout on SQL> DECLARE 2 v_FileSize BINARY_INTEGER; 3 v_FileSizeHigh PLS_INTEGER; 4 v_FileHandle BINARY_INTEGER; 5 v_filename VARCHAR2(500) :='C:\test2.csv'; 6 v_dummy BINARY_INTEGER; 7 BEGIN 8 v_FileSizeHigh := 400000000; 9 v_FileHandle:=CreateFile(v_filename -- File name 10 ,0 -- Type of access required (read/write ect) 11 ,0 -- disable share mode 12 ,0 --no securoty attribute 13 ,3 -- Means Open existing 14 ,128 --080h, File attribute normal. 15 ,0); --7 16 v_FileSize := Getsize(v_FileHandle, v_FileSizeHigh); 17 DBMS_OUTPUT.put_line('File Size in Bytes: ' ||v_FileSize); 18 v_dummy:=CloseFile(v_FileHandle); 19 END; 20 / File Size in Bytes: 61 PL/SQL procedure successfully completed. SQL> SQL> SQL> DECLARE 2 v_FileSize BINARY_INTEGER; 3 v_FileSizeHigh PLS_INTEGER; 4 v_FileHandle BINARY_INTEGER; 5 v_filename VARCHAR2(500) :='C:\Winter.jpg'; 6 v_dummy BINARY_INTEGER; 7 BEGIN 8 v_FileSizeHigh := 400000000; 9 v_FileHandle:=CreateFile(v_filename -- File name 10 ,0 -- Type of access required (read/write ect) 11 ,0 -- disable share mode 12 ,0 --no securoty attribute 13 ,3 -- Means Open existing 14 ,128 --080h, File attribute normal. 15 ,0); --7 16 v_FileSize := Getsize(v_FileHandle, v_FileSizeHigh); 17 DBMS_OUTPUT.put_line('File Size in Bytes: ' ||v_FileSize); 18 v_dummy:=CloseFile(v_FileHandle); 19 END; 20 / File Size in Bytes: 105542 PL/SQL procedure successfully completed. SQL>Verification.
C:\>dir test2.csv Volume in drive C has no label. Volume Serial Number is 6806-ABBD Directory of C:\ 12/15/2010 01:35 PM 61 test2.csv 1 File(s) 61 bytes 0 Dir(s) 3,405,336,576 bytes free C:\>dir Winter.jpg Volume in drive C has no label. Volume Serial Number is 6806-ABBD Directory of C:\ 10/11/2010 05:27 PM 105,542 Winter.jpg 1 File(s) 105,542 bytes 0 Dir(s) 3,405,336,576 bytes free C:\>
No comments :
Post a Comment