Re: Create View from within Stored Procedure?

You need to use the dbms_sql package to do that. However I think any DBA will consider view creation on the fly in a stored procedure extreemly bad practice.
As Oracle has inline views you should ask yourself whether you really need them.

Regards,

Sybrand Bakker, Oracle DBA

"David L. Hoffman" <davidh_at_abacus96.com> wrote in message news:hYkH5.31$BE5.770_at_client...
> Hi, sorry if this is the world's dumbest question:
>
> Is it possible to create a stored procedure which creates a view?
>
> It seems not, based on the results shown below (using Oracle 7.3). Or am
 I
> just missing something?
>
> Many thanks-
> -David Hoffman
> -------------------------------------------------------------
> SQL> create or replace procedure TESTY
> 2 AS BEGIN
> 3 CREATE VIEW TESTVIEW AS
> 4 SELECT * FROM BROKER;
> 5 END;
> 6 /
>
> Warning: Procedure created with compilation errors.
>
> SQL>
> SQL>
> SQL>
> SQL> show errors
> Errors for PROCEDURE TESTY:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 3/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of
> the following:
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable>
> <a single-quoted SQL string> << close delete fetch lock
> insert open rollback savepoint set sql commit
>
>
>
Received on Wed Oct 18 2000 - 16:40:44 CDT