%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
... View more
