| about 9i migration [message #72727] |
Mon, 06 October 2003 14:28  |
fengq
Messages: 38
Registered: October 2002
|
Member |
|
|
Hi all
I had a problem when I migrate a database from 8.1.7 to 9.2.0 on NT
the problem is that after a execute a package, it doesn't make any changes to a table. It run perfect at 8.1.7. I compared the two, they both are exactly the same...
I really appreciate your help
|
|
|
|
|
|
|
|
|
|
| Re: about 9i migration [message #72731 is a reply to message #72730] |
Tue, 07 October 2003 16:07  |
fengq
Messages: 38
Registered: October 2002
|
Member |
|
|
here is the package, it didn't update the table in 9i, but it worked in 8i.
--------------------------------------------
CREATE OR REPLACE PACKAGE BODY COMMIT.commit_changeorder_pkg AS
TYPE CHANGE_ORDER_REC IS RECORD (
CONTRACT_NUM CP_CHANGE_ORDER.CONTRACT_NUM%TYPE);
TYPE CHANGE_ORDER_TAB IS TABLE OF CHANGE_ORDER_REC
INDEX BY BINARY_INTEGER;
pkg_coTable CHANGE_ORDER_TAB;
pkg_clearTable CHANGE_ORDER_TAB;
PROCEDURE initializeChangeOrderArray AS
BEGIN
pkg_coTable := pkg_clearTable;
END initializeChangeOrderArray;
PROCEDURE buildChangeOrderArray(a_contract_num IN cp_contract.contract_num%TYPE) AS
keyexists EXCEPTION;
current_row number;
BEGIN
IF pkg_coTable.COUNT >=1 then
FOR I IN pkg_coTable.FIRST..pkg_coTable.LAST LOOP
IF pkg_coTable(i).contract_num = a_contract_num then
RAISE keyexists;
end if;
end loop;
end if;
current_row:=pkg_coTable.COUNT + 1;
pkg_coTable(current_row).contract_num := a_contract_num;
EXCEPTION
WHEN keyexists THEN
NULL;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20850,
'commit_changeorder_pkg.BuildChangeOrderArray -- Code: ' ||
to_char(sqlcode) || ': ' || sqlerrm);
END buildChangeOrderArray;
PROCEDURE processChangeOrderArray AS
l_originalAuthorizedCost cp_contract.original_authorized_cost%TYPE;
l_additionalAuthorizedCost cp_contract.current_authorized_cost%TYPE;
l_currentenddate cp_contract.current_completion_date%TYPE;
l_changetoenddate cp_contract.current_completion_date%TYPE;
l_count number;
BEGIN
IF pkg_coTable.COUNT >=1 then
FOR I IN pkg_coTable.FIRST..pkg_coTable.LAST LOOP
SELECT nvl(count(*),0) into l_count FROM CP_CHANGE_ORDER
WHERE contract_num = pkg_coTable(i).contract_num and
authorized_flag = 'T';
SELECT nvl(original_authorized_cost,0),nvl(current_completion_date,SYSDATE)
INTO l_originalAuthorizedCost,l_currentenddate
FROM cp_contract
WHERE contract_num = pkg_coTable(i).contract_num ;
SELECT nvl(sum(value),0) INTO l_additionalAuthorizedCost FROM CP_CHANGE_ORDER
WHERE contract_num = pkg_coTable(i).contract_num and
authorized_flag = 'T';
if l_count>0 then
SELECT nvl(completion_date,l_currentenddate) into l_changetoenddate
FROM CP_CHANGE_ORDER
WHERE change_order_id=(select max(change_order_id) from cp_change_order
where contract_num=pkg_coTable(i).contract_num and
authorized_flag = 'T');
else
l_changetoenddate:=l_currentenddate;
end if;
UPDATE cp_contract
SET current_authorized_cost = l_originalAuthorizedCost + l_additionalAuthorizedCost,
current_completion_date = l_changetoenddate
WHERE contract_num = pkg_coTable(i).contract_num;
END LOOP;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20851,
'commit_changeorder_pkg.ProcessChangeOrderArray -- Code: ' ||
to_char(sqlcode) || ': ' || sqlerrm);
END processChangeOrderArray;
END commit_changeorder_pkg;
|
|
|
|
| Re: about 9i migration [message #72734 is a reply to message #72731] |
Wed, 08 October 2003 07:54 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
try to recompile the package and execute it again.
seems the package is ok.
and
are the bases tables ( that the package is referencing)
in 8i and 9i databases have the same data?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
