Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Function to do This?
DelphiDev wrote:
>
> What is the easiest way to retrieve the value of columns from "Lookup"
> tables (a table whose primary key is another tables foreign key)? I
> would like to create a function which would accept three parameters:
>
> - A Table name (i.e. the lookup table - has a primary key which can
> span more than one column)
> - Search String
> - Return Column Name (the name of the column that you want the value
> of for the searched string)
>
> The function will do a search for Search String on the Primary Key of
> the Table. I know this is just a basic "JOIN" using primary and foreign
> keys. But is there a way that I could write a simple function to do
> this? Is this something that Oracle can do automatically? It has all
> of the necessary information.
>
> Some examples of calls:
> LookUp('JOBS','VP','JOBDESC'); --Returns "Vice President"
> LookUp('CITIES','CA'+'SF','ZIPCODE') --Returns ZipCode for S.F. CA
> (Note, CITIES primary key is "STATECODE+CITYCODE");
>
> Thanks, Stan.
For a "finite" list of possibilities, you can use package overloading as another poster has indicated...
If you want a truly generic pl/sql routine, you would need to use dbms_sql to prepare your sql string dyanmically...
HTH
--