Re: Return value of PL/SQL-functions

In article <32D29392.3490_at_hackenberg.de>, =?iso-8859- 1?q?Stephan_H=FCttner?= <stephan.huettner_at_hackenberg.de> writes
>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

What is required to call the pessub() function are the lines;

  function pessub(ch VARCHAR2, pos BINARY_INTEGER, len BINARY_INTEGER)

        return varchar2;
    pragma interface (c,pessub);

This is simply an internal reference to a stored Pro*C (or similar) type function.
It registers the function for use by the package.

I don't know of any documented use of this by developers (perhaps someone can expand on this? like Pro*C developers?), excepting in the similarity to the ORA_FFI package in Oracle Forms 4.5, where you may call MSWindows dynamic link library (.dll) functions. You might find the documentation there (Forms 4.5) helpful in understanding the requirements.

I think you would have to write your own packages to handle this, so I doubt you would gain anything by it as STANDARD will be in the SGA already anyway.

I've not tried it *yet*, but I'd be interested in hearing if/how you are successful, and why this is a requirement at your site.

Hope this helps.

-- 
Mike Proctor                    Database Consultants International (UK)

mp_at_MG-TC.demon.co.uk            Oracle vision.
                                Only Oracle.
                                All the time.

Turnpike evaluation. For information, see http://www.turnpike.com/