I have this proc....it is taking wayyy too much time....I am trying to
cut it the amount of time.. any suggestions...

PROCEDURE chassis
IS
CURSOR his_CUR IS
select i.chassis
,i.chassis_id
,i.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,chm.lt_code
,i.in_date
,i.in_visit
,i.in_reference_id
,i.out_date
,i.out_visit
,i.out_reference_id
,i.in_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,i.remarks
,'F' AREA
from inv_chassis i
,chassis_masters chm
,ssl s
,accounts a
where i.chassis = chm.chassis
and i.ssl_user_code = s.code
and i.in_trucker_code = a.code(+)
UNION ALL
select h.chassis
,h.chassis_id
,h.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,h.lt_code
,h.in_date
,h.in_visit
,h.in_reference_id
,h.out_date
,h.out_visit
,h.out_reference_id
,h.out_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,h.remarks
,'F' AREA
from his_chassis h
,ssl s
,accounts a
where h.voided_date IS NULL
and h.ssl_user_code = s.code
and h.out_trucker_code = a.code(+)
UNION ALL
select i.chassis
,i.chassis_id
,i.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,chm.lt_code
,i.in_date
,i.in_visit
,i.in_reference_id
,i.out_date
,i.out_visit
,i.out_reference_id
,i.in_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,i.remarks
,'T' AREA
from inv_chassis@tripoli i
,chassis_masters@tripoli chm
,ssl@tripoli s
,accounts@tripoli a
where i.chassis = chm.chassis
and i.ssl_user_code = s.code
and i.in_trucker_code = a.code(+)
UNION ALL
select h.chassis
,h.chassis_id
,h.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,h.lt_code
,h.in_date
,h.in_visit
,h.in_reference_id
,h.out_date
,h.out_visit
,h.out_reference_id
,h.out_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,h.remarks
,'T' AREA
from his_chassis@tripoli h
,ssl@tripoli s
,accounts@tripoli a
where h.voided_date IS NULL
and h.ssl_user_code = s.code
and h.out_trucker_code = a.code(+);
-- ordering by chassis and in_date
-- ORDER BY 1, 6;

his1_REC his_CUR%ROWTYPE := NULL;
--his2_REC his_CUR%ROWTYPE := NULL;

TYPE his_tab_type IS TABLE OF his_cur%ROWTYPE;
hisTab his_tab_type;

BEGIN
EXECUTE IMMEDIATE 'truncate TABLE nvrm_chas_temp';

OPEN his_CUR;
LOOP
FETCH his_CUR BULK COLLECT INTO hisTab LIMIT 5000 ;
nc := 0;
ChassisTab.DELETE;
FOR K IN 1 .. hisTab.COUNT LOOP
out4hours_PROC(his1_REC, hisTab(K));
-- COMMIT;
-- move his rec up...
his1_REC := hisTab(K);
END LOOP;
FORALL K IN 1 .. ChassisTab.COUNT
INSERT INTO nvrm_chas_temp VALUES ChassisTab(K);
COMMIT;

EXIT WHEN his_CUR%NOTFOUND;
END LOOP;
CLOSE his_CUR;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
IF his_CUR%ISOPEN THEN
CLOSE his_CUR;
END IF;
ROLLBACK;
RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.chassis -
'||SQLERRM);
END chassis;

end nvrm_PKG;
/

The out4hours_PROC is the following;

PROCEDURE out4hours_PROC(his_rec_1 IN chas_his_rec_TYPE
,his_rec_2 IN chas_his_rec_TYPE)
IS
v_booking gate_containers.booking%TYPE := NULL;
v_action gate_containers.action%TYPE := NULL;
v_coop_date DATE := NULL;
v_user ssl.code%TYPE := NULL;
v_user_desc ssl.long_description%TYPE := NULL;

CURSOR fg_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
s.long_description
FROM gate_containers gc
,gate_visit gv
,ssl s
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit
AND gc.ssl_user_code = s.code(+);

CURSOR tg_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
s.long_description
FROM gate_containers@tripoli gc
,gate_visit@tripoli gv
,ssl@tripoli s
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit
AND gc.ssl_user_code = s.code(+);

CURSOR fa_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gv.coop_in_date
FROM gate_containers gc
,gate_visit gv
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit;

CURSOR ta_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gv.coop_in_date
FROM gate_containers@tripoli gc
,gate_visit@tripoli gv
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit;

