Re: Something to ponder ..

A copy of this was sent to "Paul Baker" <paul.baker_at_lvs.co.uk> (if that email address didn't require changing) On Wed, 26 Jan 2000 11:38:15 -0000, you wrote:

>Can anyone explain the following?
>
>SQLWKS> SELECT HEXTORAW(RAWTOHEX('1234')) from dual;
>
>HEXTORAW
>--------
>31323334
>1 row selected.
>
>I would expect 1234 to be returned .. am I being stupid?
>

beware the implicit conversion

rawtohex('1234')

converts 1234 into a hex string first ('1234' is a character string, RAWTOHEX needs a raw, the implicit conversion turns 1234 into '1' into 0x31, '2' into 0x32, '3' into 0x33 and '4' into 0x34). So the raw input into rawtohex is actually

 0x31 || 0x32 || 0x33 || 0x34 (4 bytes).

That gets turned into a hexstring '31323334'. That is passed into hextoraw which turns it back into binary '1234' *but* SQLPlus doesn't do raws so on the way out it is bound back into a STRING type again, once again getting converted into a hexstring '31323334'.

the implicit conversions and the fact that sqlplus does not do 'raw' output makes this so.

If you do this in an environment where you have more control over the display -- you would get a different display. Consider this program:

main( argc, argv )
int argc;
char * argv[];
{
VARCHAR oracleid[50];

typedef struct tag_myraw { unsigned short len; char arr[255]; } myraw; exec sql type myraw is varraw(255);
myraw rdata;
varchar sdata[255];

    strcpy( oracleid.arr, USERID );
    oracleid.len = strlen(oracleid.arr);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    exec sql whenever sqlerror do sqlerror_hard();

    exec sql select hextoraw(rawtohex('1234')), hextoraw(rawtohex('1234'))

               into :rdata, :sdata
               from dual;

    printf( "rData = %d, '%.*s'\n", rdata.len, rdata.len, rdata.arr );     printf( "sData = %d, '%.*s'\n", sdata.len, sdata.len, sdata.arr );

    EXEC SQL COMMIT WORK RELEASE;
}

It displays the following output:

Connected to ORACLE as user: /

rData = 4, '1234'
sData = 8, '31323334'

so, depending on whether the client fetches into a RAW or a VARCHAR -- the results will differ.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 26 2000 - 08:58:03 CST