Procedure Invalid Identifier in SQL / PLSQL

[arjunkumar]

Hello All, I'm trying to make a very basic procedure on PLSQL but when I try to use it in SQL it returns an invalid identifier.

create or replace PROCEDURE YEARS_BETWEEN(date1 IN date, date2 IN date, p_result out number)
IS
    v_months number;
BEGIN
    v_months := months_between(date1, date2);

    p_result := TRUNC(v_months / 12, 0);
END years_between;

what's wrong in this code? Also, According to this post, PL/SQL handles errors and exceptions effectively with the help of the inbuilt exception handlers. Can anyone tell me the error handling feature is present in SQL as well?

 

[Kuncode]

The error in your code is that you need to use a semi-colon ( to end the CREATE OR REPLACE PROCEDURE statement:

CREATE OR REPLACE PROCEDURE YEARS_BETWEEN(date1 IN date, date2 IN date, p_result out number) IS
    v_months number;
BEGIN
    v_months := months_between(date1, date2);
    p_result := TRUNC(v_months / 12, 0);
END YEARS_BETWEEN;

As for error handling in SQL, it doesn't have a built-in error handling mechanism like PL/SQL. In SQL, you can catch errors using error code checking (e.g. using the SQLERRM function), but it is not as robust as the error handling in PL/SQL.