Re: How to prevent using shared pool with dynamic sql pivoting?

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 17 Jun 2008 11:07:25 +0200
Message-ID: <48577ed0$0$14354$e4fe514c@news.xs4all.nl>

"trbosjek" <ivan.petrovski_at_t-mobile.hr> schreef in bericht
news:fe49e171-3c2f-4a5d-9fcc-d471b309d633_at_26g2000hsk.googlegroups.com... Oracle 10.1 here. I know there is built in pivoting in Oracle 11, but until we get it...

I found two wonderful pieces of code that can ALMOST do what I want, BUT.
Let me first share with you what I got:
"Dynamic SQL Pivoting - Stealing Anton’s Thunder" by Lucas Jellema and
Anton http://technology.amis.nl/blog/?p=1207
"Dictionary Long Application" by Adrian Billington
http://www.oracle-developer.net/display.php?id=422

As far as I understand, both make use of Oracle's "Data Cartridge" and
"Pipelined Table Functions". I'm new to those things.

What doesn't work? Here's the list of my attempts:

When I add a new row in emp table with new job title, Oracle is not aware of the new column to be displayed until I do the "ALTER SYSTEM FLUSH SHARED_POOL" or change some letters in the query. Since this query is to be used in several third party tools, this "technique" does not seem acceptable to me.
I have even tried to "execute immediate" the flush inside the type body or alter the original surrounding sql(sql that selects from table(pivot()) That was throwing an error. I altered the inner query string passed as p_stmt before sending it to dbms_sql procedures. This didn't cause an error, but didn't help either.

Another annoying thing is that you CANNOT pass a result of a user defined function with result type varchar2 to the pivot function (or query_view in dla_pkg). Works only with "hard typed" quoted sql statements. Still don't understand why. The workaround was to use session context to pass the query, but again I was left with the problem of Oracle remembering column names from the last time.

Other things I have tried to temper with are

 DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE,
 DBMS_SESSION.SET_CLOSE_CACHED_OPEN_CURSORS(TRUE),
 DBMS_STATS.DELETE_COLUMN_STATS(...)

 and who knows what else, all in order to somehow invalidate what is currently stored in shared pool.

Why am I so persisting to do it this way? Because it seems to me this would be the slickest way to accomplish not only my current task, but many other things in the future. But since I have ran out of ideas to try, I will have to give it up. Unless I get some help. From here among other places...



I think you should contact "Anton" of Amis Services....

Shakespeare Received on Tue Jun 17 2008 - 04:07:25 CDT

Original text of this message