Oracle Object for OLE OO4O Access

Here's a snippet of code for Oracle Object for OLE (OO4O) access.

Session = ObjectOpen('OracleInProcServer.XOraSession')
ErrorMode(@OFF)                                                           
db = Session.OpenDatabase('dss1prd.ip', StrCat('bshepard/', pwd), 4|8)
ErrorMode(@CANCEL)                                                        
ErrorString = Session.LastServerErrText
If ErrorString == ''
 ErrorMode(@OFF)                                                         
 ds = db.CreateDynaset(SqlQuery, 0)
 ErrorMode(@CANCEL)                                                      
 ErrorString = db.LastServerErrText
 If ErrorString == ''
   fTest = ds.fields('stu_tst_cd')
   fPart = ds.fields('stu_tst_cmpnt_cd')
   fScore = ds.fields('stu_tst_scr_nbr')
   fDate = ds.fields('stu_tst_dt')
   While ! ds.eof
;do lots of stuff here
   EndWhile
   ObjectClose(ds)
 EndIf  ;SQL executed OK
 ObjectClose(db)
EndIf   ;attached to DB OK
ObjectClose(Session)

It's basic, but it gets the job done.


;   Here's a .wbt script I wrote that
;   utilizes both the Oracle Objects for OLE and shows the users that are
;   logged into the database. Must have ADMIN rights.

;   it takes the results and places them into Excel. This is an ancient script
;   from 2001.

;   if you don't know if you have ORACLE OBJECTS FOR OLE installed, simply open
;   the windows explorer, go to your ORA folder and start the ORAINST.EXE program.
;   it'll show what's installed. IF it isn't installed, simply insert your Oracle
;   client CD and run the installer and select it. It's about 75 megs with the help
;   file. There's a nice VB tutorial and it's very simple. I had it running the
;   first night.

;   Start excel here
excelxls = ObjectOpen("Excel.Application")
excelxls.visible = @TRUE
rptxls = excelxls.workbooks
;   add a blank workbook...
rptxls.add
;   setup the sheets var...
xsheets = excelxls.sheets

;   as you can see below, much easier, streamlined attaching to the database, no
;   binding columns and it looks alot like using MS Access via OLE.

;'Create the OraSession Object
OraSession = ObjectOpen("OracleInProcServer.XOraSession")

;'Create the OraDatabase Object by opening a connection to Oracle.
OraDatabase = OraSession.DbOpenDatabase("databasename", "userid/password", 0)

sqlstr = "select * from v$session order by username asc"
;sqlstr = "select * from v$session where status = 'ACTIVE' order by username asc"   ;<--- previous shows all logins, this shows only active.

;'Create the OraDynaset Object.
Oradynaset = OraDatabase.DbCreateDynaset(sqlstr, 0)

;message("Oracle OIP", oradynaset.recordcount)

odf = oradynaset.fields

;set the row subscript...
x=1

;   first do the fieldnames...
For of = 0 To odf.count-1
   field = oradynaset.fields(of)
;   message("Field Names", field.name)
   cell = excelxls.cells(x, of+1)
   cell.value = field.name
Next

; increment the row subscript for the data...
x = x + 1
While Oradynaset.EOF == 0
   Yields(6)
   For of = 0 To odf.count-1
      field = oradynaset.fields(of)
      field.value
      cell = excelxls.cells(x, of+1)
;   message("Field Names", field.value)
      cell.value = StrReplace(field.value, @CRLF, " ")
   Next
   x = x + 1   ; increment the row
   oradynaset.movenext
EndWhile

awb = excelxls.activeworkbook
awbws = awb.worksheets
currws = awb.worksheets(1)
currws.name = StrReplace(TimeYmdHms(), ":", "_")


ObjectClose(Oradynaset)
ObjectClose(OraDatabase)
ObjectClose(OraSession)

Exit

Article ID:   W16165

File Created: 2004:03:30:15:43:02

Last Updated: 2004:03:30:15:43:02

Database Search

TechHome

Oracle Object for OLE OO4O Access