This is not a normal piece of database functionality. However you are not the first person who has asked for this, or something like it.
The solution requires two things. The first is the data dictionary; the Oracle database doesn't support Reflection but it does come with a set of views which give us metadata about our database objects. In this case we need user_tab_columns
, which will give us the columns for a given table. The second thing is dynamic SQL; this is the ability to assemble a SQL query at runtime and then execute it. There are a couple of ways of do ing this, but usually ref cursors are sufficient.
The following code is a proof of concept. It takes four parameters:
- the name of table you want to search
- the name of that table's primary key
column
- the primary key value you want to
restrict by
- the value you want to search for.
It is rough'n'ready so you may need to edit it to tidy the output or to make the program more flexible.
create or replace procedure search_cols
(tname in user_tables.table_name%type
, pk_col in user_tab_columns.column_name%type
, pk in number
, val in number )
is
firstcol boolean := true;
stmt varchar2(32767);
result varchar2(32767);
rc sys_refcursor;
begin
stmt := 'select ';
<< projection >>
for lrec in ( select column_name from user_tab_columns
where table_name = tname
and column_name != pk_col
and data_type = 'NUMBER'
order by column_id )
loop
if not firstcol then
stmt := stmt || chr(10) || '||'',''||';
else
firstcol := false;
end if;
stmt := stmt || ' case when '|| lrec.column_name||' = '|| val ||
' then '''|| lrec.column_name || ''' else null end';
end loop projection;
stmt := stmt || chr(10)|| ' from '||tname||' where '|| pk_col || ' = '|| pk;
-- dbms_output.put_line(stmt);
open rc for stmt;
fetch rc into result;
close rc;
dbms_output.put_line(tname || '::' || val || ' found in '||result);
end search_cols;
/
As you can see, dynamic SQL is hard to read. It is harder to debug :) So it is a good idea to have a means to show the final statement.
Anyway, here are the results:
SQL> set serveroutput on size unlimited
SQL> exec search_cols('T23', 'ID', 111, 10)
T23::10 found in ,COL_B,COL_C,
PL/SQL procedure successfully completed.
SQL> exec search_cols('T23', 'ID', 222, 10)
T23::10 found in COL_A,,,
PL/SQL procedure successfully completed.
SQL>