Re: 'create procedure' problem

From: Arlette BROSSARD <abray_at_club-internet.fr>
Date: 9 Apr 1999 23:19:17 GMT
Message-ID: <01be82df$c3380960$LocalHost@ntdv2113>

You can't write Data Definiton Language in a PL/SQL block.

For that you must use Dynamic SQL (DBMS_SQL). You need to open a cursor, parse your DDL query, execute it and close the cursor.

It's boring to use DBMS_SQL and for that, I wrote a package with a lot of procedures and functions like this one.

(For this example I used a stand alone procedure)

CREATE OR REPLACE PROCEDURE prc_ddl
(
squery IN VARCHAR2
)
IS

icursor     INTEGER;
irows       INTEGER;

BEGIN
   icursor := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE (icursor,squery,DBMS_SQL.NATIVE);    irows := DBMS_SQL.EXECUTE (icursor);    DBMS_SQL.CLOSE_CURSOR (icursor);
EXCEPTION
   WHEN OTHERS THEN

      IF DBMS_SQL.IS_OPEN (icursor) THEN
         DBMS_SQL.CLOSE_CURSOR (icursor);
      END IF;

   DBMS_OUTPUT.PUT_LINE('ERREUR : '||SQLERRM); END prc_ddl;
/

To use it you must write something like that :

create or replace procedure pr_rapport3 (year in varchar2) as
squery VARCHAR2(2000);
begin
squery := 'create or replace view VW_REPORT3_1'||

          ' as (select * '||
                ' from tblklacht'||
               ' where to_char(klc_constatklacht,''YYYY'') = '||year||')';

prc_ddl (squery);
end pr_rapport3;

Be carefull, it doesn't work if the user is granted through a role !

abray_at_club-internet.fr

G. Kakisina <ivkaki_at_hvision.nl> a écrit dans l'article <370DDF8C.611237F2_at_hvision.nl>...
> Hi,
>
> I'm trying to create a procedure in Oracle 8 that looks like this:
>
> ------
> create or replace procedure pr_rapport3
> (year in varchar2) as
> begin
> create or replace view VW_REPORT3_1 as
> (select * from tblklacht
> where to_char(klc_constatklacht,'YYYY') = year)
> end;
> ------
>
> It has to select all records of a specific year and put it in a view
> named 'vw_report3'. When I run this code, I get the following error
> message:
>
> ------
> 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 commit <a single-quoted SQL string>
> ------
>
> What's wrong with the procedure?
>
> G. Kakisina
>
Received on Fri Apr 09 1999 - 18:19:17 CDT