Click here to Skip to main content
15,037,178 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
i am new in oracle and still learning stored procedures....
I want to create a stored procedure which will generate Views comprise of different tables(lets say for only two tables)....

Please help me... i dont know how to create it.
Your help would be appreciated greatly..

Thank you
USER
Posted

Hi,

Please check following query, hope it helps,
SQL
CREATE OR REPLACE PROCEDURE procname AS

BEGIN

execute IMMEDIATE 'CREATE OR REPLACE VIEW viewname AS
     SELECT col1,col2
     FROM table1
     UNION ALL
     SELECT col1, col2
     FROM table2;
END;

Thanks,
Hitesh
   
If you want to create a view within a SP you need to use dynamic SQL.


SQL
create procedure Procvewsample
as
exec ('create view testView as select a as Col')


Code block fixed
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900