Oracle Service

The Oracle Service Service Type allows K2 to integrate with an Oracle database by exposing Tables, Views, Functions, Packages and Stored Procedures in an Oracle database as SmartObjects.

Prerequisites

The following items are necessary for the Oracle service type to function:

  • A supported version of Oracle.
  • 64-bit Oracle Data Access Components (ODAC) 11.2 Release 6 (11.2.0.4.0) (download from Oracle site).
  • If you are using K2 Cloud and connecting to an Oracle database on-premises, the target Oracle system must be reachable by the K2 server. Some approaches you can use to do this are K2 Cloud Secure Data Access (SDA), or a VPN or other secured network connection between the Cloud environment and the environment where the Oracle database resides.

Service Authentication

The Oracle service supports the following Authentication Modes

  • Static
  • Impersonation
  • Service Account

Service Keys (Service Instance Configuration Settings)

Key Can be modified Data Type Sample Value Notes
Owner List Yes Text system Comma delimited list of owners of the objects to be retrieved from the Oracle database.
Packages List Yes Text Owner.PackageName 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.
Get Packages Yes True/ False True 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 Yes Text Owner.FunctionName 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.
Tables List Yes Text Owner.TableName 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.
Views List Yes Text Owner.ViewName 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.
Get Functions Yes True/ False True 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 Yes True/ False True 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

Yes True/ False True

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.

The default setting for this flag is True.

When set to true, the 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 :
Connection String Yes Text 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;Validate Connection=true; Connection Timeout=120

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 example 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.

If you are experiencing connection reliability issues with the broker, adding “Validate Connection=true” in the connection string can help improve reliability.

This property contains sensitive information. You can enter and see your values when you first configure this value. The value will be masked when the service instance is updated.

Adding "Connection Timeout=120" in the connection string can help prevent timeout issues when returning large amounts of records. Refer to Oracle's documentation for more information on the available connection string values.

Get Views Yes True/ False True 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 Yes Text Owner.ProceduresName 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.
ASCII Fields Yes Text Exchange2010

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 you 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 you call your object MODIFY you 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 Yes True/ False True 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
Decimal Precision and Scale Yes True/ False True The Oracle NUMBER datatype stores fixed and floating-point numbers. The precision defines the total number of digits. The scale defines the number of digits to the right of the decimal point. If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero. Setting this option to True allows the use of datatype NUMBER(p,s) .
Include Hidden Columns Yes True/ False True Table columns are visible by default. You set a column in a table as invisible either during CREATE TABLE or modifying an existing table using the ALTER TABLE command. This option includes those hidden columns in the data sent through from the K2 Oracle service instance.

Service Instance Category and Object Structure

The Oracle service can create Service Objects for Tables, Views, Functions, Packages and Stored Procedures from an Oracle database. After creating a Service Instance, cross-check the available Service Objects with the Oracle database schema for any entities that you require but were not created as Service Objects. Missing Service Objects may be as a result of unsupported data types. Correct the data type and refresh the Service Instance to register the missing objects.

All the CRUD+L (Create, Read, Update, Delete, List) operations are only created as service object methods if there is a primary key on the table. If there is no primary key on the table only a list method is created.

There is no concept of an Identity Key in Oracle. You can only designate a column as a Primary Key. To provide an auto-increment value to a Primary Key (or any field you wish), you need to create or use a Sequence. Sequences are database-wide. You can ask a sequence for the next available value which you can use to insert into the Primary key field. To automate it, create a trigger on INSERT to get the next value from sequence before inserting.

It is possible for more than one table to use the same sequence since they are database-wide. It is therefore good practice to name the sequence something that identifies it with the table name.
For example:
Consider a sequence as a Next Customer ticket number dispenser. You could have two departments using the same dispenser or they could each have their own. In K2, a situation could arise where a table might have a Primary key which is NOT NULLABLE. This means that a field is required for ServiceObjects and SmartObjects derived from that table. The problem is that the table might be using a sequence to populate the primary key, and K2 has no way of deriving that information by interrogating the database schema. The workaround is to send a NULL value as the primary key field value for the Create method. This gets you past the required validation and leaves it to the database to insert the correct value obtained from the sequence. To send a NULL, click the Null button on the SmartObject tester tool next to the text box. Using the API you can set it to null or set the value itself to scnull.

Views support the Select statement and only create a Service Object List method to return all rows. If you want to return rows in a paged fashion, enable paging in the K2 Designer.

For integration with stored procedures, the following method types are generated:

  1. List methods when stored procedures return data
  2. Execute methods when stored procedures return NO data

Stored Procedures must be declared as an Oracle Synonym for use with K2. Use the CREATE SYNONYM command to declare the Stored Procedure.

Functions support the Select statement and only expose a List method.

Packages support the Select statement and yield the standard CRUD+L methods.

SmartObjects

SmartObjects can be automatically created by selecting the Generate SmartObjects for this Service Instance check box when creating a new Service Instance. Designers can use the SmartObject design tools to build advanced SmartObjects that leverage the Service Objects in this service. It is recommended to use the SmartObject design tools to create SmartObjects rather than generating SmartObjects, since this allows better control over the naming, behavior and design of the SmartObject and its methods and properties.

Considerations

  • If the ODAC is not installed prior to registering a new service instance, a Could not load file or assembly error occurs because the Oracle service type has a dependency on the ODAC components.
  • The K2 Server must be restarted after installing the Oracle ODAC components.
  • Output parameters cannot be used
  • All the CRUD+L (Create, Read, Update, Delete, List) operations are only created as service object methods if there is a primary key on the table. If there is no primary key on the table only a list method is created.
  • Output and Read methods cannot be used
  • Stored Procedures must be declared as an Oracle Synonym for use with K2. Use the CREATE SYNONYM command to declare the Stored Procedure
  • Adding “Validate Connection=true” in the connection string can help improve reliability
  • Adding "Connection Timeout=120" in the connection string can help prevent timeout issues when returning large amounts of records