Data Types :-

When you use VB, ADO and Oracle OLE DB Provider, there are four environments, each with their own idea about data types.
· Native VB data types
· ADO data types
· OLE DB data types
· Native Oracle data types
ADO data types map almost directly into OLE DB data types, so that isn't too much of a problem. The Oracle OLE DB Provider documentation includes a table showing the mapping of Oracle data types to OLE DB data types (see Chapter 2 OraOLEDB Features; OraOLEDB Provider Specific Features section). Although in a somewhat obscure place, the mapping of ADO to OLE DB data types is shown in the Enumerated Constants part of ADO programming reference documentation (MSDN; Platform SDK; Data Access Services; Microsoft Data Access Components (MDAC) SDK; Microsoft ActiveX Data Objects (ADO); ADO API Reference; ADO Enumerated Constants; DataTypeEnum). When it comes to finding out what VB data type best maps ADO data types (and indirectly, Oracle data types), your kind of on your own but see http://www.able-consulting.com/ADODataTypeEnum.htm for some hints. Most are pretty obvious (e.g. varchar2 maps to string). VB has little or no concept of numeric precision and scale, so dealing correctly with some numeric Oracle data types may be a challenge.

Gotcha's Oracle Services for MTS instructions not quite correct :-

The Oracle document Using Microsoft Transaction Server with Oracle8 explains fairly well what Oracle Services for MTS is and how to configure. However, there are two parts that are not quite correct.

· Step 2 says to logoff and logon with the username that is to be used by the Oracle Services for MTS (e.g. MTSSYS). This is not necessary; you can complete the remaining steps while logged on with your normal NT administrative account. "Service accounts" are not associated with people and often don't have the "logon interactively" right, so logging on with the Oracle Services for MTS account doesn't make much sense.
· If you are using OS Authentication with Oracle Services for MTS, there are special instructions in Appendix A of Using Microsoft Transaction Server with Oracle8 for configuring this. One of the instructions is to remove the username and password from the Oracle Manager for MTS Services panel for configuring a service. This is incorrect. If you remove the username and password, the dialog will not complete successfully and Oracle Services for MTS won't work. Leave the username and password fields filled in. Although the values in these fields are not relevant when OS Authentication is used, their absence causes an obscure problem in the dialog that caused me much grief, as explained in the Introduction.

Oracle Services for MTS is not set to start automatically :-

Configuring Oracle Services for MTS using Oracle Manager for MTS Services, creates a new NT service. Unfortunately, at least in some cases, this service is not configured to start automatically. This means that Oracle Services for MTS will not be available after the operating system is restarted. Use the NT Services dialog (e.g. Control Panel, Services) to set the Oracle Services for MTS service to start automatically. The name of the (first instance of) Oracle Services for MTS service is OracleMTSService0.

Oracle Services for MTS may fail during start-up:-

When the Oracle Services for MTS is set to start automatically, it may fail during an operating system restart because the Oracle instance is not yet running. If you suspect this problem, look in the Oracle Services for MTS trace file (in the Oracle Home\oramts\Trace folder) for records like:
106953: [150] OracleMgr::Initialize - Error: GetOCIConn failed with error 81. 121955: [150] OracleMgr::Initialize - Start. 121975: [150] OracleMgr::GetOCIConn - Error: failed to connect. ORA-01033: ORACLE initialization or shutdown in progress
This can be a problem on slower servers. If you get this problem, use regedt32 to add the following registry value:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
OracleMTSService0
DependOnService REG_MULTI_SZ OracleServiceName
"OracleServiceName" is the name of the NT service for the Oracle instance. Normally this will be something like "OracleServiceabcd", where abcd is the Oracle service name for the instance.

Hierarchical Recordsets not updateable :

If you use the Data Shaping service to build a hierarchical ADO recordset, you will likely find that you can not make any changes to field values in the recordset. This applies to fields in the parent recordset as well as the child recordsets. If you attempt to change a field's value using VB code such as: Rs.fields(0).value = "abc" you get a run time error to the affect that a multi-step operation has failed (not very enlightening). Oracle says this problem is fixed in Oracle 8.1.7. If you use the recordset.clone method to create a copy of the recordset, that copy is not updateable either. So, if you need to bypass this problem, see MS KB article Q241202. HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset. This is a good article and includes sample code you can copy that does the job.

