looping in oracle or java

[krishna prasad gunasekaran]

hai,
Q1 : i have a procedure where i fetch some rows. which of the following is recommended when it comes to performance

option 1. loop through the cursor in stored procedure to form a variable and return the variable to java.
option 2. return the cursor to java and loop through the resultset to form that required variable

Q2: sometimes the rows returned by my SELECT statement are so high that when i loop in oracle and form that variable, it throws ORA-06502: PL/SQL: numeric or value error: character string buffer too small. the variable is declared as varchar2(32767), which is the max size. what are the other optional data types i can use.

[Jeanne Boyarsky]

If you have something that doesn't work with one approach, it is too early to worry about performance. The first thing is to get it working and see what the performance is. Then if it is too slow, it can be tuned. In other words, try the second approach and then decide.

Personally, I would decide based on the business scenario rather than a hypothetical performance concern anyway. You are returning over 32767 characters of data either way.

[krishna prasad gunasekaran]

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?

[Jeanne Boyarsky]

Originally posted by krishna prasad gunasekaran:
my need is to fetch each equipment, exactly once with it's models and quantities put in to a variable separated by ",". .... are there any data type other than varchar2(32767) i can store the variables in?

Using a comma delimited table to return tabular data is a very non relational database way of looking at things. Which means you are forcing a square peg into a round hole type of thing. This leads me to favor the "iterate in Java" scenario for your business case. It's a more natural idiom.

I still think the performance is likely to be similar and you should go with the one that makes most sense. (which would be looping in Java.)

i have already found out a solution and am just going for performance.

Not completely. You are saying that the data doesn't fit in a varchar. This is a functionality issue, not a performance one.

[krishna prasad gunasekaran]

the existing solution is solution 2(looping in java), not solution 1. i thought may be if oracle is better way, i can try to rectify that error, otherwise stick to already existing solution, which, by the way, is looping in java. thank you for your suggestion. still does a callable statement carry any overhead? because as explained earlier, i have to make the same number of calls to the procedure as the number of rows returned.

[Jeanne Boyarsky]

Originally posted by krishna prasad gunasekaran:
the existing solution is solution 2(looping in java)

I see. That makes sense now.

thank you for your suggestion. still does a callable statement carry any overhead?

No. It's pretty much the same overhead as the same number of prepared statements.