Re: Deleting tables via a SP

In article <39A6805A.9364557_at_cern.ch>,   Erika.Grondzakova_at_cern.ch wrote:
> Hello Robert,
>
> Use Dynamic SQL - DBMS_SQL package.
>
> Hth,
>
> Erika
>
> Robert Vabo wrote:
> >
> > I want to use a stored procedure to delete a lot ov tables one table
 at a
> > time.
> >
> > In VB I wrote a component that returns a recordset containing the
 tables. I
> > want to loop that rs, get tablenames (do some checks) and then call
 a sp to
> > drop/delete the tables.
> >
> > CREATE PROCEDURE spDelTempTables ( TableName IN varchar2 )
> > AS
> > BEGIN
> > DROP TABLE + TableName;
> > END;
> >
> > Any help to get out there ??
> >
> > --
> > Regards
> > Robert Vabo
> > Application developer
> > Gecko Informasjonssystemer AS
> > www.gecko.no
> > robert.vabo_at_gecko.no
>

This is a good general case solution, however if you are running 8i you also have the option of 'execute immediate':

CREATE PROCEDURE spDelTempTables ( TableName IN varchar2 ) AS
  drptbl varchar2(255);
  BEGIN
    drptbl := 'DROP TABLE '||TableName;
    execute immediate drptbl;
  END;
/

This is much easier to write than the DBMS_SQL code:

CREATE PROCEDURE spDelTempTables ( TableName IN varchar2 ) AS
  source_cursor integer;
  rows_processed integer;
  BEGIN
    source_cursor := dbms_sql.open_cursor;     dbms_sql.parse(source_cursor, 'drop table '||TableName, DBMS_SQL.NATIVE);
    rows_processed := dbms_sql.execute(source_cursor);     dbms_sql.close_cursor(source_cursor);   exception
    when others then

        dbms_sql.close_cursor(source_cursor);   end;
/

However, on 8i, either method should work. For portability it might be a good idea to stick with the tried-and-true DBMS_SQL.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.