RE: Function for Geting Description

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 01 Feb 2002 13:31:38 -0800
Message-ID: <F001.00403D69.20020201133326@fatcity.com>

> -----Original Message-----
> From: Hamid Alavi [mailto:hamid.alavi_at_quovadx.com]
>
> I want to define a function for lookup tables like this
> f_get_lookup_value(<table_name>, <column_name>, <id_value>) returns
> description
> which send the table_name,column_name & ID then function return the
> description,
> Can we use dba_tables to pass the table_name& column_name
> then check with ID
> and return the description from lookup tables or not?
> Any Idea?

I'm not sure what you mean by "use dba_tables to pass the table_name & column_name". You can use that view to verify the table_name and column_name. Or if you have a set of tables with very similar table/column names:

e.g.

LU_STATE STATE_CODE/STATE_DESCRIPTION
LU_TITLE TITLE_CODE/TITLE_DESCRIPTION
LU_DEGREE DEGREE_CODE/DEGREE_DESCRIPTION

you can use dynamic SQL to retrieve the ID. For example:

SQL> describe lu_country

 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ------------
 COUNTRY_CODE                                       VARCHAR2(2)
 COUNTRY_DESCRIPTION                                VARCHAR2(30)

SQL> select * from lu_country ;

CO COUNTRY_DESCRIPTION

-- ------------------------------

F  France
CH Switzerland

SQL> create or replace function f_get_desc (code_type in varchar

  2                              code_id in varchar2)
  3     return varchar2
  4  is
  5     return_desc varchar2 (30) ;
  6  begin
  7     execute immediate 'select ' || code_type ||
  8        '_description from lu_' || code_type ||
  9        ' where ' || code_type || '_code = :id'
 10        into return_desc using code_id ;
 11     return return_desc ;

 12  end ;
 13  /

Fonction créée.

SQL> select f_get_desc ('country', 'CH') from dual ;

F_GET_DESC('COUNTRY','CH')

---

Switzerland Received on Fri Feb 01 2002 - 15:31:38 CST