posted 21 years ago
Thank you.I get probem for the following stored procedure during the execution:
CREATE OR REPLACE procedure procCopsCircuitOraclestatement
(
Form_name varchar,
Object_ID varchar,
Action_Name varchar,
Circuit_Number varchar,
Location_A varchar,
Location_B varchar,
Customer_Name varchar,
Ts_Code varchar,
TC_Name varchar,
SC_Name varchar,
Customer_ID varchar,
COPS_Customer_Code varchar)
is
BEGIN -- executable part starts here
--Inventory_Item_Type CONSTANT CHAR(7) := 'CIRCUIT';
begin
CASE Action_Name
WHEN 'add' THEN
SELECT CUSTOMERID, TC from Service where CUSTOMERID = Customer_ID and TC = TC_Name;
--IF !
INSERT INTO Service (CustomerID, TC) VALUES (Customer_ID, TC_Name);
--END IF;
INSERT INTO InventoryItem
(InventoryEntryID, InventoryItemID , InventoryItemType, Action, LOCNA,LOCNZ,SC, CustomerID,COPSTMCustomerCode)
VALUES
(Object_ID, Circuit_Number, Inventory_Item_Type, Action_Name, Location_A ,Location_B ,SC_Name, Customer_ID,COPS_Customer_Code);
INSERT INTO ServiceItem
(CustomerID, TC, LineItem, LineItemType, LineItemData)
VALUES
(Customer_ID, TC_Name, Object_ID, Inventory_Item_Type, Circuit_Number);
INSERT INTO ServiceInventory
(CustomerID, TC, InventoryItemID, InventoryEntryID)
VALUES
(Customer_Num, TC_Name, Circuit_Number, Object_ID);
WHEN 'delete' THEN
Delete InventoryItem where InventoryEntryID = Object_ID and InventoryItemID = Circuit_Number;
Delete ServiceInventory where InventoryEntryID = Object_ID and InventoryItemID = Circuit_Number;
Delete ServiceItem where LineItem = Object_ID and LineItemData = Circuit_Number;
WHEN 'change' THEN
SELECT CUSTOMERID, TC from Service where CUSTOMERID = Customer_ID and TC = TC_Name;
--IF !
update Service set customerID = customer_ID;
--END IF;
update InventoryItem set InventoryEntryID = Object_ID, InventoryItemID = Circuit_Number , Action = Action_Name , LoCNA = Location_A ,
LOCNZ = Location_Z , CustomerID = Customer_ID , COPSTMCustomerCode = COPS_Customer_Code
where InventoryEntryID = Object_ID and InventoryItemID = Circuit_Number;
update ServiceItem set LineItem = Object_ID, LineItemType = Inventory_Item_Type, LineItemData = Circuit_Number
where LineItem = Object_ID and LineItemData = Circuit_Number;
update ServiceInventory set InventoryEntryID = Object_ID, InventoryItemID = Circuit_Number
where InventoryEntryID = Object_ID and InventoryItemID = Circuit_Number;
COMMIT;
END CASE Action_Name;
end;
/*
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
*/
END procCopsCircuitOraclestatement;
/
I don't know what is the problem.