How to catch and return (or print) an exception in an ORACLE PL/SQL create or replace stored procedure

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?

Answers


Just remove the DECLARE statement.


Need Your Help

store changes from html to cookie

c# jquery asp.net cookies shopping-cart

i have this code for cart items . data retrieve from website cookie,'MyKookie'

Show window in OS X >= 10.10 (plugin template)?

osx window xib nswindow nswindowcontroller

There are a lot of similar questions but they appear to be too old - nothing happens, beginSheet:... methods undeclared and etc. I need something like the following (but for window in OS X):