Forum Stats

  • 3,827,547 Users
  • 2,260,791 Discussions
  • 7,897,297 Comments

Discussions

Invalid number error when using apex_region.open_query_context

Kinjan Bhavsar
Kinjan Bhavsar Member Posts: 87 Blue Ribbon

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

Tagged:

Best Answer

  • Carsten Czarski-Oracle
    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:

    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

«1

Answers

  • Ananya Chatterjee-Oracle
    Ananya Chatterjee-Oracle Member Posts: 14 Employee

    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
    Kinjan Bhavsar Member Posts: 87 Blue Ribbon

    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
    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

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 87 Blue Ribbon

    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
    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

  • Kinjan Bhavsar
    Kinjan Bhavsar Member Posts: 87 Blue Ribbon

    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.

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

    Kinjan Bhavsar
  • Carsten Czarski-Oracle
    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:

    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
    Kinjan Bhavsar Member Posts: 87 Blue Ribbon

    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
    Kinjan Bhavsar Member Posts: 87 Blue Ribbon

    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?