Site hosted by Build your free website today!


SAS(r) Software Tips
A Macro To Delete Oracle Tables
This macro is useful when you are creating temporary tables in Oracle, and need to restart the program after the table is created. Usually when you restart and attempt to create the table again your code will fail. By calling this macro, it deletes the table if it exists, and then just carries on your program. If the table doesn't exist, effectively the macro does nothing and your program just continues. The macro should be called before a table create takes place.

%macro oradel(owner=,tabname=,orapath=) ;

  Macro for deleting Oracle tables. The userid that runs this query must
  have drop access to the table. This macro should be used in any job
  that creates temporary Oracle tables to remove any that may not have
  been cleaned up from a failed run, and can be called after use of any
  Oracle tables that the job created, in order to delete them.

  Call once per table that the calling job wishes to create.

  Author::  Don Stanley
            Sysware Consulting Group
            OCT 2000

  PARMS     &owner. The owner of the table that is to be deleted.
          &tabname. The table name of the table that is to be deleted.
          &orapath. PATH to the Oracle system

    Do not enclose any of the above in quotes (single or double)

  The macro is to be run on the system that Oracle exists on. It can be
  remote submitted, but the parameters would need to be sent to the
  remote site via SYSLPUT first.

  Change History

  Coder              Date         Comments
  Don Stanley        25 Oct 2000  First Written

  If any parameter is missing, skip over the macro
  as we cannot acccomplish the delete

 %if &owner   = %str() OR
     &tabname = %str() OR
     &orapath = %str()
 %then %do   ;
   %put 'ERROR:: Parameters Are Incorrect ' _user_ ;
   %goto nogood ;
 %end ;

  Make sure everything is uppercase so that ORACLE data dictionary
  tables will find table to delete

 %let owner   = %sysfunc(upcase(&owner))   ;
 %let tabname = %sysfunc(upcase(&tabname)) ;

 proc sql noprint ;
   connect to oracle (path="&orapath"
                          preserve );

   Count the number of times the table is found in the Oracle data
   dictionary. This will be 0 (it does not exist and therefore cannot
   be dropped), or 1 (the table can only exist uniquely). Store this in
   a SAS macro variable &f_exist for later use.

     select f_exist into: f_exist from connection to oracle
       ( select count(*) as f_exist
       from all_tables
          where owner=%nrbquote(')&owner%nrbquote(')
            and table_name = %nrbquote(')&tabname%nrbquote(')
       ) ;

   Quit the SQL, or the next &f_exist variable will be resolved before
   it is created (because there is no step boundary otherwise)

  quit ;

   If we got a count of 1 from the dictionary query above, then the
   table must exist (otherwise the count would be 0, it can only be 0
   or 1). Hence we can now generate the SQL to delete it.

  %if &f_exist = 1 %then %do ;
    %put oradel:: Deleting Table &owner..&tabname;
    proc sql noprint ;
         execute (drop table &owner..&tabname)
          by oracle ;
    quit ;
  %end ;
  %else %do ;
    %put oradel:: Table &owner..&tabname Not Found ;
  %end ;

 %mend oradel;

Contact Don Stanley