Dynamic SQL Engine using Variable Parameter Input

Whitepaper that discusses the creation and use of Dynamic SQL and the building of search conditions/business rules on the fly, to return desired data.

This document discusses the use of refcursors and associative arrays to build dynamic conditional logic. The resulting logic is capable of simple and complex scenarios. We will provide two versions of the api with this whitepaper. Version#1 will utilize a refcursor for the parameter criteria grid and the other will use an associative array to perform the same. The latter is more than likely far more cross platform, since the use of refcursors requires persistent connections.

Note : indicator will apply to both use of REFCURSOR (dynsql_engine.q_demo_pg_current_snap_c1) and Associative Array (dynsql_engine.q_demo_pg_current_snap_c2)

Essentially it provides the following key features :

Selective Column Return using two methods
o Dynamic selection
o No bind variables required
o Table driven

Pagination or Chunking
o Define page/chunk size dynamically
o Very few bind variables required, and same regardless of call
o Collect a page/chunk at a time
o Improves Web Page performance
o Decreases impact of data pulls on overall system performance
o Permits data-stream processing

Variable Parameter Input
o Instead of hardcode api parameters or limiting to an array containing multiple values for a single parameter type, uses an associative array to build a Criteria Grid of all parameters to applied to SQL execution.
o No bind variables required
o Logic to filter out potential SQL Injection routines

Click here to access whitepaper