This content has been marked as final. Show 8 replies
Your first query is confusing in that I don't understand the trailing characters:
...which don't exist in the second query.
')) xml from dual;
That said, where does Oracle indicate that the buffer error is occurring? I would have thought it was much more likely to be occurring in the LABELS.GET_COLLECTION_LABELS function. When you run this query from SQL*Plus or SQL*Developer, does it execute without an error? If it gets a buffer error there, it would have nothing to do with your PDF report.
My off-the-cuff guess would be that there is a variable in your pipelined table function that is not large enough (or not getting cleared when it should be).
Thanks for the reply, sorry I have corrected first script with the length of response in xml format.
The script executes just fine in SQL developer and Toad, and in APEX SQL Workshop , and even I was able to create a classic report with the same script.
It is Report Queries generation. Not PDF it self, because if I select HTML i am getting same error
If I execute this select, it gives me the error:
If i execute this one, everything is ok:
SELECT ROW_ID, SUBJECT_ID_1, DOC_1, PATIENT_ID_1, SPECIMEN_ID_1, SUBJECT_ID_2, DOC_2, PATIENT_ID_2, SPECIMEN_ID_2, SUBJECT_ID_3, DOC_3, PATIENT_ID_3, SPECIMEN_ID_3, SUBJECT_ID_4, DOC_4, PATIENT_ID_4, SPECIMEN_ID_4, SUBJECT_ID_5, DOC_5, PATIENT_ID_5, SPECIMEN_ID_5, SUBJECT_ID_6, DOC_6, PATIENT_ID_6, SPECIMEN_ID_6, SUBJECT_ID_7, DOC_7, PATIENT_ID_7, SPECIMEN_ID_7, SUBJECT_ID_8, DOC_8, PATIENT_ID_8, SPECIMEN_ID_8 FROM TABLE (LABELS.GET_COLLECTION_LABELS(103))
It is defiantly some limitation on the number of columns, but what is the reason and why? Maybe there is a way to avoid this.
SELECT ROW_ID, SUBJECT_ID_1, DOC_1, PATIENT_ID_1||SPECIMEN_ID_1 a1, SUBJECT_ID_2, DOC_2, PATIENT_ID_2||SPECIMEN_ID_2 a2, SUBJECT_ID_3, DOC_3, PATIENT_ID_3||SPECIMEN_ID_3 a3, SUBJECT_ID_4, DOC_4, PATIENT_ID_4||SPECIMEN_ID_4 a4, SUBJECT_ID_5, DOC_5, PATIENT_ID_5||SPECIMEN_ID_5 a5, SUBJECT_ID_6, DOC_6, PATIENT_ID_6||SPECIMEN_ID_6 a6, SUBJECT_ID_7, DOC_7, PATIENT_ID_7||SPECIMEN_ID_7 a7, SUBJECT_ID_8, DOC_8, PATIENT_ID_8||SPECIMEN_ID_8 a8 FROM TABLE (LABELS.GET_COLLECTION_LABELS(103))
Many questions to be answered BEFORE anyone can provide you with an answer...
Version of database?
Version of APEX?
What tool are you using to produce PDF's?
If it is FOP, then maybe there is a problem with the # of columns or the XML being produced.
If it is BI Publisher maybe there is an issue with how you are producing the XML file to be processed?
Until you provide more info, it is kinda hard to help further..
Can you post an example up on Oracle's hosted free site with Code AND Sample Data to demonstrate the issue?
it is really not related to PDF, I have the same issue if I select XLS, for example or HTML.
The problem is in number of columns.
Try in any application: Shared Components / Reports / Report Queries
create any report with 32 columns.
Please go take a look here: http://apex.oracle.com/pls/apex/f?p=4550:1:0::::F4550_P1_COMPANY:ANDREIDEMO
Demo app, Shared Components / Reports / Report Queries.
Please open Test Report and click test button
It would seem tha there is a limit to the SIZE of your query in report queries builder... When I reduced the # of columns being selected to 24, I was able to produce a report. What I would suggest is either reducing the # of columns down or look at another way to produce your required report with the # of columns you need.
If it is to XLS, then there are alternative ways to produce an XLS file than using a application level report query..
Two different places in APEX, so they are probably different pieces of code processing your SQL Select.... Simple answer is, if you don't like the way it's doing it, use a different method to produce reports.. You can export CSV's/XLS files using other methods, and also PDF files..