Re: Procedure compilation error

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 24 Feb 2000 12:10:36 +0100
Message-ID: <8933jv$2fis$1@news6.isdnet.net>

You can't execute directly DDL statements in your procedure.

If you are in Oracle8i, you can use 'execute immediate': execute immediate 'create or replace view testview1 as select * from emp';

Before 8i, you can create a procedure and call it:

create or replace procedure ExecuteSQL (codeSql varchar2)

   is

      cid      integer;
      resultat integer;
   begin
      cid := dbms_sql.open_cursor;
      dbms_sql.parse (cid, codeSql, dbms_sql.native);
      resultat := dbms_sql.execute (cid);
      dbms_sql.close_cursor (cid);
   exception
      when others then
         if dbms_sql.is_open (cid) then
           dbms_sql.close_cursor (cid);
         end if;
         raise;

   end ExecuteSQL;
/

and then add in your procedure:
executeSql ('create or replace view testview1 as select * from emp');

--
Have a nice day
Michel

kev <kevin.porter_at_fast.no> a écrit dans le message : 38B506D3.2134DDA0_at_fast.no...
> Hi,
>
> Sorry if this is really obvious, but I'm very new to all this PL/SQL
> malarkey. I want a procedure to create a view. Here's the procedure:
>
> -------------------------------------
> create or replace procedure viewproc
> ( ns in varchar2, nt in varchar2, nvid in varchar2 )
> as
> begin
> create or replace view testview1 as
> select * from emp;
> end viewproc;
> -------------------------------------
>
> it fails to compile: here's what the error message says:
>
> SQL> show errors
> Errors for PROCEDURE VIEWPROC
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 5/1 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> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall
> <a single-quoted SQL string>
>
>
> What have I done wrong?
>
> thanks,
>
> - Kev
>
Received on Thu Feb 24 2000 - 05:10:36 CST