Re: Problem writing blobs

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 17 Feb 1999 14:30:43 GMT
Message-ID: <36cbd05f.7488067@192.86.155.100>

A copy of this was sent to Thomas Rieffel <thomasr_at_fun.de> (if that email address didn't require changing) On Wed, 17 Feb 1999 09:30:42 +0100, you wrote:

>I have a problem with writing blobs. In the following PL/SQL-block i get
>a
> "ORA-06502: PL/SQL: numeric or value error" when calling the Funktion
>"dbms_lob.write".
>I Think the problem ist, that empty_blob() doesn´t work correct, because
>if set locator_var to NULL before calling "dbms_lob.write" I got the
>same error. Does anybody know what´s the problem here. (If I run the
>same PL/SQL-block for clobs, the block works korrect.);
>
>Thanks,
>
>Thomas Rieffel
>
>....
>char my-buf[40];
>....
> EXEC SQL EXECUTE
> declare
> locator_var blob;
> amount_var binary_integer;
> offset_var binary_integer;
> begin
> amount_var := 27;
> offset_var := 1;
> INSERT INTO blobtest(nr, lg) VALUES(1, empty_blob());
> commit;
> SELECT lg into locator_var FROM blobtest
> WHERE nr=1 for update;
> dbms_lob.write(locator_var, amount_var, offset_var, :my_buf);
> end;
> END-EXEC;
>
>Table-definition
> CREATE TABLE blobtest(nr integer, lg blob));
>
>

You don't post all of your pro*c code but it is most like a datatype issue between the char [40] and the fact you want a 27 byte RAW. The Char type would be expected to contain a string of hex characters that would represent the RAW data (char is mapped to the database CHAR type which when used as a RAW is expected to be 2 times the size of the raw and hold the hex digits for it). So, the amount_var being set to 27 is a problem as you would need at least a char [54] for that.

Here is a proc snippet based on your code that does this using a VARRAW. VARRAW is probably the best way to deal with raw data. I used pointers (just as easy to use a fixed size structure by not using REFERENCE in the exec sql type and by putting the actual max size of the blob into the record definition) and malloc for storage....

static void process()
{

typedef struct TAGmy_raw
{

    long len;
    unsigned char arr[1];
}

    my_raw;

/*
 * Use type equivalencing to tell Oracle that the C type "my_raw" is
 * equivalent to the Oracle type LONG VARRAW and can hold upto 100k
 * bytes of data (we will never allocate that much here, just an upper
 * bound, could be MUCH higher (eg: 10,000,000,000)
 */

EXEC SQL TYPE my_raw IS LONG VARRAW(32760) REFERENCE;

my_raw * buffer;

int     size = 27;
int     i;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    buffer = malloc( sizeof(my_raw)+size );

    for( i = 0; i < size; i++ )

        buffer->arr[i] = i;
    buffer->len = size;

    EXEC SQL DELETE FROM BLOBTEST;    EXEC SQL EXECUTE

     declare
        locator_var blob;
        amount_var  binary_integer;
        offset_var  binary_integer;
     begin
        amount_var := utl_raw.length( :buffer );
        offset_var := 1;

        INSERT INTO blobtest(nr, lg)
        VALUES(1, empty_blob())
        returning lg into locator_var;

        dbms_lob.write(locator_var, amount_var, offset_var, :buffer);
     end;

   END-EXEC;
}

to see that it worked, i did the following in sqlplus:

SQL> select nr, rawtohex( dbms_lob.substr( lg, dbms_lob.getlength(lg),1 ) )   2 from blobtest;

        NR RAWTOHEX(DBMS_LOB.SUBSTR(LG,DBMS_LOB.GETLENGTH(LG),1))

----------   ------------------------------------------------------
         1   000102030405060708090A0B0C0D0E0F101112131415161718191A

so, the hexstring has 27 bytes, and they are 0-26 in value as expected....

Now, i deviated from your code in the following ways:

o       INSERT INTO blobtest(nr, lg)
        VALUES(1, empty_blob())
        returning lg into locator_var;

   instead of insert; commit; select ... for update.

   the insert; commit; select ... for update will work, I just thought you might not have seen insert ... returning yet. faster, easier, cleaner to use and the row is alread locked for you.

o amount_var := utl_raw.length( :buffer );

  insert of amount_var := 27. utl_raw may /may not be installed (if you have replication or gateways it is, if not it probably isn't). utl_raw is handy for dealing with some of these raw issues. it can be found in $ORACLE_HOME/rdbms/admin if you do an ls *raw* (or dir *raw* on NT). you'll find a spec (.sql file) and wrapped body (.plb file). install them using svmgrl when connected as SYS or INTERNAL (not as any other user else they will *not* work)....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 17 1999 - 08:30:43 CST