Re: Using DECODE in stored function

Terry Walker <terryw> wrote:

>Can anyone tell me why Oracle does not allow us to use the DECODE function
>inside a stored function?
 

>I get the message: "ORA-1403: No data found" when I try to invoke the function.
>However, I do not get any compile errors when the function is created. Thanks
>in advance.

I've also encountered this problem. Usually when I try to do something like this in a stored object:

cursor c1 is
  select

             tab1.col1
            ,tab2.,col2
  from
             tab1
            ,tab2
  where
             tab1.col1 = decode(tab1.col1
                                           ,0,tab2.col1
                                           ,   tab2.col2)

This sort of thing works fine when run from SQL*Plus and should also work when embedded in a stored object. I'm pretty sure that it worked in release 7.0.14 or the database and stopped working with 7.1.x So I believe it's a bug.

If anyone knows more about it, I'd love to know the scoop.

Mike    

>--
>============================================================================
>Terry Walker Internet: terryw_at_kgf.com
>Kraft Foods twalker_at_kraft.com
>250 North Street N1-3
>White Plains, NY 10625
>(914) 335-4385 Fax - 2015 "I'm not paranoid, everyone IS out to get me!"
>============================================================================
Received on Mon Jan 22 1996 - 00:00:00 CET