Home » SQL & PL/SQL » SQL & PL/SQL » help! second half of procedure not executing
| help! second half of procedure not executing [message #38005] |
Wed, 13 March 2002 06:33  |
Trevor Pearson
Messages: 5
Registered: October 2001
|
Junior Member |
|
|
Could someone help me with this?
I'm executing the following procedure, but the second half is never executed. I suspect it is related to the placement of the end; or end if; statements.
here's the proc:
--created by Trevor Pearson on 3/07/02
--last modified by Trevor Pearson on 3/11/02
CREATE OR REPLACE PROCEDURE "TW31"."GET_ORDER_IDS2" as
cursor cur_goi is
select unique(order_id) from twcust_order_detail where dell_ship_status_code in ('D1','D2');
v1 varchar2(10); --order_id
v2 number(10) :=0; --count of tracks with D1 or D2 for order
v3 number(10) :=0; --count of total tracks on order
v4 number(10); --custom_status_id of task to alter
v11 varchar2(10) :=0;
v_ErrorNumber number;
v_ErrorText varchar2(200);
begin open cur_goi; loop
fetch cur_goi into v1;
exit when cur_goi%notfound;
select count(*) into v2 from twcust_order_detail where order_id = v1 and dell_ship_status_code in ('D1','D2');
select count(*) into v3 from twcust_order_detail where order_id = v1;
if v2-v3=0 then
begin
DBMS_OUTPUT.PUT_LINE('order_id being worked is '||v1);
select custom_status_id into v4
from lsw_task
where task_id = (
select created_message_id
from twcust_order_summary
where order_id = v1);
DBMS_OUTPUT.PUT_LINE('custom status is '||v4);
if v4 = 1011 then
DBMS_OUTPUT.PUT_LINE('entering Re-Attempt if statement');
update twcust_order_summary
set POD_FLAG = 'R'
where A3_FLAG = 'Y'
and ORDER_ID = v1
and POD_FLAG is NULL;
update twcust_order_summary
set POD_FLAG = 'R'
where ORDER_ID in (
select ORDER_ID
from twcust_order_detail
where ORDER_ID = v1
and DEST_ZIP_CODE is NULL)
and POD_FLAG is NULL;
update twcust_order_summary
set POD_FLAG = 'R'
where ORDER_ID in (
select ORDER_ID
from twcust_order_detail
where ORDER_ID = v1
and DEST_ZIP_CODE in ('78756','78758','43122'))
and POD_FLAG is NULL;
commit;
end if;
update twcust_order_summary
set POD_FLAG = 'D'
where A3_FLAG = 'Y'
and ORDER_ID = v1
and POD_FLAG is NULL;
update twcust_order_summary
set POD_FLAG = 'D'
where ORDER_ID in (
select ORDER_ID
from twcust_order_detail
where ORDER_ID = v1
and DEST_ZIP_CODE is NULL)
and POD_FLAG is NULL;
update twcust_order_summary
set POD_FLAG = 'D'
where ORDER_ID in (
select ORDER_ID
from twcust_order_detail
where ORDER_ID = v1
and DEST_ZIP_CODE in ('78756','78758','43122'))
and POD_FLAG is NULL;
commit;
update twcust_order_summary
set POD_FLAG = 'C'
where ORDER_ID = v1
and (POD_FLAG not in ('D','R')
or POD_FLAG is NULL);
commit;
exception
when others then
rollback;
v_ErrorNumber := SQLCODE;
v_ErrorText := substr(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('Oracle Error Num:'||v_ErrorNumber||', Error: '||v_ErrorText);
end;
end if;
end loop;
close cur_goi;
DBMS_OUTPUT.PUT_LINE('entering update section');
update lsw_task
set close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1007
where task_id in (
select a.created_message_id
from twcust_order_summary a,
lsw_task b
where a.POD_FLAG = 'D'
and a.created_message_id = b.task_id
and b.status != 32);
update lsw_task
set close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1011
where task_id in (
select a.created_message_id
from twcust_order_summary a,
lsw_task b
where a.POD_FLAG = 'R'
and a.created_message_id = b.task_id
and b.status != 32);
update lsw_task
set close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1008
where task_id in (
select a.created_message_id
from twcust_order_summary a,
lsw_task b
where a.POD_FLAG = 'C'
and a.created_message_id = b.task_id
and b.status != 32);
commit;
exception
when others then
rollback;
end;
|
|
|
|
| Re: help! second half of procedure not executing [message #38014 is a reply to message #38005] |
Wed, 13 March 2002 11:31  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this:
CREATE OR REPLACE PROCEDURE get_v2(v1 IN varchar2, v2 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO v2
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
CREATE OR REPLACE PROCEDURE get_v3(v1 IN varchar2, V3 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO V3
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
DECLARE
v1 varchar2(10); --order_id
v2 number(10) :=0; --count of tracks with D1 or D2 for order
v3 number(10) :=0; --count of total tracks on order
v4 number(10); --custom_status_id of task to alter
v11 varchar2(10) :=0;
v_ErrorNumber number;
v_ErrorText varchar2(200);
CURSOR cur_goi IS
SELECT UNIQUE(order_id)
FROM twcust_order_detail
WHERE order_id = v1
AND dell_ship_status_code IN ('D1', 'D2');
BEGIN
OPEN cur_goi;
LOOP
FETCH cur_goi into v1;
EXIT WHEN cur_goi%NOTFOUND;
get_v2(V1, V2);
get_v3(V1, V3);
IF (V2-V3=0)
THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('order_id being worked is '||v1);
SELECT custom_status_id
INTO V4
FROM lsw_task
WHERE task_id = (SELECT created_message_id
FROM twcust_order_summary
WHERE order_id = V1);
DBMS_OUTPUT.PUT_LINE('custom status is '||v4);
-- What happen if v4 is not 1011?
IF (v4 = 1011)
THEN
DBMS_OUTPUT.PUT_LINE('entering Re-Attempt if statement');
UPDATE twcust_order_summary
SET pod_flag = 'R'
WHERE ((A3_flag = 'Y'
AND order_id = V1)
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL) OR
dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
-- Depending on you business process whenever it is possible you should
-- include a commit statement out side of your
-- loop, because it will reduce your system overhead (run faster)
COMMIT;
END IF;
-- If the following statements will happen if v4 is not 1011 you should do
-- an 'if then else' statement. Therefore after your commit statement above
-- put the ELSE then following statements. However, I am not clear with logic on the two
-- update statements (updating the pod_flag to 'C' or 'D'). I mean what are the conditions
-- relationship its has with the above statement (v4 is equal to 1011)? What is the business process here?
-- Currently, by first glance your update pod_flag to 'C' statement
-- will updated (overwrite) everything you just did from update pod_flag to 'R'. Is this what you want to do?
UPDATE twcust_order_summary
SET pod_flag = 'D'
WHERE a3_flag = 'Y'
AND (order_id = V1
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL)
OR dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
COMMIT;
UPDATE
UPDATE twcust_order_summary
SET pod_flag = 'C'
WHERE order_id = V1
AND (pod_flag NOT IN ('D', 'C')
OR pod_flag IS NULL);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_ErrorNumber := SQLCODE;
v_ErrorText := substr(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('Oracle Error Num:'||v_ErrorNumber||',
Error: '||v_ErrorText);
END;
END IF; -- end of first if
END LOOP;
CLOSE cur_goi;
END;
/
EXEC DBMS_OUTPUT.PUT_LINE('entering update section');
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1007
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'D'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1011
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'R'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1008
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'C'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
COMMIT;
Hope this help a bit.
--Cindy
|
|
|
|
| Re: help! second half of procedure not executing [message #38018 is a reply to message #38014] |
Wed, 13 March 2002 13:15 |
Trevor Pearson
Messages: 5
Registered: October 2001
|
Junior Member |
|
|
Is there a performance gain to moving v2 and v3 out as other procedures?
other replies/comments below:
Try this:
CREATE OR REPLACE PROCEDURE get_v2(v1 IN varchar2, v2 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO v2
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
CREATE OR REPLACE PROCEDURE get_v3(v1 IN varchar2, V3 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO V3
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
DECLARE
v1 varchar2(10); --order_id
v2 number(10) :=0; --count of tracks with D1 or D2 for order
v3 number(10) :=0; --count of total tracks on order
v4 number(10); --custom_status_id of task to alter
v11 varchar2(10) :=0;
v_ErrorNumber number;
v_ErrorText varchar2(200);
CURSOR cur_goi IS
SELECT UNIQUE(order_id)
FROM twcust_order_detail
WHERE order_id = v1
AND dell_ship_status_code IN ('D1', 'D2');
BEGIN
OPEN cur_goi;
LOOP
FETCH cur_goi into v1;
EXIT WHEN cur_goi%NOTFOUND;
get_v2(V1, V2);
get_v3(V1, V3);
IF (V2-V3=0)
THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('order_id being worked is '||v1);
SELECT custom_status_id
INTO V4
FROM lsw_task
WHERE task_id = (SELECT created_message_id
FROM twcust_order_summary
WHERE order_id = V1);
DBMS_OUTPUT.PUT_LINE('custom status is '||v4);
-- What happen if v4 is not 1011?
--/ Then it will skip this section and execute none of these statements
IF (v4 = 1011)
THEN
DBMS_OUTPUT.PUT_LINE('entering Re-Attempt if statement');
UPDATE twcust_order_summary
SET pod_flag = 'R'
WHERE ((A3_flag = 'Y'
AND order_id = V1)
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL) OR
dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
-- Depending on you business process whenever it is possible you should
-- include a commit statement out side of your
-- loop, because it will reduce your system overhead (run faster)
--/ thanks. some of these updates only work right if the others have not inserted. If I move the commits, won't the changes not appear?
COMMIT;
END IF;
-- If the following statements will happen if v4 is not 1011 you should do
-- an 'if then else' statement. Therefore after your commit statement above
-- put the ELSE then following statements. However, I am not clear with logic on the two
-- update statements (updating the pod_flag to 'C' or 'D'). I mean what are the conditions
-- relationship its has with the above statement (v4 is equal to 1011)? What is the business process here?
-- Currently, by first glance your update pod_flag to 'C' statement
-- will updated (overwrite) everything you just did from update pod_flag to 'R'. Is this what you want to do?
--/ this was not my intent. Thanks for the heads up on the else clause. The purpose of the procedure is to decide how to update POD_FLAG (either C,D,orR) and then make some other descisions based on those flags.
UPDATE twcust_order_summary
SET pod_flag = 'D'
WHERE a3_flag = 'Y'
AND (order_id = V1
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL)
OR dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
COMMIT;
UPDATE
UPDATE twcust_order_summary
SET pod_flag = 'C'
WHERE order_id = V1
AND (pod_flag NOT IN ('D', 'C')
OR pod_flag IS NULL);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_ErrorNumber := SQLCODE;
v_ErrorText := substr(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('Oracle Error Num:'||v_ErrorNumber||',
Error: '||v_ErrorText);
END;
END IF; -- end of first if
END LOOP;
CLOSE cur_goi;
END;
/
EXEC DBMS_OUTPUT.PUT_LINE('entering update section');
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1007
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'D'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1011
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'R'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1008
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'C'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
COMMIT;
Hope this help a bit.
--Cindy
|
|
|
|
| Re: help! second half of procedure not executing [message #38028 is a reply to message #38014] |
Thu, 14 March 2002 05:54 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Is there a performance gain to moving v2 and v3 out as other procedures?
other replies/comments below:
-- Yes, because once you have complied it successfully, the procedures are available
-- for you to use at any time. Therefore, the system does not have to re-complied these codes every
-- signal time you run the main program that calls these procedures, until either your
-- system crash and lost everything or something. (Put these procedures on separate file). If you do it this way,
-- it will save some time and I believe it makes the program earier to read too.
-- How much time will you save? Depends on complexity of codes.
Try this:
CREATE OR REPLACE PROCEDURE get_v2(v1 IN varchar2, v2 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO v2
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
CREATE OR REPLACE PROCEDURE get_v3(v1 IN varchar2, V3 OUT NUMBER)
AS
BEGIN
SELECT count(*)
INTO V3
FROM twcust_order_detail
WHERE order_id = v1;
END;
/
-- Read comments below on appropriate section below about this new procedure (ref: ****).
CREATE OR REPLACE PROCEDURE get_v4(V1 IN varchar2, v3 OUT NUMBER, pod_flag OUT varchar2)
AS
BEGIN
SELECT custom_status_id, pod_flag
INTO V4,
pod_flag
FROM lsw_task
WHERE task_id = (SELECT created_message_id
FROM twcust_order_summary
WHERE order_id = V1);
END;
/
DECLARE
v1 varchar2(10); --order_id
v2 number(10) :=0; --count of tracks with D1 or D2 for order
v3 number(10) :=0; --count of total tracks on order
v4 number(10); --custom_status_id of task to alter
v11 varchar2(10) :=0;
v_ErrorNumber number;
v_ErrorText varchar2(200);
-- Add this variable if you use the get_v4 procedure.
pod_flag varchar2(1); -- I am assuming it is one character.
CURSOR cur_goi IS
SELECT UNIQUE(order_id)
FROM twcust_order_detail
WHERE order_id = v1
AND dell_ship_status_code IN ('D1', 'D2');
BEGIN
OPEN cur_goi;
LOOP
FETCH cur_goi into v1;
EXIT WHEN cur_goi%NOTFOUND;
get_v2(V1, V2);
get_v3(V1, V3);
IF (V2-V3=0)
THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('order_id being worked is '||v1);
-- (ref: ****)
-- You could also put the following select statement in another procedure
-- such as an example written above. Also, you may want to pull the current pod_flag code
-- or any other fields that help you differentiate between the cases when will you update
-- pod_flag to 'D' or 'C'. However, I need to know more information under what circumstances
-- you will update pod_flag to 'R','D', or 'C', besides v4 is equal 1011 or not.
-- Judging from the current codes, a circumstance for update pod_flag to 'R' is when v4 is
-- equal to 1011, and my assumption that your intent to update pod_flag to 'D' and 'C' is when
-- v4 is not equal to 1011, but what other circumstances distinguish 'D' and 'C'(If
-- any more)? Also judging from the current update codes, you update pod_flag only if you have
-- done with updating pod_flag to 'D' and any other cases (circumstances) in the current system
-- you will update the pod_flag to 'C', which meaning you will do update pod_flag to 'C' last.
-- Correct? Is this what you want to do?
-- Depending on the circumstances, you might need to do a nested if statement
-- ( An 'if then else' statement within a 'if then else' statement).
SELECT custom_status_id
INTO V4
FROM lsw_task
WHERE task_id = (SELECT created_message_id
FROM twcust_order_summary
WHERE order_id = V1);
DBMS_OUTPUT.PUT_LINE('custom status is '||v4);
-- What happen if v4 is not 1011?
--/ Then it will skip this section and execute none of these statements
IF (v4 = 1011)
THEN
DBMS_OUTPUT.PUT_LINE('entering Re-Attempt if statement');
UPDATE twcust_order_summary
SET pod_flag = 'R'
WHERE ((A3_flag = 'Y'
AND order_id = V1)
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL) OR
dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
-- Depending on you business process whenever it is possible you should
-- include a commit statement out side of your
-- loop, because it will reduce your system overhead (run faster)
--/ thanks. some of these updates only work right if the others have not inserted. If I move the commits, won't the changes not appear?
-- Oh, base on your explainations I understand your business process,
-- then what you had is fine (the commit inside the
-- loop), because of your business process.
COMMIT;
END IF;
-- If the following statements will happen if v4 is not 1011 you should do
-- an 'if then else' statement. Therefore after your commit statement above
-- put the ELSE then following statements. However, I am not clear with logic on the two
-- update statements (updating the pod_flag to 'C' or 'D'). I mean what are the conditions
-- relationship its has with the above statement (v4 is equal to 1011)? What is the business process here?
-- Currently, by first glance your update pod_flag to 'C' statement
-- will updated (overwrite) everything you just did from update pod_flag to 'R'. Is this what you want to do?
--/ this was not my intent. Thanks for the heads up on the else clause. The purpose of the procedure is to decide how to update POD_FLAG (either C,D,orR) and then make some other descisions based on those flags.
UPDATE twcust_order_summary
SET pod_flag = 'D'
WHERE a3_flag = 'Y'
AND (order_id = V1
OR order_id IN (SELECT order_id
FROM twcust_order_detail
WHERE order_id = V1
AND ((dest_zip_code IS NULL)
OR dest_zip_code IN ('78756','78758','43122'))))
AND pod_flag IS NULL;
COMMIT;
UPDATE
UPDATE twcust_order_summary
SET pod_flag = 'C'
WHERE order_id = V1
AND (pod_flag NOT IN ('D', 'C')
OR pod_flag IS NULL);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_ErrorNumber := SQLCODE;
v_ErrorText := substr(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('Oracle Error Num:'||v_ErrorNumber||',
Error: '||v_ErrorText);
END;
END IF; -- end of first if
END LOOP;
CLOSE cur_goi;
END;
/
EXEC DBMS_OUTPUT.PUT_LINE('entering update section');
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1007
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'D'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1011
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'R'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
UPDATE lsw_task
SET close_datetime = sysdate,
close_by = 1,
status = 32,
execution_status = 1,
custom_status_id = 1008
WHERE task_id in (
SELECT a.created_message_id
FROM twcust_order_summary a, lsw_task b
WHERE a.POD_FLAG = 'C'
AND a.created_message_id = b.task_id
AND b.status != 32)
/
COMMIT;
Hope this help a bit.
--Cindy
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 01:12:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
