This content has been marked as final. Show 27 replies
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?
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.
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
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:
Any reason the session would be ambiguous based on how the collection is created?
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));
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
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:
Make sure to backup the DDL for the view on your system before doing this.
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 );
Don't know if you've used this, but you might take a look at this command:
I use it a lot to clear out collections I use and it really helps avoid problems with getting bad data.
apex_collection.create_or_truncate_collection(p_collection_name => 'IR_COLLECTION');
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.
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.
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;
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.
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
Edited by: jarola on Sep 30, 2010 11:53 PM
If I did read code right it would be
Edited by: jarola on Oct 1, 2010 12:04 AM
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 !' );
And you can create your own "helper" database procedures, where is extra parameter to delete collection like
Edited by: jarola on Oct 1, 2010 12:11 AM
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; /
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?