Re: 'create procedure' problem

Arlette,

In Oracle8 you can use the DBMS_UTILITY.EXEC_DDL_STATEMENT procedure to execute DDL statements, rather than DBMS_SQL.

Chris.

Arlette BROSSARD wrote:
>
> 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 Sun Apr 11 1999 - 14:38:05 CDT