Re: SUBSTR(str,n) error

Marcin Buchwald <Marcin.Buchwald_at_agora.pl> wrote in message news:<3CFB356A.CD5B9AD5_at_agora.pl>...
> 11:02:54 SPA.SPAC.GWSPACER> select
> machine,length(machine),ascii(substr(machine,15,1))
> from v$session where username='VELVET';
>
> MACHINE LENGTH(MACHINE) ASCII(SUBSTR(MACHINE,15,1))
> --------------- --------------- ---------------------------
> GWNT\NT-VELVET 15 0
>
> On pl/sql level the expression:
> select machine into vmachine...
> stores this tailing '0' into the variable
>
> Let see the expression
>
> vmachine := substr(vmachine,5)
>
> THE LENGTH IS NOT KNOWN!
>
> try
> vi := length(vmachine)
>
> CONCATENATION OPERATION ON vmachine WILL NOT WORK
>
> try
> vmachine := vmachine||'12345'
>
> the solution is easy to find.
> Just use substr(vmachine,5,length(vmachine)-5-1) instead of
> substr(vmachine,5)
>
> Substr(s,n) call is documented and legal
>
> This is an error in Oracle!
> What a shame!
>
> Or maybe I misunderestand something :-9

I believe you do 'misunderstand something'. Notice your length() of machine is 15, yet there are only 14 visible characters in the string.  This indicates a null character (\'0') terminating the string (I also get such behaviour for network machine names that include the domain). The ASCII value for '\0' is 0, so your query is returning the correct value for the character you specified. I can't see this being an 'error in Oracle', since non-domain name entries are not affected:

MACHINE LENGTH(MACHINE) ASCII(SUBSTR(MACHINE,14,1))

--------------- --------------- ---------------------------
DAVIDF                        6
DAVIDF                        6
DAVIDF                        6
DAVIDF                        6
DAVIDF                        6
DAVIDF                        6
SYSDIV\DAVIDF                14                           0

A little basic math would have brought this discrepancy to light, and a little further digging into string termination in C/C++ would have completed the picture. :) Received on Mon Jun 03 2002 - 08:43:37 CDT