Oracle Tip: Consider using Oracle objects for OLE in Windows
来源:互联网 发布:top k算法 编辑:程序博客网 时间:2024/04/28 11:22
Takeaway: This tip demonstrates how you can use the Oracle Objects for OLE (OO4O) In-Process Automation Server to connect to Oracle database servers and execute SQL or PL/SQL procedures through COM Automation Objects.
This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.Oracle Objects for OLE (OO4O) is a set of software layers designed to provide access to an Oracle database from Microsoft COM Automation and ActiveX compatible languages such as Visual Basic, Visual Basic for Applications (VBA), IIS Active Server Pages (ASP), Windows Script Host, and Visual C++. These layers were written directly against the Oracle Call Interface (OCI), bypassing the standard ODBC, OLE DB, and ADO layers. While this creates incompatibility with products using other databases, it enables direct access and broader access to an Oracle database.
Developers can use the OO4O In-Process Automation Server to connect to Oracle database servers and execute SQL or PL/SQL procedures through COM Automation Objects. You can also use the In-Process Server by creating a COM object for a session from its interface, "OracleInProcServer.XOraSession," and accessing subobjects, as shown in this example using Visual Basic:
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("","scott/tiger",0)
With a database object, you can query records from the database or execute DDL or DML statements directly. For example, here's a VBA script that you can run from inside Microsoft Excel to fetch all the data from the EMP table into worksheet cells:
Sub GetEmployees()
' Use OO4O
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("", "scott/tiger", 0)
Sql = "select * from emp"
Set oraDynaSet = objDatabase.DBCreateDynaset(Sql, 0)
If oraDynaSet.RecordCount > 0 Then
oraDynaSet.MoveFirst
For x = 0 To oraDynaSet.Fields.Count - 1
Cells(1, x + 1) = oraDynaSet.Fields(x).Name
Cells(1, x + 1).Format = Bold
Next
For y = 0 To oraDynaSet.RecordCount - 1
For x = 0 To oraDynaSet.Fields.Count - 1
Cells(y + 2, x + 1) = oraDynaSet.Fields(x).Value
Next
oraDynaSet.MoveNext
Next
End If
Set objSession = Nothing
Set objDatabase = Nothing
End Sub
While it's possible to fetch data from an external datasource into an Excel spreadsheet through the Data/External Datasource function, this macro has much more direct control, allowing you to specify exactly how the data is read into the spreadsheet; and, because the macro doesn't use as many layers to get to the Oracle database, it should be faster. It also provides additional functionality, including the ability to store and retrieve blobs (such as images).
The OO4O set also includes an Oracle Data Control, which is an ActiveX control that can be bound to custom controls, such as those used in Visual Basic which automatically track data with user interface interactions.
OO4O also comes with an OLE C++ class library, which helps provide similar access to Visual C++ developers and can be bound to foundation classes.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit ourOracle Dev Tips Library.
- Oracle Tip: Consider using Oracle objects for OLE in Windows
- Oracle Objects for OLE(OO4O)
- Oracle Objects for OLE 及在VB中的应用
- VC 使用ADO 需要 Oracle Objects for OLE
- Methods for Using Java in ORACLE(转荐)
- oracle tip
- (转)Methods for Using Java in ORACLE
- Microsoft OLE DB Provider for Oracle
- What are the objects in oracle?
- ASP中使用Oracle Object For Ole连接Oracle数据库
- Exporting and Importing Objects Using Oracle Data Pump(1.expdp)
- Exporting and Importing Objects Using Oracle Data Pump(2.impdp)
- oracle for in loop
- Using C++ objects in Delphi
- Oracle 11g R2 for Windows All in One 下载
- Oracle Tip: Understand how NULLs affect IN and EXISTS
- Oracle---Tip for SQL Database tuning and performance
- Microsoft OLE DB Provider for Oracle error '80004005'
- 背熟以下文章,就能脱离鼠标,光用键盘操作(转)
- HashSet /LinkedHashSet/TreeSet排序的不同
- 浅谈如何事半功倍的学习Struts框架
- ZOJ 1024 Calendar Game
- STL的is_heap算法
- Oracle Tip: Consider using Oracle objects for OLE in Windows
- 《WCF技术内幕》翻译27:第2部分_第5章_消息:使用消息头(上)
- junit4.0新特性
- AS3学习——共享对象
- AT070TN83
- 实时温度控制系统 2007.12
- AS3构造函数
- 单例模式
- 编写前置和后置条件的连贯接口库:CuttingEdge.Conditions