1 Reply Latest reply: Jan 27, 2013 9:09 AM by fac586 RSS

    Issue in apex classic report

    Nithyarajan Manmathan-Oracle
      We have created a classic report using type as PL/SQL function body returning SQL query. When we run the report with “Use Query-Specific Column Names and Validate Query” mode, we are getting the below error, which forces us to select the second option – “Use Generic Column Names (parse query at runtime only)”.

      “Function returning SQL query: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the generic columns checkbox below the region source to proceed without parsing.
      (ORA-01403: no data found)

      Using “Use Generic Column Names (parse query at runtime only)” to create a classic report, we are not able to add “order by” in our query. When executing this report, we are getting “SQL query not properly ended”. When enabled the debug and checked finally formed query, we found that one extra “order by” clause is automatically added by apex. So we are getting “SQL query not properly ended”. Please let us know how we can overcome this issue in classic report.

      Also this automatically added “order by” clause is not consistent for all the users. With my user id I am getting “order by 3,1” automatically with our classic report query meanwhile for my colleague it is “order by 19,1”.

      Any idea on this will be very helpful.


      Thanks,
      Nithyarajan M
        • 1. Re: Issue in apex classic report
          fac586
          >

          Please update your forum profile with a real handle instead of "984229".

          Your questions are not being responded to because you are not providing sufficient relevant information. Read the Forums FAQ, forum sticky threads, and Tom Kyte's article on how to ask questions.

          When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

          <li>Full APEX version
          <li>Full DB/version/edition/host OS
          <li>Web server architecture (EPG, OHS or APEX listener/host OS)
          <li>Browser(s) and version(s) used
          <li>Theme
          <li>Template(s)
          <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

          With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
          We have created a classic report using type as PL/SQL function body returning SQL query. When we run the report with “Use Query-Specific Column Names and Validate Query” mode, we are getting the below error, which forces us to select the second option – “Use Generic Column Names (parse query at runtime only)”.

          “Function returning SQL query: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the generic columns checkbox below the region source to proceed without parsing.
          (ORA-01403: no data found)
          Post the region source code (use <tt>\
          ...\
          </tt> tags as described in the FAQ). Better still, reproduce the problem on apex.oracle.com.
          Using “Use Generic Column Names (parse query at runtime only)” to create a classic report, we are not able to add “order by” in our query. When executing this report, we are getting “SQL query not properly ended”. When enabled the debug and checked finally formed query, we found that one extra “order by” clause is automatically added by apex. So we are getting “SQL query not properly ended”. Please let us know how we can overcome this issue in classic report.
          Post the debug trace (again use <tt>\
          ...\
          </tt> tags), or reproduce this on apex.oracle.com.
          Also this automatically added “order by” clause is not consistent for all the users. With my user id I am getting “order by 3,1” automatically with our classic report query meanwhile for my colleague it is “order by 19,1”.
          This suggests that column sorting is enabled in the report attributes. Is it? If so, disable it for all columns, and possibly think about clearing sort preferences for all users.