CODEWITHSUNDEEP
oracle-databaseselectdynamic
Kapil
Kapil

Reputation: 149

Select Query Column name based on Table name

I have like thousands tables, each of them will have their own unique key(s) columns. I want to select the keys based on the table name in a single query. Its always the second column in the table if it helps.

I want something like

    select c_name from t_name
    where c_name (is)
    (
    select column_name as c_name
    from Dba_tab_Columns 
    where table_name = t_name 
    and column_name like '%name' --->>>(((or column_id =2)))
    )

I know the t_name but I need a single query to select column name based on table_name.

So let say if I say select c_name from Animals, it should give me list of all animals and if I say select c_name from Cars, it should give me a list of avilable cars.

Upvotes: 0

Views: 129

Answers (1)

gsalem
gsalem

Reputation: 2028

You cannot do this in pure SQL, you'll need a table function. Here's a way:

create or replace type tvc as table of varchar2(128);
/


create or replace function return_col (tname user_tables.table_name%type) return tvc pipelined
as
c_statement varchar2(400);
get_data    sys_refcursor;
out_d       varchar2(128);
begin
    for gettnames_and_cols in (select  c.column_name
        from user_cons_columns c, user_constraints uc
        where constraint_type in ('P','U') and uc.table_name=c.table_name and c.table_name=upper(tname)) loop
            c_statement:='select '||gettnames_and_cols.column_name||' as output_col from '||tname;
            open get_data for c_statement;
            while true loop
                fetch get_data into out_d;
                exit when get_data%notfound;
                pipe row(out_d);
            end loop;
            close get_data;
    end loop;
    return;
end;
/

Thing is that this just gives the data, with no idea what's the column_name or from which table the data comes. You can modify the PL/SQL code to add this info. Also, this assumes that the data will be returned as VARCHAR2(128), you may need to adapt this to your needs. You use this table function as follows:

select *
from table (return_col('your_table_name'));

Upvotes: 1

Related Questions