[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

oracle how to drop multiple tables in a script

getting error in this script
--*********************************************
--*  R311013 RESTORE                          *
--*  Restore table from backup tables.        *
--*  Delete current table                     *
--*  Create table by copying from bkup table  *
--*********************************************
DECLARE
   table_does_not_exist   EXCEPTION;
   table_does_not_exist2  EXCEPTION;
   PRAGMA EXCEPTION_INIT (table_does_not_exist, -00942);    -- ORA-00942
   table_1                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_ACCEL';
   table_2                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_CLASSIC';
   
BEGIN
   EXECUTE IMMEDIATE('DROP TABLE ' || table_1);
   EXCEPTION WHEN table_does_not_exist THEN NULL;  
 
   EXECUTE IMMEDIATE
         'create table '
      || table_1
      || ' AS SELECT * FROM '
      || table_1    
      || '_BKUP';

  EXECUTE IMMEDIATE('DROP TABLE ' || table_2);
  EXCEPTION WHEN table_does_not_exist2 THEN NULL;  

    EXECUTE IMMEDIATE
          'create table '
       || table_2
       || ' AS SELECT * FROM '
       || table_2
       || '_BKUP';

END;
/
R311013-Restore-Tables.sql
0
ALad2005
Asked:
ALad2005
  • 2
  • 2
2 Solutions
 
flow01Commented:
what error do you get ?
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the ?

add an extra begin end to make the exeption at the end of block

DECLARE
   table_does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (table_does_not_exist, -00942);    -- ORA-00942
   table_1                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_ACCEL';
   table_2                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_CLASSIC';
   
BEGIN
   BEGIN
     EXECUTE IMMEDIATE('DROP TABLE ' || table_1);
   EXCEPTION WHEN table_does_not_exist THEN NULL;  
   END;
   
   EXECUTE IMMEDIATE
         'create table '
      || table_1
      || ' AS SELECT * FROM '
      || table_1    
      || '_BKUP';

  BEGIN
    EXECUTE IMMEDIATE('DROP TABLE ' || table_2);
  EXCEPTION WHEN table_does_not_exist THEN NULL;  
  END;

    EXECUTE IMMEDIATE
          'create table '
       || table_2
       || ' AS SELECT * FROM '
       || table_2
       || '_BKUP';

END;
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Hi,

try the below. nothing much of functional changes but have added the exception sections.

for any exception handling block / dynamic sql while you are working/testing on the script, it is always good to have debug/error messages written to the screen or to the table etc in order for you to clearly understand the code flow/execution. May be you comment out some of the debug/error messages if you do not want once your script if fully tested and ready.

DECLARE
    code_step_no number := 0;

    table_does_not_exist   EXCEPTION;
    PRAGMA EXCEPTION_INIT (table_does_not_exist, -00942);    -- ORA-00942
    table_1                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_ACCEL';
    table_2                VARCHAR2 (30) := 'SIMDATA.TEMP_R311013_CLASSIC';

   
   
 BEGIN
  code_step_no := 10;

    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ' || table_1 ;
    EXCEPTION
WHEN table_does_not_exist THEN
   dbms_output.put_line('in exception section 1: after drop table ' || table_1 || '. code step no:' || code_step_no);
   dbms_output.put_line('sqlcode:' || sqlcode );
   dbms_output.put_line('sqlerrm : ' || sqlerrm );

    END;
   
  code_step_no:=20;
    EXECUTE IMMEDIATE
          'create table '
       || table_1
       || ' AS SELECT * FROM '
       || table_1    
       || '_BKUP';

code_step_no:=30;
   BEGIN
     EXECUTE IMMEDIATE('DROP TABLE ' || table_2);
   EXCEPTION
WHEN table_does_not_exist THEN
   dbms_output.put_line('in exception section 1: after drop table ' || table_2 || '. code step no:' || code_step_no);
   dbms_output.put_line('sqlcode:' || sqlcode );
   dbms_output.put_line('sqlerrm : ' || sqlerrm );
   END;

code_step_no:=40;
     EXECUTE IMMEDIATE
           'create table '
        || table_2
        || ' AS SELECT * FROM '
        || table_2
        || '_BKUP';

exception
WHEN others THEN
   dbms_output.put_line('in others exception section. code step no:' || code_step_no);
   dbms_output.put_line('sqlcode:' || sqlcode );
   dbms_output.put_line('sqlerrm : ' || sqlerrm );

 END;
 /
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
please test and post your error message otherwise we could not understand which error you are getting.
0
 
ALad2005Author Commented:
I don't know how to close the question.  I already accepted the answers given by members.
0
 
ALad2005Author Commented:
thank you.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now