Re: Updating LONG RAW fields in PL/SQL

surman_at_dlsun338.us.oracle.com (Scott Urman) wrote:
>In article <31F79537.5FF2_at_ingr.com>, Brad Maxwell <bcmaxwel_at_ingr.com> writes:
>|> Hi,
>|>
>|> I need to be able to take a buffer of binary data (geometry, in my case)
>|> and update a LONG RAW column in my table. I'm using PL/SQL and trying
> ^^^^^^
>No you're not. You're using Pro*C. :)
>
>|> something like this:
>|>
>|> EXEC SQL BEGIN DECLARE SECTION;
>|> char *UpdateString;
>|> unsigned char BinaryData[2400];
>|> EXEC SQL END DECLARE SECTION;
>|>
>|> ...
>|>
>|> EXEC SQL PREPARE ORASTATEMENT FROM :UpdateString;
>|> (UpdateString contains "update Table1 set ID=99, Geometry=:BinaryData
>|> where ID=1")
>|>
>|> memcpy(BinaryData, (char *) BinData, 200);
>|>
>|> EXEC SQL EXECUTE ORASTATEMENT USING :BinaryData;
>|>
>|> I wind up with an Oracle 1465, "Invalid hex number", which tells me
>|> something about the ROWID in my Update statement containing invalid
>|> characters. The error isn't helpful, needless to say.
>
>The geometry column is long raw, and your host variable is char. Thus Oracle
>is doing an implicit HEXTORAW on your data. Since you aren't representing it
>in hex, you get the error. The proper way to do this is using a raw external
>datatype. I recommend long varraw, which you use with datatype equivalencing.
>Something like:
>
>typedef struct {
> int len;
> unsigned char buf[2400];
>} lvr_2400;
>
>EXEC SQL BEGIN DECLARE SECTION;
> EXEC SQL TYPE lvr_2400 IS LONG VARRAW (2400);
> lvr_2400 BinaryData;
>EXEC SQL END DECLARE SECTION;
>
> memcpy(BinaryData.buf, (unsigned char *)BinData, 200);
> BinaryData.len = 200;
>
> EXEC SQL EXECUTE ORASTATEMENT USING :BinaryData;
>
>The advantage of LONG VARRAW is that you can dynamicly specify the length each
>time you execute, without having to rePREPARE the statement. Just put the
>actual length in the .len field, and the data in the .buf field.
>
>|>
>|> Am I on the right track? Any advice is appreciated!
>|>
>|> Thanks,
>
>De nada.
>
>|> Brad
>------------------------------------------------------------------------
>Scott Urman Oracle Corporation surman_at_us.oracle.com
>------------------------------------------------------------------------
>Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
>Published by Oracle Press - http://www.osborne.com/oracle/index.htm
>------------------------------------------------------------------------
>"The opinions expressed here are my own, and are not necessarily that of
> Oracle Corporation"
>------------------------------------------------------------------------
Received on Fri Jul 26 1996 - 00:00:00 CEST