> -----Original Message-----
> From: Hamid Alavi [mailto:[EMAIL PROTECTED]]
>
> 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
