Create dynamic View in Oracle

Hi,

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?

Example

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

Name
Age
Location

I want to create a dynamic View based on this View and create another view lets say "VW_OUTPUT"
CREATE OR REPLACE VIEW VW_OUTPUT AS
SELECT NAME, AGE, LOCATION FROM TEST_TABLE ...........

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
angel7170Asked:
Who is Participating?
 
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');
commit;


create or replace procedure myCreateView
is
	v_sql	clob;
begin
	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;
end;
/

show errors
		
exec myCreateView;

Open in new window

0
 
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.
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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!
0
 
Geert GOracle dbaCommented:
Why not use a function whcih returns a sysrefcursor ?
And then
Select from table(yourfunction)
0
 
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!
0
 
angel7170Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.