You can use oraext:query-database() This function Returns a node-set by executing the sql-query against the specified database. The second parameter rowset indicates if the rows should be enclosed in a element. The third parameter row indicates if each row should be enclosed in a element. The fourth parameter datasource is either a JDBC connect string (jdbc:oracle:thin:USER-NAME/PASSWORD@HOST:PORT:SID) or a JNDI name for the database. Only Oracle Thin Driver is supported if JDBC connect string is used. Usage: oraext:query-database(sqlquery as string, rowset as boolean, row as boolean, datasource as string). Example: oraext:query-database('select last_name from employee where id=1234',false(),false(),'jdbc:oracle:thin:scott/tiger@localhost:1521:ORCL')
thanks for reply; But this approach, the connection details would be hardcorded. Can we not achieve this using standard Database Adapter?
It is difficult using the standard database adapter approach. You may have to use nested sql queries.Following is a good example of the same.
If your only problem in using this that you are hard coding the connection details , you can use the datasource jndi name in the query. hence you will not be required to change your code when moving from one env to other env. You just have to take care of keeping the jndi name same in all the env.
Why cant you go for ADF to achieve your requirement end to end?? As mentioned by you Creating search page, and configuring dynamic where clause, everything is very easy in ADF.