Re: SUBSTR(str,n) error

there is complete example:

 declare
 mach varchar2(100);
 begin
 select min(machine) into mach from v$session where username=user;  dbms_output.put_line(mach);
 dbms_output.put_line(length(mach));
 mach := substr(mach,6);
 dbms_output.put_line(mach);
 dbms_output.put_line(length(mach));
 mach := mach||'0123456789';
 dbms_output.put_line(mach);
 dbms_output.put_line(length(mach));
 end;

it results in

13:33:17 SPA.SPAC.GWSPACER> /
GWNT\NT-VELVET
15
NT-VELVET
10
NT-VELVET
20

I tested it on several instalations. There is always the same wrong result.
Can You explain this?

Marcin Buchwald wrote:

> 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
Received on Mon Jun 03 2002 - 06:39:28 CDT