Oracle Dynamic SQL – generic search

From Michael Rosenblum’s blog a series of three posts on how to do a generic dynamic SQL search in PL/SQL with

  1. 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!).
  2. Ref Cursor – returns a pointer to a rowset (ref cursor) instead of the actual rowset
  3. 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  

Share this!
Tagged with: , , ,
Posted in BLOG, Oracle, PL/SQL, What's New

Leave a Reply

Your email address will not be published. Required fields are marked *


The information presented on this blog is presented to provide general technical information. If, while attempting to apply any of the ideas, procedures, or suggestions herein, you experience any kind of programming or system problems or failure, it will be as a result of your own actions. Dulcian, Inc. and all authors of text found anywhere on this site, and all internally-linked Web sites, Mail Lists, Blogs and/or e-mail group discussion, disclaim responsibility for any user's actions and any damage that may occur based on information found on this website and associated Mail Lists, Blogs and/or e-mail group discussion. Any technical advice or directions found on or through this site is provided AS IS and its provided without warranty or any guarantee of its accuracy. You perform any modifications to programs or software AT YOUR OWN RISK.