Create dynamic View in Oracle


I have a View named "VW_COLUMN_NAMES" created that has names of columns. It is dynamic. So the number or rows in this view changes depending on the query result.

I want to use the values from this view and create another view lets say "VW_OUTPUT" with column names that is return from the selection of "VW_COLUMN_NAMES". How can I do that?


VIEW: VW_COLUMN_NAMES has a column named "COLUMN_NAMES" with 3 rows


I want to create a dynamic View based on this View and create another view lets say "VW_OUTPUT"

The number of columns in the above selection list should change based on the number of rows in VW_COLUMN_NAMES.

Please help.Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You can probably create a stored procedure that will drop the previous view then build the create view syntax BUT, this is ALWAYS a really bad idea.

What are you hoping to gain by constantly dropping and creating views?
What if two people need this at the same time?

If you can provide more details about your requirements we can try to guide you in a better direction.

If you still want it, look around for EXECUTE IMMEDIATE in PL/SQL.  There are many examples.
angel7170Author Commented:
The problem we have is because we are doing data quality for some tables. If there is an issue with any of the columns in a table, we then have to report it. So the number of columns can vary from 1 to many depending on if there is an issue or not.  That is why it has to be dynamic. Only one account is using it at any given time so that shouldn't be a problem.
slightwv (䄆 Netminder) Commented:
I would still suggest you take a look at a better way to handle this but since you think it is what you want,
try this:
drop table tab1 purge;
create table tab1(column_names varchar2(30));

insert into tab1 values('Name');
insert into tab1 values('Age');
insert into tab1 values('Location');

create or replace procedure myCreateView
	v_sql	clob;
	select 'create or replace view vw_ouptut as select ' || listagg(column_names,',') within group(order by column_names) || ' from test_table'
		into v_sql from tab1;
	execute immediate v_sql;

show errors
exec myCreateView;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

angel7170Author Commented:
Thank you so much! You are correct. The approach that I am taking may not be an ideal one and I can't think of any :)

I really appreciate all your help!
Geert GOracle dbaCommented:
Why not use a function whcih returns a sysrefcursor ?
And then
Select from table(yourfunction)
slightwv (䄆 Netminder) Commented:
>>Why not use a function whcih returns a sysrefcursor ?

I like that concept better but I'm not sure you can cast a ref cursor as a table?  That would involve dynamic columns and that is sort of the Holy Grail of Oracle.  I welcome an example!
angel7170Author Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.