[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Oracle Database -ReplyMessage-Id:
From:       Jules Bosch <0006974523 () MCIMAIL ! COM>
Date:       1996-09-30 20:48:00
[Download RAW message or body]

Last week Timothy S. Sullivan <tsulliv@siue.edu> wrote -

     Does anyone have any experience reading Oracle Databases
into SAS?  Is there a relatively easy way to do it?

RESPONSE:

I have been developing a major SAS/AF project which reads
reproductive toxicology study records stored in an ORACLE
database to create SAS-customized reports.  We are using the
SAS/ACCESS Interface to ORACLE product and have had nothing but
smooth sailing.

We are running Sun SPARC 10 workstations as servers, SUN UNIX as
the OS, and SAS 6.11.  Users are at remote PCs using the eXceed
X-windows product.

The system response time is excellent and the choices a developer
has with regard to processing points (Oracle or SAS) is a major
plus.  This processing latitude exists because of the "pass
through" facility which is native to the SAS/ACCESS Interface to
ORACLE product.  The pass through facility enables a developer to
embed ORACLE SQL code in SAS SCL and have it "passed through" to
ORACLE for processing.  It is an invaluable tool.

For example, suppose a user needs the means and standard
deviations for a large group of body weights which are stored in
ORACLE.  The developer could compose code to gather the numerous
body weight records from ORACLE (using the pass through
facility), pass the many records back to SAS, and process them
with SAS PROC MEANS.  Or, the developer could embed
additional SQL code for ORACLE means and standard deviations and
pass this code through, have ORACLE analyze the records for means
and standard deviations and pass back to SAS just the few records
containing the analysis results.

Some examples of pass through code follow -

The following code is run in SCL.  The first example creates a
SAS data set via the pass through facility to ORACLE.

/* Create data set of animal numbers and census ids */
   SQL_ANIM:
      submit continue sql;
         connect to ORACLE
            (user=XXXXXXX orapw=XXXXXXX path="XX:XXXXXXXX:XXXX");
         create table work.animals as
            select animalno format=$char20., censusid
         from connection to ORACLE

/* Pass through code starts next */
            (select animalnumber as animalno, censusid
               from census
              where studyid=&studyid   /* SCL var */
                and groupid<101
               order by animalnumber) ;
/* Pass through code ends above */

         disconnect from ORACLE;
         quit;
      endsubmit;
   return;

One of the nice features about SCL "SUBMIT CONTINUE SQL;" blocks
is that the developer can run TESTAF within the entry (i.e., in the
build directory instead of having to exit to the Program Editor as is the
case with submit blocks containing data-step code.

In the next example a SAS macro var is created via SQL.  Note the
code no longer contains the ACCESS code that connects/disconnects
ORACLE.  Instead, the connect/disconnect code has been stored as
a method.  Again, this is all SCL-based code.

/* Connect to ORACLE server */
   call method('meth_sql.scl','ora_open',status,x);
      if (x) then return;

/* Create a macro var named gender */
   submit continue sql;
      select sex into :gender
         from connection to oracle
/* Pass through code starts next */
         (select distinct a.sex as sex
            from study_param  a
           where a.studyid=&studyid    /* SCL var */
             and a.phaseid=&phaseid    /* SCL var */
             and a.paramid=1) ;
/* Pass through code ends above */

   endsubmit;

/* Disconnect from ORACLE */
   call method('meth_sql.scl','ora_clos');


The methods follow -

/****************************************************************
METHOD=ORA_OPEN
Connect the user to the ORACLE server
****************************************************************/
length ora_stat $1;
ORA_OPEN:
method status $ x 8 / resident;  /* RESIDENT not needed with SAS
                                    6.11 */
   submit continue sql;
      reset noprint;
      connect to oracle
      (user=XXXXXXX orapw=XXXXXXX path="XX:XXXXXXXX:XXXX");
   endsubmit;
/* Check whether CONNECTION to ORACLE succeeded   */
   if (symgetn('sqlxrc')^=0) then
      do;
         alarm;
         msgtype='E';
         msg1='The CONNECTION to ORACLE cannot be made.';
         msg2='This may be a major problem.  Please call';
         msg3='the System Administrator.  This process is';
         msg4='being stopped.';
         call display('popmsg.program',
             msgtype,msg1,msg2,msg3,msg4);
         status='NG'; x=1;
         return;
      end;
   status='OK';
   put 'ORACLE IS OPEN'; ora_stat='O';
   call symput('ora_stat',ora_stat);
endmethod;

/****************************************************************
METHOD=ORA_CLOS
Disconnect the user from the ORACLE server
****************************************************************/
ORA_CLOS:
method  / resident; /* RESIDENT not needed with SAS 6.11 */
   submit continue sql;
      disconnect from oracle;
      quit;
   endsubmit;
   put 'ORACLE IS CLOSED'; ora_stat='C';
   call symput('ora_stat',ora_stat);
endmethod;

Hope this information helps.

Jules Bosch
jules_bosch@mcimail.com

[prev in list] [next in list] [prev in thread] [next in thread]