"Jim Starkey" <jas@...> wrote:

>
> It is certainly possible to write a procedure that does something
> meaningful if invoked in either environment, though it is unlikely that
> anyone has actually done this. The fact that the procedure can't tell
> which way it was invoked is kind of a showstopper. But if someone did,
> he either knew or should have known that this was entirely too cute to
> work in the long run. And if not, next time he'll know.

CREATE PROCEDURE P_CURRENT_SESSION
RETURNS (SESSION_ID INT)
AS
BEGIN
SELECT ID
FROM SYS_SESSIONS
WHERE CONNECTION_ID = CURRENT_CONNECTION
INTO :SESSION_ID;

SUSPEND;
END;

Then I may use this procedure in another SPs:

...
EXECUTE PROCEDURE CURRENT_SESSION
RETURNING_VALUES :SESSION_ID;
...

This is very unnaturally for me to make a select to always return a single
value. But also I may use this procedure to limit some rowset:

SELECT ... WHERE SESSION_ID = ( SELECT SESSION_ID FROM P_CURRENT_SESSION )

Believe me, you'll find enough such examples. This usage is 100% legal.

Taking this example apart, I agree with your other words.


Dmitry