Re: ORA-00920 when using function returning boolean...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Tue, 13 Sep 2005 11:14:05 +0200
Message-ID: <dg658t$9b5$1@nntp.fujitsu-siemens.com>

Maxim Demenko wrote:
> Volker Hetzer schrieb:
>

>> Maxim Demenko wrote:
>>
>>> select SessionId
>>>      from (select SessionId from CVC_EDDY1 UNION select SessionId from
>>>  CVC_EDDY2)
>>>      where 
>>> SYS.DIUTIL.BOOL_TO_INT(dbms_session.is_session_alive(SessionId)) = 1;
>>
>>
>> Tried it, still got the same error.
>> I've opened a TAR for this.
>>
>> Lots of Greetings!
>> Volker

>
>
> Sorry, i provided wrong example to you.
> That should work
>
> scott_at_ORA92> var usid varchar2(200)
> scott_at_ORA92> exec :usid := dbms_session.unique_session_id
>
> PL/SQL procedure successfully completed.
>
> scott_at_ORA92> create or replace function is_session_alive(p_usid in
> varchar2)
> 2 return number
> 3 is
> 4 l_retval number;
> 5 begin
> 6 l_retval :=
> sys.diutil.bool_to_int(dbms_session.is_session_alive(p_usid));
> 7 return l_retval;
> 8 end;
> 9 /
>
> Function created.
>
> scott_at_ORA92> select is_session_alive(:usid) from dual;

Yes, that's exactly what support told me today. So I rolled my little function too:
create or replace function session_is_alive(sid varchar2)

	return number
	is
	begin
		return sys.diutil.bool_to_int(dbms_session.is_session_alive(sid));
	end session_is_alive;
	/

Which worked like a charm too.

Lots of Greetings and thanks!
Volker Received on Tue Sep 13 2005 - 04:14:05 CDT