Oracle Catalog Functions

Add the following property in the studio.tra file to use the Oracle catalog functions.

TIBCO.BE.function.catalog.Oracle=true

Default value of the property is false. In the Built-in Functions Oracle catalog, this property enables/disables the following functions:

• closeResultSet
• commit
• executeQuery
• getColumnValueByIndex
• getColumnValueByName
• getConnection
• getConnectionWithTimeout
• next
• registerConnection
• releaseConnection
• rollback

The Oracle catalog function executeQuery is used only to select queries and not to perform the insert, update, and delete operations. Use the RDBMS catalog function to perform the insert, update, and delete operations, see Insert Operations and Update and Delete Operations.

The registerConnection function creates a pool of database connections and registers with the specified key. The syntax of the registerConnection function is:

void registerConnection(String key,String uri,int poolSize)

In the registerConnection function, the poolsize parameter defines the required number of database connections in the pool. The settings of the parameter depends on your project requirements, such as, using more connections improves the runtime performance when the registered connection is used in multiple rules.

Example

The following code shows the example usage of the Oracle catalog functions:

String connection ="/SharedResources/JDBCConnection.sharedjdbc";
Oracle.registerConnection("sstConnection",connection, 10);
Object cnx = Oracle.getConnection("sstConnection");
String sRequest =  "select to_char(count(*)) as NB from d_account";
String  nextResponse ;
Object[] args = {};
Object res = Oracle.executeQuery(cnx, sRequest, args);
if (res!= null){
   while(Oracle.next(res)) {
      nextResponse =  String.trim(Oracle.getColumnValueByName(res,"NB"));
    }
}
Oracle.closeResultSet(res);
Oracle.releaseConnection(cnx);