Re: Sample BLOB Scripts

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Mon, 19 Jul 1999 13:29:10 -0700
Message-ID: <7mvu9o$f1t$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)
      )

    )

  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"

Rob Calfee <trace_at_primenet.com> wrote in message news:37935c6e.248969717_at_news.primenet.com...

> I'm just starting to use BLOBs.  Does anyone have any sample scripts
> for inserting and extracting BLOBs (SQL-PL/SQL (? and Visual Basic,
> maybe)?  Thanks in advance.  Just need some basic stuff to get going.
>
>
> Rob Calfee
> DBA
> rcalfee_at_incsystem.com

Received on Mon Jul 19 1999 - 15:29:10 CDT