Using Oracle with Iron Speed Designer

Oracle Provider Use

Iron Speed Designer uses two different Oracle providers to connect to an Oracle database.  Iron Speed Designer uses Microsoft’s “OLE DB provider for Oracle” to read the database schema from an Oracle database.  In contrast, applications built with Iron Speed Designer use Microsoft’s “Oracle Provider for .NET”.  The Oracle OLEDB provider is not used because it does not work in all cases, and applications cannot be successfully created with this provider.

Both of these providers rely on Oracle Call Interface (OCI) that is part of the Oracle Client.  The Oracle Client must be installed on your development and production system to use Oracle with Iron Speed Designer and the application.

Using an Oracle Database with Iron Speed Designer

Iron Speed Designer does not support multiple homes installations. If there are multiple Oracle installations (multiple homes) on the server Iron Speed Designer will only see the first one and if the TNS string is not found in the first home, Designer will not be able to connect. See more details on Oracle homes here: https://docs.oracle.com/cd/B10501_01/em.920/a96697/moh.htm

You need to perform the following steps to use an Oracle database with Iron Speed Designer.  Please note that each of these steps is important, and we find most of the problems are corrected by following each of these steps in order.

Step 1:  Install Oracle Client on your system.  This can be downloaded from Oracle’s website as part of the Oracle Data Provider for .NET (ODP.NET)

http://www.oracle.com/technology/software/tech/dotnet/odt_index.html

http://otn.oracle.com/software/content.html (Oracle Technology Network link)

You can install the “Oracle Developer Tools for Visual Studio .NET” on your development system or install the version of Oracle Client without the Developer Tools for the production system.  The installed software must include the Oracle Client.

Step 2:  Change permissions to allow IUSR_machinename and ASPNET user full access to the Oracle folder on your system.  We have determined that this is a very important step that is required to use Oracle with Iron Speed Designer.  You can do this using Windows Explorer as follows:

  1. Open Windows Explorer and select the ORACLE_HOME folder, e.g. C:\Oracle

  2. Right-click on the folder and look at its properties.

  3. Go to the Security tab and select Add.

  4. (Important) Press the Locations button and select the name of the local machine.  Typically this is the first name listed on the Locations dialog.

  5. Click on the Advanced tab.

  6. Click on the Find Now button to display a list of all users and groups on the local machine.

  7. Select the ASPNET user and the IUSR_machinename.  Use Control-Click to select the two users.

  8. Click OK to return to the Select Users or Groups dialog.

  9. Click OK to return to the Security tab.

  10. Click ASPNET user and select Full Control.

  11. Click IUSR_machinename and select Full Control.

  12. Click OK to apply permissions to all subfolders and files recursively.  This may take a minute or two to update.

Step 3:  Reboot your system. We have determined that this is an important requirement and must be done after the installation and after the permissions have been set.

Step 4:  Update the TNSNAMES.ORA file using the Oracle Net Configuration Assistant utility. 

In the event that the Oracle Net Configuration Assistant is not available, the TNSNAMES.ORA can be updated using a text editor. This file needs to contain information about your servers.  Each entry in this file is of the form:

MyServer.MyDomain.COM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = MyMachine.MyDomain.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

You will need to replace MyServer, MyDomain and MyMachine in the above example to your server names.  The TNSNAMES.ORA file is typically located in the Oracle Client’s Network\Admin folder such as:

C:\Oracle\product\10.2.0\client_1\network\ADMIN

Note that if you had a previous TNSNAMES.ORA file, it may be resident in a different folder after the installation of the Oracle Data Provider for .NET.  In this case, you need to copy or update the TNSNAMES.ORA file in the newly installed folder.

Once you have performed these steps, you will be able to connect to your Oracle server from both Iron Speed Designer and the application.

