Oracle Procedure compiled | Warning: execution completed with warning

Question

I have created below-mentioned procedure in Oracle. It compiles but gives a warning sayin "execution completed with warning"

create or replace
PROCEDURE check_upc
  (
  upc_id1  IN VARCHAR,
  upc_id2  IN VARCHAR,
  upc_id3  IN VARCHAR 
  )
IS  
BEGIN
  insert into testing2 SELECT upc_id1,upc_id2 from dual;
END;

But when i change the SQL statement to

insert into testing2 SELECT upc_id1,upc_id2,upc_id3 from dual;

It compiles without any warning.

Basically, I am supposed to run a long code (~100 line) for 10 combinations of UPCs (parameter in this procedure), for which I'll be replacing the above-mentioned SQL code with my actual code. But, it is failing for this basic insert statement.

Thanks in advance.

Answer 1

Always list the columns you are using for an update:

create or replace procedure check_upc (
  in_upc_id1  IN VARCHAR,
  in_upc_id2  IN VARCHAR,
  in_upc_id3  IN VARCHAR 
) IS  
BEGIN
  insert into testing2 (id1, id2)  -- or whatever the names are
      select in_upc_id1, in_upc_id2
      from dual;
END;

Notes:

• I traditionally name input parameters to distinguish them from local variables and column names.
• VARCHAR looks strange. Oracle recommends varchar2.
• When using insert, list the column names.

The warning is essentially saying: "This isn't going to work with the current table definition. But I'm accepting it because you might change the definition in the future and I really want to help you by not rejecting the stored procedure."