How to loop through NESTED TABLE in ORACLE for Oracle Forms stored procedure based block?

Question

I'm working on Oracle Forms Builder 10g. Inside my application, I have a Nested table type that holds a bunch of varchar2. it's type my_type_1 is table of varchar2(255). I have created the same type in my database.

Now, I'm creating a form based on a stored procedure. I want to pass a variable of my_type_1 in the body is like that:

procedure my_proc (my_var_in_out IN OUT some_type, my_var_test IN my_type_1) is

cursor my_cursor(id varchar2(255)) is
select name from emp where emp_id = id;

idx number := 1;

begin

for I in my_cursor(my_var_test )  loop <<< this is where I'm stuck. Can I pass it like that ?
   my_var_in_out (idx) := I;
   idx  := idx  +1;
end loop;
end;

for r in (select Column_Value from table(cast(my_var_test as my_type_1))) loop dbms_output.put_line(r.column_value); end loop; — Ayubxon Ubaydullayev

XING XING · Accepted Answer · 2017-10-10T06:46:25

You can loop it like the below:

Type Created

  CREATE OR REPLACE TYPE my_type_1 IS TABLE OF VARCHAR2 (1000);
    /


    CREATE TABLE emp
    (
       fname    VARCHAR2 (100),
       emp_id   VARCHAR2 (10)
    );

SQL> select * from emp;

FNAME                                                                                                EMP_ID
---------------------------------------------------------------------------------------------------- ----------
XXX                                                                                                  1
YYY                                                                                                  2

Procedure

CREATE OR REPLACE PROCEDURE my_proc (my_var_test IN OUT my_type_1)
IS
   CURSOR my_cursor (my_var_test my_type_1)
   IS
      SELECT fname
        FROM emp
       WHERE emp_id MEMBER OF my_var_test; --<--This is how you implement in clause while using a collection

   v_var   my_type_1 := my_type_1 ();
BEGIN
   OPEN my_cursor (my_var_test);

   FETCH my_cursor BULK COLLECT INTO v_var;

   CLOSE my_cursor;

   FOR rec IN 1 .. v_var.COUNT
   LOOP         
      my_var_test (rec) := v_var (rec);         
   END LOOP;
END;

Execution:

DECLARE
   var   my_type_1 := my_type_1 ();
BEGIN
   var.EXTEND(2);

   var (1) := '1';
   var (2) := '2';

   my_proc (MY_VAR_TEST => var);


   FOR i IN 1 .. var.COUNT
   LOOP
      DBMS_OUTPUT.put_line (var (i));
   END LOOP;
END;

Output:

SQL> /
XXX
YYY

PL/SQL procedure successfully completed.

How to loop through NESTED TABLE in ORACLE for Oracle Forms stored procedure based block?

1 Answers