From Michael Rosenblum’s blog a series of three posts on how to do a generic dynamic SQL search in PL/SQL with
- Native Dynamic SQL – the example does not use DBMS_SQL and uses bind variables to help optimize the query. In addition, the developer can easily see the query to be executed (very important for complex queries!).
- Ref Cursor – returns a pointer to a rowset (ref cursor) instead of the actual rowset
- XMLType is used as both a collection of data elements and a collection of structural ones – uses a function to allow any two parameters (specified at runtime) to be passed as filters
All entries have coding tips and working code included. Well worth checking them out if you are learning or working with PL/SQL.
He just told me there will be a fourth option so if neither of these resolves your problem, say tuned or follow his blog.
You can also follow us on Twitter @DulcianInc