Passing raw type from VB client

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 Tue Apr 08 2003 - 15:16:54 CDT