Re: PL/SQL problem

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 07 Feb 2003 07:17:45 +0000
Message-ID: <b1vfin$eok$1@ctb-nnrp2.saix.net>

Paul Brewer wrote:

> Billy,
>
> It can be used in SQL statements contained in PL/SQL blocks, but not I
> think within PL/SQL code.

Paul, I prefer no to "think", but to actually try it and Oracle and see what Oracle "think" about it.

Oracle 8.1.7 on HP-UX.

SQL> create or replace function YNtoTF( yesno char ) return varchar as   2 truefalse varchar2(5);
  3 begin
  4 select

  5      decode( upper(yesno), 'Y', 'TRUE', 'FALSE' ) 
  6      into truefalse from dual;

  7 return( truefalse );
  8 end;
SQL> / Function created.

SQL> select
  2 YNtoTF( 'Y' )
  3 from dual;

YNTOTF('Y')

---

TRUE SQL> select
  2 YNtoTF('crap')
  3 from dual;

YNTOTF('CRAP')

---

FALSE SQL> Is the above not PL/SQL code using a DECODE function?

Or are we're going to get into semantic definitions of what is PL/SQL code, SQL code, what is not, and crap like that?

The fact is that DECODE *can* be used in PL/SQL. You simply need to know how to use it. Instead of confusing definitions and quoting 3rd party books (which surprise, surprise, not all of us have), I would think a poster with a problem would want to know *how* to use the DECODE statement and not told half untruths that DECODE is not a PL/SQL statement, thus implying that it can not be used in PL/SQL at all.

--
Billy

Received on Fri Feb 07 2003 - 01:17:45 CST