| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle8 External Procedures Question
A copy of this was sent to vjavaly_at_hotmail.com
(if that email address didn't require changing)
On Mon, 01 Mar 1999 20:11:24 GMT, you wrote:
>I'm encountering a problem trying to setup a simple external procedure call
>(Oracle8/NT). The C code is compiled into a DLL under MS Visual C++ 5.0.
>Can anyone help? Reply to vjavaly_at_hotmail.com. Thank you.
>
you'r missing the export of the function from the DLL.
>#include <stdio.h>
_declspec (dllexport)
>void writeOutstr (char *path, char *message)
>{
> FILE *fp;
> fp = fopen (path,"w");
> fprintf (fp, "%s\n", message);
> fclose (fp);
>}
>
you need to export the function. Also, you'll want to always send null indicators and length fields with your stuff as well.
[snip]
You can pick up a zip file from http://govt.us.oracle.com/~oracle/utl_io.zip that is a working example for NT and Unix that does binary file IO from pl/sql included is:
extproc.c and Makefile -- this builds the external procedure library (.so file/.dll). You'll have to compile this on your system with your Oracle install (needs to be done on the server).
listener.ora and tnsnames.ora -- sample listener and tns config files for extprocs. I'll go through them a little down below.
extproc.sql -- the pl/sql bindings to the external procedure. this maps the pl/sql functions in the utl_io package i wrote to their c counterparts in extproc.c. Also, it creates a 'library' in the dtabase. You will have to CHANGE this create libary statement to fix up the path to the .so file!
test.sql -- shows how to use this package to read and write binary files. It has in it:
declare
l_filehandle binary_integer;
l_amt number;
begin
l_filehandle := utl_io.fopen( '/tmp/binary.dat', 'w' );
for i in 1 .. 10 loop
l_amt := utl_io.fwrite( utl_raw.cast_to_raw( 'Hello World' || chr(10) ),
l_filehandle );
dbms_output.put_line( 'wrote ' || l_amt || ' bytes...' );
end loop;
utl_io.fclose( l_filehandle );
end;
/
declare
l_filehandle binary_integer;
l_rawdata raw(25);
l_amt number;
l_filehandle := utl_io.fopen( '/tmp/binary.dat', 'r' );
for i in 1 .. 10 loop
l_amt := utl_io.fread( l_rawdata, l_filehandle );
dbms_output.put_line( utl_raw.cast_to_varchar2(l_rawdata) );
end loop;
utl_io.fclose( l_filehandle );
end;
/
which demonstrates creating and then reading a binary file.... The entire api is demonstrated here.
the api is simply:
CREATE OR REPLACE PACKAGE utl_io
as
function fopen( p_filename in varchar2,
p_mode in varchar2 ) return binary_integer;
function fwrite( p_data in raw,
p_filehandle in binary_integer ) return binary_integer;
function fread( p_data out raw,
p_filehandle in binary_integer ) return binary_integer;
procedure fclose( p_filehandle in binary_integer );
procedure fcloseall;
end;
/
fopen is mapped to the C fopen function. p_filename is the fully qualified file name you want to work with. In my example, i am writing and reading the file /tmp/binary.dat. p_mode is the mode you want to open the file with. This mode is the C mode. values typically are:
r or rb open file for reading
w or wb truncate to zero length or create file for
writing
a or ab append; open or create file for writing at
end-of-file
r+ or rb+ or r+b open file for update (reading and writing)
w+ or wb+ or w+b truncate to zero length or create file for
update
a+ or ab+ or a+b append; open or create file for update,
writing at end-of-file
if you do a man on fopen, you can get more information.
fwrite writes the data in a RAW to a file. You put the data into a raw variable and it will write that much data. It returns the number of bytes actually written to disk.
fread reads the data from an open file. it looks at the raw variable that was passed in and tries to read that many bytes at a time (eg: if you pass it a raw(20), it'll try to read 20 bytes, pass in a raw(2000) and it'll try 2000 bytes). It also returns the number of bytes actually read.
fclose -- closes the file. IMPORTANT if you are using fwrite to call fclose to get the buffers flushed and all..
fcloseall, closes all open files.
so, thats the entire api, fopen, fwrite, fread, fclose and fcloseall.
You need to setup the listener.ora and tnsnames.ora file for extprocs.
Here is a sample listener.ora file:
(GLOBAL_DBNAME=sheepdog.us.oracle.com)
(ORACLE_HOME= /d01/home/oracle81)
(SID_NAME = oracle81)
(SID_NAME = extproc)
(ORACLE_HOME = /d01/home/oracle81)
(PROGRAM = extproc)
#(ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/test/ext_proc.log)
)
The important things in the listener file for extprocs:
o ADDRESS=(PROTOCOL=IPC)(KEY=oracle81.world))
set up an IPC based listener. remember the value of the KEY= (you can make it whatever you want, just remember what it is)
o (SID_DESC=(SID_NAME=extproc)
remember that SID_NAME, call it extproc.
o (ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/test/ext_proc.log)
if you want a specific set of environment variables setup for your extproc routines, put them here, separated by commas. The above for example, sets up 2 environment variables for the extproc servcies, DEBUG_MODULES and DEBUG_FILE. Since extprocs run detached from a console and are a little touchy to debug, having copious amounts of 'tracing' that you can enable/disable via the environment is suggested (by me)...
The next file is the tnsnames.ora file, it needs an entry like:
Important things:
o (ADDRESS = (PROTOCOL = IPC)(KEY = oracle81.world))
should be the same as was in the listener.ora file....
o (CONNECT_DATA = (SID = extproc))
the sid must match the sid in the (SID_DESC=(SID_NAME=extproc) from the listener.ora....
Also, if your sqlnet.ora specifies some default domain, it needs to be on the tnsnames entry. So if you have a sqlnet.ora with stuff like:
sqlnet.authentication_services=(none)
names.directory_path = (TNSNAMES, HOSTNAME)
names.default_domain = world
name.default_zone = world
automatic_ipc = off
in it, that would be extproc_connection_data.world -- not just extproc_connection_data.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |