Re: Updating LONG RAW fields in PL/SQL
Date: 1996/07/25
Message-ID: <4t8mdk$l1j_at_inet-nntp-gw-1.us.oracle.com>#1/1
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 Thu Jul 25 1996 - 00:00:00 CEST