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.

 

How to Create Rest Mock Service in SOAP UI and use as dummy service in Jdeveloper

Mocking:

Mocking is creating virtual service that works like a real service.

It enables you to stay productive while the service is being implemented.

Mocks could be used for testing and developing the front end even when the back end is not available.

To Create Rest Mock Service in SOAP UI and we can use as dummy service in Jdeveloper

1        Create New REST Project

2        paste- http://localhost:7101

3        Rite click -> Generate REST MOCK Service

4        start this mock  service

5        Crete new REST service with new REST Mock url.

          http://localhost:8089/

we can find this host and port in property section

Please find below detailed steps to create Mock service:

S1: Create New REST Project

S2: Rename Project name (Give an appropriate name for the MockService)

S3: Rite click -> Generate REST MOCK Service

S4: Start Created Mock Service by clicking green play button

You can view this mock service is started on port 8089 (This can be random port)

Below is the response I am setting up with code value.

S5: Update Rest service with new port and test the service. Or  

Crete new REST service with new REST Mock url .

http://localhost:8089/

We can find this host and port in property section

S6: we can use same url and implement rest service in jdeveloper.

http://localhost:8089/