Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLSQL Function Based Reports with Generic Columns -Internal Error when save

438346Mar 14 2011 — edited May 31 2011
**** DEAR ALL. I AM POSTING THIS SAME QUESTION AGAIN AND AGAIN NOW FOR FORTH DAY. BUT NO ONE ANSWER FOR THIS.
I AM USING LATEST APEX VERSION AND THIS IS A BIG ISSUE FOR ME. ONES I CHANGE THE REPORT SOURCE TO PL?SQL FUNCTION AND DEFINE THE COLUMN HEADING, APEX NOT ALLOWED TO SO ANY CHANGES TO THAT REPORT AND SAVE. SO EVERYTIME EVEN I WANT TO DO A SMALL CHANGE I HAVE TO REBUILD THAT WHOLE REPORT AGAIN. SO THIS IS WASTING LOT OF TIME FROM ME. (Even to do a 1 simple chnage something like changing column heading I need to rebuild whole report from begining). SO I NEED YOUR GREATE HELP TO SOLVE THIS

*** PLEASE HELP ME



Dear All,

I think this is a serious issue with APEX Reports. Because I have face this issue again and anyone can easily reproduce this issue too.
This is what I did,

My Apex Version is : 4.0.2.00.07

01. Create a Dynamic Sql Report with the data source type as a "SQL Query (PL/SQL Function body returning SQL Query)" Option
Type : "SQL Query (PL/SQL Function body returning SQL Query)"
Under region Source I have put something like below
BEGIN
RETURN 'SELECT '||v('P31_COLUMNNAMES')||' FROM '||v('P31_TABLENAME');
END;
Ex: P31_COLUMNNAMES :='FIRST_NAME,POST_CODE'
P31_TABLENAME := 'CUSTOMERS'

02. When we use this option we unable to use "Use Query-Specific Column Names and Validate Query" option because query becomes dynamic and we need to use other option.
So I choose "Use Generic Column Names (parse query at runtime only) "
Then this report shows correctly with the Generic column headings such as "Col01, Col02"
So the output is something like below

Col 01 Col 02
David 2012
Richard 2000


**Above output not properly format because I cant put any html tags in this post. But assume those two columns heading/data show in two separate columns

03. Then I have changed the Heading Type to "PL/SQL"
Heading Type "PL/SQL"
Then under the "Function returning colon delimited headings" I have put something like below
RETURN v('P31_HEADINGNAMES')
Ex: P31_HEADINGNAMES := 'Customer Name: Post Code"

Then I start getting correct output with column heading as well
It looks like below

Customer Name Post Code
David 2012
Richard 2000

**Above output not properly format because I cant put any html tags in this post. But assume those two columns heading/data show in two separate columns

Then after that I try to do some changes to the "Report Attributes" and save it. Then it start giving "Internal Error"..

Now I cant save anything in "Report Attributes" and save it. Because it always shows "Internal error" in a white screen

You can regenerate this error using same steps.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 28 2011
Added on Mar 14 2011
6 comments
1,401 views