|
|
|
|
| Re: Automatic recompile of procedure body failed [message #613324 is a reply to message #613321] |
Mon, 05 May 2014 01:19   |
 |
pramod.sramesh
Messages: 23
Registered: November 2011
Location: Bangalore
|
Junior Member |
|
|
Hi Littlefoot,
Thanks for your time. I dont think it is related to a particular procedure in a package because i am calling around 5-6 procedures from the same package, i noticed that all the procedures are giving the same error as i mentioned above. I am posting one procedure from the package. Please check.
CREATE OR REPLACE
PACKAGE process_barcode_variant AUTHID CURRENT_USER
IS
-- Global variable - Limit for bulk fetch
g_bulk_limit NUMBER := 500;
g_user_id VARCHAR2(20) := NVL(dw_jobs.g_userid, USER);
PROCEDURE remove_barcode(
p_brand IN VARCHAR2,
p_part_number IN VARCHAR2,
p_barcode IN VARCHAR2);
END process_barcode_variant;
--Spec Ends
/
CREATE OR REPLACE
PACKAGE BODY process_barcode_variant
AS
PROCEDURE remove_barcode(
p_brand IN VARCHAR2,
p_part_number IN VARCHAR2,
p_barcode IN VARCHAR2)
AS
BEGIN
DELETE
FROM barcode_variant
WHERE brand = p_brand
AND part_number = p_part_number
AND barcode = p_barcode;
DELETE
FROM barcode
WHERE brand = p_brand
AND part_number = p_part_number
AND barcode = p_barcode;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20050, 'REMOVE_BARCODE =>' || sqlerrm || dbms_utility.format_error_backtrace);
END remove_barcode;
END process_barcode_variant;
--Body Ends
/
[Updated on: Mon, 05 May 2014 01:20] Report message to a moderator |
|
|
|
|
|
| Re: Automatic recompile of procedure body failed [message #613328 is a reply to message #613326] |
Mon, 05 May 2014 01:47 |
|
pramod.sramesh
Messages: 23
Registered: November 2011
Location: Bangalore
|
Junior Member |
|
|
Hi Littlefoot, Here i am posting complete code from a button.
DECLARE
i NUMBER DEFAULT 0;
l_cnt NUMBER := 0;
al_con ALERT;
BEGIN
IF :SYSTEM.mode <> 'ENTER-QUERY' THEN
IF :CONTROL.brand IS NULL THEN
disp_msg('Brand has to be entered');
go_item('CONTROL.brand');
ELSE
IF :BARCODE_VARIANT_V.article IS NOT NULL THEN
i := barcode.g_select.first;
IF i IS NULL THEN
disp_msg('You have not selected any Barcode');
ELSE
go_block('BARCODE_VARIANT_V');
LOOP
go_record(i);
SELECT COUNT(1)
INTO l_cnt
FROM barcode_variant_v
WHERE brand = :CONTROL.brand
AND part_number = :BARCODE_VARIANT_V.part_number;
IF l_cnt > 1 THEN
al_con := find_alert('CONFIRMATION');
set_alert_property(al_con,alert_message_text,'Do you really want to delete the barcode ?');
IF show_alert(al_con) = alert_button1 THEN
process_barcode_variant.remove_barcode(p_brand => :BARCODE_VARIANT_V.brand, p_part_number => :BARCODE_VARIANT_V.part_number, p_barcode => :BARCODE_VARIANT_V.barcode);
END IF;
END IF;
i := barcode.g_select.next(i);
EXIT
WHEN i IS NULL;
END LOOP;
END IF;
END IF;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
[Updated on: Mon, 05 May 2014 01:51] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Automatic recompile of procedure body failed [message #613353 is a reply to message #613343] |
Mon, 05 May 2014 06:48 |
|
pramod.sramesh
Messages: 23
Registered: November 2011
Location: Bangalore
|
Junior Member |
|
|
Hi Littlefoot,
Let me correct my comments above.
package is in another schema and we have a privilege to execute it. I noticed that our DB is migrated from 10g to 11g. I just moved the declaration of the variable to package body and the problem got resolved. I did not understand the reason behind that.
Thanks,
Pramod
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
