Re: Drop Table in PLSQL Script
Date: Wed, 26 Jun 2002 00:40:51 +0200
Message-ID: <3d18f18b$0$724$edfadb0f_at_dspool01.news.tele.dk>
> I wrote this plsql script to drop some of the tables I no longer need:
> 1 declare
> 2 my_table_name varchar(30);
> 3 cursor my_cursor is
> 4 select table_name from user_tables
> 5 where table_name like 'TEST_TABLE%';
> 6
> 7 begin
> 8 open my_cursor;
> 9 loop
> 10 fetch my_cursor into my_table_name;
> 11 exit when my_cursor%NOTFOUND;
> 12 drop table my_table_name;
> 13 end loop;
> 14 end;
> 15 /
> ~
Your problem is that Data Definition Language, the language that modifies the structure of the database, is not allowed in PL/SQL.
You can use dynamic SQL instead, calling the dbms_sql package or using built in dynamic sql.(depends on the version of Oracle you use).
Here is a function that i wrote for that purpose on Oracle 8, I just tested it on Oracle 9i where it also works.
declare
gn_cursor number; /* private cursor for DoDynDDL */
/********************************** DoDynDDL *****************************************************
- makes it possible to perform dynamic DDL, ignoring an expected error (if
apllicable).
*/
function DoDynDDL (pv_sqlst in varchar2, pn_suppress in number) return pls_integer is
li_retval pls_integer := -4020;
li_retrys pls_integer := 0;
begin
while (-4020 = li_retval and li_retrys <3) loop
li_retval := 0;
li_retrys := li_retrys + 1;
begin
if(gn_cursor is null) then gn_cursor := dbms_sql.open_cursor; end if; dbms_sql.parse(gn_cursor,pv_sqlst,dbms_sql.native); exception when others then if (-4020 = SQLCODE and li_retrys < 3) then dbms_output.put_line(' ERROR: ''' || SQLERRM || ' retry ' || li_retrys); commit; dbms_lock.sleep(10); elsif (SQLCODE != pn_suppress) then dbms_output.put_line(' ERROR: ''' || SQLERRM || ''' performing dynamic SQL ''' || pv_sqlst ||''''); li_retval := SQLCODE; end if;
end;
end loop;
return li_retval;
end DoDynDDL;
Hope this helps you delete your temp files.
Greetings from Denmark
Jesper Wolf Jespersen
PS. Oldtimers will probably sugest you simply pipe the output of below
command to a script,
and use the output directly as input to sqlplus,
--- select 'drop table ' || table_name || ';' from user_tables where table_name like 'TEST_TABLE%'; --- Its not as fancy but a lot faster to type.Received on Wed Jun 26 2002 - 00:40:51 CEST