title: Looping through nested tables in oracle and returning a list of objects to java
body: <p>I have this table created as follow: </p>
  
  <pre><code>    CREATE TABLE FORNECPRODS 
         (    SUPPLIER FORNEC_OBJ , 
          PRODUCTS PRODTABLE 
         )NESTED TABLE "PRODUCTS" STORE AS "PRODUCTSTABLE";
      /
  
      create or replace
      type PRODTABLE as table of PROD_OBJ;
  
      create or replace
      TYPE PROD_OBJ AS OBJECT (
  
         ID_PROD              NUMBER(6,0),
         NOME_PROD            VARCHAR2(100),        
         PREC_COMPRA_PROD     NUMBER(10,2),         
         PREC_VENDA_PROD      NUMBER(10,2),         
         QTD_STOCK_PROD       NUMBER(10),
         QTD_STOCK_MIN_PROD   NUMBER(10),
         IVA_PROD             NUMBER(6,2)
      );
  
  /
  create or replace
  type PRODTABLE as table of PROD_OBJ;
  /
  create or replace type FORNEC_OBJ as object (
     ID_FORNECEDOR        NUMBER(6) ,
     NOME_FORNECEDOR      VARCHAR2(100) ,
     MORADA               VARCHAR2(300),
     ARMAZEM              VARCHAR2(300),
     EMAIL                VARCHAR2(30),
     TLF                  NUMBER(30) ,
     TLM                  NUMBER(30),
     FAX                  NUMBER(30)
     ); 
  /
  </code></pre>
  
  <p>The problem is that I'm trying to get a list of products for each supplier, from table FORNECPRODS, but I can't (my idea was to return a struct like an hash with :list_of_products). To try that, I used this code only to print products from each supplier:</p>
  
  <pre><code>declare
      v_products    prodtable;
      TYPE t_supplier is TABLE OF FORNEC_OBJ;
      v_supplier           t_supplier;
  
  begin
      select supplier bulk collect into v_supplier from fornecprods;
      for j in v_supplier.first.. v_supplier.last
      loop
          select products into v_products
          from fornecprods where supplier = v_supplier(j);
          dbms_output.put_line('-----------------------');
          dbms_output.put_line('Products list of ' || v_supplier(j).NOME_FORNECEDOR);
          dbms_output.put_line('-----------------------');
          for i in v_products.first .. v_products.last
          loop
              dbms_output.put(v_products(i).NOME_PROD);
          end loop;
      end loop;
  end;
  </code></pre>
  
  <p>but it returned no data found for the first select.</p>
  
  <p>so, could someone please help me find a way to retrieve a list (prodtable) from oracle to java? I already have the class to map a supplier and a product, I even passed an array of each of them from java to oracle, so they're good, I only need my j-tree to look like this:</p>
  
  <p>SUPPLIERS<br>
     ->SUPPLIER1<br>
         -prod1<br>
         -prod2<br>
         - ...<br>
     ->SUPPLIER2<br>
       -prod1<br>
       -prod2<br>
       - ....  </p>
  
  <p>Is it possible to retrieve all that info like an hash of <code>supplier:list_of_products</code> with my current table and types?</p>