topic Re: How to check if an Oracle table exist using sas code in SAS Enterprise Guide

How to check if an Oracle table exist using sas code

alepage — Mon, 05 Feb 2024 21:29:12 GMT

%LET Owner=Owner1;
%LET Database=MRKTUAT;
%LET SCHEMA=SCHEMA1;
%let TblName1=ALPTARGET;
%let TblName2=ALPSOURCE;
%LET LIBREF=myoracle;

%PUT &=Owner. &=Database. &=SCHEMA ;
Libname &LIBREF. oracle AUTHDOMAIN=ORACLE_&OWNER._&Database. PATH=&Database. schema=&schema. readbuff=1000;

/* Defining A macro function */

%macro check_table(db_name,table_name);
  %if %sysfunc(exist(&db_name.&table_name.)) %then %do;
    %put The table exists.;
  %end;
  %else %do;
      %Put Table does not exist;
  %end;
%mend;

%check_table(&LIBREF.,&TblName2.);

I have tested this function and it is not working at all.

I have also tried this function below and it is not working.

Any idea ?

%LET Owner=Owner1;
%LET Database=UAT;
%LET SCHEMA=UAT1;
%let TblName1=ALPTARGET;
%let TblName2=ALPSOURCE;
%LET LIBREF=MYORACLE;

%macro check_oracletbl (owner,database,schema,tablename);

proc sql;
connect to oracle (AUTHDOMAIN=ORACLE_&OWNER._&database. PATH=&database. readbuff=1000);
execute(
DECLARE
tbl_count number;
string varchar2(100);

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM &SCHEMA..&tablename.;
    
    IF tbl_count <> 0
        THEN
		STRING:= "'  "||"DROP TABLE "||"&SCHEMA."||"."||"&TABLENAME."||"  '";
/*		string:= 'drop table UAT1.ALPSOURCE';		*/
        EXECUTE IMMEDIATE string  ;       
    END IF;
END;


) BY ORACLE;
DISCONNECT FROM ORACLE;
quit;

%mend check_oracletbl;
%check_oracletbl(&owner., &Database.,&schema.,&TblName2.);

What should be the value of string to equal that 'drop table UAT1.ALPSOURCE' ,i.e. replacing the macro variable into string to give the good value

Re: How to check if an Oracle table exist using sas code

Kurt_Bremser — Mon, 05 Feb 2024 21:42:20 GMT

You need an additional dot:

%if %sysfunc(exist(&db_name..&table_name.))

as the first dot terminates the macro variable/parameter reference. Without the second dot, it would resolve to

myoracleALPSOURCE

Re: How to check if an Oracle table exist using sas code

alepage — Mon, 05 Feb 2024 21:57:41 GMT

You got a good point Mr. Bremser, but do you think that I will still need a pass trough facility to drop the table, ie. something that will produce that string: 'drop table myoracle.ALPSOURCE';

%macro check_oracletbl (owner,database,schema,tablename);

proc sql;
connect to oracle (AUTHDOMAIN=ORACLE_&OWNER._&database. PATH=&database. readbuff=1000);
execute(
DECLARE
tbl_count number;
var1 varchar2(100);

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM &SCHEMA..&tablename.;
    
    IF tbl_count <> 0
        THEN
		var1:= "'  "||"DROP TABLE "||"&SCHEMA."||"."||"&TABLENAME."||"  '";
/*		var1:= 'drop table myoracle.ALPSOURCE';		*/
        EXECUTE IMMEDIATE var1  ;       
    END IF;
END;


) BY ORACLE;
DISCONNECT FROM ORACLE;
quit;

%mend check_oracletbl;
%check_oracletbl(&owner., &Database.,&schema.,&TblName2.);

Re: How to check if an Oracle table exist using sas code

Patrick — Mon, 05 Feb 2024 22:50:43 GMT

If this is just about not running into an error condition when trying to drop a table then I'd consider to first test for existence of the table as the way to go.

%let lref =myora;
%let tbl  =mytable;

%if %sysfunc(exist(&lref..&tbl.)) %then
  %do;
    proc sql;
      drop table &lref..&tbl.;
    quit;
  %end;

Also using Proc Datasets with option Nowarn should do the job

proc datasets lib=&lref nolist nowarn;
  delete &tbl;
quit;

And if you want to use explicit pass-through syntax then look into the discussion here.

It appears that the latest Oracle release now also introduced IF EXISTS.