Re: View from a stored procedure

Can you do that by using ref cursors? E. g. define a function returning a ref cursor result and then fetching from that opened ref cursor?

Martin

Michel Cadot wrote:
>
> Sorry for not answering you earlier.
>
> You cannot use a PL/SQL in the definition of the view but you can call a
> stored procedure.
>
> For example (that has no meaning!):
>
> create table t (col number);
> ...
> select * from t;
> COL
> ----------
> 0
> 1
> 2
> 3
> 4
> 5
>
> create or replace function f (val number) return varchar2 is
> result varchar2(2000);
> i number;
> begin
> result := '';
> for i in 1..val loop
> if result is not null then
> result := result || ',';
> end if;
> result := result || i;
> end loop;
> return result;
> end;
> /
>
> create or replace view v as select col, substr(f(col),1,50) val from t;
> select * from v;
>
> COL VAL
> ---------- --------------------------------------------------
> 0
> 1 1
> 2 1,2
> 3 1,2,3
> 4 1,2,3,4
> 5 1,2,3,4,5
>
> select val from v where col=3;
>
> VAL
> --------------------------------------------------
> 1,2,3
>
> 1 row selected.
>
> select col from v where val like '%2,%';
>
> COL
> ----------
> 3
> 4
> 5
>
> 3 rows selected.
>
> Hope this will help you.
>
> Filip Hanik a écrit dans le message ...
> >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 Sat Sep 04 1999 - 03:14:27 CDT