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 Lets 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: 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. 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 http://www.databasejournal.com/features/oracle/article.php/2222781#fig1 http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php Integration Repository The message type for the sample table will look like this Actions in Integration Directory Use the table function in the JDBC Sender Adapter as described in the screen shot below.