>    Select first(1) u.UNIT_ID from m_unit u
> where (upper(trim(u.UNIT_NAME))= upper(trim(:MAIN_UNIT))
> or upper(trim(u.UNIT_SYMBOL))=upper(trim(:MAIN_UNIT)))
> and coalesce(CONVERSION_UNIT_ID,0)=0 Into :Main_Unit_Id;

Does this select use any index at all? How many records are there in M_UNIT (and similarly for other tables referred in a similar manner in this stored procedure or its sub-procedures)? Generally, using functions on fields normally means they don't use indexes (computed indexes being the exception). Unless such tables are small, calling this procedure 100000 times could well be the reason for your program taking over 10 hours.

I'll try to look a bit closer at this procedure later today, though I'm not certain I will get the opportunity.

Set