topic Re: job to collect oracle tables statistics in a schema.. in SAS Studio

job to collect oracle tables statistics in a schema..

Sai1 — Fri, 04 Nov 2016 04:00:02 GMT

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

Re: job to collect oracle tables statistics in a schema..

SASKiwi — Fri, 04 Nov 2016 06:23:13 GMT

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.

Re: job to collect oracle tables statistics in a schema..

RW9 — Fri, 04 Nov 2016 08:28:24 GMT

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.

Re: job to collect oracle tables statistics in a schema..

Ksharp — Fri, 04 Nov 2016 08:51:12 GMT

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;

Re: job to collect oracle tables statistics in a schema..

BrunoMueller — Fri, 04 Nov 2016 10:28:36 GMT

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