BEGIN
IF his_rec_1.chassis = his_rec_2.chassis THEN
IF his_rec_1.out_date IS NULL OR his_rec_1.out_visit IS NULL OR
his_rec_1.out_reference_id IS NULL THEN
RAISE EXIT_EXCEPTION;
END IF;
IF his_rec_2.in_visit IS NULL OR his_rec_2.in_reference_id IS
NULL THEN
RAISE EXIT_EXCEPTION;
END IF;
IF his_rec_1.area = 'F' THEN
OPEN fg_cur(his_rec_1.out_visit,his_rec_1.out_reference _id);
FETCH fg_cur INTO v_action, v_booking, v_coop_date, v_user,
v_user_desc;
CLOSE fg_cur;
IF v_action IS NULL
THEN
OPEN tg_cur(his_rec_1.out_visit,
his_rec_1.out_reference_id);
FETCH tg_cur INTO v_action, v_booking, v_coop_date,
v_user, v_user_desc;
CLOSE tg_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;
END IF;

ELSE
OPEN tg_cur(his_rec_1.out_visit,his_rec_1.out_reference _id);
FETCH tg_cur INTO v_action, v_booking, v_coop_date, v_user,
v_user_desc;
CLOSE tg_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;

END IF;
IF v_coop_date IS NULL OR v_action <>
global_PKG.DELIVERED_NAKED_CHASSIS THEN
RAISE EXIT_EXCEPTION;
END IF;
-- Chassis has to have returned thru Coop Depot NAKED
OPEN fa_cur(his_rec_2.in_visit,his_rec_2.in_reference_i d);
FETCH fa_cur INTO v_action, v_coop_date;
CLOSE fa_cur;
IF v_action IS NULL
THEN
OPEN ta_cur(his_rec_2.in_visit,his_rec_2.in_reference_i d);
FETCH ta_cur INTO v_action, v_coop_date;
CLOSE ta_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;
END IF;

IF v_coop_date IS NULL OR v_action <>
global_PKG.RECEIVED_NAKED_CHASSIS THEN
RAISE EXIT_EXCEPTION;
END IF;
-- and, Chassis has to have out for more than 4 hours...
IF his_rec_2.in_date - his_rec_1.out_date > 1/6 THEN
write_to_chas_temp_PROC(his_rec_1
,his_rec_2.in_date
,his_rec_2.in_visit
,v_booking
,v_user
,v_user_desc);
END IF;
END IF;
EXCEPTION
WHEN EXIT_EXCEPTION THEN
NULL;
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.out4hours_PROC
- '||SQLERRM);
END;

On 14 May 2006 21:27:51 -0700, "lou_nyc" <lnlx@aol.com> wrote:

>I have this proc....it is taking wayyy too much time....I am trying to
>cut it the amount of time.. any suggestions..

Please don't repost (and crosspost) this until you get advice that
suits you. You know the design is a mess, get it fixed!

--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker wrote:
> On 14 May 2006 21:27:51 -0700, "lou_nyc" <lnlx@aol.com> wrote:
>
> >I have this proc....it is taking wayyy too much time....I am trying to
> >cut it the amount of time.. any suggestions..
>
> Please don't repost (and crosspost) this until you get advice that
> suits you. You know the design is a mess, get it fixed!
>
> --
> Sybrand Bakker, Senior Oracle DBA

who the heck are you????? Please do not answer any of
my post or crosspost! THANK YOU!!!

>who the heck are you????? Please do not answer any of
>my post or crosspost! THANK YOU!!!

It's easy to see who people are:
http://groups.google.com/groups/prof...QRgoLt03TvsZNA

You might consider listening to Sybrand, just like you would listen to
a gruff drill sergeant who's teaching you how not to get body parts
blown off - because you know he's seen people have painful learning
experiences.

jg
--
@home.com is bogus.
http://www.usps.com/postalinspectors...d/razmilov.htm

On 17 May 2006 15:37:15 -0700, "joel garry" <joel-garry@home.com>
wrote:

>>who the heck are you????? Please do not answer any of
>>my post or crosspost! THANK YOU!!!
>
>It's easy to see who people are:
>http://groups.google.com/groups/prof...QRgoLt03TvsZNA
>
>You might consider listening to Sybrand, just like you would listen to
>a gruff drill sergeant who's teaching you how not to get body parts
>blown off - because you know he's seen people have painful learning
>experiences.
>
>jg

Actually I had those painful learning experiences myself.

--
Sybrand Bakker, Senior Oracle DBA