execute multiple views within a single stored procedure

[AlStl]

Is it possible to execute multiple views within a single stored procedure that feed off of each other? Here is an example.

I created following views:

CREATE OR REPLACE FORCE VIEW VIEW1 ( first_name, last_name, address )
AS Select First_Name, last_name, address from table1 Where Last_name=’SMITH’

CREATE OR REPLACE FORCE VIEW VIEW2 (names of columns....)
AS SELECT * FROM PEOPLE A WHERE EXISTS (SELECT * FROM VIEW1 B WHERE B.Address = A.Address);

There could be possible be a third view i.e. VIEW3 that could feed off of VIEW2 in the same store procedure.

My question is: How can I wrap 1,2,or 3 view in a single stored procedure that feed off each other?

Do I need to use Execute Immediate? If yes, what would be the syntax of this SP?

Thx,

Al

 

[carp]

AlStl -
These are not stored procedures. They are SQL DDL statements.
And yes, View3 can reference View1 and View2, just as any other select statement would.

Once a view is created, it persists, so there is no reason to put these statements into a stored procedure - unless you are doing so in case somebody drops your views and you need to create them. Then you would use EXECUTE IMMEDIATE. However, it might be just a simple (and even safer) to just retain a script that runs the three creation statements. This would preclude the person who drops the views from also dropping your procedure.

 

[AlStl]

carp,

It was an example and you are correct they are basic DDL's for creating Views. I did not include SP syntax that will encapsulate all three views.

I just wanted a single place to run all three in a squence and review my final view with data in it. I tought I could create a SP and wrap all three views in it and just execute the SP.

May be it was not a good idea.

Al

 

[carp]

Al -
Perhaps if you give us the pseudocode for what you are trying to do, we can be of more assistance.

 

[sem]

You may use CREATE SCHEMA statement


Regards, Dima

http://seminihin.narod.ru