neverMind neverMind - 1 month ago 6
Java Question

Looping through nested tables in oracle and returning a list of objects to java

I have this table created as follow:

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)
);
/


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:

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;


but it returned no data found for the first select.

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:

SUPPLIERS

->SUPPLIER1

-prod1

-prod2

- ...

->SUPPLIER2

-prod1

-prod2

- ....

Is it possible to retrieve all that info like an hash of
supplier:list_of_products
with my current table and types?

Answer

First I would suggest against permanently storing nested tables in the database. While objects types can be really useful for variables and temporary results, you will find that nested tables tend to complicate SQL queries, add overhead and in general produce code that is less maintainable than regular relational normalized designs.

Now for your problem, first let's populate your table:

SQL> DECLARE
  2     l_prodtable prodtable := prodtable();
  3  BEGIN
  4     l_prodtable.extend(2);
  5     l_prodtable(1) := prod_obj(1, 'Prod A', '', '', '', '', '');
  6     l_prodtable(2) := prod_obj(2, 'Prod B', '', '', '', '', '');
  7     FOR i IN 1 .. 2 LOOP
  8        INSERT INTO fornecprods VALUES (fornec_obj(i, 'Forn '||i, '',
  9                                                   '', '', '', '', ''),
 10                                        l_prodtable);
 11     END LOOP;
 12  END;
 13  /     
PL/SQL procedure successfully completed

You would then loop through the elements rather simply:

SQL> BEGIN
  2     FOR cc IN (SELECT supplier, products FROM fornecprods) LOOP
  3        dbms_output.put_line('-----------------------');
  4        dbms_output.put_line('Products list of '
  5                             || cc.supplier.NOME_FORNECEDOR);
  6        dbms_output.put_line('-----------------------');
  7        FOR i IN 1 .. cc.products.count LOOP
  8           dbms_output.put_line('-' || cc.products(i).NOME_PROD);
  9        END LOOP;
 10     END LOOP;
 11  END;
 12  /

-----------------------
Products list of Forn 1
-----------------------
-Prod A
-Prod B
-----------------------
Products list of Forn 2
-----------------------
-Prod A
-Prod B

PL/SQL procedure successfully completed
Comments