Sidebars
Instance vs database :

Oracle documentation distinguishes between an Oracle instance and an Oracle database. A database is the set of files and associated permanent control information that constitute the persistent, managed data. An instance is the set or processes and associated control information that manipulate the content of a database. Since (unless Oracle Parallel Server feature is used) there is always a one-to-one relationship between an instance and a database, this is not always a useful distinction. Common usage tends to blur these distinctions, with the combined instance and database merely being referred to as an Oracle database. In this article, I've tried to use the terms as they are used in the Oracle documentation, but perhaps not always successfully.

Identifying and locating an Oracle instance :

In a client-server or n-tier environment there must be a mechanism for the Oracle database client (which may be an MTS component) to locate and establish communication with the processes that comprise the target Oracle instance. Oracle provides several mechanisms for solving this problem. Net8, the part of Oracle that communicates between Oracle client programs and Oracle instance, implements these mechanisms. One way is to have a set of configuration files on each Oracle client computer that has the required information. These files provide a mapping between a TNS or Net Service name and the instance, which is often on a different computer. Another mechanism, which avoids having to replicate the mapping information to all clients, is the Oracle Name Service. Instances of the Oracle Name Service on different computers, in conjunction with the Listener service, co-operate to maintain the required name to computer and instance mappings in an "Enterprise". A client only needs to know how to contact a Name Service instance and can then use that service to find out how to communicate with any Oracle instance known to any of the Name Service instances. The Net Service Name exposed by the Oracle Name Service or recorded in the TNSName configuration files is used in the ADO Connection's Data Source property to identify the particular Oracle instance that is to be associated with that connection. The Oracle Client uses whatever Oracle name resolution mechanisms it has been configured to resolve the Net Service Name to the target Oracle instance. This Net Service Name is also used by the Oracle Services for MTS to establish communication with the particular Oracle instance it is configured to co-operate with.

Oracle Security :

Security for any database is an important issue. Oracle has a fairly sophisticated built in security system. However, when users use many services, including one or more Oracle databases, maintaining the user information separately for each service gets to be a user and administrative nightmare. Users get multiple user names and passwords to remember and the passwords have to be changed every so often. It may be possible, in the general, theoretical sense, to keep all security related information in one place. However, particularly when products from multiple vendors are used together, we have no way to reach this utopia yet. Each service has unique things that need to be "secured" and the same user may have different rights and permissions for different instances of a service. However, there are ways to reduce the problem and Oracle provides facilities for this. Instead of "authenticating" users by passwords stored in the Oracle database, Oracle allows you to use the authentication service of the operating system it is installed on or more sophisticated "enterprise" schemes, such as Kerberos. Using the operating system's authentication service is called OS Authentication. Even though authentication is handled by the operating system, each user must still be identified to Oracle and be granted the appropriate System Privileges and Roles. On Windows NT and Windows 2000, this is done using the Oracle Administration Assistant for NT. Using this tool you can create External OS Users and grant External OS Roles to them. See the Oracle Administrator's Guide, Chapter 8: Authenticating Database Users with Windows for additional information.

About the author :

Bruce Sanderson was born in England in 1947 and emigrated to Canada with his parents when he was seven. After graduating from the BC Institute of Technology (BCIT) in 1967, Bruce moved to Ottawa, Ontario and worked for Bell Northern Research. While at BNR, he worked initially on support software (e.g. assembler, loader, data compiler, simulator) for the electronic telephone exchange then under development. Later, Bruce provided operating system programming support for IBM's CP 67/CMS and VM/CMS. Besides diagnosing and fixing problems, Bruce was responsible for performance measurement. Since 1975, Bruce has lived in Victoria and worked for the BC Assessment, first as an application programmer, and now as System Architect. Part of his job is solving problems others can't solve and figuring out how to best use new technologies in the BCA applications. This often involves "breaking new ground" and is well known by his co-workers for devouring vendor documentation and digging out how things work.
Over the years, Bruce has worked on many different computer systems and become fluent in several programming languages, including 360 Assembler, COBOL, PL/1 and VB. Having worked with it for many years, he is an expert in IBM's IMS DB/DC.
Bruce is also an avid reader and frequent contributor to various Microsoft software newsgroups.
Bruce is married and has three children; two boys and a girl.