Hello All,
I have a PL/SQL code which will run once a week, and every time this job
will stuck doing nothing .. and end up waiting on " LIBRARY CACHE LOCK "
.Most of the time this job results in a deadlock . As I know I am not a SQL
tuning expert ,once again I am seeking your suggestions and help in
resolving the issue !!
Another interesting thing is , after restarting the job ( after killing for
the first time ) it will go through fine. I am suspecting the way it is
coded. Any inputs ???
Thanks
Madhu
****************************************************************************
***************************
****************************************************************************
***************************
****************************************************************************
***************************
SET SERVEROUTPUT ON
SET LINESIZE 255
SET TAB OFF
VARIABLE g_return_code NUMBER;
DECLARE
CURSOR c_incoming_rows IS
SELECT product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag
FROM mdo_pre_temp_retek_price;
v_existing_count NUMBER;
e_invalid_row_count EXCEPTION;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
:g_return_code := 1;
FOR v_row IN c_incoming_rows LOOP
BEGIN
-- test for existence of existing records
SELECT COUNT(*)
INTO v_existing_count
FROM mdo_bse_temp_retek_price
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
-- if record does not already exist then insert (unless it's a
delete)
IF (v_existing_count = 0 AND v_row.flag != 'D') THEN
:g_return_code := 2;
INSERT INTO mdo_bse_temp_retek_price (
product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag )
VALUES (
LTRIM(v_row.product_id,'0')
, LTRIM(v_row.store_id,'0')
, TO_NUMBER(v_row.clearance_price) / 100.0
, TO_DATE(v_row.effective_date,'YYYYMMDD')
, TO_DATE(v_row.out_of_stock_date,'YYYYMMDD')
, TO_DATE(v_row.reset_date,'YYYYMMDD')
, v_row.flag
);
-- if record already exists then update or delete as needed
ELSIF (v_existing_count = 1) THEN
:g_return_code := 3;
-- check for delete command
IF (v_row.flag = 'D') THEN
DELETE
FROM mdo_bse_temp_retek_price
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
ELSE
UPDATE mdo_bse_temp_retek_price
SET clearance_price = TO_NUMBER(v_row.clearance_price) /
100.0
, effective_date =
TO_DATE(v_row.effective_date,'YYYYMMDD')
, out_of_stock_date =
TO_DATE(v_row.out_of_stock_date,'YYYYMMDD')
, reset_date =
TO_DATE(v_row.reset_date,'YYYYMMDD')
, flag = v_row.flag
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
END IF;
-- if we have neither 0 nor 1 records, something is terribly wrong
ELSE
:g_return_code := 4;
RAISE e_invalid_row_count;
END IF;
EXCEPTION
WHEN OTHERS THEN
:g_return_code := 5;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Record ignored for store ' ||
v_row.store_id || ' sku ' ||
v_row.product_id || '.');
END;
END LOOP;
:g_return_code := 0;
END;
/
EXIT :g_return_code
****************************************************************************
***************************
****************************************************************************
***************************
****************************************************************************
***************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
