Passing raw type from VB client

Hi all,
I have the same problem as Flavio. I am using OO4O to connect my vb6 application to a Oracle 8.1.7 DB.

I want to call a Oracle SP to store two images. Two parameters are of type LONG RAW. In my vb program I am using

OraDatabase.Parameters.Add "IMG", ByteData(), ORAPARM_INPUT OraDatabase.Parameters("IMG").serverType = ORATYPE_RAW

where ByteData() is a byte array.
When I execute the SP I get ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error.

Any idea?
Thanx a lot.
Valentina

Message 1 in thread
From: FC (flavio_at_tin.it)
Subject: Passing raw type from VB client Date: 2003-04-08 13:16:54 PST  

Hello there,
does anyone know if there is an easier way of acclomplishing the following?

A Visual Basic client app. needs to pass a hundred bytes string to a stored
procedure for later processing. Processing means storing the data into a
table and also convert each byte into its decimal equivalent and update
several columns in a couple of other tables. Needless to say, this must be
done as
quickly as possible.

As far as I know I have a few options for doing this, but I am not very keen
at the solutions we've found so far, the methods look too cumbersome to me.

1. convert the binary data into a "hex" varchar2 string (twice the size) on the client and then use the hextoraw function inside the stored procedure. The input parameter is defined as varchar2.

For instace the binary (hex) string 00 01 (2 bytes) is translated as the
"0001" varchar2 string literal (4 bytes).

2) convert the binary data into a "hex" format varchar2 string (twice the
size) on the client and pass it directly to the parameter defined as raw.
Slightly faster than 1.

3) use a 'short' BLOB perhaps ? Is dbms_lob faster than utl_raw ? I tried
using utl_raw.substr instead of the standard substr function (taking into
account the double size of strings) and it runs slower than 2. I performed
some
tests extracting the same piece of data in a loop repeated 10000 times and solution 2 is 25% faster. The main problem is that I am not dealing with a file, in which case a blob field would be the best solution.

4) find some way to pass the original raw data into the raw argument ? When the binary string contains the equivalent of CHR(0), that is a byte 00
in the first position, the stored procedure ends in error (Cannot insert
null into ... etc.). On the client side the input parameter is defined as
ORATYPE_RAW and its value, if displayed inside the vb debugger, is correct.
The
error occurs later, inside the stored procedure, apparently because the
input value is null, something that has certainly to do with the presence of
Chr(0) in
position 1.

So, in the end, is there any way to pass the parameter without having to
worry about Chr(0)?

Thanks,
Flavio Received on Fri Jul 25 2003 - 06:34:42 CDT