1 2 Previous Next 27 Replies Latest reply: Oct 4, 2010 2:00 PM by joelkallman-Oracle RSS

    invalid number querying collection

    734426
      hi -- I have an interactive report that queries a collection to get its source data. It's been working great.
      Now (starting today) I get "invalid number" when the interactive report page comes up. I haven't changed
      anything in the way the collection is generated.

      I've used the "Select Columns" feature of the IR to narrow the problem down to one column. That is, when
      I remove this column from those that are displayed, the IR data comes up correctly. The problem column
      is a number (I convert it to a number when selecting from the collection); there are several other numbers in
      the collection and IR that are not a problem.

      Any ideas what might be happening and/or how I can debug it? There's no way the data in the database is
      non-numeric; the field is defined as a number.

      Thanks,
      Carol
        • 1. Re: invalid number querying collection
          734426
          Another bit of information: I made a copy of the application, and the problem does not occur in the copy.

          I also exported the application, then imported and installed it. The problem does not occur in the newly-installed
          application.

          (and it is still occurring in the original).

          C
          • 2. Re: invalid number querying collection
            Udo
            Hi Carol,

            do you use the same collection somewhere else in your application? Probably some other values in that column than the ones you expect cause the problem. The collection's scope is the user session and it's not cleaned up automatically.

            -Udo
            • 3. Re: invalid number querying collection
              Madhu BR
              Carol,

              It appears to be a data-related-issue and not an issue with your reports.
              • 4. Re: invalid number querying collection
                734426
                hi Udo, Madhu -- Yes, I use the collection elsewhere. Whenever the user navigates to an interactive report,
                the collection is deleted and then recreated for the proper database tables. Because it clearly contains correct
                data for most of the columns in the IR (this has to mean it's getting deleted and recreated) I don't see how
                it could still have "old" data in it. But I've thought about this too, regardless. Could there be some sort of anomaly
                that's resulting in things not getting cleaned up?

                Madhu -- Can you explain what you mean by a "data issue"? Something like Udo is suggesting w/ the collection
                not containing what I think it does? I know it's not a database data issue, as I get the error in the faulty application
                immediately after having success in the good application...

                Is there a way to look at the current values in a collection, either in session state or in the database?

                Thanks,
                Carol
                • 5. Re: invalid number querying collection
                  Udo
                  Hi Carol,

                  we had some issues like that too. It may not only occur, when other parts of the collection don't contain numbers, but also depending on the execution plan. Do you use an explicit cast (to_number) in the situation you get the error, or do you have an implicit cast (e.g. in a join like :PX_ID=collection.c001 )?
                  You can look for the collections contents in the session state. In APEX 3 and 4, just call the "Session"-state and change the view from page items to collections. You could try to run a loop for debug output in that situation.

                  -Udo

                  P.S.: I just had a look into one of the projects where we first ran into that issue. In that case, we took the data from apex_collections, restricting the selection to "collection_name = '<OUR_COLLECTION>'". The problem is, that other collections are stored in the same table and may have non-numeric values in the same column where we expect only numbers for the selected collection. If the execution plan does not do the restriction to the target collection first (creating an internal view for further processing) it runs into those other values and crashes. So if you have other conditions or joins in your query and for some reason the database decides to execute on these first, you may run into that problem. We started to workaround by giving hints for the execution, but decided to use other data structures later.

                  Edited by: Udo on 30.09.2010 14:37
                  • 6. Re: invalid number querying collection
                    734426
                    hi Udo -- As I said, the collection is deleted and re-created every time the user navigates to an interactive report.
                    the collection is defined based on the table (data) to be displayed in the pertinent interactive report; the collection is based on
                    a query which is constructed by a database function.

                    This is the only collection used in the application (that I define, anyway). All of the interactive reports that select
                    from the collection restrict only by the collection name, nothing else. So, I'm not sure if parsing is the issue.
                    All of the numbers are cast explicitly with to_number.

                    A bit of another problem trying to debug this morning: the problem isn't occurring anymore. (Actually, this happened
                    yesterday morning also -- I was getting invalid numbers the night before, then not initially in the morning, then it
                    started happening again.) Almost seems like something's not getting cleaned up in session state. I do at times
                    have the application builder running in more than one browser, and on rarer occasions have the application
                    running in more than one browser window (sometimes as the same user, sometimes as different users), but I'm
                    rigorous about logging out. Also, my understanding is that collections are session-specific, so it doesn't seem like that
                    should cause a problem regardless.

                    The collection does not show up when I view session state / collections. That seems weird...

                    Not sure if this is pertinent, but the following process code creates the collection:
                    IF (apex_collection.collection_exists (       
                        p_collection_name=>'IR_COLLECTION')) then
                        apex_collection.delete_collection(p_collection_name=>'IR_COLLECTION');
                    END IF;
                    
                    apex_collection.create_collection_from_query_b(p_collection_name=>'IR_COLLECTION', p_query=>meta_data_pkg.build_ir_collection_query(:TABLE_NAME));
                    Any reason the session would be ambiguous based on how the collection is created?

                    Thanks much,
                    Carol
                    • 7. Re: invalid number querying collection
                      Udo
                      Hi Carol,

                      I think, the problem can arise anyway if you use the same collection for different reports in a way that in (most) cases you have numbers in a certain column and non-numbers in other cases.
                      Though the collection state is mapped to be only valid in a certain session, apex_collections is just a view (actually a synonym for a view) enforcing this. The collection actually resides in two tables (wwv_flow_collections$, wwv_flow_collection_members$) along with data from all other sessions. The view does not enforce a certain execution plan with a hint (I'm not sure if this would cause problems elsewhere).
                      So you can run into situations where your optimizer decides to rewrite the query and not reduce the results first by session and then collection, but performs a to_number-cast too early. The execution stays safe, i.e. you'd never get into other sessions data, but casts are not safe on collections.
                      My known options to solve this are the following:
                      - try to implement hints to enforce execution order
                      - ensure that a certain column of any collection in any session never gets cast-incompatible
                      - not use collections

                      -Udo

                      Edited by: Udo on 30.09.2010 17:17

                      Edited by: Udo on 30.09.2010 20:58
                      I've found the view-code including hint we incorporated for testing, but did not bring into production, because it modifies the original plattform software which could cause problems with customers. For APEX 4.0, the corresponding view with enforced execution of the view as it is intended would look like follows:
                      CREATE OR REPLACE FORCE VIEW "APEX_040000"."WWV_FLOW_COLLECTIONS" ("COLLECTION_NAME", "SEQ_ID", "C001", "C002", "C003", "C004", "C005", "C006", "C007", "C008", "C009", "C010", "C011", "C012", "C013", "C014", "C015", "C016", "C017", "C018", "C019", "C020", "C021", "C022", "C023", "C024", "C025", "C026", "C027", "C028", "C029", "C030", "C031", "C032", "C033", "C034", "C035", "C036", "C037", "C038", "C039", "C040", "C041", "C042", "C043", "C044", "C045", "C046", "C047", "C048", "C049", "C050", "CLOB001", "BLOB001", "XMLTYPE001", "N001", "N002", "N003", "N004", "N005", "D001", "D002", "D003", "D004", "D005", "MD5_ORIGINAL")
                      AS
                        SELECT /*+ NO_QUERY_TRANSFORMATION */
                          c.collection_name,
                          m.seq_id,
                          m.c001,
                          m.c002,
                          m.c003,
                          m.c004,
                          m.c005,
                          m.c006,
                          m.c007,
                          m.c008,
                          m.c009,
                          m.c010,
                          m.c011,
                          m.c012,
                          m.c013,
                          m.c014,
                          m.c015,
                          m.c016,
                          m.c017,
                          m.c018,
                          m.c019,
                          m.c020,
                          m.c021,
                          m.c022,
                          m.c023,
                          m.c024,
                          m.c025,
                          m.c026,
                          m.c027,
                          m.c028,
                          m.c029,
                          m.c030,
                          m.c031,
                          m.c032,
                          m.c033,
                          m.c034,
                          m.c035,
                          m.c036,
                          m.c037,
                          m.c038,
                          m.c039,
                          m.c040,
                          m.c041,
                          m.c042,
                          m.c043,
                          m.c044,
                          m.c045,
                          m.c046,
                          m.c047,
                          m.c048,
                          m.c049,
                          m.c050,
                          m.clob001,
                          m.blob001,
                          m.xmltype001,
                          m.n001,
                          m.n002,
                          m.n003,
                          m.n004,
                          m.n005,
                          m.d001,
                          m.d002,
                          m.d003,
                          m.d004,
                          m.d005,
                          m.md5_original
                        FROM wwv_flow_collections$ c,
                          wwv_flow_collection_members$ m
                        WHERE c.session_id =
                          (SELECT v('SESSION') FROM dual
                          )
                        AND c.security_group_id =
                          (SELECT wwv_flow.get_sgid FROM dual
                          )
                        AND c.id      = m.collection_id
                        AND c.flow_id =
                          (SELECT nv('FLOW_ID') FROM dual
                          );
                      Make sure to backup the DDL for the view on your system before doing this.
                      • 8. Re: invalid number querying collection
                        510477
                        Don't know if you've used this, but you might take a look at this command:
                        apex_collection.create_or_truncate_collection(p_collection_name => 'IR_COLLECTION');
                        I use it a lot to clear out collections I use and it really helps avoid problems with getting bad data.

                        The other thing I have seen is collections getting populated with DATE-based information that starts throwing INVALID NUMBER errors. In those cases, I have to make sure that as long as the information is in the collection, I treat all the DATE-based values as text and explicitly convert them.
                        • 9. Re: invalid number querying collection
                          734426
                          hi -- Sounds like a good option. How would I use this in combination with create_collection_from_query_b,
                          which I think is important 1) to create the collection from a query and 2) to improve performance on
                          the collection creation (where it definitely helped!).

                          Thanks,
                          carol
                          • 10. Re: invalid number querying collection
                            510477
                            Sure. I've never had any problems with using this just prior to the actual procedure I use to populate the query. I typically use PL/SQL procedures firing BEFORE HEADER to populate my collections.
                            Example:
                            apex_collection.create_or_truncate_collection
                              (p_collection_name => 'EDIT_LOADS');
                            
                            DECLARE
                              cursor c_prepop is select LOAD_JOB_ID,
                                   STATION_ID, PILE_CODE_ID, CONFIG, PILE_CODE_ALT_FLAG,
                                   FACTORY_ID, LOAD_RATE_ID, JOB_COMMENTS,
                                   TO_CHAR(JOB_START_TIME,'MM/DD/YYYY HH:MI AM'),
                                   TO_CHAR(JOB_END_TIME,'MM/DD/YYYY HH:MI AM'), 
                                   JOB_EQUIP_BEGIN_METER, JOB_EQUIP_END_METER, MAN_SPOT_WT
                                from TC_LOAD_JOBS
                               where TC_ID = :P1091_TC_ID
                               order by NVL(JOB_START_TIME,JOB_END_TIME), LOAD_JOB_ID;
                              v_job        NUMBER;
                              v_stn        NUMBER;
                              v_pl         NUMBER;
                              v_cfg        VARCHAR2(3);
                              v_alt        VARCHAR2(3);
                              v_fact       NUMBER;
                              v_rate       NUMBER;
                              v_cmt        VARCHAR2(255);
                              v_tm_in      VARCHAR2(20);
                              v_odom_in    NUMBER;
                              v_tm_out     VARCHAR2(20);
                              v_odom_out   NUMBER;
                              v_man_wt     NUMBER;
                            BEGIN
                              OPEN c_prepop;
                                LOOP
                                  FETCH c_prepop into v_job, v_stn, v_pl, v_cfg, v_alt,
                                      v_fact, v_rate, v_cmt, v_tm_in, v_tm_out,
                                      v_odom_in, v_odom_out, v_man_wt;
                                  EXIT WHEN c_prepop%NOTFOUND;
                                    APEX_COLLECTION.ADD_MEMBER(
                                      p_collection_name => 'EDIT_LOADS',
                                      p_c001 => v_job,                       --Checkbox
                                      p_c002 => v_stn,                       --Station
                                      p_c003 => v_pl,                        --Pile
                                      p_c004 => v_cfg,                       --Config
                                      p_c005 => v_alt,                       --Special/Alt
                                      p_c006 => v_fact,                      --Factory
                                      p_c007 => v_rate,                      --Rate ID          
                                      p_c008 => v_tm_in,                     --Load Time
                                      p_c009 => v_tm_out,                    --Load Time
                                      p_c010 => v_odom_in,                   --Load Odom          
                                      p_c011 => v_odom_out,                  --Load Odom
                                      p_c012 => v_cmt,                       --Comment
                                      p_c013 => v_man_wt                     --Manual Weight
                                    );
                                END LOOP;
                              CLOSE c_prepop;
                            END;
                            • 11. Re: invalid number querying collection
                              734426
                              Cool, thanks.

                              Another question: in your experience, is create_or_truncate_collection a more reliable way of eliminating
                              data issues than actually deleting the collection prior to recreation, which is what I'm doing?

                              C
                              • 12. Re: invalid number querying collection
                                510477
                                It saves a step and performs the exact same functions, really. It will create the array if it doesn't exist, eliminating the need for a create_collection call, and if the array exists, it kills all the data and cleans it out. It basically allows you to use one call when other wise it might take two or three. Once someone told me about it, I never looked back.
                                • 13. Re: invalid number querying collection
                                  jariola
                                  Hi,

                                  What I check sample code, would it be easier delete collection and use CREATE_COLLECTION_FROM_QUERY, than cursor and loop ?
                                  That might be faster also

                                  Regards,
                                  Jari

                                  Edited by: jarola on Sep 30, 2010 11:53 PM

                                  If I did read code right it would be
                                  IF APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => 'EDIT_LOADS') THEN
                                    APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'EDIT_LOADS');
                                  END IF;
                                   APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
                                     p_collection_name => 'EDIT_LOADS',
                                     p_query => q'!
                                      SELECT LOAD_JOB_ID,
                                        STATION_ID,
                                        PILE_CODE_ID,
                                        CONFIG,
                                        PILE_CODE_ALT_FLAG,
                                        FACTORY_ID,
                                        LOAD_RATE_ID,
                                        JOB_COMMENTS,
                                        TO_CHAR(JOB_START_TIME,'MM/DD/YYYY HH:MI AM'),
                                        TO_CHAR(JOB_END_TIME,'MM/DD/YYYY HH:MI AM'),
                                        JOB_EQUIP_BEGIN_METER,
                                        JOB_EQUIP_END_METER,
                                        MAN_SPOT_WT
                                      FROM TC_LOAD_JOBS
                                      WHERE TC_ID = v('P1091_TC_ID')
                                      ORDER BY NVL(JOB_START_TIME,JOB_END_TIME),
                                        LOAD_JOB_ID
                                      !'
                                    );
                                  Edited by: jarola on Oct 1, 2010 12:04 AM

                                  And you can create your own "helper" database procedures, where is extra parameter to delete collection like
                                  CREATE OR REPLACE
                                  PROCEDURE apex_collection_from_query(
                                    p_collection_name IN VARCHAR2,
                                    p_query           IN VARCHAR2,
                                    p_generate_md5    IN VARCHAR2 DEFAULT 'NO',
                                    p_del_collection  IN VARCHAR2 DEFAULT 'NO'
                                  )
                                  AS
                                  BEGIN
                                    IF p_del_collection = 'YES' THEN
                                      IF APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => p_collection_name ) THEN
                                        APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => p_collection_name );
                                      END IF;
                                    END IF;
                                    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
                                      p_collection_name  => p_collection_name,
                                      p_query            => p_query,
                                      p_generate_md5     => p_generate_md5
                                    );
                                  END;
                                  /
                                  CREATE OR REPLACE
                                  PROCEDURE apex_collection_from_query_b(
                                    p_collection_name IN VARCHAR2,
                                    p_query           IN VARCHAR2,
                                    p_del_collection  IN VARCHAR2 DEFAULT 'NO'
                                  )
                                  AS
                                  BEGIN
                                    IF p_del_collection = 'YES' THEN
                                      IF APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => p_collection_name ) THEN
                                        APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => p_collection_name );
                                      END IF;
                                    END IF;
                                    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
                                      p_collection_name  => p_collection_name,
                                      p_query            => p_query
                                    );
                                  END;
                                  /
                                  Edited by: jarola on Oct 1, 2010 12:11 AM
                                  typo
                                  • 14. Re: invalid number querying collection
                                    734426
                                    hi Jari -- I tend to agree w/ you about not using the cursor. I guess the pertinent question regarding this "invalid number"
                                    error that occurs on rare occasion is: is there a reliable way to clear all data from the collection? It seems that deleting the collection before recreating it does not always do the trick -- possibly because of issues w/ the execution plan like Udo mentioned. Is there any reason to think a truncate would be more effective?

                                    What query hint would restrict to the pertinent collection (specified in the where clause on the apex_collections query)
                                    before gather any other (possibly problem) data?

                                    Thanks,
                                    C
                                    1 2 Previous Next