This discussion is archived
1 Reply Latest reply: Aug 16, 2013 2:49 AM by timgaulza RSS

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

user13709832 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points