Return value of PL/SQL-functions

From: Stephan Huettner <sh_at_hackenberg.de>
Date: 1996/12/19
Message-ID: <32B9B278.76F2@hackenberg.de>#1/1

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 Received on Thu Dec 19 1996 - 00:00:00 CST