This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Oct 4, 2010 12:00 PM by jkallman RSS

invalid number querying collection

734426 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MadhuBR Journeyer
    Currently Being Moderated
    Carol,

    It appears to be a data-related-issue and not an issue with your reports.
  • 4. Re: invalid number querying collection
    734426 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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