job to collect oracle tables statistics in a schema..

[Sai1]

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...

[SASKiwi]

You are getting Oracle errors related to calling an Oracle-specific procedure. Not sure why you use => on the procedure parameters, why not just = ? Also test by replacing  the macro variable references with hard-coded values - I suspect they should be contained in quotes.

You will get better help posting just your Oracle-specific code on an Oracle forum. 

[RW9]

Do you not need to quote username and password?

Anyways, firs decision - which application to use.  There is no benefit to wrapping this code in SAS code.  Either do the whole thing on Oracle - in which case visit an Oracle forum - or do the whole thing other than the data extract in SAS - i.e. select * from dictionary.columns where ...; Then do your "statistics" in SAS.  It is rarely a good idea to drive one application from another when either could do the job on its own.

[Ksharp]

You can use this to get all the schema:

proc sql;
connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
create table cmpntbls as
select * from connection to oracle(ORACLE::SQLTables);
quit;

[BrunoMueller]

The schema and table name have to be in single quotes. In the SAS Macro language you can use the %TSLIT autocall macro to achieve this.

See the code snippet below:

      execute(
        begin
        DBMS_STATS.GATHER_TABLE_STATS(
        ownname=> %tslit(&_schema),
        tabname=>  %tslit(&&_table&i.),
        cascade => false);
        end;
      ) by oracle;

Bruno