0

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.

Improve this question
2
  • 1
    You can query the user_errors view to see what the actual compilation errors are. Some clients also have a show errors command which just does that for you. Commented May 24, 2018 at 14:47
  • Please include the tool you are using, the definition for table testing2 and the full text of the warning. When I tested it with a 2-column testing2 table, the first version compiled but PL/SQL Developer warned me Hint: Parameter 'upc_id3' is declared but never used in 'check_upc'. (This is a feature of PL/SQL Developer though, and not a PL/SQL compiler message. Are you using PL/SQL Developer?) Changing it to the invalid 3-column insert failed compilation with ORA-00913: too many values. Commented May 24, 2018 at 15:06

1 Answer 1

Reset to default
3

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."

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks. your suggestion of listing the column names in insert helped. Can you please help me with below SQL code. It again gives me same warning when put in procedure, whereas it runs fine when run seperately. insert into test123(store_id,promoweek_id,holiday) select p.store_id,p.promoweek_id,q.holiday from i_eyc_ab_sf_h_stg p left join i_ab_sf_promo_lkp_dim q on p.promoweek_id=q.promoweek_id left join abs_store_cluster_lkp_dim c on p.store_id=c.store_id where lpad(upc_id,13,0)=lpad(in_upc_id0,13,0) and p.promoweek_id >= 201544 and p.promoweek_id<=201548;
Added my code in the question description. Please have a look and help.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.