One of the Best Questions I Ever Asked Tom Kyte

I started as a FORTRAN programmer, eventually migrating to RBAR PL/SQL programming when I morphed into a data modeler and ETL developer.

I was forced, against my will and better judgment, into abandoning PL/SQL by the decree of my then-boss -- around 2001. No PL/SQL, he said, just write specifications for the ETL team.

However, he didn't and couldn't outlaw SQL... and I learned to make SQL do anything I needed. And I found it was faster too.

(I never forgave him for forcing me to abandon PL/SQL.. because I can't thank him enough!)

However, the largest weakness of views, in my opinion, is the difficuly of searching their internal queryt text. With procedure and packages, it's easy: just query the USER_SOURCE views (or ALL_ or DBA_). Easy.

However, the view query is stored as a LONG datatype. UG!

In 2008 I got the opportunity to ask Tom Kyte how to query the view's SQL. His answer works, I'd've never thought of it on my own, and I still use it to today...

For example, just last week, we needed to search everywhere that used a database link. That included searching view SQL for any matches on the database link names.

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.