Re: View from a stored procedure

Hi Michel,
thanks for you reply, unfortunately I realized that I formulated my question in a vague manor.
What I really want is to have the view read its rows from a stored procedure.
So instead of having the view just be a SQL statement I'd like the view to have a stored procedure as data fetch instead.

CREATE VIEW v AS
BEGIN
  --PL/SQL code here instead of a regular sql statement   --if this would work how would the end line look like that actually selects the data?
END Is this possible?

thanks
Filip

Michel Cadot <micadot_at_netcourrier.com> wrote in message news:7q33gk$7va$1_at_oceanite.cybercable.fr...
> 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;
> 20
> 21 end p;
> 22 /
>
> 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 - 13:03:18 CDT