Procedure is created on SQL server
inserting the data from sql server to oracle databse
When individually run the query it executed in 10 sec
When run through procedure it takes sometime 1.5min and 5min
CREATE procedure sp_Trade_Book as
Begin
SET NOCOUNT ON
BEGIN TRY --Begin of TRY BLOCK
DELETE FROM [XXXX]..[XXX].[TRADE_REPORT] WHERE SUBSTRING(TRANSACT_TIME,1,8)
IN (SELECT DISTINCT SUBSTRING(TRANSACT_TIME,1,8) FROM TRADE_REPORT)
INSERT INTO [XXXX]..[XXX].[TRADE_REPORT](Trade_Report_ID, Transact_Time, Trade_Status,
Exec_Type, Symbol, Buy_Clearing_Alpha, Buy_Broker_ID, Buy_Trader_ID, Buy_PAN_ID, Buy_Client_ID,
Old_Buy_Client_ID, Executed_Qty, Executed_Value, Last_Trade_Net_Change,
Sell_Clearing_Alpha, Sell_Broker_ID, Sell_Trader_ID, Sell_PAN_ID,
Sell_Client_ID, Old_Sell_Client_ID, Trade_Report_Ref_ID, Trade_Report_Parent_ID)
SELECT Trade_Report_ID, Transact_Time, Trade_Status, Exec_Type,
Symbol, Buy_Clearing_Alpha, Buy_Broker_ID, Buy_Trader_ID, Buy_PAN_ID, Buy_Client_ID,
Old_Buy_Client_ID, Executed_Qty, Executed_Value, Last_Trade_Net_Change,
Sell_Clearing_Alpha, Sell_Broker_ID, Sell_Trader_ID, Sell_PAN_ID,
Sell_Client_ID, Old_Sell_Client_ID, Trade_Report_Ref_ID, Trade_Report_Parent_ID FROM TRADE_REPORT
SELECT 1 AS STATUS
END TRY --END of TRY BLOCK
BEGIN CATCH --Begin of CATCH BLOCK
SELECT 0 AS STATUS
END CATCH --End of CATCH BLOCK
END