Using varray as a constant in PL SQL

Question

Is it possible to use varray of strings as a constant in PL SQL and how? For example I've created a CRUD matrix builder in PL SQL and it works fine with this kind of code:

        v_check := INSTR2(v_string_fnc, 'DROP ');
string_to_parse := v_string_fnc;

  WHILE v_check > 0 LOOP
            v_check := INSTR2(string_to_parse, 'DROP ');
                            IF v_check > 0 THEN
                            v_check := INSTR2(string_to_parse, 'TABLE ', v_check) + 6;
                            result_table := SUBSTR(string_to_parse, v_check);
                            string_to_parse := result_table;
                            result_table := RTRIM(SUBSTR(result_table, 0, INSTR(result_table, ' ')));
                            table_indx := result_table;
                            tab_res(table_indx).table_ := result_table;
                            tab_res(table_indx).delete_ := 'D';
                            end if;
                            end loop;

And similar to this are all other commands. Now I was wondering how to do it like this instead of separate block for every operation to make single block for the cases when commands are like this 'DROP TABLE', 'INSERT INTO', 'DELETE FROM'. Make a varray of

      commands := ( 'DROP TABLE ',  'INSERT INTO ', 'DELETE FROM ');

And after that something like this:

For i in 1..commands.length() loop
    v_check := instr2(string_to_parse, commands(i));
    v_check := INSTR2(string_to_parse, ' ', v_check) + 2;
  result_table := SUBSTR(string_to_parse, v_check);
  string_to_parse := result_table;
  result_table := RTRIM(SUBSTR(result_table,
                               0,
                               INSTR(result_table, ' ')));
  table_indx := result_table;
  tab_res(table_indx).table_ := result_table;

score 0 · Answer 1 · answered Oct 11 '13 at 11:31

Try this:

DECLARE
  TYPE my_varray_t IS VARRAY(10) OF VARCHAR2(100);
  v_my_varray my_varray_t :=
    my_varray_t('DROP TABLE ', 'INSERT INTO ', 'DELETE FROM');
BEGIN
  FOR v_i IN 1..v_my_varray.COUNT
  LOOP
    dbms_output.put_line(v_my_varray(v_i));
  END LOOP;
END;

Not sure - is this what you needed?

Using varray as a constant in PL SQL

1 Answers1