The above steps also address the following errors:

  1. For Oracle Provider for OLE DB

    1. Error Type:  Microsoft OLE DB Service Components (0x80070005)
      Access is denied.

    2. OraOLEDB.Oracle Provider is not registered on the local machine

  2. For Microsoft OLE DB Provider for Oracle

    1. Error Type:  Microsoft OLE DB Provider for Oracle (0x80004005)
      Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.

    2. Error Type:  Microsoft OLE DB Provider for Oracle (0x80004005)
      Oracle error occurred, but error message could not be retrieved from Oracle.

  3. For Microsoft .NET Framework Data Provider for Oracle

    System.DllNotFoundException: Unable to load DLL (oci.dll).

    at System.Data.OracleClient.DBObjectPool.GetObject(Boolean&isInTransaction)

    at System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(String encryptedConnectionString,OracleConnectionString options, Boolean& isInTransaction)

    at System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionString parsedConnectionString, Object transact)

    at System.Data.OracleClient.OracleConnection.Open()

    at WCWSItemAvailability.ETAAvailability.SingleAvailability(String ItemNumber, String BusinessUnit, Int32 OrderQty, Int32& AvailableQty, Int32& OnHandQty, String& ETADate, Int32& Error)

    at WCWSItemAvailability.ETAAvailability.XMLAvailability(String XMLInput)

  4. For any of the providers:

    1. The Specified Module Could Not Be Found

    2. ORA-00604: error occurred at recursive SQL level 1
      ORA-12705: invalid or unknown NLS parameter value specified

    3. Unable to load DLL (OraOps9.dll)
      [DllNotFoundException: Unable to load DLL (OraOps9.dll).]

    4. System Error 998 trying to run ASP page

    5. System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

    6. ORA-12154: TNS:could not resolve service name

    7. OleDbException (0x80040154): No error information available: REGDB_E_CLASSNOTREG(0x80040154).]
      [InvalidOperationException: The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.]
      System.Data.OleDb.OleDbConnection.CreateProviderError(Int32 hr) +82
      System.Data.OleDb.OleDbConnection.CreateProvider(OleDbConnectionString constr) +107
      System.Data.OleDb.OleDbConnection.Open() +203

In some cases, applications that were working fine with previous versions of Oracle software will stop working when they upgrade to a new version of Oracle Client.  To fix this problem, you will need to set the permissions for the ASPNET and IUSR_machinename.

Using Oracle XE on Microsoft Windows XP Professional Edition, 64 bit

Iron Speed Designer creates applications that will run in 64-bit mode.  Use the following general steps to create your application in a 64-bit environment using Oracle XE.

Step 1:  Install Oracle XE on your system.

Step 2:  Install Oracle 11g and Oracle Developer Tools for Visual Studio on your system.

Step 3:  Copy the client_1 folder from Oracle 11g Client installation to C:\oraclexe\app\oracle\product\10.2.0.

Step 4:  Launch Iron Speed Designer and create a new application using the Application Wizard.

Step 5:  Specify the name of your machine as the server.  Do not include the domain in the name.

Step 6:  Enter your user ID and password.  You may need to create a new user with DBA rights to get it to connect.

Additional Resources

Build a .NET Application on the Oracle Database

http://www.oracle.com/technology/pub/articles/cook_dotnet.html

Oracle Developer Tools for Visual Studio .NET

http://www.oracle.com/technology/tech/dotnet/tools/index.html

Microsoft’s .NET Managed Provider for Oracle (download link)

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=4F55D429-17DC-45EA-BFB3-076D1C052524

How To Troubleshoot an ASP-to-Oracle Connectivity Problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q255084

Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q259959

How to connect to an Oracle database by using ASP and ADO

http://support.microsoft.com/kb/193225/EN-US/

Oracle TNS: Authentication Service Initialization Error

http://support.microsoft.com/kb/178391/

Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider

http://support.microsoft.com/kb/244661/

Oracle Call Interface

http://www.oracle.com/technology/tech/oci/index.html

http://orafaq.com/faqoci.htm

Database Access using .NET Data Providers

http://www.akadia.com/services/dotnet_dbaccess.html

See Also

Using Oracle with Iron Speed Designer

Database Field Support in Oracle

Minimum Permissions Needed for Oracle Users

Changing an Oracle Database Schema Name in an Application

PL/SQL Stored Procedures

Matching Oracle Sequences with Primary Keys