Return value of PL/SQL-functions

From: Stephan Hüttner <stephan.huettner_at_hackenberg.de>
Date: 1997/01/07
Message-ID: <32D29392.3490@hackenberg.de>#1/1

A very mindbugging problem we still have to deal with is, that 'hand made' PL/SQL-functions with character return values always give back something other than Oracle functions.
For example 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!

Every return value seems is turned in a kind of long string regardless of the defined function type (CHAR, VARCHAR, VARCHAR2).
We got some interesting clues for this behaviour but none of them hit the point. What I need is to create PL/SQL functions that return real strings whithout having to alter the function call within the SQL statements.

A deeper look into the standard package of oracle (standard.sql) showed the definition of SUBSTR(). SUBSTR() itself calls a function pessub().

function SUBSTR( STR1 VARCHAR2, POS BINARY_INTEGER,

                LEN BINARY_INTEGER := NULL )
        return varchar2 is
  begin
        return pessub( STR1, POS, LEN );

  end SUBSTR;

It should be possible to call internal functions like pessub() within PL/SQL functions. Are these internal functions documented? What do I have to do to make use of them?

Thanks for any help!

Stephan Hüttner
Hackenberg & Partner
Germany Received on Tue Jan 07 1997 - 00:00:00 CST