%* Sample use of Oracle procedure Get_View_Source_Text_for_SAS
%* and SAS macro purtysql.
%*
%* Richard A. DeVenezia
%* 5/2000
%*;

%*---------------------------------------------------------------------------;
%macro sample;

  %let purtysql = purtysql.sas;  %*location of sql formatter macro;

  %let ORAUSER = MY_ORACLE_USERNAME;
  %let ORAPW   = MY_ORACLE_USERNAME_PASSWORD;
  %let PATH    = ORACLE_DATABASE_TO_CONNECT_TO;

  %let ORATABLE = SOME_ORACLE_VIEW;
  %let SASDSN   = WORK.SOURCE;

  %let RAWSOURC = View_Source_Text_for_SAS;     %* Oracle Table where Oracle procedure will put Oracle view source text;
  %let GETSOURC = Get_View_Source_Text_for_SAS; %* Oracle procedure name;

  proc sql;
    reset noprint;

    connect to ORACLE (USER=&ORAUSER ORAPW=&ORAPW PATH="@&PATH");

    select * from connection to ORACLE
    (select * from user_objects
     where object_name = %str(%')&GETSOURC%str(%')
       and object_type = 'PROCEDURE'
    );

    %if &SQLOBS eq 0 %then %do;
      disconnect from ORACLE;
      %put Oracle procedure &GETSOURC was not found.;
      %goto ByeBye;
    %end;

    EXECUTE (drop   table &RAWSOURC ) by ORACLE;
    EXECUTE (create table &RAWSOURC (text char(80))) by ORACLE;
    EXECUTE (EXECUTE &GETSOURC (%str(%')&ORATABLE%str(%'))) by ORACLE;

    create table &SASDSN as
    select * from connection to ORACLE
    (
      select * from &RAWSOURC
    ) ;

    EXECUTE (drop   table &RAWSOURC ) by ORACLE;

    disconnect from ORACLE;
  quit;

  %include "&purtysql";
  %purtySQL (data=&SASDSN, var=text, out=&SASDSN);

  proc print data=&SASDSN;
  run;

%ByeBye:

%mend sample;