Re: substr, substrb and raw datatype

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 14 Jun 2005 09:00:36 -0700
Message-ID: <1118764846.802138@yasure>

Rafal Dabrowa wrote:
> I have noticed that a column with "raw" datatype may be passed to
> substr and substrb functions instead of char table. But, despite
> Oracle documentation says that the functions are the same for
> single-byte database character set, these functions behave
> differently with raw column type.
>
> Let's say, I have the following table:
>
> Name Type
> ------------------------
> ID NUMBER(38)
> VAL RAW(20)
>
> and I have one row in the table:
>
> ID VAL
> ----- --------------------
> 1 0102030405060708090A
>
> The following statement:
> select id, substr(val, 5, 2) from xyz;
> gives the following output:
>
> ID SU
> ----- --
> 1 03
>
> But, when I replace "substr" by "substrb",
> output is the following:
>
> ID SUBS
> ----- ----
> 1 0506
>
> Why ? What is reason for the output difference ?
> Do I perform something forbidden ? Do all Oracle servers
> behave in the same way here, or maybe is it an "undefined"
> behavior ?

SQL> CREATE TABLE t (

   2 charcol VARCHAR2(20),
   3 rawcol RAW(20));

Table created.

SQL> INSERT INTO t VALUES ('ABC', 'ABC');

1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT DUMP(charcol), DUMP(rawcol)

   2 FROM t;

DUMP(CHARCOL) DUMP(RAWCOL)

----------------------  ---------------------

Typ=1 Len=3: 65,66,67 Typ=23 Len=2: 10,188

SQL>

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Jun 14 2005 - 11:00:36 CDT