Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Invalid number error when using apex_region.open_query_context

Hi All,
Has anyone used apex_exec API with the classic report?
I am getting invalid number
an error every time when I use the following PL/SQL code on line
v_context := apex_region.open_query_context (p_page_id => :APP_PAGE_ID, p_region_id => v_region_id);
select region_id into v_region_id from apex_application_page_regions where application_id = :APP_ID and page_id = :APP_PAGE_ID and static_id = 'my_static_id'; -- Get the query context for the report v_context := apex_region.open_query_context (p_page_id => :APP_PAGE_ID, p_region_id => v_region_id); v_id := apex_exec.get_column_position(v_context, 'MY_COLUMN_NAME');
I am using this piece of code in a page process to fetch all data and update values in DB. I am using APEX 20.2 and 18c DB.
Let me know if anything is wrong with this logic?
Thanks
Best Answer
-
Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,336 Employee
In addition to Ananya's point:
There are two overloads of the APEX_EXEC.GET_NUMBER function. One accepts a column index (as NUMBER), the other one expects a column name, as VARCHAR2.
As your variable v_id is of the VARCHAR2 type, you're calling the overload which accepts a column name. However, your v_id variable contains a column index. This is where things start to fail.
There are two possible solutions:
- Change the declaration of V_ID to use PLS_INTEGER or NUMBER, as Ananya suggested.
- Use named notation when you're calling APEX_EXEC.GET_NUMBER:
.. apex_exec.get_number( p_context => v_context, p_column_idx => v_id );
regards
-Carsten
Answers
-
Hi,
- Can you confirm if v_region_id returned by the query
select region_id into v_region_id from apex_application_page_regions where application_id = :APP_ID and page_id = :APP_PAGE_ID and static_id = 'my_static_id';
returns a non-null number?
- If you replace the bind variables with actual numeric values for the region and the page, do you still see the error?
-
Hi,
Yes, I get a number value as 229080201368133593.
Even, though I tried adding hardcoded values for : APP_ID, :APP_PAGE_ID, and v_region_id but still I am getting an invalid number error.
-
Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,336 Employee
Hi,
please share the exact error message, which you have received, and also provide the full error stack. Best would be enabling debug, reproducing the error and providing debug output - that should contain all relevant information.
regards
-Carsten
-
Now, after checking again with hardcoded values, I am getting an error at a new line in my code
Column "1" specified for attribute "1" has not been found in the data source!
-- Get the column positions for column(s) v_id:= apex_exec.get_column_position(v_context, 'ID'); -- Loop through the query of the context while apex_exec.next_row(v_context) loop if v_ids is null then -- first row without seperator ":" v_ids := apex_exec.get_number(v_context, v_id); else -- add seperator ":" to each additional row v_ids := v_ids || ':' || apex_exec.get_number(v_context, v_id); end if; end loop; -- Close query context apex_exec.close(v_context);
Here is the debug html
-
Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,336 Employee
I’m afraid, this is still only a small part of the picture - I still don’t really understand what’s going on. The error stack indicates that the error happens at like 34 of your code. Which statement do you have there?
Also: what does this very region execute? A plain SQL Query, a PL/SQL block returning a SQL query? If the latter, the error could well originate from there.
It would be best if you provided the full code, so that we can see what you’re doing and what fails. Providing a test case on apex.oracle.com would be even better.
Regards
-Carsten
-
I have a classic report which returns data based on sql query and a where clause which includes filter items to match and fetch only those records which matches with filter criteria.
Here is the full code
declare v_region_id number; v_context apex_exec.t_context; v_id varchar2(100); v_ids varchar2(4000); begin itv_debug.log_me('ids_1: ' || :APP_ID); itv_debug.log_me('ids_2: ' || :APP_PAGE_ID); -- Get the region id for the report select region_id into v_region_id from apex_application_page_regions where application_id = 1110 and page_id = 1168 and static_id = 'rules_rpt'; itv_debug.log_me('_ids_3: ' || v_region_id); -- Get the query context for the report v_context := apex_region.open_query_context (p_page_id => 1168, p_region_id => 229080201368133593);--v_region_id); itv_debug.log_me('ids_4: ' || v_context); -- Get the column positions for column(s) v_id := apex_exec.get_column_position(v_context, 'ID'); itv_debug.log_me('ids_5: ' || v_id); -- Loop through the query of the context while apex_exec.next_row(v_context) loop if v_ids is null then -- first row without seperator ":" itv_debug.log_me('ids_6: ' || v_ids); v_ids := apex_exec.get_number(v_context, v_id); else -- add seperator ":" to each additional row itv_debug.log_me('ids_7: ' || v_ids); v_ids := v_ids || ':' || apex_exec.get_number(v_context, v_id); end if; end loop; itv_debug.log_me('ids_8: ' || v_ids); -- Close query context apex_exec.close(v_context); -- exception -- when others then -- apex_exec.close(v_context); -- raise; end;
In the code above, line 34 is
apex_exec.get_number(v_context, v_id)
Ignore, itv_debug.log_me, it's my internal function to gather logs.
-
You have v_id defined as varchar2 and not number. Is this intended?
-
Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,336 Employee
In addition to Ananya's point:
There are two overloads of the APEX_EXEC.GET_NUMBER function. One accepts a column index (as NUMBER), the other one expects a column name, as VARCHAR2.
As your variable v_id is of the VARCHAR2 type, you're calling the overload which accepts a column name. However, your v_id variable contains a column index. This is where things start to fail.
There are two possible solutions:
- Change the declaration of V_ID to use PLS_INTEGER or NUMBER, as Ananya suggested.
- Use named notation when you're calling APEX_EXEC.GET_NUMBER:
.. apex_exec.get_number( p_context => v_context, p_column_idx => v_id );
regards
-Carsten
-
Thanks @Carsten Czarski-Oracle .
I will try and re-run the code. Actually my requirement is I want to get all the ids from the report and update all records without running the report query again in the page process, so was exploring apex_exec.open_query_context.
-
Thanks, you and Ananya were right. I made a mistake and it was due to the declaration of the variable.
One more point, as you might have seen from my logic that I am trying to get all the IDs (primary key of my table) and combine them in a varchar2 variable using a colon. I will then update all these records using an instr. Is it the correct method to use apex_exec or am I doing it wrong?
Also, one more question, is apex_exec.open_query_context better in performance than loading the query in a TYPE using BULK COLLECT and then using that TYPE in forall?