XPATH - Database Functions

Database Functions

lookup-table

query-database

sequence-next-val

lookup-table [Database Functions]

This function is used for table lookup operations within Oracle BPEL processes, typically as part of data manipulation or transformation tasks.

This function returns a string based on the SQL query generated from the parameters.
The string is obtained by executing:

SELECT outputColumn FROM table WHERE inputColumn = key 

Signature:

oraext:lookup-table(table, inputColumn, key, outputColumn, data source)

Arguments:

• table: The table from which to draw the data.
• inputColumn: The column within the table.
• key: The key value of the input column.
• outputColumn: The column to output the data.
• data source: The source of the data.

Example:

oraext:lookup-table('employee','id','1234','last_name','jdbc:oracle:thin:xyz/xyz@localhost:1521:ORCL')

RefrenceEx: Select last_name FROM employee WHERE id=1234

Ex:

oraext:lookup-table ("TB_ENTITE_ACCUEIL", "ID", /ns1:demandeAccesPort/ns0:demande/entiteAccueilId, "IDENTIFIANT", "jdbc/gap" )

RefrenceEx: Select IDENTIFIANT FROM TB_ENTITE_ACCUEIL WHERE ID =  /ns1:demandeAccesPort/ns0:demande/entiteAccueilId

XML:

<bpelx:assign>
  <bpelx:copy>
    <bpelx:from>
      <!-- Perform a table lookup based on a key -->
      oraext:lookup-table(
        'EmployeeTable',     <!-- Table name -->
        'EmployeeID',        <!-- Input column -->
        $inputVariable/key,  <!-- Key to search for -->
        'EmployeeName',      <!-- Output column -->
        'DataSource'         <!-- Data source reference -->
      )
    </bpelx:from>
    <bpelx:to>$outputVariable</bpelx:to>
  </bpelx:copy>
</bpelx:assign>

In this example, oraext:lookup-table is used to retrieve the 'EmployeeName' associated with a specific 'EmployeeID' in the 'EmployeeTable' data source. The result of the lookup is stored in the $outputVariable.

query-database [Database Functions]

This function is typically used to execute SQL queries against a database and retrieve results as part of a BPEL process.

This function returns a node set by executing the SQL query against the specified database.

Signature:

oraext:query-database(sqlquery as string, rowset as boolean, row as boolean, data source as string)

Arguments:

• sqlquery: The SQL query to perform.
• rowset: Indicates if the rows should be enclosed in an element.
• row: Indicates if each row should be enclosed in an element.
• data source: Either a JDBC connect string (jdbc:oracle:thin:username/password@host:port:sid) or a JNDI name for the database.

sqlquery as string: This parameter represents the SQL query that you want to execute against the database. It should be provided as a string, and it can be any valid SQL SELECT statement.

rowset as boolean: This parameter is a boolean flag that determines whether the result of the SQL query should be treated as a rowset (a set of rows) or as a single value. If set to true, the result is treated as a rowset; if set to false, it's treated as a single value.

row as boolean: Similar to the rowset parameter, this parameter is also a boolean flag. If set to true, it indicates that you are expecting multiple rows in the result set, and the result should be treated as a rowset. If set to false, it indicates that you are expecting a single row or value.

data source as string: This parameter specifies the data source or database connection to use for executing the SQL query. It typically references the data source defined in your BPEL process or a configuration file.

Ex:

xml

Copy code

<bpelx:assign>

  <bpelx:copy>

    <bpelx:from>

      <!-- Execute an SQL query against the database -->

      oraext:query-database(

        'SELECT EmployeeName FROM EmployeeTable WHERE EmployeeID = 123',

        false,   <!-- Treat the result as a single value -->

        false,   <!-- Expect a single row or value -->

        'DataSource' <!-- Data source reference -->

      )

    </bpelx:from>

    <bpelx:to>$outputVariable</bpelx:to>

  </bpelx:copy>

</bpelx:assign>

 oraext:query-database is used to execute an SQL query against the database and retrieve the 'EmployeeName' associated with a specific 'EmployeeID' (in this case, EmployeeID = 123). The result is stored in the $outputVariable.

Ex:

oraext:query-database(SELECT CODE FROM TB_TYPE_ACTIVITE WHERE ID = /ns0:guInput/ns0:typeActiviteid ,false(), false(),"jdbc/gap") 

oraext:query-database (concat ("SELECT EMAIL FROM TB_ENTITE_ACCUEIL WHERE PORT_ID=", /ns1:demandeAccesPort/ns0:demande/portId, " AND TYPE_ENTITE ='DGSN'" ), false(), false(), "jdbc/gap" )

 

Property IDs:

• namespace-uri: http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc
• namespace-prefix: oraext

 

sequence-next-val [Database Functions]

Returns the next value from a database sequence.

Database sequences are often used to generate unique, incremental values, such as primary key values for database records.

You execute it against a data source that can be either a JDBC connect string or a data source JNDI identifier.

The next value is obtained by executing the following:

SELECT sequence.nextval FROM dual

 

Signature:

oraext:sequence-next-val(sequence as string, data source as string)

sequence as string: This parameter represents the name of the database sequence from which you want to obtain the next value. The sequence name should be provided as a string.

data source as string: This parameter specifies the data source or database connection to use for accessing the database sequence. It typically references the data source defined in your BPEL process or a configuration file.




Ex:

<bpelx:assign>
  <bpelx:copy>
    <bpelx:from>
      <!-- Get the next value from a database sequence -->
      oraext:sequence-next-val(
        'EmployeeSeq',   <!-- Name of the database sequence -->
        'DataSource'     <!-- Data source reference -->
      )
    </bpelx:from>
    <bpelx:to>$outputVariable</bpelx:to>
  </bpelx:copy>
</bpelx:assign>

oraext:sequence-next-val ('employee_id_sequence','jdbc:oracle:thin:xyz/xyz@localhost:1521:ORCL')

 oraext:sequence-next-val is used to obtain the next value from a database sequence named 'EmployeeSeq.' The retrieved value is then stored in the $outputVariable for use in your BPEL process.

oraext:sequence-next-val ("MANDATAIRE_SEQ", "jdbc/gap" )

Arguments:

• sequence: The sequence number in the database.
• data source: Either a JDBC connect string or a data source JNDI identifier.

Property IDs:

·         namespace-uri:

http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc

·         namespace-prefix: oraext

In the above blogs, I have described a few XPath functions based on my experience while working on  Project.