Executing view within a stored procedure

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;

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