Re: View from a stored procedure

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 6 Sep 1999 12:11:43 +0200
Message-ID: <7r047k$243$1@oceanite.cybercable.fr>

Yes, you can do something like that (using the same function and date from my previous example):

v734>set serveroutput on
v734>
v734>declare
  2     type my_record is record (
  3        col number,
  4        val varchar2(100)
  5     );
  6     type my_cursor is ref cursor return my_record;
  7     curs my_cursor;
  8     rec my_record;
  9
 10     procedure g (curs in out my_cursor) is
 11     begin
 12        open curs for select col, f(col) val from t;
 13     end;

 14
 15 begin
 16

 17     dbms_output.enable(1000000);
 18     g(curs);
 19     loop
 20        fetch curs into rec;
 21        exit when curs%notfound;
 22        dbms_output.put_line ('col='||rec.col||', val='||rec.val);
 23     end loop;
 24     close curs;

 25
 26 end;
 27 /

col=0, val=
col=1, val=1
col=2, val=1,2
col=3, val=1,2,3
col=4, val=1,2,3,4
col=5, val=1,2,3,4,5

PL/SQL procedure successfully completed.

Martin Haltmayer a écrit dans le message <37D0D4E3.8668E5EB_at_in.augsburg.net>...
>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 Mon Sep 06 1999 - 05:11:43 CDT