Re: Implementation of boolean types.

From: Chris ( Val ) <chrisval_at_bigpond.com.au>
Date: 17 Jul 2005 19:55:17 -0700
Message-ID: <1121655317.475571.269470@o13g2000cwo.googlegroups.com>

Matthias Hoys wrote:
> "Ach C via DBMonster.com" <forum_at_DBMonster.com> wrote in message
> news:517044239B0E4_at_DBMonster.com...
> >
> > Who is responsible to INTERPRET the meaning of 0 and 1? Your
> > application? I
> > do not like this secret coding style! I prefer char(1) or even
> > varchar2(5)
> > in this case.
> > -Regards
> >
> >
> > --
> > Message posted via DBMonster.com
> > http://www.dbmonster.com/Uwe/Forums.aspx/oracle/200507/1
>
> See in my other post :
>
> You could then link the table to a small lookup table with the description
> of the 0 and 1.
> 0 = male
> 1 = female

Or call a PL/SQL function with a temporary declared type:

CREATE OR REPLACE FUNCTION GetGender( GenderType IN PLS_INTEGER )   RETURN VARCHAR2 AS
    TYPE Gender IS TABLE OF VARCHAR2( 7 )       INDEX BY PLS_INTEGER;     PersonGender Gender;
  BEGIN
    PersonGender( 0 ) := 'male';
    PersonGender( 1 ) := 'female';

    RETURN PersonGender( GenderType );
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;
/

COLUMN Gender FORMAT a10
SQL> SELECT NVL( GetGender(0), 'NULL' ) "Gender" FROM DUAL;

Gender

---

male

SQL> SELECT NVL( GetGender(1), 'NULL' ) "Gender" FROM DUAL;

Gender

---

female

SQL> SELECT NVL( GetGender(2), 'NULL' ) "Gender" FROM DUAL;

Gender

---

NULL  
> It's called a relational database ;-)

Yep ;-)

Cheers,
Chris Val Received on Sun Jul 17 2005 - 21:55:17 CDT