Skip to Content

Oracle Table Functions and JDBC Sender Adapter

Overview SAP Note 941317 outlines some restrictions on the usage of stored procedures with the Oracle JDBC Driver for Release 10g and XI 7.0 JDBC sender adapter. As it is quite common to use the stored procedure technique with the XI JDBC adapter, this weblog aims to highlight the recommended table functions technique with Oracle Database Release 10g 10.2.x JDBC Driver. Business Scenario Let’s assume a scenario wherein the JDBC adapter polls an Oracle database to read records from a table. A sample table schema (Table name: Student) is described below: image Here the READFLAG field is a boolean field and indicates the read status of the record. Whenever XI picks up a record, it updates this field to indicate that the record has been processed. Oracle Table Functions What are Table Functions? • Table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table of PL/SQL collection variable. • Table functions are a new feature in Oracle 9i that allow us to define a set of PL/SQL statements that will, when queried, behave just as a regular query to table would. • Table functions are used to return collections that mimic tables. • We can call a Table Function within the FROM clause of a SQL statement and have it return a resultset that mimics what we would normally expect from a traditional SQL SELECT statement. What is the benefit of Table Functions? The added benefit to having a table function is that we can perform transformations to the data in question before it is returned in the resultset. This is of great use when perform ETL operations (Transformation Load) Creating Table Functions Step 1: Create an “Object Row Type” CREATE TYPE object_row_type AS OBJECT( ID VARCHAR2(10), NAME VARCHAR2(10), BIRTHYEAR VARCHAR2(10), BIRTHMONTH VARCHAR2(10), READFLAG CHAR(1) ); We have defined type ‘object_row_type’. It is a row type which contains the fields what we defined in table student. (Not necessary be same, in our case we return records from the table student using this object type). Step 2: Create a “Table of Object Row Type” CREATE TYPE object_table_type AS TABLE OF object_row_type; We have created a table of ‘object_row_type’ called ‘object_table_type’. The table ‘object_table_type‘ is what we will use to return the rows from the table function within a simple select statement. Step 3: Create Function CREATE OR REPLACE FUNCTION get_all_objects RETURN object_table_type PIPELINED AS BEGIN FOR cur IN(SELECT * FROM STUNDENT WHERE READFLAG = ‘ ‘) LOOP PIPE ROW (object_row_type(cur.ID, cur.NAME, cur.BIRTHYEAR, cur.BIRTHMONTH, cur.READFLAG)); END LOOP; RETURN; END; This creates a function ‘get_all_objects’ that is used to retrieve records. Executing Table Function The Table Function can be used in the FROM clause of a query, or like a column name in the SELECT list of a query. image Here, we pass our function “get_all_objects” in the TABLE clause of the SELECT statement to execute the function. The same command is issued as value for the filed ‘Query SQL Statement’ under Parameter tab in Sender JDBC Adapter. Further Info on the syntax and detailed explanation of the PL/SQL statement Integration Repository The message type for the sample table will look like this image Actions in Integration Directory Use the table function in the JDBC Sender Adapter as described in the screen shot below. image
You must be Logged on to comment or reply to a post.
  • Hello,
    thank you for this useful weblog. My question is, how can you make sure that the update statement marks only those records that have been processed by XI? Think of a situation where another DB process inserts additional records in the table that haven’t been picked up by the select statement. In this case the Update statement would also mark the new records . As a consequence XI would never pick them up.

    Best regards,

  • Its possible to avoid mutiple DB’s try to update the rescued rows at the same time. For this, SAP XI sender adapter, provides to set the option ‘Transaction Isolation Level’. To understand how to set this, read help document. Thank you.
    • Hi thanks alot for that solution. Actually we tried that but we do not have transaction isolation level access so this solution ain’t possible. Can u please suggest some 0ther solution.