Is it possible to execute a view with a stored procedure?
My procedure should first run the views (I have 2 in total) and then am combining these 2 views data into a different table for applying transformations.
Something like this:
create or replace procedure procname as begin here my views should get triggered and then starts my execute immediate 'truncate table transtable'; insert ... ..... ... exception end;
Advertisement
Answer
What do you call “execute a view with stored procedure”? How are views “triggered”?
If you meant to ask whether you can create views in a procedure – then yes, you can – using execute immediate
. Though, in Oracle, that’s highly unusual practice. We create views first (at SQL level), use them everywhere (in SQL or PL/SQL) and that’s what I’d suggest you to do.
If you ask why?, reason is simple: dynamic SQL doesn’t scale, it is difficult to maintain and debug, and – if there’s nothing “dynamic” in it, don’t use it.
Moreover, if you reference those views later in your code (and they don’t exist at time of compilation), PL/SQL procedure will fail with ORA-00942 (table or view doesn’t exist) which means that the rest of your code should also be dynamic.
Nightmare at the horizon, in my opinion.
From what you posted so far, I don’t see why would you insist on doing that dynamically:
- create view “now” at SQL level
- use them in the procedure