RE: Thanks

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 01 Feb 2002 14:17:54 -0800
Message-ID: <F001.00403E94.20020201141350@fatcity.com>

> -----Original Message-----
> From: Hamid Alavi [mailto:hamid.alavi_at_quovadx.com]
>
> Thanks for reply but i want the function do this:
> send the table_name & column_name & ID as input and return
> the description
> like this:
> create or replace function f_get_desc
> (table_name,column_name,code_in in
> varchar2)
> if we have a table like languages_lan(lan_id,lan_des) the
> function must get
> the following parameter as input and return desc as output
> languages_la,lan_id,1 as input then return French as description.

You can use the example I sent you and adapt it for accepting as input the table name/column name. However, you need two column names: the id column name and description column name. 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

  2    (table_name in varchar2,
  3     code_column_name in varchar2,
  4     description_column_name in varchar2,
  5     code_id in varchar2)
  6     return varchar2
  7  is
  8     return_desc varchar2 (30) ;
  9  begin
 10     execute immediate 'select ' || description_column_name ||
 11        ' from ' || table_name || ' where ' || code_column_name ||
 12        ' = :id' into return_desc using code_id ;
 13     return return_desc ;
 14  end ;
 15  /

Fonction créée.

SQL> select f_get_desc ('lu_country', 'country_code', 'country_description', 'CH') from dual ;

F_GET_DESC('LU_COUNTRY','COUNTRY_CODE','COUNTRY_DESCRIPTION','CH')

---

Switzerland Received on Fri Feb 01 2002 - 16:17:54 CST