Re: Extproc listener configuration

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Mon, 19 Jul 1999 13:32:32 -0700
Message-ID: <7mvug3$g4s$1@inet16.us.oracle.com>

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)
      )

)

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. Received on Mon Jul 19 1999 - 15:32:32 CDT