Re: Return value of PL/SQL-functions

It's because substr( variable, CONSTANT1, CONSTANT2 ) is known to return a string that is CONSTANT2-CONSTANT1+1 bytes long.

my_substr( variable, CONSTANT1, CONSTANT2 ) could return anything of any length. Hence, if it returns CHAR, sql*plus will assume it is up to 255 bytes. If varchar2, 2000 bytes.

Even substr can't always be relied on to return a fixed length string. Consider the following example:

SQL> variable n number
SQL> variable m varchar2(200)
SQL> exec :n := 2;
SQL> exec :m := 'Hello';

SQL> select substr( 'hello', 1, 2 ) from dual; -- 2 bytes long since 2-1+1 = 2

SU

--
he
 
SQL> variable n number
SQL> exec :n := 2;
 
PL/SQL procedure successfully completed.
 
SQL> select substr( 'hello', 1, :n ) from dual; 
-- Now since constant2 is unknown but the string is 5 bytes, the
-- max length is 5 bytes
SUBST
-----
he
 
SQL> variable m varchar2(2000)
SQL> exec :m := 'Hello' ;
 
PL/SQL procedure successfully completed.
 
SQL> select substr( :m, 1, :n ) from dual;
-- now that neither the max string length NOR the constant2 is known
-- substr returns a really long string... (not really, it returns 2 characters
-- sqlplus just reserves lots of space for it cause it *could* be really long)
 
SUBSTR(:M,1,:N)
--------------------------------------------------------------------------------
He
 
SQL> 



So, substr actually behaves like your function when the inputs are not
constants.  If you want sql*plus to format your data tho, you can always do the
following:


SQL> column c1 format a20
SQL> select my_substr( a, b, c ) c1 from T

That will force sql*plus to format the result of my_substr in a field of 20
characters..


On Thu, 19 Dec 1996 13:24:08 -0800, Stephan Huettner <sh_at_hackenberg.de> wrote:



>A very mindbugging problem we do have to deal with is, that 'hand made' 

>PL/SQL-functions with caharcter return values always give back something other 

>than Oracle functions.

>Every return value seems to bo turned in a kind of long string regardless of the 

>defined function type (CHAR, VARCHAR, VARCHAR2).

>

>A self written funtion like

>

>	CREATE OR REPLACE FUNCTION my_substr(

>	  str_Input IN VARCHAR2,

>	  n_Start IN INTEGER,

>	  n_Len IN INTEGER )

>	RETURN CHAR IS

>

>	BEGIN

>	  RETURN SUBSTR( str_Input, n_Start, n_Len );

>	END my_substr;

>will not return the same as SUBSTR().

>

>You can see the difference in SQL*Plus:

>	SELECT c1, SUBSTR( c1, 1, 2 )

>	FROM   tyb;

>will return one line for each row.

>

>	SELECT c1, my_substr( c1, 1, 2 )

>	FROM   tyb;

>will show you two lines for each row!

>

>If anyone knows how to make a function like my_subtr functions to behave like 

>SUBSTR we' be very very happy!

>

>Thanks,

>Stephan Hüttner

>Hackenberg & Partner

>Germany


Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
                         
http://govt.us.oracle.com   

---- Check out Oracle Governments web site!  -----
     Follow the link to "Tech Center"
     and then downloadable Utilities for some free software...


-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation