Skip to Main Content


Generic columns classic report - refresh column list

Scott WesleyJul 15 2015 — edited Jul 20 2015

Consider a dynamic pivot statement like the following (as described by AMIS) which

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno,job' ) )

I've used this as source SQL for classic report, selecting "Use generic column names"

The report looks like


I can parameterise the data shown with a select list, and including a where clause

where job =v(''P39_JOB'') or v(''P39_JOB'') is null

The select list does PPR on the region, demonstrated here:

Note: , and all pivoted jobs are still visible as columns, only the data is restricted

- non-related jobs are blank

- any depts with no clerks aren't shown


Let's say you have another select list that groups jobs, say the "Pres" and "Presidents".

How could you refresh the region to exclude certain jobs?

Reloading the page with the P39_JOB value already set will not affect the outcome, while including a literal string does.

It seems perhaps ODCI function parses the query on null binds, then populates with actual results.

Does anyone have any suggestions?

update: it seems one workaround is to inject the value as a substitution string, eg:

and job like '&P39_CEO.'

where P39_CEO defaulted in session state to %

I only got this concept working in my client app (4.2, 11gr2), not my demo (5.0, 12c), even attempting to cater for relevant quotes and when null. I kept getting

report error: ORA-1403: no data found

I also couldn't work out how to properly apply sys.dbms_assert.enquote_literal()


This post has been answered by fac586 on Jul 15 2015
Jump to Answer
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 17 2015
Added on Jul 15 2015