Package taking too long to create a file

We have a particular package which creates a file. It is taking a long time to create the file, even though the data in the tables in not very high (around 50000 rows).

I've mentioned one of the four queries/cursor below. This is the style in which the coding is done :

CURSOR j_order_activity IS
SELECT A.activity_number,
A.activity_category,
A.activity_type,
A.engr_id_1,
A.queue_name,
A.charge_ind,
A.activity_status,
A.completion_abort_date,
A.required_by_date,
p.progress_date,
p.current_status,
p.progress_recr,
p.delay_code
FROM pstn_order_activity_o_1 A,
pstn_order_progress_o_1 p
WHERE A.job_number = wo_job_number
AND A.order_number = w_order_number
AND A.SUB_ZONE = w_sub_zone
AND A.data_date = w_data_date
AND A.activity_number = p.activity_number(+) AND A.job_number =
 p.job_number(+) AND A.order_number = p.order_number(+) AND
 A.SUB_ZONE = p.SUB_ZONE(+) AND A.data_date = p.data_date(+);

joacr j_order_activity%ROWTYPE;

Then there is some more code and then we have:

TYPE VC1 IS REF CURSOR;
v_cursor1 VC1;

v_sql_stmt1 VARCHAR2(10000);

v_sql_stmt1 := 'SELECT
A.activity_number,
A.activity_category,
A.activity_type,
A.engr_id_1,
A.queue_name,
A.charge_ind,
A.activity_status,
A.completion_abort_date,
A.required_by_date,
p.progress_date,
p.current_status,
p.progress_recr,
p.delay_code
FROM pstn_order_activity_o_'||v_table_name||' A,
 pstn_order_progress_o_'||v_table_name||' p WHERE A.job_number
 = :bv_order_number AND A.order_number = :bv_order_number AND
 A.SUB_ZONE = :bv_sub_zone AND A.data_date = :bv_data_date AND A.activity_number
 = p.activity_number(+) AND A.job_number = p.job_number(+) AND
 A.order_number = p.order_number(+) AND A.SUB_ZONE = p.SUB_ZONE(+) AND
 A.data_date = p.data_date(+)';

v_table_name is assigned some value earlier itself

OPEN v_cursor1 FOR v_sql_stmt1 USING w_order_number,w_order_number,v_sub_zone,v_run_date;
LOOP
FETCH v_cursor1 INTO joacr;
EXIT WHEN V_CURSOR1%NOTFOUND;

Some processing goes in here and based on certain conditions, another cursor is opened and closed after some processing.

-- wo_activity_number := joacr.activity_number;
-- wo_activity_category := joacr.activity_category;
-- wo_activity_type := joacr.activity_type;

END LOOP;

CLOSE v_cursor1;

Can the code be made more easy? All the tables are indexed properly and stats have been gathered. We're not able to explain why the performance is slow. As shown above, four cursors are being used in the package and they are all nested. Can there be a better substitution to the above code (like using other collection variables or BULK collects or GTTs)? Could they improve performance.

We're using Oracle version 9i (9.0.2) on HP UX Titanium.