posted 16 years ago
thank you,
i have already found out a solution and am just going for performance. this is my actual scenario.
I have a table eis_mst_electrical_equipment.
The table has equipment_no which is the primary key. equipment_name, equipment_quantity, equipment_model.
Each equipment can have multiple models(multiple rows in the table)
Eg: 1 | Electric Bulb | 3 | 40W
2 | Electric Bulb | 5 | 60W
and so on....
my need is to fetch each equipment, exactly once with it's models and quantities put in to a variable separated by ",". like this - Electric Bulb - 40,60 & 3,5. put in to respective setXXXX methods.
SOLUTION 1:
In DAO:
1. Call procedure that has following query.
open cursor for
select distinct(equipment_name), equipment_no,
pkg_electrical.fnGetModel(equipment_name) as models_quantity
from eis_mst_electrical_equipment;
In fnGetModel(equipment_name):
open cur for
select equipment_quantity, equipment_model
from eis_mst_electrical_equipment;
where eis_mst_electrical_equipment.equipment_name = equipment_name;
loop through the cursor and form two variables separated by ","(40,60 & 3,5). the final variable is separated by '~' like models~quantity (40,60 ~ 3,5). and return this model~quantity to that select statement.
this is where i get that string buffer too small error when forming those variables.
SOLUTION 2
In DAO:
call the procedure that returns cursor for the following query.
select distinct(equipment_name), equipment_no
from eis_mst_electrical_equipment;
while iterating through this resultset call another procedure, sending equipment_name as the argument, that returns cursor for the following query.
select equipment_quantity, equipment_model
from eis_mst_electrical_equipment;
where eis_mst_electrical_equipment.equipment_name = equipment_name;
iterate through this cursor's resultset and form the variables. And put it into respective set methods.
my question is :
1) in the second solution, is there any overhead associated with callable statement?
2) iterating in store procedure or in java - which one is efficient?
3) if stored procedure is the right way, are there any data type other than varchar2(32767) i can store the variables in?
have a great day,
krishna prasad