Hi, i am asked to create a job with userwritten ,job includes only transformation and no tables ,it should be able to collect stats on oracle tables in a particular schema in sas di. here is the requirement: Tables with CMPGN_* like will be included to collect the table stats in RTDMASL schema > User written code module will be used to find out the cmpgn_* like tables > User written code module will used to loop throgh the tables and collect the table stats > here is my code: %macro CMPGN_TBL_Stats_Coll; proc sql; connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' ); create table cmpntbls as select * from connection to oracle ( SELECT table_name, owner FROM all_tables where owner='RTDMASL' and table_name like 'CMPGN_%' ORDER BY owner, table_name ); disconnect from oracle ; quit; options symbolgen; DATA _NULL_; SET cmpntbls END=NOMORE; CALL SYMPUT ('_TABLE' || COMPRESS(PUT(_N_,3.)), TRIM(table_name)); call symput ('_schema',owner); IF NOMORE then CALL SYMPUT ('TOT_TAB', PUT(_N_,3.)); RUN; %DO I=1 %TO &TOT_TAB; proc sql; connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' ); execute( begin DBMS_STATS.GATHER_TABLE_STATS( ownname=> &_schema, tabname=> &&_table&i., cascade => false); end;) by oracle; disconnect from oracle; quit; %end; %mend CMPGN_TBL_Stats_Coll; %CMPGN_TBL_Stats_Coll; and the error message i am getting is : Line 215: ERROR: ORACLE execute error: ORA-06550: line 1, column 48: PLS-00201: identifier 'RTDMASL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored. please help me with this...
... View more