Posts

Showing posts with the label SQL

SQL Authentication Provider for Oracle ADF

Image
JDev: 12.2.1.3.0

Using SQL authentication provider turns out to be a much needed feature for applications which have been transformed into ADF web applications from legacy forms. For applications with really large user bases, it becomes quite tedious to transfer users from database tables to other providers like LDAP.

We do have quite a lot of discussions on this topic. This article will be an extension of two great blogs by Edwin Biemond and Chris Muir, and intends to provide a complete solution with this approach.

The detailed initial steps of this approach are mentioned in the above two blogs. So I will quickly run through them.

I set up a new auth provider from WebLogic console - I will call it CustomSQLAuthenticator.


I would want this authentication system to take precedence over the DefaultAuthenticator. In order to do that, I would do the following:

1) Re-order the providers, to bring up CustomSQLAuthenticator above DefaultAuthenticator.


2) Set the control flag for both Custom…

Advanced PL/SQL integration with ADF (new API)

Image
JDev: 12.2.1.3.0 Source: GitHub
Till ADF 11g, we have been integrating ADF with PL-SQL modules having user-defined types with "oracle.sql" packages. We have used oracle.sql.StructDescriptor and oracle.sql.ArrayDescriptor to create implementations of user-defined types on Oracle database.
Since the migration to 12c, we have been getting a warning that most of oracle.sql's APIs have been deprecated.


The new solution is to use java.sql.Struct (not STRUCT) and java.sql.Array (not ARRAY).
However, using the Array API has been made a bit tricky. Now we do not have the support of ArrayDescriptor to create a definition object for the user-defined type. Let us see how exactly we go about it.
This is the use case we will solve: 1) Call a packaged procedure with a user-defined type as IN parameter. 2) Modify the object and return as an OUT parameter of a user-defined type. 3) Intercept the out parameter in ADF and read the modified contents.
We will be using the default HR schema. …

Advanced programmatic view object in 12.2.1.x

Image
JDev version: 12.2.1.3.0 Source: GitHub
The previous versions of ADF required a lot of knowledge of framework methods, while attempting to create programmatic view objects based on non-SQL data sources, such as a PL/SQL ref cursor.
This blog shows you how to create a search form, which passes a department id to a packaged procedure, and display results based on a ref cursor returned by the procedure.
Create a simple procedure, which returns some employee data, based on a department_id entered by user.

Create a view object - EmployeesVO, and select Programmatic while selecting Data Source. Create the necessary view object implementation class. Create transient attributes for the columns you need to display on the GUI.


If you open the EmployeesVOImpl class, you will see that JDev has extended it from oracle.jbo.server.ProgrammaticViewObjectImpl.

This class contains an overridden getScrollableData method, and this is the only method we need to populate in order to display data from the …

Efficient dynamic query in ADF

Image
JDev version: 12.1.3.0.0 Source Code: GitHub
Use Case: Quite often, we are required to attach dynamic where clause to an ADF view objects's query. Although we do have view criteria to support our cause, more often than not, they do not quite support complex business logic to derive dynamic where clauses.
The GUI:


We usually make use of ViewObject's setWhereClause API to attach a dynamic where clause. However, a common mistake is to write a dynamic query like this:

Now this approach, although technically correct, leads to 2 problems: 1. SQL Injection - imagine someone passing first-name as 'Steven' (with quotes). Your entire SQL statement goes for a toss. 2. Improper query caching - let's explain this with some data and examples.
Imagine you have a very complex query, running on a table with a huge volume of records. The DBAs will usually attach a SQL profile to your query. Now when you have a sql which is a new String for every execution, it effectively creates a ne…