CODEWITHSUNDEEP
plsqlcursor
newman
newman

Reputation: 117

Does Oracle support non-scalar cursor parameter?

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.

Upvotes: 1

Views: 406

Answers (2)

mkuligowski
mkuligowski

Reputation: 1604

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;

Upvotes: 0

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

You can certainly pass a parameter to a cursor, just like you can to a function - but only IN parameters. However, PL/SQL is a strongly typed language, so the datatype must be specified at the time of compilation.

It looks to me like what you will need to do is construct the query dynamically and then use

OPEN cursor FOR l_query;

where l_query is the constructed string. This should give you a feel for what you can do:

CREATE OR REPLACE PACKAGE return_id_sal
   AUTHID DEFINER
IS
   TYPE employee_rt IS RECORD
   (
      employee_id   employees.employee_id%TYPE,
      salary        employees.salary%TYPE
   );

   FUNCTION allrows_by (append_to_from_in IN VARCHAR2 DEFAULT NULL)
      RETURN SYS_REFCURSOR;
END return_id_sal;
/

CREATE OR REPLACE PACKAGE BODY return_id_sal
IS
   FUNCTION allrows_by (append_to_from_in IN VARCHAR2 DEFAULT NULL)
      RETURN SYS_REFCURSOR
   IS
      l_return   SYS_REFCURSOR;
   BEGIN
      OPEN l_return FOR
         'SELECT employee_id, salary FROM employees ' || append_to_from_in;

      RETURN l_return;
   END allrows_by;
END return_id_sal;
/

DECLARE
   l_cursor   SYS_REFCURSOR;
   l_row      return_id_sal.employee_rt;
BEGIN
   l_cursor := return_id_sal.allrows_by ('WHERE department_id = 10');

   LOOP
      FETCH l_cursor INTO l_row;

      EXIT WHEN l_cursor%NOTFOUND;
   END LOOP;
END;
/

You will need to take precautions against SQL injection with this sort of code. Certainly a user should never be able to pass SQL text directly to such a function!

Upvotes: 4

Related Questions