Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: View from a stored procedure
Yes, you can but you must have the create view privilege yourself
and not within a role.
And then, for example:
SQL> create table t (col number);
Table created.
SQL> select * from v;
select * from v
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create or replace procedure p
2 is
3 cid integer; 4 result integer; 5 6 begin 7 cid := dbms_sql.open_cursor; 8 dbms_sql.parse (cid, 9 'create view v as select distinct col from t', 10 dbms_sql.v7); 11 result := dbms_sql.execute (cid); 12 dbms_sql.close_cursor (cid); 13 14 exception 15 when others then 16 if dbms_sql.is_open (cid) then 17 dbms_sql.close_cursor (cid); 18 end if; 19 raise;
Procedure created.
SQL> exec p;
PL/SQL procedure successfully completed.
SQL> select * from v;
no rows selected
Filip Hanik a écrit dans le message ...
>Hi ya all,
>hope you all are doing well.
>I have a question about views.
>Can I create a view from a stored procedure where I can do calculations etc?
>If so, how would I do it?
>
>thank you in advance
>
>Filip
>fhanik_at_digitalworkforce.net
>
>
Received on Thu Aug 26 1999 - 05:04:30 CDT