Re: Deleting tables via a SP
Date: Mon, 11 Sep 2000 18:13:58 GMT
Message-ID: <8pj7cf$u4f$1_at_nnrp1.deja.com>
Hi Robert,
If you're using 8.1 or later you can use Execute Immediate in place of
DBMS_SQL...same deal but a lot neater..you can create a cursor to
select table names from the data dictionary subject to user criteria
etc..anyway then
EXECUTE IMMEDIATE 'DROP TABLE'||table_name;
S
In article <8o636u$q66$1_at_nnrp1.deja.com>,
oratune_at_aol.com wrote:
> 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.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Sep 11 2000 - 20:13:58 CEST