My Oracle Support Banner

Fetching Individual Column From Pipelined Function in RefCursor Throws ORA-01007 (Doc ID 2430818.1)

Last updated on AUGUST 08, 2018

Applies to:

PL/SQL - Version 11.2.0.4 to 12.2.0.1 [Release 11.2 to 12.2]
Information in this document applies to any platform.

Symptoms

Fetching only a element from a pipelined function (which returns record of multiple elements) in reference cursor fails with ORA-01007: variable not in select list

It can be explained with the help of below example

sql> CREATE OR REPLACE PACKAGE my_pac IS

     TYPE my_rec IS RECORD(

           col_a VARCHAR2(500),

           col_b VARCHAR2(500));

     TYPE my_tab IS TABLE OF my_rec;

     FUNCTION f_my_tab_fn(p_num IN NUMBER) RETURN my_tab

      PIPELINED;

  END;

/

 

sql> CREATE OR REPLACE PACKAGE BODY my_pac IS

      FUNCTION f_my_tab_fn(p_num IN NUMBER) RETURN my_tab

      PIPELINED IS

      v_rec my_rec;

      BEGIN

       FOR i IN 1 .. p_num LOOP

          v_rec.col_a := to_char(i);

          v_rec.col_b := 'hello';

        PIPE ROW(v_rec);

      END LOOP;

   END;

END;

/

sql> set serveroutput on

sql>DECLARE

      v_sql VARCHAR2(3000) := 'select col_a from table(my_pac.f_my_tab_fn(5))';

      v_src sys_refcursor;

      test_rec my_pac.my_rec;

      BEGIN

       OPEN v_src FOR v_sql;

         FETCH v_src INTO test_rec;

         dbms_output.put_line('Fetched '||test_rec.col_a);

       CLOSE v_src;

    END;

/

Fails with

ERROR at line 1:

ORA-01007: variable not in select list

ORA-06512: at line 7

Changes

Issue is generic to all versions.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.