I have a PL/SQL stored procedure inside which I want to run several updates, then capture any exceptions and return them in an out parameter. For simplicitys sake in the code block below I am simply outputting the exception code and error message to the console. However, the code block below does not work (I am experiencing a "found / expecting" syntax error)
CREATE OR REPLACE PROCEDURE DBP.TESTING_SP AS
DECLARE
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
UPDATE PS_NE_PHONE_TBL SET NE_PHONE_TYPE = 'TEST' WHERE NEMPLID_TBL = 'N14924';
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 64);
DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
END TESTING_SP;
/
What is the correct syntax for what I am trying to do?
I read on one forum
"When using the Create or Replace syntax, you can not use Declare. Declare is only for anonymous blocks that are not named. So either remove line 1 and create an anonymous block, or remove line 2 and create a named procedure."
but I'm not sure how to create an anonymous block or create a named procedure to accomplish what I want to do (if that, indeed, is the 'solution'). Could somebody lend a hand?
Just remove the DECLARE statement.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With