Re: View from a stored procedure

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 26 Aug 1999 12:04:30 +0200
Message-ID: <7q33gk$7va$1@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 - 05:04:30 CDT