5 Replies Latest reply: Jun 24, 2011 1:57 PM by 871289 RSS

    ORA-01446 on Interactive query

    CoreyT
    CoreyT Nov 21, 2008 5:51 PM
      All,

      I am having trouble creating an interactive report using a hierarchical query - which I need. The error reported is ORA-01446 indicating that APEX is wrapping this in another query trying to get a ROWID. It does work as an SQL report but not as an interactive one causing the loss of functionality.

      Has anybody experienced this before or know anyway around it.

      Thanks,
      Corey

      -----
      To illustrate, try the following from any SQL editor:

      +Create a tes

      create table scratch (a number, p_a number);



      Execute this query - this should run fine in SQL but fails in APEX for ORA-01446*
      select * from scratch
      connect by prior a = p_a;

      Execute the same query wrapped with a ROWID query in SQLPLUS and you get the same error as in APEX interactive report*
      select rowid from
      (
      select * from scratch
      connect by prior a = p_a
      );


      -----
      Actual error message:

      1 error has occurred <ul><li>The report query needs a unique key to identify each row. The supplied key cannot be used for this query. Please edit the report attributes to define a unique key column. ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.</li>
      </ul>
        • 1. Re: ORA-01446 on Interactive query
          60437
          60437 Nov 21, 2008 5:54 PM (in response to CoreyT)
          If you don't need the single-row view, uncheck that option and it should work.

          Scott
          • 2. Re: ORA-01446 on Interactive query
            438381
            438381 Nov 21, 2008 6:18 PM (in response to CoreyT)
            Corey:

            Does the table that you are using in the query have a primary key/Unique key ?
            If so, you can have APEX use that column as the column instead of 'rowid' to uniquely identify a row in the table

            Varad

            Edited by: varad acharya on Nov 21, 2008 4:18 PM
            • 3. Re: ORA-01446 on Interactive query
              CoreyT
              CoreyT Nov 22, 2008 2:43 PM (in response to 438381)
              Thank you both so much for your input. Both worked great. I did the following with success:

              Based on Scott suggestion, initially, I disabled the single-row view and that allowed me to enter the query in interactive mode.

              Then based on Varad suggestion, I took it a little further and added an alias rowid column (from the inner joined table in the from-clause query). I re-enabled the single-row view and specified this aliased rowid and it works well still.

              Thanks,
              Corey
              • 4. Re: ORA-01446 on Interactive query
                804111
                804111 Dec 31, 2010 7:37 PM (in response to CoreyT)
                Corey,

                Thanks for suggesting the solution. This has been giving me fits while developing my first Apex application. I wasn't able to figure out when I would get the error or how to fix it. I do need the single row views as an application "standard". Since it took me a while to figure out how to do what you suggested, for others, here's specifically what I did.

                In an interactive report linking several tables, with subqueries counting child entities with outer joins, I added as the first column in the SELECT statement "acr.rowid MY_ROWID,", where the primary table alias name is "acr".

                I also added "acr.rowid MY_ROWID," to the GROUP BY list.

                Then in the Report Attributes > Link Column, I linked to a single row view, chose Unique Column, and entered MY_ROWID and everything worked fine.

                Tried it in another report that used UNION, and it worked there, too.

                I may have run into trouble using lower case in various places. I'm still getting used to how Apex and Oracle SQL are case sensitive some places.

                Skip
                • 5. Re: ORA-01446 on Interactive query
                  871289
                  871289 Jun 24, 2011 1:57 PM (in response to 804111)
                  Skip, thank you for adding the additional description and information. I had been beating my head against a wall on this one. Solution worked perfectly.

                  -Sean

                  Edited by: user9030474 on Jun 24, 2011 11:56 AM

                  Edited by: user9030474 on Jun 24, 2011 11:57 AM