Re: SUBSTR(str,n) error

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Jun 2002 04:37:12 -0700
Message-ID: <adfkd80307a@drn.newsguy.com>

In article <3CFB356A.CD5B9AD5_at_agora.pl>, Marcin says...
>
>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
>

sigh, no version, no error messages -- nothing to indicate what is happening on your machine.

All I can say is I ran:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare   2 vmachine varchar2(20) default 'GWNT\NT-VELVET' || chr(0);   3 begin

  4          vmachine := substr( vmachine, 5 );
  5          dbms_output.put_line( length(vmachine) );
  6          for x in ( select dump(vmachine) d from dual )
  7          loop
  8                  dbms_output.put_line( '"' || x.d || '"' );
  9                  dbms_output.put_line( '"' || vmachine || '"' );
 10          end loop;

 11 end;
 12 /
11
"Typ=1 Len=11: 92,78,84,45,86,69,76,86,69,84,0" "\NT-VELVET PL/SQL procedure successfully completed.

on 734, 806, 817, 901 without fail. Now, the interesting thing to note is that the line:

"\NT-VELVET is missing the trailing quote. That is because sqlplus is written in C using C printf's and C strings and in C a '\0' character means "end of string" and so the trailing '"' is "missing" from the output. Perhaps this display issue is part of your issue.

The length and concatenation are most likely working -- it is just that the OUTPUT of same is not since C stops when it sees the '\0'.

Can you be more detailed in the description of the error, including actual cut and pastes demonstrating what you mean?

>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
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 03 2002 - 06:37:12 CDT