CODEWITHSUNDEEP
sqloracle-database
user1433167
user1433167

Reputation: 29

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

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.

Upvotes: 2

Views: 8502

Answers (2)

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 1

shareef
shareef

Reputation: 9581

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
);

Upvotes: 0

Related Questions