Extproc listener configuration
Doug Cowles
The following is straight out of the Oracle Documentation.
----------------------------------------------------------------------------------------------------------
For environments where the configuration files have been overwritten,
edit the LISTENER.ORA and TNSNAMES.ORA files as
follows:
1.Add a system identifier (SID) name and a program name
for EXTPROC in the server's LISTENER.ORA file (entries are
shown in boldface text):
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = INVENTORY)
)
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ORCL)
)
(ADDRESS =
(PROTOCOL = IPC)
(KEY = extproc)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = INVENTORY)
(PORT = 1521)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ADMIN
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
)
(SID_DESC =
(SID_NAME = extproc)
(PROGRAM = extproc)
)
)
2.Add a service name entry for EXTPROC in the server's TNSNAMES.ORA file (entries are shown in boldface text):
INVENTORYDB.WORLD =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(Host = INVENTORY)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORCL)) )
extproc_connection_data.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ORCL)
)
(CONNECT_DATA = (SID=extproc))
)
Please note that the value for KEY must match the KEY value specified
in the LISTENER.ORA file. In order to support a
multiple Oracle home environment, the Oracle Installer automatically
creates unique keys for the external procedures in different
Oracle homes.
---------------------------------------------------------------------------------------------------
Correct me if I'm wrong but it says that the value for KEY in tnsnames.ora
must match
the value for KEY in listener.ora. In their example, the KEY in tnsnames.ora
is
ORCL, and in listener.ora it's extproc, no? Do they really
mean the SID? Or is
this just a bad example on how to do it? (are these things case
sensitive by the way).
- Thanks,
Dc.
Yass Khogaly
=======
This article explains about how to retrive the BLOB/CLOB/BFILE
contents and write in to a file. PL/SQL program will retrive the
BLOB/CLOB/BFILE contents and pass into the extrenal procedure.
The external procedure will write the contents to a file.
Description
===========
Create a file extern.c file in the directory c:\orant\rdbms80\extproc
directory.
Before this take a backup of the file extproc.c file in this directory.
The extern.c file :
===================
#include <oci.h>
#define NullValue -1
#include<stdio.h>
#include<string.h>
long __declspec(dllexport) OutputString(context ,
path , path_ind ,
message , message_ind,
filemode , filemode_ind ,
len , len_ind )
char *path;
char *message;
char *filemode;
int len;
OCIExtProcContext *context;
short path_ind;
short message_ind;
short filemode_ind;
short len_ind;
{
FILE *file_handle;
int i ;
char str[3];
int value;
/* Check whther any parameter passing is null */
if (path_ind == OCI_IND_NULL || message_ind == OCI_IND_NULL ||
filemode_ind == OCI_IND_NULL || len_ind == OCI_IND_NULL ) {
text *initial_msg = (text *)"One of the Parameter Has a Null Value
!!! ";
text *error_msg;
/* Allocate space for the error message text, and set it up.
We do not have to free this memory - PL/SQL will do that
automatically. */
error_msg = OCIExtProcAllocCallMemory(context,
strlen(path) + strlen(initial_msg) + 1);
strcpy((char *)error_msg, (char *)initial_msg);
/*strcat((char *)error_msg, path); */
OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);
/* OCIExtProcRaiseExcp(context, 6502); */
return 0;
}
/* Open the file for writing. */
file_handle = fopen(path, filemode);
/* Check for success. If not, raise an error. */
if (!file_handle) {
text *initial_msg = (text *)"Cannot Create file ";
text *error_msg ;
/* Allocate space for the error message text, and set it up.
We do not have to free this memory - PL/SQL will do that
automatically. */
error_msg = OCIExtProcAllocCallMemory(context,
strlen(path) + strlen(initial_msg) + 1);
strcpy((char *)error_msg, (char *)initial_msg);
strcat((char *)error_msg, path);
OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);
return 0;
}
i = 0;
while (i < len)
{
/* Read the hexadecimal value(1). */
str[0] = message[i];
i++;
/* Read the hexadecimal value(2). */
str[1] = message[i];
/* Convert the first byte to the binary value. */
if (str[0] > 64 && str[0] < 71)
str[0] = str[0] - 55;
else
str[0] = str[0] - 48;
/* Convert the second byte to the binary value. */
if (str[1] > 64 && str[1] < 71)
str[1] = str[1] - 55;
else
str[1] = str[1] - 48;
/* Convert the hex value to binary (first & second byte). */
value = str[0] * 16 + str[1];
/* Write the binary data to the binary file. */
fprintf(file_handle,"%c",value);
i++;
}
/* Output the string followed by a newline. */
/* fwrite(message,len,1,file_handle); */
/* Close the file. */
fclose(file_handle);
}
Use the make.bat available in the c:\orant\rdbms80\extproc directory.
You need to run vcvars32.bat file before running this batch file.
This will create a dll file.
I am attching the dll file for your convienence.
Now you have to configure the tnsnames.ora and the listener.ora files.
tnsnames.ora should contain the following entries along with your usual
entries.
extproc_connection_data.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ORCL)
)
(CONNECT_DATA = (SID = extproc)
)
)
# P:\ORANT\NET80\ADMIN\LISTENER.ORA Configuration
File:p:\orant\net80\admin\listener.ora
# Generated by Oracle Net8 Assistant
LISTENER8 =
(ADDRESS = (PROTOCOL = TCP)(HOST = winnt_nsc)(PORT = 1521))
SID_LIST_LISTENER8=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = winnt_nsc)
(SID_NAME = ORCL)
)
(SID_DESC =
(SID_NAME = extproc)
(PROGRAM = extproc)
)
)
create library externProcedures as 'C:\orant\RDBMS80\EXTPROC\extern.dll';
Create or replace PROCEDURE OutputString(
p_Path IN VARCHAR2,
p_Message IN VARCHAR2,
p_mode in VARCHAR2,
p_NumLines IN BINARY_INTEGER) AS EXTERNAL
LIBRARY externProcedures
NAME "OutputString"
With context
PARAMETERS (CONTEXT,
p_Path STRING,
p_path INDICATOR,
p_Message STRING,
p_message INDICATOR,
p_mode STRING,
p_mode INDICATOR,
p_NumLines INT,
p_numlines INDICATOR);
This is the pl/sql block used to write the contents of the blob into a
file.
set the serveroutput on before running.
SQL> desc lob_tab;
Name Null? Type
------------------------------- -------- ----
C1 NUMBER
C2 BLOB
lob_tab is the table which contains the blob data.
declare
i1 blob;
len number;
my_vr raw(10000);
i2 number;
i3 number := 10000;
begin
-- get the blob locator
SELECT c2 INTO i1 FROM lob_tab WHERE c1 = 2;
-- find the length of the blob column
len := DBMS_LOB.GETLENGTH(i1);
dbms_output.put_line('Length of the Column : ' || to_char(len));
-- Read 10000 bytes at a time
i2 := 1;
if len < 10000 then
-- If the col length is < 10000
DBMS_LOB.READ(i1,len,i2,my_vr);
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*len);
-- You have to convert the data to rawtohex format. Directly sending the
buffer
-- data will not work
-- That is the reason why we are sending the length as the double the size
of the data read
dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
else
-- If the col length is > 10000
DBMS_LOB.READ(i1,i3,i2,my_vr);
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*i3);
dbms_output.put_line('Read ' || to_char(i3) || ' Bytes ');
end if;
i2 := i2 + 10000;
while (i2 < len ) loop
-- loop till entire data is fetched
DBMS_LOB.READ(i1,i3,i2,my_vr);
dbms_output.put_line('Read ' || to_char(i3+i2-1) || ' Bytes ');
outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'ab',2*i3);
i2 := i2 + 10000 ;
end loop;
end;
Regards
"The Views expressed here are my own and not necessarily those of Oracle
Corporation"
Doug Cowles <dco...@bigfoot.com> wrote in message news:37937918...@bigfoot.com...
Doug Cowles
- Dc.
Yass Khogaly wrote:
Description
===========
{
OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);
file_handle = fopen(path, filemode);
i = 0;
while (i < len)
{
i++;
i++;
}
/* fwrite(message,len,1,file_handle); */
Regards
Doug Cowles <dco...@bigfoot.com> wrote in message news:37937918...@bigfoot.com...I'm trying to set up a database so that external procedures can be used.
Doug Cowles
Where can I get more documentation on trying this stuff out? I tried the
Oracle 8 docs that came with the client CD, but they didn't really say much.
Will a UNIX .a style library serve the same purpose? Is metalink a good resource?
There must be some documentation on this somewhere or their wouldn't be a
sample file out there, no?
- Dc.
Pete Sharman wrote:
DougI only have 8.1 installed, but for that the make.bat file is simply:
REM USAGE: just type MAKE
cl -I. /LD -Zi extern.c /link msvcrt.lib /nod:libcmt /DLLCouldn't find the other .bat file Yass mentioned at all.
HTH.
Pete
--
RegardsPete