BLOB operation in external procedures

From: Alex <no_SPAM_atnite_at_freemail.ru>
Date: Tue, 29 Jan 2002 17:52:20 +0100
Message-ID: <a36k04$5v6$1_at_rznews2.rrze.uni-erlangen.de>

Hi folks!
I'm not very expirienced in C as well as in ORACLE and PL/SQL so please forgive me if I'm asking obvious things. But maybe someone could explain me how to handle correctly BLOB fields in an external procedure. The goal is to create an PL/SQL function which is capable to create BLOB's basing on user input. (Source code given at the end of this message (see below))

The C code compiles fine and gives no errors. SQLPlus also runs the script correctly and gives no error messages. BUT, when, for example I'm issuing the following command:

 update lobtab set binary=blobdemo.BlobValue('some string') where ID=1;

I'm always receiving the following error message:

 update lobtab set binary=blobdemo.BlobValue('some string') where ID=1

                          *

ERROR at line 1:

ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "TESTUSER.BLOBDEMO", line 0
ORA-06512: at line 1

Any help is very appreciated.
Thanx.

---

The C code is as follows:

/****************************************************************************

#include <stdio.h>
#include <stdlib.h>

#ifndef OCI_ORACLE
# include <oci.h>
#endif

/* here some code commented out since it is found to be working correctly 
 * in this part of code str2bin function is implemented.
 */

/***************************************************************************
 *
 * Function blob_value()

 *

 **************************************************************************/

OCILobLocator *blob_value (OCIExtProcContext *with_context,
                           unsigned char *input_string)

/* with_context - context handler

• input_string - input string */ { unsigned char *result; int len;

  /* Allocate buffer for binary output. */   len = strlen(input_string)/8;
  result = OCIExtProcAllocCallMemory(with_context, len);

  /* Convert input string into binary output. */   str2bin(input_string, result);

  /* Return current binary output to SQL procedure. */   return((OCILobLocator *)result);
}

/**************************************************************************/

PL/SQL script as follows:

Rem Create sample table to hold lob data. DROP TABLE lobtab;
CREATE TABLE lobtab (

       id       int,
       screen   varchar2(50),
       binary   blob

);

Rem Insert some data into table.
INSERT INTO lobtab VALUES (

               1,
               '0101010010101010010',
               empty_blob()

);

INSERT INTO lobtab VALUES (

               2,
               '0101010011101010010',
               empty_blob()

);

INSERT INTO lobtab VALUES (

               3,
               '0001000010101010000',
               empty_blob()

);

COMMIT; Rem create library bloblib is 'full_path_to_library.so' CREATE OR REPLACE LIBRARY bloblib IS '/some_path/sv.so'; /
show errors;

Rem Create package - it can be used in SQL statements CREATE OR REPLACE PACKAGE blobdemo IS
  FUNCTION BlobValue(input_string IN VARCHAR2)   RETURN BLOB;
END blobdemo;
/
show errors;

CREATE OR REPLACE PACKAGE BODY blobdemo IS   FUNCTION BlobValue(input_string IN VARCHAR2) RETURN BLOB   IS EXTERNAL
    NAME "blob_value"
    LIBRARY bloblib
    WITH CONTEXT
    PARAMETERS (CONTEXT,

                input_string STRING,
                RETURN OCILobLocator);

end blobdemo;
/
show errors;
== end of SQL code == Received on Tue Jan 29 2002 - 17:52:20 CET