Invalid number error when using apex_region.open_query_context

Kinjan Bhavsar

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

Carsten Czarski-Oracle

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:

1. Change the declaration of V_ID to use PLS_INTEGER or NUMBER, as Ananya suggested.
2. 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

Ananya Chatterjee-Oracle

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?

Kinjan Bhavsar

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

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

Kinjan Bhavsar

Hi @Carsten Czarski-Oracle ,

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

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

Kinjan Bhavsar

Hi @Carsten Czarski-Oracle ,

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.

Ananya Chatterjee-Oracle

You have v_id defined as varchar2 and not number. Is this intended?

Carsten Czarski-Oracle

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:

1. Change the declaration of V_ID to use PLS_INTEGER or NUMBER, as Ananya suggested.
2. 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

Kinjan Bhavsar

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.

Kinjan Bhavsar

Hi @Carsten Czarski-Oracle ,

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?