Re: SUBSTR(str,n) error

From: Mark D Powell <mark.powell_at_eds.com>
Date: 3 Jun 2002 09:29:52 -0700
Message-ID: <178d2795.0206030829.40fa3a@posting.google.com>

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

Marcin, it might help if you posted your Oracle Version, Platform, and actual code as I am unable to duplicate your problem:

• Test ran on 8.1.7.2 -- UT1> set echo on UT1> set serveroutput on UT1> declare 2 v_machine v$session.machine%type; 3 v_len number; 4 v_ascii varchar2(60); 5 begin 6 select machine, length(machine), ascii(substr(machine,length(machine),1)) 7 into v_machine, v_len, v_ascii 8 from v$session 9 where sid = (select sid from v$mystat where rownum = 1); 10 dbms_output.put_line('Machine => '||v_machine); 11 dbms_output.put_line('Length => '||v_len); 12 dbms_output.put_line('VASCII => '||v_ascii); 13 end; 14 / Machine => ddcdev1 Length => 7 VASCII => 49

PL/SQL procedure successfully completed.

UT1> select chr(49) from dual;

C
-
1

UT1>

Everthing appears as expected in the above test.

IMHO -- Mark D Powell -- Received on Mon Jun 03 2002 - 11:29:52 CDT