Re: View from a stored procedure

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 1 Sep 1999 11:32:25 +0200
Message-ID: <7qirsg$47f$1@oceanite.cybercable.fr>

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 Wed Sep 01 1999 - 04:32:25 CDT