1 Reply Latest reply: Aug 16, 2013 4:49 AM by timgaulza RSS

    List View Report with pipelined function in Mobile application and ORA-01007: variable not in select list

    user13709832

      Hi!

      I have a problem with List View Report in mobile application (theme 50 in apex) after updating to apex 4.2.2. I created Report -> List View. I used select from pipelined function in Region Source. Then when page is running and submited three times (or refreshed three times) I get an error:

       

      Error during rendering of region "LIST VIEW".

      ORA-01007: variable not in select list

      http://apex.oracle.com/i/htmldb/builder/rollup_minus_dgray.gifTechnical Info (only visible for developers)

      • is_internal_error: true
      • apex_error_code: APEX.REGION.UNHANDLED_ERROR
      • ora_sqlcode: -1007
      • ora_sqlerrm: ORA-01007: variable not in select list
      • component.type: APEX_APPLICATION_PAGE_REGIONS
      • component.id: 21230833903737364557
      • component.name: LIST VIEW
      • error_backtrace:

           ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 4613

           ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 3220

       

      I get this error only when I use select from pipelined function in Region Source (for example: "select value1, value2 from table(some_pipelined_function(param1, param2)) ").

      You can check it on http://apex.oracle.com/pls/apex/f?p=50591 (login - demo, password - demo).

      In this application:

       

      - I created package TAB_TYPES_PKG:

      create or replace PACKAGE TAB_TYPES_PKG IS

       

      TYPE cur_rest_r IS RECORD (

          STR_NAME          VARCHAR2(128),

          INFO              VARCHAR2(128)

          );

      TYPE cur_rest_t IS TABLE OF cur_rest_r;

       

      END TAB_TYPES_PKG;

      - I created pipelined function TEST_FUNC:

      create or replace

      FUNCTION TEST_FUNC

      RETURN TAB_TYPES_PKG.cur_rest_t  PIPELINED IS

      r_cur_rest TAB_TYPES_PKG.cur_rest_r;

       

      BEGIN

         

      r_cur_rest.STR_NAME := 'ROW 1';

      r_cur_rest.INFO := '10';

      PIPE ROW (r_cur_rest);

       

      r_cur_rest.STR_NAME := 'ROW 2';

      r_cur_rest.INFO := '20';

      PIPE ROW (r_cur_rest);

       

      r_cur_rest.STR_NAME := 'ROW 3';

      r_cur_rest.INFO := '30';

      PIPE ROW (r_cur_rest);

       

      r_cur_rest.STR_NAME := 'ROW 4';

      r_cur_rest.INFO := '40';

      PIPE ROW (r_cur_rest);

       

      r_cur_rest.STR_NAME := 'ROW 5';

      r_cur_rest.INFO := '50';

      PIPE ROW (r_cur_rest);

       

      RETURN;

      END TEST_FUNC;

       

      - I created List View Report on Page 1:

      Region Source:

      SELECT str_name,

             info

      FROM TABLE (TEST_FUNC)

       

      We can see error ORA-01007 after refresing (or submiting) Page 1 three times or more.

      How to fix it?

        • 1. Re: List View Report with pipelined function in Mobile application and ORA-01007: variable not in select list
          timgaulza

          Hi all

           

          I'm experiencing the same issue.  Predictably on every third refresh I receive:

           

          http://localhost:8080/i/error.gifErrorError during rendering of region "Results".
          ORA-01007: variable not in select list
          http://localhost:8080/i/htmldb/builder/rollup_minus_dgray.gifTechnical Info (only visible for developers)
          • is_internal_error: true
          • apex_error_code: APEX.REGION.UNHANDLED_ERROR
          • ora_sqlcode: -1007
          • ora_sqlerrm: ORA-01007: variable not in select list
          • component.type: APEX_APPLICATION_PAGE_REGIONS
          • component.id: 6910805644140264
          • component.name: Results
          • error_backtrace: ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 4613 ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 3220
          OK

           

          I am running Application Express 4.2.2.00.11 on GlassFish 4 using Apex Listener 2.0.3.221.10.13.

           

          Please note: this works perfectly using a classic report in my desktop application; however, no joy on the mobile side with a list view.  I will use a classic report in the interim.

           

          My region source is as follows:

           

          SELECT description AS "DESCRIPTION", reference AS "REFERENCE" FROM TABLE(AUTOCOMPLETE_LIST_VIEW_FNC('RESULTS'))

           

          The procedure:

           

            FUNCTION AUTOCOMPLETE_LIST_VIEW_FNC(
                p_collection_name IN VARCHAR2)
              RETURN list_row_table_type
            AS
              v_tab list_row_table_type := list_row_table_type();
             
            BEGIN
              DECLARE
                jsonarray json_list;
                jsonobj json;
                json_clob CLOB;
               
              BEGIN
          
                SELECT clob001
                INTO json_clob
                FROM apex_collections
                WHERE collection_name = p_collection_name;
               
                jsonobj              := json(json_clob);
                jsonarray            := json_ext.get_json_list(jsonobj, 'predictions');
               
                FOR i IN 1..jsonArray.count
                LOOP
                  jsonobj := json(jsonArray.get(i));
                  v_tab.extend;
                  v_tab(v_tab.LAST) := list_row_type(json_ext.get_string(jsonobj, 'description'), json_ext.get_string(jsonobj, 'reference'));
                END LOOP;
               
                RETURN(v_tab);
               
              END;   
            END AUTOCOMPLETE_LIST_VIEW_FNC;
          

           

          Thanks!

           

          Tim