Oracle® Fusion Middleware
Part 32. Common Configuration

The Database Statement dialog enables you to enter an SQL query, stored procedure, or function call that the API Gateway runs to return a specific user's profile from a database.

The following fields should be completed on this screen:

Name:

Enter a name for this database query here.

Database Query:

Enter the actual SQL query, stored procedure, or function call in the text area provided. When executed, the query should return a single user's profile. The following are examples of SQL statements and stored procedures:

select * from users where username=${authentication.subject.id} 
 
{ call load_user (${authentication.subject.id}, ${out.param}) }
 
{ call ${out.param.cursor} := p_test.f_load_user(${authentication.subject.id}) }

These examples show that you can use selectors in the query. The selector that is most commonly used in this context is ${authentication.subject.id}, which specifies the message attribute that holds the identity of the authenticated user. For more details on selectors, see Selecting Configuration Values at Runtime.

Statement Type:

The database can take the form of an SQL query, stored procedure, or function call, as shown in the above examples. Select the appropriate radio button depending on whether the database query is an SQL Query or a Stored procedure/function call

Table Structure:

To process the result set that is returned by the database query, the API Gateway needs to know whether the user's attributes are structured as rows or columns in the database table.

The following example of a database table shows the user's attributes (Role, Dept, and Email) structured as table columns:

In the following table, the user's attributes have been structured as name-value pairs in table rows:

If the user's attributes are structured as column names in the database table, select the attributes as column names radio button. If the attributes are structured as name-value pair in table rows, select the attribute name-value pairs in rows option.