8 Replies Latest reply: Jan 2, 2013 1:11 PM by TexasApexDeveloper RSS

    Report Queries column limitation

    aracila
      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
          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
            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
              Anyone ...
              • 4. Re: Report Queries column limitation
                TexasApexDeveloper
                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
                  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
                    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
                      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
                        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