Re: Extproc listener configuration

Doug

I 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 /DLL

Couldn't find the other .bat file Yass mentioned at all.

HTH. Pete

Doug Cowles wrote:

> Can you help me out with those bat files? I don't see them on my
> system.
> - Dc.
>
> Yass Khogaly wrote:
>
>> PURPOSE
>> =======
>> 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)
>> )
>> )
>>
>>
>> The listener.ora should contain the following entries.
>>
>> # 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)
>> )
>> )
>>
>> Login from sqlplus and issue the following statements.
>>
>> 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 <dcowles_at_bigfoot.com> wrote in message
>> news:37937918.99E1370B_at_bigfoot.com...I'm trying to set up
>> a database so that external procedures can be used.
>> 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.
>>

--
Regards

Pete

Received on Thu Jul 22 1999 - 11:04:51 CDT