0

Calling another procedure with insert query(same package). Procedure 2 is parametized with array as input for manipulation

Approaches Tried: 1-exec CreateShipmentLines(p_result);

2-CreateShipmentLines(p_result);

Compiled sucessfully but record did not get inserted into table.

CREATE or REPLACE package WSH_Delivery_Detail_Shipment is

type Result IS VARRAY(8) OF INTEGER;
p_result Result:=Result();

PROCEDURE CreateShipment(p_delivery_detail_interface_id IN 
WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT 
String);
PROCEDURE CreateShipmentLines(p_result IN RESULT);

END WSH_Delivery_Detail_Shipment;
/


CREATE OR REPLACE package body WSH_Delivery_Detail_Shipment as

PROCEDURE CreateShipment(p_delivery_detail_interface_id IN 
WSH_DEL_DETAILS_INTERFACE.DELIVERY_DETAIL_INTERFACE_ID%TYPE,p_status OUT 
String)

IS

CURSOR wddi_cur IS SELECT * FROM WSH_DEL_DETAILS_INTERFACE WHERE 
DELIVERY_DETAIL_INTERFACE_ID=p_delivery_detail_interface_id;
wddi_record WSH_DEL_DETAILS_INTERFACE%ROWTYPE;

p_result Result:=Result(1,1,1,1,1,1,1,1);

BEGIN

OPEN wddi_cur;

LOOP

FETCH wddi_cur into wddi_record;/* Validation2 : Check if Organization Id 
exists and store Organization_Id corresponding to Organization_Code*/
EXIT when wddi_cur%NOTFOUND;

>>>>>CODE>>>>>

CreateShipmentLines(p_result); // Calling Procedure 2 for insertion



END LOOP;
CLOSE wddi_cur;
END CreateShipment;

procedure CreateShipmentLines(p_result IN RESULT)

is

BEGIN

INSERT INTO WSH_DELIVERY .......
COMMIT;

END CreateShipmentLines;
END WSH_Delivery_Detail_Shipment;
/
rahul bhandari
  • 1
  • 5
  • Did you get any error? If so, which one? Is there, by any chance, exception handler section that uses WHEN OTHERS? If so, how does it look like? – Littlefoot Jan 03 '19 at 17:36
  • @Littlefoot No I didn't get any errors..I tried with exception handler but it didn't work..I just wanted to know the exact location of where we can place exec procedure2 command within procedure1 – rahul bhandari Jan 03 '19 at 20:12
  • First step would be to put some debugging in. Either DBMS_OUTPUT or insert into debugging table, find out if it is even getting into CreateShipmentLines. If not then work backwards and find out where it is getting up to in CreateShipment. Possibility 1 -- there is an issue with your cursor and you are never entering the loop, Possibility 2 -- there is a error that you are not seeing, Possibility 3 -- there is some logic in the >>>>>CODE>>>>> section that is causing it to exit the loop and never call the CreateShipmentLines. All just guesses at the moment till we get more details. – Shaun Peterson Jan 03 '19 at 21:12

0 Answers0