Alternative ways of getting the return value of a FUNCTION when using DBMS_SQL

From: <tmcguiga_at_my-dejanews.com>
Date: Fri, 30 Oct 1998 15:43:44 GMT
Message-ID: <71cmrg$qjn$1@nnrp1.dejanews.com>

This question is standalone, but arisis from my earlier posting "CAn DBMS_SQL be used to get the RETURN from a PL/SQL FUNCTION?".

It appears that there are a few ways to capture the return from a FUNCTION under DBMS_SQL.

Let me examplifiy this. I have a PACKAGE called 'MyPackage' which is to call a FUNCTION 'FunctionName' in a PACKAGE called 'PackageName'

In order to get the RETURN value from PackageName.FunctionName I have, using DBMS_SQL, the following alternatives

[1] Declare a global variable in MyPackage, e.g. FnReturn, and have
FunctionName populate that.

    DBMS_SQL.PARSE( SQLHandle,

                    'BEGIN MyPackage.FnReturn := ' ||
                    PackageName || '.' || FunctionName || '; END;',
                    DBMS_SQL.NATIVE);

    SQLResult := DBMS_SQL.EXECUTE( SQLHandle );

[2] SELECT from DUAL and use DBMS_SQL.COLUMN_VALUE.

    DBMS_SQL.PARSE( SQLHandle, 'SELECT ' ||

                    PackageName || '.' || FunctionName || ' FROM DUAL',
                    DBMS_SQL.NATIVE);

    ...
    DBMS_SQL.COLUMN_VALUE(SQLHandle, 1, Result);

[n] I'm sure there are many more alternatives (using a pipe, table, etc).

The question is which method should be preferred?

There are some surrounding issues, e.g. in [2] if the function PackageName.FunctionName does not guarantee not to update the database an ORA- 06571 exception will be raised by DBMS_SQL.PARSE.

Finally, as a seperate point; whilst working on this PACKAGE I thought it would be usefull if it could use either Oracle WebServer's PL/SQL Toolkit for HTML output or DBMS_OUTPUT.PUTLINE. This idea fails immediately because if I have any references to HTP or HTF when they are not present my PACKAGE will be marked INVALID.

There seems to be no way around this 'use if present' approach, or is there?

I could try trickery via DBMS_SQL but this isn't exactly efficient! I could have two different PACKAGEs but I'd have preferred to have one. Wish wish wish!

Thanks for your time and any replies.

Tiernan.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 30 1998 - 09:43:44 CST