What would you do if everything in your environment looked perfectly fine, the data looked correct and yet every time you query a table, the query crashes with some sort of error? Yes, here I am back banging the drum about taking care with your data types 😊. In today’s example I’m going to show you how not correctly choosing the right data type can cause all sorts of grief when you are using APEX collections and it’s incredibly hard to diagnose because much of the data may be hidden away from you.
Let’s consider the following example where we are taking advantage of APEX collections in order to store some static data that might be used to populate say a list of values or some other small table grid on the screen.
The first selection I have is for options when it comes to the gender of someone. I can use the PL/SQL API to populate the entries I need and then a simple query shows me the data I’ve created.
SQL> DECLARE 2 l_collection_name VARCHAR2(255); 3 BEGIN 4 l_collection_name := 'GENDER'; 5 apex_collection.create_or_truncate_collection(l_collection_name); 6 apex_collection.add_member( 7 p_collection_name=>l_collection_name, 8 p_c001=>'Male', 9 p_c002=>'M'); 10 11 apex_collection.add_member( 12 p_collection_name=>l_collection_name, 13 p_c001=>'Female', 14 p_c002=>'F' ); 15 16 apex_collection.add_member( 17 p_collection_name=>l_collection_name, 18 p_c001=>'Unspecified', 19 p_c002=>'U' ); 20 21 END; 22 /PL/SQL procedure successfully completed.SQL>SQL> select ac.c001, ac.c002 2 from apex_collections ac 3 where ac.collection_name = 'GENDER';C001 C002 -------------------- -------------------- Male MFemale FUnspecified U3 rows selected.
Also in this APEX application I want to have a related set of data which lets people choose from a set of age ranges. So once again I will use APEX collections for that and have a finite set of options spanning 20 year age gaps that people might be able to choose from.
SQL> DECLARE 2 l_collection_name VARCHAR2(255); 3 BEGIN 4 l_collection_name := 'AGE'; 5 apex_collection.create_or_truncate_collection(l_collection_name); 6 apex_collection.add_member( 7 p_collection_name=>l_collection_name, 8 p_c001=>'0 to 20', 9 p_c002=>0, 10 p_c003=>20 11 ); 12 13 apex_collection.add_member( 14 p_collection_name=>l_collection_name, 15 p_c001=>'21 to 40', 16 p_c002=>21, 17 p_c003=>40 18 ); 19 20 apex_collection.add_member( 21 p_collection_name=>l_collection_name, 22 p_c001=>'41 to 60', 23 p_c002=>41, 24 p_c003=>60 25 ); 26 27 apex_collection.add_member( 28 p_collection_name=>l_collection_name, 29 p_c001=>'61 to 80', 30 p_c002=>61, 31 p_c003=>80 32 ); 33 34 END; 35 /PL/SQL procedure successfully completed.SQL>SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'AGE';C001 C002 C003-------------------- -------------------- --------------------0 to 20 0 2021 to 40 21 4041 to 60 41 6061 to 80 61 804 rows selected.
I am doing all of this in a command line window but the same logic applies here if I was doing it in my APEX application or in SQL Worksheet. Let’s say from a drop-down list someone picks the entry in the age collection where their age falls into the 21 to 40 range. The underlying query would probably look something like this and it works fine.
SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'AGE' 4 and ac.c002 = 21;C001 C002 C003-------------------- -------------------- --------------------21 to 40 21 40
but here’s where things can get quite insidious. I come back 15 minutes later, run the exact same application and run the exact same query. And now the following happens.
SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'AGE' 4 and ac.c002 = 21;and ac.c002 = 21 *ERROR at line 4:ORA-01722: invalid number
That seems a mystery. Moreover, it is entirely within the realms of possibility that some people will not get the error and some people will get the error. It seems totally random.
What is happening here is dependent on how the optimizer decides to run your query against APEX_COLLECTIONS. If I dig down into the underlying view definition for APEX_COLLECTIONS, we can see it consists of a row of metadata indicating who you are (sgid), then a table for the collection itself (c) and a table for the members (m), i.e. each row of the collection.
create or replace
view apex_260100.wwv_flow_collections as
select ...
from wwv_flow_current_sgid_for_dml sgid,
wwv_flow_collections$ c,
wwv_flow_collection_members$ m
where c.session_id = (select v('session') from sys.dual)
and c.security_group_id = sgid.security_group_id
and m.security_group_id = sgid.security_group_id
and c.id = m.collection_id
and c.flow_id = (select nv('flow_id') from sys.dual)
It is the optimizer that decides in which order these tables will be processed and in which order the predicates will be applied during query execution. This is why using the correct data types is so essential. If I dig into the underlying APEX table, you can see column C002 contains the data for both collections.
SQL> select c002 from apex_260100.WWV_FLOW_COLLECTION_MEMBERS$;C002--------------------MFU0214161MFU
It is only when we apply the various workspace and security related predicates embedded in the view that we manage to limit the data exposed on a collection by collection basis.
So how does this relate to the “ORA-01722: invalid number” error that we are getting? Because we are at the whim of the optimizer in terms of which table it will process first when executing the query, I’ve made a fatal error by storing a numeric item, the age of 21, in a character data type column C002. In this particular example, I tweaked the optimizer statistics such that the optimizer would lean toward starting with the collection members table and therefore scan all possible rows for all possible collections. And as it tries to ensure the data types are consistent, it applies a TO_NUMBER function around my character column. We can see that in the execution plan.
-------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49362 | 36 (0)| 00:00:01 || 1 | MERGE JOIN CARTESIAN | | 1 | 49362 | 32 (0)| 00:00:01 || 2 | NESTED LOOPS | | 1 | 49362 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL | WWV_FLOW_COLLECTION_MEMBERS$ | 1 | 49181 | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | WWV_FLOW_COLLECTIONS$ | 1 | 181 | 0 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | WWV_FLOW_COLLECTIONS_UK | 1 | | 0 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 || 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 || 8 | BUFFER SORT | | 1 | | 32 (0)| 00:00:01 || 9 | VIEW | | 1 | | 29 (0)| 00:00:01 || 10 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | | 29 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter(TO_NUMBER("M"."C002")=21 AND "M"."SECURITY_GROUP_ID"=CASE WHEN ("WWV_FLOW"."GET_SGID"()=0) THEN NULL ELSE "WWV_FLOW"."GET_SGID"() END ) 4 - filter("C"."ID"="M"."COLLECTION_ID") 5 - access("C"."SESSION_ID"=TO_NUMBER( (SELECT "V"('SESSION') FROM "SYS"."DUAL" "DUAL")) AND "C"."FLOW_ID"= (SELECT "NV"('FLOW_ID') FROM "SYS"."DUAL" "DUAL") AND "C"."COLLECTION_NAME"='AGE' AND "C"."SECURITY_GROUP_ID"=CASE WHEN ("WWV_FLOW"."GET_SGID"()=0) THEN NULL ELSE "WWV_FLOW"."GET_SGID"() END ) filter("C"."COLLECTION_NAME"='AGE' AND "C"."SECURITY_GROUP_ID"=CASE WHEN ("WWV_FLOW"."GET_SGID"()=0) THEN NULL ELSE "WWV_FLOW"."GET_SGID"() END AND "C"."FLOW_ID"= (SELECT "NV"('FLOW_ID') FROM "SYS"."DUAL" "DUAL"))
Because things like the collection name and the workspace criteria are applied after this initial scan, the TO_NUMBER function is being applied to rows that pertain not just to our AGE collection but any other collection in the table.
You might be thinking to yourself:
“Hey that’s not really a big drama. I would simply query the APEX_COLLECTIONS view in its entirety to have a look at what other collections are there and then make the appropriate changes. This would let me troubleshoot it without too much difficulty”
However, it could be far more difficult than that. Let me recreate the example but this time in Session 1 in workspace WS1, I will create my gender information and then in a totally different APEX session logged on to a totally different workspace I will create my AGE related information.
Session 1, Workspace WS1========================SQL> DECLARE 2 l_collection_name VARCHAR2(255); 3 BEGIN 4 l_collection_name := 'GENDER'; 5 apex_collection.create_or_truncate_collection(l_collection_name); 6 apex_collection.add_member( 7 p_collection_name=>l_collection_name, 8 p_c001=>'Male', 9 p_c002=>'M'); 10 11 apex_collection.add_member( 12 p_collection_name=>l_collection_name, 13 p_c001=>'Female', 14 p_c002=>'F' ); 15 16 apex_collection.add_member( 17 p_collection_name=>l_collection_name, 18 p_c001=>'Unspecified', 19 p_c002=>'U' ); 20 21 END; 22 /PL/SQL procedure successfully completed.SQL>SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'GENDER';C001 C002 C003-------------------- -------------------- --------------------Male MFemale FUnspecified USession 2, Workspace: WS2=========================SQL> DECLARE 2 l_collection_name VARCHAR2(255); 3 BEGIN 4 l_collection_name := 'AGE'; 5 apex_collection.create_or_truncate_collection(l_collection_name); 6 apex_collection.add_member( 7 p_collection_name=>l_collection_name, 8 p_c001=>'0 to 20', 9 p_c002=>0, 10 p_c003=>20 11 ); 12 13 apex_collection.add_member( 14 p_collection_name=>l_collection_name, 15 p_c001=>'21 to 40', 16 p_c002=>21, 17 p_c003=>40 18 ); 19 20 apex_collection.add_member( 21 p_collection_name=>l_collection_name, 22 p_c001=>'41 to 60', 23 p_c002=>41, 24 p_c003=>60 25 ); 26 27 apex_collection.add_member( 28 p_collection_name=>l_collection_name, 29 p_c001=>'61 to 80', 30 p_c002=>61, 31 p_c003=>80 32 ); 33 34 END; 35 /PL/SQL procedure successfully completed.SQL>SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'AGE';C001 C002 C003-------------------- -------------------- --------------------0 to 20 0 2021 to 40 21 4041 to 60 41 6061 to 80 61 80
Even though these are separate collections in separate applications in separate workspaces, the optimizer does not discriminate based on which workspace you are in. That is done by the predicates in your query and the optimizer can choose to execute them in any way it wants.
My application in workspace WS2 still can get the error:
SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac 3 where ac.collection_name = 'AGE' 4 and ac.c002 = 21;and ac.c002 = 21 *ERROR at line 4:ORA-01722: invalid number
Now when I go try to explore why this might be the case, I get absolutely no information to assist me because the offending data actually belongs to someone else’s workspace in someone else’s collection. You will not be able to see it using the standard view.
SQL> select ac.c001, ac.c002, ac.c003 2 from apex_collections ac;C001 C002 C003-------------------- -------------------- --------------------0 to 20 0 2021 to 40 21 4041 to 60 41 6061 to 80 61 80
That data looks totally fine … but you will not be able to query it successfully because of an unseen collection in an unseen application in an unseen workspace.
Ultimately, this comes back to that same old chestnut that you should be using the correct data types for the data you want to store. The APEX_COLLECTIONS view isn’t just character columns, it also has predefined columns for numeric data types and date data types and you should be using them appropriately.
SQL> desc APEX_COLLECTIONS Name Null? Type ----------------------------- -------- -------------------- COLLECTION_NAME NOT NULL VARCHAR2(255) SEQ_ID NOT NULL NUMBER C001 VARCHAR2(32767) C002 VARCHAR2(32767) C003 VARCHAR2(32767)... N001 NUMBER N002 NUMBER N003 NUMBER N004 NUMBER N005 NUMBER D001 DATE D002 DATE D003 DATE D004 DATE D005 DATE ...
It’s just that when it comes to this particular example, it can be very hard to troubleshoot and very hard to decipher what is going on. The best way you can troubleshoot this is to avoid the problem happening in the first place. As I keep saying in my posts here, here, and here, data type discipline is extremely important when it comes to building successful applications with the Oracle Database.
Leave a Reply