Can't create programm properly. Encountered the symbol "end-of-file" in Oracle

Question

<<VARCHAR2_TO_NUMBER>>
DECLARE
    NUM NUMBER(10);
    FUNCTION VARCHAR2_TO_NUMBER(SYM IN VARCHAR2) RETURN NUMBER IS
    BEGIN
        RETURN CAST(SYM AS NUMBER);
        EXCEPTION
        WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: argument is not a number: ' || str);
            RETURN NULL;
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20103, 'Unexpected error');
        RETURN NULL;
   END;
--END VARCHAR2_TO_NUMBER;

I need to write a programm but my error is:

ORA-06550: line 16, column 0: PLS-00103: Encountered the symbol "end-of-file" .

What should I do to fix it?

Answer 1

If you use PL\SQL block you need to finish it:

<<VARCHAR2_TO_NUMBER>>
DECLARE
    NUM NUMBER(10);
    FUNCTION VARCHAR2_TO_NUMBER(SYM IN VARCHAR2) RETURN NUMBER IS
    BEGIN
        RETURN CAST(SYM AS NUMBER);
        EXCEPTION
        WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.PUT_LINE('ERROR: argument is not a number: ' || sym);
            RETURN NULL;
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20103, 'Unexpected error');
        RETURN NULL;
   END;
BEGIN
   NUM := VARCHAR2_TO_NUMBER('a');
END;
/

db<>fiddle demo

Related:

“Safe” TO_NUMBER()/How can you tell if a value is not numeric in Oracle?

Answer 2

You have error with the order of the code(and of course DECLARE is not needed through this way). Prefer converting to :

SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE FUNCTION VARCHAR2_TO_NUMBER(SYM IN VARCHAR2) RETURN NUMBER IS
    NUM NUMBER;        
BEGIN
       NUM := CAST(SYM AS NUMBER);     
       RETURN NUM;
    EXCEPTION
    WHEN VALUE_ERROR THEN
         DBMS_OUTPUT.PUT_LINE('ERROR: argument is not a number: ' || SYM);
         RETURN NULL;
    WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20103, 'Unexpected error');
         RETURN NULL;
END;
/

and call by

SQL> EXEC VARCHAR2_TO_NUMBER('someCharval');
/