OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Decode issues

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Decode issues [message #9482]

Fri, 14 November 2003 08:03

Luke Shannon
Messages: 3
Registered: November 2003

Junior Member

Hi;

I am trying to amend an existing procedure. Below is the executable section.

Basically I want to ensure that in the where clause we are never misusing a NULL from SYSDATE (if dt_last_updated is NULL we will use the value of dt_create which can never be NULL).

I can run the SELECT DECODE statement on its own without error. But if I try and run it in the procedure I get the error:

Compilation errors for PROCEDURE EMESSENGER.EA_SP_GET_MESSENGER_TIMEDOUT

Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Line: 18
Text: WHERE ((SYSDATE - (SELECT * FROM (SELECT DECODE(dt_last_updated, NULL, dt_create, dt_last_updated)nonNullDate FROM ea_messenger)) ) * 24 * 60 * 60 * 1000) >

I am not experienced in Oracle and am not sure what to try now.

Any suggestions/tips/comments would be greatly appreciated.

Thanks,

L
OPEN RC1 FOR
SELECT n_session
, s_messenger
FROM ea_messenger
WHERE ((SYSDATE - (SELECT * FROM (SELECT DECODE(dt_last_updated, NULL, dt_create, dt_last_updated)nonNullDate FROM ea_messenger)) ) * 24 * 60 * 60 * 1000) >
ea_sp_get_messenger_timedout.n_timeout
AND n_socketenabled = 0;
--timeout period has elapsed

Report message to a moderator

Re: Decode issues [message #9483 is a reply to message #9482]

Fri, 14 November 2003 08:30

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Is there only one row in ea_messenger?

Anyhow, what about NVL?

OPEN rc1 FOR
    SELECT ea.n_session
    ,      ea.s_messenger
    FROM   ea_messenger     ea
    WHERE  ((SYSDATE - NVL(ea.dt_create,ea.dt_last_updated)) * 24 * 60 * 60 * 1000) >
        ea_sp_get_messenger_timedout.n_timeout
    AND    ea.n_socketenabled = 0;
--timeout period has elapsed

And I just wanted to double-check with you: obviously n_timeout is measured in milliseconds?

I hope this helps,

Art.

Report message to a moderator

Re: Decode issues [message #9484 is a reply to message #9483]

Fri, 14 November 2003 08:41

Luke Shannon
Messages: 3
Registered: November 2003

Junior Member

There is more than one row in ea_messenger.

Hmm, n_timeout is a number. It contains a second value in run time.

I am having trouble to get NVL to work. Will this work in 8i?

Thanks very much,

L

Report message to a moderator

Re: Decode issues [message #9485 is a reply to message #9483]

Fri, 14 November 2003 09:20

Luke Shannon
Messages: 3
Registered: November 2003

Junior Member

NVL works great! A nice addition to my Oracle Tool Box.

My procedure now compiles and runs.

Thanks Art!

Report message to a moderator

Previous Topic:	set autotrace on ; returns a error
Next Topic:	Package is getting INVALID after execution

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:13:40 CDT 2024