Does Oracle support non-scalar cursor parameter?

Question

This is a question about Oracle PL/SQL.

I have a procedure in which the exact WHERE clause is not known until the run time:

DECLARE
    CURSOR my_cursor is 
    SELECT ...
    FROM ...
    WHERE terms in (
        (SELECT future_term2 FROM term_table),  -- whether this element should be included is conditional
        (SELECT future_term1 FROM term_table),
        (SELECT present_term FROM term_table)
    );
BEGIN
    (the processing)
END;
/

What the (SELECT ... FROM term_table) query returns is a 4-character string.

For a solution to this, I am thinking of using a parameterized cursor:

DECLARE
    target_terms SOME_DATATYPE;

    CURSOR my_cursor (pi_terms IN SOME_DATATYPE) IS 
        SELECT ...
        FROM ...
        WHERE terms in my_cursor.pi_terms;
BEGIN
    target_terms := CASE term_digit
    WHEN '2' THEN (
        (SELECT future_term2 FROM term_table),
        (SELECT future_term1 FROM term_table),
        (SELECT present_term FROM term_table)
    ) ELSE (
        (SELECT future_term1 FROM term_table),
        (SELECT present_term FROM term_table)
    )
    END;

    FOR my_record IN my_cursor (target_terms) LOOP
        (the processing)
    END LOOP;
END;
/

The problem is what the datatype for SOME_DATATYPE should be is not known to me, nor is it known whether Oracle supports such a cursor parameter at all. If supported, is the way shown above to fabricate the value for target_terms correct? If not, how?

Hope someone who know can advise. And thanks a lot for the help.

Answer 1

You can use also some built-in VARRAY SQL types like SYS.ODCIVARCHAR2LIST or create your own :

CREATE OR REPLACE NONEDITIONABLE TYPE VARCHARLIST
  AS VARRAY(32767) OF VARCHAR2(4000);

Then you can use it with SELECT COLUMN_VALUE FROM TABLE(COLLECTION) statement in your cursor:

DECLARE
    l_terms SYS.ODCIVARCHAR2LIS; --or VARCHARLIST

    CURSOR my_cursor (p_terms IN SYS.ODCIVARCHAR2LIS) IS 
        SELECT your_column
        FROM your_table
        WHERE terms in (select COLUMN_VALUE from table (p_terms));
BEGIN
    select term 
    bulk collect into l_terms 
    from (
      select 'term1' term from dual
      union all
      select 'term2' term from dual
     );

    FOR my_record IN my_cursor (l_terms) LOOP
        --process data from your cursor...
    END LOOP;
END;