Oracle Service - Service Instance Configuration

The Service Instance Configuration wizard screen adds new Oracle Service Instances. Two screens are used to configure the Service Instance.

Required fields are marked in red as in Owner List in the image below.

Fig. 1. Oracle Service - Service Instance Configuration Screen 1

Option	What it is
Owner List	Comma delimited list of owners of the objects to be retrieved from the Oracle database
Packages List	This field can be left blank if ALL package objects belonging to the listed owners need to be retrieved. Individual packages to be retrieved can be listed here, separated by a comma. The format is: Owner.PackageName
Get Packages	If set to false, no packages will be retrieved. If set to true, the service instance will try to retrieve ALL package objects for the specified owners (Owner List) unless the packages to be retrieved have been set in the Packages List field.
Functions List	This field can be left blank if ALL functions objects belonging to the listed owners need to be retrieved. Individual functions to be retrieved can be listed here, separated by a comma. The format is: Owner.FunctionName
Tables List	This field can be left blank if ALL table objects belonging to the listed owners need to be retrieved. Individual tables to be retrieved can be listed here, separated by a comma. The format is: Owner.TableName
Views List	This field can be left blank if ALL view objects belonging to the listed owners need to be retrieved. Individual views to be retrieved can be listed here, separated by a comma. The format is: Owner.ViewName
Get Functions	If set to false, no functions will be retrieved. If set to true, the service instance will try to retrieve ALL function objects for the specified owners (Owner List) unless the functions to be retrieved have been set in the Functions List field.
Get Procedures	If set to false, no procedures will be retrieved. If set to true, the service instance will try to retrieve ALL procedure objects for the specified owners (Owner List) unless the procedures to be retrieved have been set in the Procedures List field.
Complex Output as XML * see the Complex Output as XML section below	If set to True, returns complex data (REF CURSORS) as XML. It is set to True by default and maintains backward compatibility with existing service instances.
Connection String ** see the note below	If the default connection string causes a TNS error try using the full connection string, bypassing the tnsnames. i.e.: Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dlx.denallix.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));User Id=k2demo;Password=k2pass;
Get Views	If set to false, no views will be retrieved. If set to true, the service instance will try to retrieve ALL view objects for the specified owners (Owner List) unless the views to be retrieved have been set in the Views List field.
Procedures List	This field can be left blank if ALL procedures objects belonging to the listed owners need to be retrieved. Individual procedures to be retrieved can be listed here, separated by a comma. The format is: Owner.ProceduresName
ASCII Fields	As some properties may require ASCII characters within them, this field is used to specify which properties may contain ASCII by using: {decimal value of code}. At runtime the system will resolve the ASCII characters, for example, {33} will resolve to !. Separate multiple entries with a comma. Ascii characters with a decimal value from 0 to 127 are resolved. All ASCII characters are supported, including control codes. For example there are two ASCII control codes which are not printable but that you may wish to use: 31 – unit separator and 30 – record separator. If we had a method called MODIFY and a property of that method called ATTR_, the _ (ASCII value of 95) would need to be added to the ASCII field: {95} Then when we call our object MODIFY we could set the value of ATTR_ to be : PersonID{31}87{30} As a string this will look like: "PersonID87" but there are the two control code hidden in this string, the unit separator and the record separator. The different ASCII characters can be found at the following link. http://www.asciitable.com/
Get Tables	If set to false, no tables will be retrieved. If set to true, the service instance will try to retrieve ALL table objects for the specified owners (Owner List) unless the tables to be retrieved have been set in the Tables List field.
Security Provider	Specifies the Security Provider. Not applicable to this service
User Name	Specifies the User Name that should be used to access the specified Server
Password	Specifies the Password that should be used to access the specified Server
Extra	Provides additional information, for example dll paths needed. Not applicable to this service
Impersonate	The impersonate option allows the K2HostServer service account to impersonate the user.

Connection String: Probably the most common Oracle connection error is the TNS error (Oracle error ORA-12154). This error occurs when a connection using a connection identifier is used that can not be resolved into a connect descriptor using one of the naming methods configured in Oracle. The full connection string noted in the table above bypasses the tnsnames configuration in Oracle. If the short connection string fails (generating the ORA TNS error) the long connection string, adapted to the users environment, can be used or the TNSNAMES.ORA file can be regenerated on the client.

The following link is an excellent resource for more information Oracle error ORA-12154.

When configuring the rights the following applies:

If the User Name and password is configured and the Impersonate checkbox is selected (set to True) the service will ignore the User Name and password and use Impersonate.
If the User Name and password is configured and the Impersonate checkbox is not selected (set to False) the service will use SQL Authentication.
If the User Name and password is not configured and the Impersonate checkbox is not selected (set to False) the service will use the K2 Service Account.
If only the User Name and password is configured the service will use SQL Authentication.

The second Service Instance Configuration screen:

Fig. 2. Oracle Service - Service Instance Configuration Screen 2

Option	What it is
System Name	Automatically generated System Name for the new Service Instance
Display Name	Provides an automatically generated display name for the new Service Instance. The display name can be edited to be more user friendly. This allows users to distinguish between different Oracle Service Instances as multiple Oracle Service Instances can be created
Description	Provides a description for the new Service Instance

Complex Output as XML

As noted in the table above, the default setting for this flag is True.

When set to true, it means that REF CURSORS will return their data as a single property in XML format:

Compared to setting the flag to false and returning it as properties :