Re: Drop Table in PLSQL Script

From: Jesper Wolf Jespersen <oz8ace_at_qsl.net.spam>
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

Original text of this message