Re: DDL in stored procedures?

From: Mikhail Tyulenev <misha_at_park.ru>
Date: 1997/06/06
Message-ID: <01bc7339$94111990$513fd1c3@misha>#1/1

Hi.
Generally DDL is not allowed in PL/SQL becowse compiler can resolve references only if
the database objects are known at compile time. However, the package DBMS_SQL,
whitch is supplied with Oracle7, allows PL/SQL to execute DDL at run time.

example (dropping arbitrary index):

CREATE PROCEDURE my_index (aIndex in VARCHAR2 ) AS   cid INTEGER;
BEGIN
  cid := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cid, 'DROP INDEX ' || aIndex, dbms_sql.v7);   ...
  DBMS_SQL..CLOSE_CURSOR(cid);
EXCEPTION
  WHEN OTHERS THEN
  DBMS_SQL.CLOSE_CURSOR(cid);
  RAISE;
END my_index;

For more info see Oracle Application Developer's Guide.

Regards.

Mikhail V. Tyulenev                              phone: +007(095)932 9051 
Programmer/Analyst, Garant Park        fax  :     +007(095)932 9150 

Internet: misha_at_park.ru
Garant Park : http://www.park.ru/    

The statements and opinions expressed here are my own and do not necessarily represent those of Garant Park

Lisa M. Lewis <lmg_at_isdsa.pgh.wec.com> wrote in article <33945CC1.FAADC884_at_isdsa.pgh.wec.com>...
> I am trying to recreate an index inside a stored procedure. Can anyone
> tell me if this is allowed. Is any DDL allowed inside of stored
> procedures?
>
> Thanks in advance!
>
> Lisa
>
>
  Received on Fri Jun 06 1997 - 00:00:00 CDT