OO4O FAQ

Oracle Objects for OLE (OO4O) FAQ:

Can I use OO4O to access Oracle data from Microsoft applications?

Yes, you can use OO4O from any Microsoft application that uses the VB-style macro language. This includes applications like:

• Microsoft Visual Basic
• Microsoft Excel
• Microsoft MS-Access
• Active Server Pages (ASP)
• C++, etc.

How does one install and configure OO4O?

Follow these steps to install and configure OO4O:

• Install the Oracle Client CD on your workstation. This will install OO4O (Oracle Objects for OLE) and SQL*Net on your system.

• Configure SQL*Net and ensure you can tnsping and connect your Oracle database. This is done by adding an entry to the TNSNAMES.ORA file or by using utilities like the "Net Easy Configurator" to do it for you. See the SQL*Net FAQ for details.

• You are ready to GO!!!

How does one connect to an Oracle Database?

Connect to Oracle using the OpenDatabase function. The OpenDatabase function returns an OraDatabase object. Parameters are DB_NAME, CONNECT_DESCRIPTOR and CONNECT_OPTIONS. Look at the following Visual Basic code example:

Dim OraSession  As Object     'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset  As Object

Set OraSession  = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
 
MsgBox "Connected to " & OraDatabase.Connect & "@" & OraDatabase.DatabaseName
MsgBox "OO4O Version:   " & OraSession.OIPVersionNumber 
MsgBox "Oracle Version: " & OraDatabase.RDBMSVersion

How does one select data from an Oracle Database using OO4O?

Use the OraDynaSet object to define recordsets. Look at this simple example:

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("SQL*Net_Connect_String", "scott/tiger", 0&)
Set OraDynaset = OraDatabase.DbCreateDynaset("select empno, ename from emp", 0&)
MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & OraDynaset.Fields("ename").value

One can also loop through the result set using code like this:

Do While(OraDynaset.EOF = FALSE)
   Response.write(OraDynaset.Fields("TNAME") & " ")
   osRecordSet.MoveNext
Loop

How does one use bind variables in OO4O?

Use the "OraParameters.Add Name, Value, IOType" method to substitute bind variables with values before executing a statement. If you don't, you will get error "not all variables bound". The IOType filed can be one of the following:

• ORAPARM_INPUT - Use as input variable only
• ORAPARM_OUTPUT - Use as output variable only
• ORAPARM_BOTH - Use the variable for input and output

Look at this example:

sqlStmt = "SELECT tname FROM tab WHERE tname LIKE :var1"
OraDatabase.Parameters.add "var1", "%A%", 1   ' Substitute Name=Value of type ORAPARM_INPUT
Set osRecordSet = OraDatabase.DbCreateDynaset(sqlStmt, cint(0))  
Do While(osRecordset.EOF = FALSE)
   ' Use data: osRecordset.Fields("TNAME") in this example...
   osRecordSet.MoveNext
Loop

How does one execute DML and DDL statements using OO4O?

One can use any valid DDL and DML statement with the ExecuteSQL (or DBExecuteSQL) method. Look at this example:

rowcount = OraDatabase.ExecuteSQL("create table X (A date)")
rowcount = OraDatabase.ExecuteSQL("insert into X values (sysdate)")
OraSession.CommitTrans    ' or OraSession.Rollback

How does one call a stored procedure from OO4O?

A stored procedure block can be executed from the CreateSQL or ExecuteSQL methods. OO4O supports the SQL statement objects for PL/SQL stored procedures. Look at this VB example:

sql1 = "begin test_sproc(param2=>'two_b', param1=>'one_b'); end;"
rowcnt = OraDatabase.DbExecuteSQL(sql1)

More complex example using input and output parameters. Note that the output parameter returns a cursor:

OraDatabase.Parameters.Add "NAME", "SUPERSPORT", ORAPARM_INPUT
OraDatabase.Parameters("NAME").ServerType = ORATYPE_VARCHAR2

OraDatabase.Parameters.Add "ORDCURSOR", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("ORDCURSOR").ServerType = ORATYPE_CURSOR

Set OraSQLStmt = OraDatabase.CreateSql("begin CUSTOMERS.GetCutomerSalesOrder(:Name,:OrdCursor);end;", ORASQL_FAILEXEC)

Set OrderDynaset = OraDatabase.Parameters("ORDCURSOR").Value
'Now display the Dynaset's field value
MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & 

How does one use the OraMetaData Object to describe tables?

The OraMetaData object (available from Oracle8i) can be used to retrieve all sorts of information about an Oracle schema. For example, one can list all objects in a schema, all columns of a table, etc. Look at this simple ASP example:

Response.write("<H1>Describe Table EMP:</H1&ht;")
Set objOraMetaData    = OraDatabase.Describe("EMP") 
Set objOraMDAttribute = objOraMetaData("ColumnList")
Set objColumnList     = objOraMDAttribute.Value

For iColCount = 0 To objColumnList.Count - 1 
    Set objColumnDetails = objColumnList(iColCount).Value 
    Response.Write "<P>>Column: " & objColumnDetails("Name") & _ 
                   "     Type: " & objColumnDetails("DataType") & _ 
                   "     Size: " &  objColumnDetails("DataSize") & _ 
                   "   IsNull: " & objColumnDetails("IsNull") & _ 
                   "Precision: " & objColumnDetails("Precision") & _ 
                   "    Scale: " & objColumnDetails("Scale")
Next