Building Generic Search Screens Using Dynamic SQL
Most modern computer applications include an “advanced search” screen that allows users to enter multiple criteria and parameters. Unfortunately, behind the scenes, this functionality can cause major implementation headaches on the back end because the resulting queries may reference an unknown number of tables and/or columns and may return unknown numbers of rows. In addition, the results may mutate depending upon the parameters. There are multiple ways of solving this problem with various levels of efficiency. This presentation will demonstrate how to handle generic search functionality within an Oracle database by utilizing Dynamic SQL, object collections, and a bit of creativity.
Presented by Michael Rosenblum, Software Architect/Development DBA, Dulcian, Inc.
Michael Rosenblum is a Software Architect/Development DBA at Dulcian, Inc. where he is responsible for system tuning and application architecture. Michael supports Dulcian developers by writing complex PL/SQL routines and researching new features. He is the co-author of PL/SQL for Dummies (Wiley Press, 2006), PL/SQL Performance Tuning Tips & Techniques (Oracle Press, 2014), contributing author of Expert PL/SQL Practices (APress, 2011), and author of multiple database related articles and conference papers. Michael is an Oracle ACE, a frequent presenter at various Oracle user group conferences (Oracle OpenWorld, ODTUG, IOUG Collaborate, RMOUG, NYOUG, etc.)