Re: Looking for examples that call C functions from PL/SQL!

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Thu, 22 Jul 1999 14:39:09 -0700
Message-ID: <7n7vgj$538$1@inet16.us.oracle.com>

PURPOSE

---

   This article explains about how to retrieve the BLOB/CLOB/BFILE   contents and write in to a file. PL/SQL program will retrieve the   BLOB/CLOB/BFILE contents and pass into the external 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.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

Chow Hoi Ka, Eric <eric138_at_yahoo.com> wrote in message news:37975E87.2071707E_at_yahoo.com...

> Hello,
>
> Would you please to show me a simple example that from PL/SQL calls
> external C functions ?
>
> Or from Pro C/C++ calls PL/SQL(store procedure) ???
>
> Best regards,
> Eric
>
> --
>      _                                                  _
>     / ) |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ( \
>    / /  |                                            |  \ \
>  _( /_  | _          Chow Hoi Ka, Eric             _ |  _) )_
> (((\ \> |/ )                                      ( \| </ /)))
> (\\\\ \_/ /                                        \ \_/ ////)
>  \       /       E-Mail : eric138_at_yahoo.com         \        /
>   \    _/                                            \_     /
>   /   / |____________________________________________| \    \
>  /   /                                                   \    \
>
>

Received on Thu Jul 22 1999 - 16:39:09 CDT