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...

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 16 Jun 2008 06:02:07 -0700 (PDT), trbosjek
<ivan.petrovski (AT) t-mobile (DOT) hr> wrote:

This is very vague description of what you are doing.
You would need to post actual statements.
Let's assume for a moment you are issuing an ordinary INSERT
statement.
Only the session INSERTing will be able to SELECT the data courtesy of
Oracle's consistency model.
Your ALTER SYSTEM statement performs an IMPLICIT COMMIT, making the
data visible to other sessions.
Obviously it is this HORRIBLE KLUDGE, caused by your lack of
understanding how Oracle works, which is COMPLETELY unacceptable,
as by flushing the shared pool, you invalidate ALL cursors, and cause
ALL statements to be reparsed.

You should seriously reconsider, or better still: be directed to the
door of unemployment.

--
Sybrand Bakker
Senior Oracle DBA

While not being polite at all, your answer is the only one so far.
This is a Data Warehouse environment. Not some OLTP. Even if there
would be a 5 minute parsing of a statement(which I assume is
impossible), it would be acceptable when the whole select takes half
an hour to finish. It's been over a decade since RDBMSs have ceased to
be in favor of only some ATM machines and other stuff like this. And,
yes before posting this question I have spent ten days trying to find
a way NOT TO FLUSH THE WHOLE SHARED POOL, but only the part involved
in this. No luck, but even flushing the whole shared pool would not do
any harm in this environment(it’s a staging area for ETL). Why on
earth is it that people always assume that there is some “Forms
Application” that hundreds and thousands of people use on their client
side and issue the same or almost the same statement over an over
again? Please do not respond any more unless you have some
constructive advice to share.

While not being polite at all, your answer is the only one so far.
This is a Data Warehouse environment. Not some OLTP. Even if there
would be a 5 minute parsing of a statement(which I assume is
impossible), it would be acceptable when the whole select takes half
an hour to finish. It's been over a decade since RDBMSs have ceased to
be in favor of only some ATM machines and other stuff like this. And,
yes before posting this question I have spent ten days trying to find
a way NOT TO FLUSH THE WHOLE SHARED POOL, but only the part involved
in this. No luck, but even flushing the whole shared pool would not do
any harm in this environment(it’s a staging area for ETL). Why on
earth is it that people always assume that there is some “Forms
Application” that hundreds and thousands of people use on their client
side and issue the same or almost the same statement over an over
again? Please do not respond any more unless you have some
constructive advice to share.