Insert Procedure showing compilation error (Oracle)

I am using Oracle 12c and a certain requirement needs me to prepare an insert procedure. I am not very familiar with procedures so would you please tell me what’s wrong?

Table statement:

create table sa_d_pmnt(
  a_id number,
  d_n_id number,
  b_id number,
  b_b_id number,
  p_a number,
  r_n varchar2(255),
  dt date
  );

Procedure:

create or replace procedure setdepositpayment (
  a_id sa_d_pmnt.a_id%TYPE,
  d_n_id sa_d_pmnt.d_n_id%TYPE,
  b_id sa_d_pmnt.b_id%TYPE,
  b_b_id sa_d_pmnt.b_b_id%TYPE,
  p_a sa_d_pmnt.p_a%TYPE,
  r_n sa_d_pmnt.r_n%TYPE,
  dt sa_d_pmnt.dt%TYPE)
is
begin
  insert into sa_d_pmnt (
    a_id,
    d_n_id,
    b_id,
    b_b_id,
    p_a,
    r_n,
    dt
  )
  values
    (
      a_id,
      d_n_id,
      b_id,
      b_b_id,
      p_a,
      r_n,
      dt
    );
  commit;
end;
/

I am getting ora-00900 invalid sql statement and ora-24344 success with compilation error

Answer

I’d suggest you to set parameters’ names differently than column names; that causes problems. For example, use par_ prefix.

Though, even if you don’t do that in this case (as you’re only inserting values), nothing would happen.

Procedure looks OK and executes correctly with or without prefix.

SQL> create or replace procedure setdepositpayment (
  2    par_a_id sa_d_pmnt.a_id%TYPE,
  3    par_d_n_id sa_d_pmnt.d_n_id%TYPE,
  4    par_b_id sa_d_pmnt.b_id%TYPE,
  5    par_b_b_id sa_d_pmnt.b_b_id%TYPE,
  6    par_p_a sa_d_pmnt.p_a%TYPE,
  7    par_r_n sa_d_pmnt.r_n%TYPE,
  8    par_dt sa_d_pmnt.dt%TYPE)
  9  is
 10  begin
 11    insert into sa_d_pmnt (
 12      a_id,
 13      d_n_id,
 14      b_id,
 15      b_b_id,
 16      p_a,
 17      r_n,
 18      dt
 19    )
 20    values
 21      (
 22        par_a_id,
 23        par_d_n_id,
 24        par_b_id,
 25        par_b_b_id,
 26        par_p_a,
 27        par_r_n,
 28        par_dt
 29      );
 30    commit;
 31  end;
 32  /

Procedure created.

Testing:

SQL> exec setdepositpayment(1, 1, 1, 1, 1, 'A', sysdate);

PL/SQL procedure successfully completed.

SQL> select * from sa_d_pmnt;

      A_ID     D_N_ID       B_ID     B_B_ID        P_A R_N   DT
---------- ---------- ---------- ---------- ---------- ----- -------------------
         1          1          1          1          1 A     24.01.2022 11:22:01

SQL>

---

As of compilation errors: if you’re using SQL*Plus, type show err and Oracle will let you know what’s wrong. For example, I removed comma:

<snip>
 23        d_n_id,
 24        b_id,
 25        b_b_id,
 26        p_a,
 27        r_n        --> removed comma
 28        dt
 29      );
 30    commit;
 31  end;
 32  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE SETDEPOSITPAYMENT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/3     PL/SQL: SQL Statement ignored
28/7     PL/SQL: ORA-00917: missing comma
SQL>

Or, query USER_ERRORS:

SQL> select line, position, text
  2  from user_errors
  3  where name = 'SETDEPOSITPAYMENT';

      LINE   POSITION TEXT
---------- ---------- --------------------------------------------------
        28          7 PL/SQL: ORA-00917: missing comma
        11          3 PL/SQL: SQL Statement ignored

SQL>