Procedure Invalid Identifier in SQL / PLSQL

Joined
Apr 22, 2022
Messages
2
Reaction score
0
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.

SQL:
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?
 
Joined
Jan 30, 2023
Messages
107
Reaction score
13
The error in your code is that you need to use a semi-colon (;) to end the CREATE OR REPLACE PROCEDURE statement:

SQL:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,792
Messages
2,569,639
Members
45,353
Latest member
RogerDoger

Latest Threads

Top