Connect to Oracle

Configuring SI Web Parts to use the Oracle system
In the Catalog property of the SI Web Part, defining the entity and operations for Oracle is the same as with WebServices or SQL Server. The only difference is that when defining the operations, there is a new field called Method Type for Oracle. It is a dropdown field with the following choices:

1. TableDirect - select this type to return all the data in a table. Specify the schema and table name in the Method field below this field.
2. Stored Procedure - select this type to use a stored procedure to update or return data. Specify the schema and stored procedure in the Method field below this field. Once a stored procedure is selected, the configurator updates with the parameters that are defined for the stored procedure. It is recommended that you do not add or remove these parameters or change the "Name", "Type", or "Usage" fields.
3. Text - select this type to use free form PL/SQL string to query or update the database. If your SQL string requires parameters, you will need to add them manually by clicking the Add Parameter button.

The configurator retrieves the meta?data for you when you specify the method type to be either TableDirect or Stored Procedure. In the below screenshot, the configurator list all the schemas (object owner) and the tables the schema owns. This allows the user to easily identify the table or procedure they want to use.

Adding new Oracle Systems in the SI Configuration File
The SI Configuration File contains the definition for the Systems and Services that are available for the SI WebParts. You may have multiple element in the SI Configuration File. The system type of Oracle is now supported. Use this system type to connect to Oracle. You must specify the Connection String in the Service Settings when you use this type. Configuring an Oracle system for an Oracle database using the Configuration Editor is very similar to configuring a SqlServer sstem. You need to specify the connection string property. A sample connection string would be "User ID=xxx;Password=xxx;Host=xxx;SID=xxx;Direct=true". Connecting to Oracle in Direct mode, you must know the address of the computer where the Oracle server is, port number to connect, and database global system identifier (SID). The ConnectionString property must include the following fragments:

• Direct - Whether to use Direct mode
• Data Source (or Host, or Server) ? Oracle server's IP address or DNS name
• Port - Port number to connect to
• SID - System identifier (Global Database Name)

The whole connection string may look as follows: "User Id=Scott;Password=tiger;Direct=true;Data Source=192.168.0.1;Port=1521;SID=orcl". Port number defaults to 1521 (most commonly used in Oracle databases). Thus you can omit this option in the connection string if your Oracle server uses the default port number.

You can connect to Multi?Threaded Server using Direct mode. The server must be configured to use specific port and TTC protocol. This can help you avoid firewall conflicts.If you have the Oracle client installed, you can use Direct=false flag in the connection string. But if you do not have the Oracle client installed, make sure Direct=true is specified. Note that when Direct is false (by default), Data Source option has another meaning: you must specify either TNS name or TNS description. Port and SID options are not allowed when Direct is false.

You may also use Windows authentication, just leave the User ID and Password blank. In order to use Windows Authentication, you must have the Oracle client installed.