How can i pass multiple values to an array parameter function

Question

i need your help.....how can i pass multi values into single parameter in a function?

The values 'AAA 1','BBB 2', 'CCC 3' 'DDD 4' are to be passed to the same parameter "v_type", the values will be sent based on the selection from the drop down in the front end screen. The user can select one or more values from the list and those values should be passed to the procedure which in turn will be passed to the WHERE clause of the SELECT statement inside the procedure.

My function is somenthing like this:

Example

CREATE OR REPLACE FUNCTION FN_GET_ROWS
(v_date_ini         IN DATE,
  v_date_end         IN DATE,
  v_type             IN VARCHAR2
)
RETURN TEST_TABTYPE
AS
V_Test_Tabtype Test_TabType;
BEGIN
     SELECT TEST_OBJ_TYPE(DATE, NAME, ALERT)
            BULK COLLECT INTO V_Test_TabType
            FROM (select date, name, alert
                from Table
             where DATE BETWEEN v_date_ini AND v_date_end
                AND Alert in  (select REGEXP_SUBSTR (v_type, '[^,]+', 1, level)
                                        from dual
                                    connect by level <= length(regexp_replace(v_type,'[^,]*'))+1)
);
RETURN V_Test_TabType;
END;

Searching internet i found that maybe an Varray works but i dont know how to assign it to the variable :type with the parameters that the user selects on the screen.

I create this types on database, how can i used it? i'm kind a new in plsql.

CREATE TYPE alert_obj AS OBJECT (type_alert   VARCHAR2(60));
CREATE TYPE alert_varray_typ AS VARRAY(100) OF alert_obj;

Thanks for your help

Emanuel.

The values will be passed from where - what is the front end? What language are you calling the Oracle function from? (And you're returning a collection too? Maybe you want a ref cursor return?) — Alex Poole, Jun 26 '15 at 18:34
Hi Alex, the front end is BI publisher 11, the query of dataset is: SELECT * FROM TABLE(FN_GET_ROWS ( :date_ini, :date_end, :type)); The problem is that seems when BI Publisher passes the parameter :type it interprets each comma as a separate parameter so I get an invalid number of parameters error. If select a value not error, and then select two values of an error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'FN_GET_ROWS' — Emanuel Padilla, Jun 29 '15 at 15:39

score 0 · Answer 1 · answered Jul 08 '15 at 12:56

I dont know, if I really understand your problem. But I think, that there is more solutions.

You can use string of VARCHAR2 as parameter and after that parse it with function like that:

PROCEDURE p_parse_into_array (
  lv_str         IN          VARCHAR2,
  lt_table       IN OUT      sys.dbms_debug_vc2coll,
  lv_splitter    IN          VARCHAR2)
IS
  ln_position    NUMBER := 0;
  ln_position_2  NUMBER;
  ln_i           NUMBER := 1;
BEGIN
  ln_position_2 := INSTR(lv_str,lv_splitter,1,1);

  WHILE ln_position_2 != 0
  LOOP
    lt_table.extend(1);
    lt_table(ln_i) := SUBSTR(lv_str,ln_position+1,ln_position_2-ln_position-1);
    ln_position := INSTR(lv_str,lv_splitter,1,ln_i);
    ln_position_2 := INSTR(lv_str,lv_splitter,1,ln_i+1);
    ln_i := ln_i + 1;
  END LOOP;
END;

where lv_str is string to parse, lt_table is table of varchar(2000) and lv_splitter is character to split (, . ; - etc) and this function return values into lt_table, which you can use in you select menu.

Second solution is to use varray as you say, but there you need to use dynamic sql with command:

execute immediate 'select * from dual where some_value in (select * from table('||my_varray_table||'));

And other solution is to use nested table. It´s your choice, which of this solution you prefer :)

How can i pass multiple values to an array parameter function

1 Answers1