How can I use a plsql table type in a sql query?

Question

Let me start off by saying I'm open to other ways of doing this, but right now, this is all I've been able to come up with.

I'm working within a package.procedure and I am using a table type as an array/list to store IDs of records that match search criteria. Once I have compiled the list, I want to open a cursor that selects from a table where the record ID is in my list.

Data structure:

TYPE i_array IS TABLE OF t_table.id_column%TYPE INDEX BY PLS_INTEGER;
lt_results i_array;
ln_count pls_integer;

Populating list:

    ln_count := 0;
    for recs in (select id_column from t_child where other_column = ls_criteria)
    loop
        ln_count := ln_count + 1;
        lt_results(ln_count);
    end loop;

Opening cursor and accessing list:

    open cur for 
        select col_a,
               col_b,
               col_c
        from t_table
        where id_column in (select lt_results(level)
                            from dual
                            connect by level <= ln_count);

Forgot the most important part. The current setup is giving ORA-01403: no data found at the open cursor line. I'm confident the error is being caused by the list access in the where clause. — wombatatat
– wombatatat, Commented Aug 17, 2017 at 14:04
You would be better off to populate the array using bulk collect. Also, if you want to use the array in SQL, then you must create the type at the schema level - i.e. create or replace type .... Better yet, why are you using an array at all? Why not do where id_column in (select id_column from t_child where ...) in your cur ref cursor?? — Boneist
– Boneist, Commented Aug 17, 2017 at 14:15
The sticking point isn't the collecting.. it's using the list in the where clause of the return cursor. — wombatatat
– wombatatat, Commented Aug 17, 2017 at 14:20
You can't. What version of Oracle are you using? 12C allows nested table collections (not index-by arrays) declared in packages to be used in SQL. — Tony Andrews
– Tony Andrews, Commented Aug 17, 2017 at 14:22
I'm not doing the usual where in because ls_criteria is a substring of a text input . ["Atlanta", "Thursday"] each string will be used as search criteria. The records that match will be added to the list for the return cursor — wombatatat
– wombatatat, Commented Aug 17, 2017 at 14:29

Tony Andrews · Accepted Answer · 2017-08-17 14:35:58Z

If using Oracle 12C you can use a nested table collection in the package like this:

create or replace package test is

  TYPE i_array IS TABLE OF t_child.id_column%TYPE;

  procedure run;

end;

create or replace package body test is

  procedure run is
    lt_results i_array := i_array();
    cur sys_refcursor;
  begin
    for r in (select id_column from t_child where other_column = ls_criteria) loop
      lt_results.extend;
      lt_results(lt_results.count) := r.id_column ;
    end loop;

    open cur for 
      select col_a,
             col_b,
             col_c
      from t_table
      where id_column in (select column_value from table(lt_results));
  end run;

end;

Prior to 12C the type i_array would need to be in the database:

create type i_array is table of varchar2(10); -- For example

You could use a handy pre-existing collection like SYS.KU$_VCNT instead of creating your own.

BTW Note that this:

    for r in (select id_column from t_child where other_column = ls_criteria) loop
      lt_results.extend;
      lt_results(lt_results.count) := r.id_column ;
    end loop;

can be replaced more efficiently by this:

    select id_column 
      bulk collect into lt_results
      from t_child where other_column = ls_criteria;

Collectives™ on Stack Overflow

How can I use a plsql table type in a sql query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related