Add/Update stored procedure in Oracle, inserting a null or empty field

Question

I am new to using Oracle, and I am trying to create an add/insert stored procedure for a table. The PROD_CD and PLAN_CD fields in my table can have no value (empty or null) Can you please check my code and let me know what I am doing wrong?

Table definition:

CREATE TABLE DCWEB.USER_PLAN_PREFERENCE
(
  USERID        VARCHAR2(40) NOT NULL,
  PROD_CD       VARCHAR2(9)  NULL,
  PLAN_CD       VARCHAR2(9)  NULL,
  STATE_LST     VARCHAR2(2)  NOT NULL,
  STATE_NM      VARCHAR2(40) NOT NULL,
  LST_UPDATE_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

ALTER TABLE DCWEB.USER_PLAN_PREFERENCE
  ADD CONSTRAINT USER_PLAN_PREFERENCE_XPK PRIMARY KEY (USERID, PROD_CD, PLAN_CD);

-- Grant/Revoke object privileges 
grant select, insert, update, delete on DCWEB.USER_PLAN_PREFERENCE to HIGGIB1;

Stored Procedure Definition:

  procedure setUserPlanPref (
    userid in varchar2,
    prod_cd in varchar2,
    plan_cd in varchar2,
    state_lst in varchar2,
    state_nm in varchar2
  )
  is
    currentTimestamp timestamp := current_timestamp;
  begin
       insert into user_plan_preference (userid, prod_cd, plan_cd, state_lst, state_nm, lst_update_ts)
       values (upper(userid), upper(prod_cd), upper(plan_cd), upper(state_lst), upper(state_nm), currentTimestamp);
       commit;
       exception
       when dup_val_on_index then
         begin
          update user_plan_preference up set
            up.userid = upper(userid),
            up.prod_cd = upper(prod_cd),
            up.plan_cd = upper(plan_cd),
            up.state_lst = upper(state_lst),
            up.state_nm = upper(state_nm),
            up.lst_update_ts = currentTimestamp
          where up.userid = upper(userid)
                and up.prod_cd = upper(prod_cd)
                and up.plan_cd = upper(plan_cd);
          commit;
          exception 
          when others then
            rollback;
        end;
      when others then
        rollback;
    end;
  end;

INPUT DATA

I am unable to insert a record calling the stored procedure with values: DCWEB4578, , 2P, CA, CALIFORNIA but when I change to the string "NULL", the insert succeeds. When I try to call the stored procedure to update the inserted record with values: DCWEB4578, "NULL", 2P, CO, COLORODO the update does not happen since I still see the original record in the table.

Justin Cave · Accepted Answer · 2012-06-03 06:24:26Z

1

Your immediate problem is that if you include PROD_CD and PLAN_CD in the primary key, the primary key constraint is going to require that both columns are NOT NULL. You can either allow NULL values in those columns or you can include them in the primary key but not both.

This would have been clearer to you had your exception handlers not been written to swallow exceptions. It is almost always a bug waiting to happen when you code something like this

when others then
  rollback;
end;

If you are going to have a when others exception handler, you almost always want to at least re-raise the exception.

when others then
  rollback;
  raise;
end;

Otherwise, the caller has no idea that an error occurred and has no idea what the error was. Had you re-raised the exception, you would have seen something like

ORA-01400: cannot insert NULL into ("DCWEB"."USER_PLAN_PREFERENCE"."PROD_CD")

which would at least have pointed you in the right direction and would have given you something to post here.

Additionally, declaring parameters that have the same name as columns in your tables is another error waiting to happen. You really want to adopt some sort of convention to differentiate parameter names from column names. Personally, I prefix parameter names with p_, i.e. p_userid in varchar2,. Otherwise, your code will almost certainly not do what you expect.

Since column names have precedence over local variables when you are executing a SQL statement, your UPDATE statement resolves upper(userid), upper(prod_cd), etc to the columns in the USER_PLAN_PREFERENCE table not to the parameters that have been passed in. Assuming your data is always in upper case, this UPDATE statement will update every row of USER_PLAN_PREFERENCE, not just the one row you expect to update, and it will set every row's LST_UPDATE_TS to currentTimestamp.

 update user_plan_preference up 
    set up.userid = upper(userid),
        up.prod_cd = upper(prod_cd),
        up.plan_cd = upper(plan_cd),
        up.state_lst = upper(state_lst),
        up.state_nm = upper(state_nm),
        up.lst_update_ts = currentTimestamp
  where up.userid = upper(userid)
    and up.prod_cd = upper(prod_cd)
    and up.plan_cd = upper(plan_cd);

If your naming convention differentiates column names and parameter names, it would be much harder to inadvertently use a column name when you intended to use a parameter or local variable name.

answered Jun 3, 2012 at 6:24

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

3 Comments

user1433167 Over a year ago

Justin. Thanks for the info - very helpful. The table now allows null in PROD_CD and PLAN_CD, and I removed those fields from the primary key. I also followed your suggestion of prepending a p_ to my parameter names. The only problem that remains is that the insert succeeds with values: DCWEB4578, , 1P, CO, COLORODO When I call the setUserPlanPref a second time with params: DCWEB4578, , 1P, CA, CALIFORNIA the table does not update. Any further suggestions? Thanks again.

user1433167 Over a year ago

In the table definition I put:PROD_CD VARCHAR2(9) , PLAN_CD VARCHAR2(9) , and I also tried: PROD_CD VARCHAR2(9) NULL, PLAN_CD VARCHAR2(9) NULL, The only way I was able to make the row update is when I used the string "NULL" as the input value for the PROD_CD rather than leaving it blank/null.

Justin Cave Over a year ago

@user1433167 - Assuming that USERID is now the primary key of the table, the problem is that the predicate up.prod_cd = upper(p_prod_cd) won't be true if either up.prod_cd or p_prod_cd is NULL. NULL values are never equal to each other (they are also never unequal to each other). You would need to either use an NVL or explicitly check for NULL, i.e. (up.prod_cd = upper(p_prod_cd) or (up.prod_cd is null and p_prod_cd is null) or NVL(up.prod_cd, 'NULL') = nvl(upper(p_prod_cd),'NULL').

shareef · Accepted Answer · 2012-06-03 05:36:06Z

0

the problem is here

if you want a col to not accept null values you define it as NOT NULL and vice versa

CREATE TABLE DCWEB.USER_PLAN_PREFERENCE
(
  USERID        VARCHAR2(40) NOT NULL,
  PROD_CD       VARCHAR2(9)  NULL,--**this should be NOT NULL** 
  PLAN_CD       VARCHAR2(9)  NULL,--**this should be NOT NULL** 
  STATE_LST     VARCHAR2(2)  NOT NULL,
  STATE_NM      VARCHAR2(40) NOT NULL,
  LST_UPDATE_TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

answered Jun 3, 2012 at 5:36

shareef

9,64116 gold badges64 silver badges94 bronze badges

Collectives™ on Stack Overflow

Add/Update stored procedure in Oracle, inserting a null or empty field

Table definition:

Stored Procedure Definition:

INPUT DATA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table definition:

Stored Procedure Definition:

INPUT DATA

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related