This discussion is archived
8 Replies Latest reply: Jan 2, 2013 11:11 AM by TexasApexDeveloper RSS

Report Queries column limitation

aracila Newbie
Currently Being Moderated
Hi All,
is there any limitation on PDF report queries regarding the column number?

the error message I've got is: ORA-06502: PL/SQL: numeric or value error: character string buffer too small error
I was thinking that the result is too long but is only 3299 according to this script:
select length(dbms_xmlgen.getxml('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))')) xml from dual;
Well if I run it like 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))
I have no errors.

Does some one know what is the limit and why?

Thanks a lot,
Andrei
  • 1. Re: Report Queries column limitation
    matthew_morris Expert
    Currently Being Moderated
    Your first query is confusing in that I don't understand the trailing characters:
    ')) xml from dual;
    ...which don't exist in the second query.

    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).
  • 2. Re: Report Queries column limitation
    aracila Newbie
    Currently Being Moderated
    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:
    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))
    If i execute this one, everything is ok:
    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))
    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.

    Thanks
  • 3. Re: Report Queries column limitation
    aracila Newbie
    Currently Being Moderated
    Anyone ...
  • 4. Re: Report Queries column limitation
    TexasApexDeveloper Guru
    Currently Being Moderated
    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?

    Happy Holidays,

    Tony Miller
    Ruckersville, VA
  • 5. Re: Report Queries column limitation
    aracila Newbie
    Currently Being Moderated
    Hi,

    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.

    Oracle 11g
    Apex 4.2.0.00.27

    Please go take a look here: http://apex.oracle.com/pls/apex/f?p=4550:1:0::::F4550_P1_COMPANY:ANDREIDEMO
    User: dev
    Password: dev

    Demo app, Shared Components / Reports / Report Queries.
    Please open Test Report and click test button

    Thank you,
    Andrei
  • 6. Re: Report Queries column limitation
    TexasApexDeveloper Guru
    Currently Being Moderated
    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..

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 7. Re: Report Queries column limitation
    aracila Newbie
    Currently Being Moderated
    I do get that, but i don't understand why i can create a classic report with 32 columns, but i can't create a print report.
    Where is the limitation in APEX, why it has it.
    Did you see the select: is selecting numbers from dual, the size is minimal possible.
  • 8. Re: Report Queries column limitation
    TexasApexDeveloper Guru
    Currently Being Moderated
    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..

    Thank you,

    Tony Miller
    Ruckersville, VA

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points